Product Launch Day
ASKED IN INTERVIEWApple
Problem Statement
Apple retail analysts are evaluating the initial market reception of the latest iPhone lineup. You are provided with sales records for various products. Your task is to calculate the total units sold on the official launch day for the iPhone 16 and compare it to the launch day sales of the iPhone 15.
Launch Dates:
- iPhone 15 Launch Day: 2023-09-22
- iPhone 16 Launch Day: 2024-09-20
Rules:
- Only consider sales where the product_name matches exactly iPhone 15 or iPhone 16.
- The comparison must be based specifically on the launch dates provided above.
- Output columns: iphone_15_launch_sales, iphone_16_launch_sales, sales_growth.
- sales_growth is calculated as: iphone_16_launch_sales - iphone_15_launch_sales.
Table Schema:
- Products: product_id, product_name, category, launch_price.
- Sales: sale_id, product_id, sale_date, quantity_sold, store_id.
Tests your understanding of
Common Table Expressions, Join, DateTime and Window Functions
Input Tables
Products
| product_id(INTEGER) | product_name(VARCHAR) |
|---|---|
| 101 | iPhone 15 |
| 102 | iPhone 16 |
| 103 | MacBook Air |
Sales
| sale_id(INTEGER) | product_id(INTEGER) | sale_date(DATE) | quantity_sold(INTEGER) |
|---|---|---|---|
| 1 | 101 | 2023-09-22 | 500 |
| 2 | 101 | 2023-09-22 | 350 |
| 3 | 102 | 2024-09-20 | 600 |
| 4 | 102 | 2024-09-20 | 420 |
| 5 | 101 | 2023-09-23 | 100 |
| 6 | 102 | 2024-09-21 | 150 |
Expected Output
| iphone_15_launch_sales(INTEGER) | iphone_16_launch_sales(INTEGER) | sales_growth(INTEGER) |
|---|---|---|
| 850 | 1020 | 170 |
Tags
MediumASKED IN INTERVIEWCommon Table ExpressionsJoinDateTimeWindow Functions
15-20 min
55%