iPhone Model Sales Share by Country
Apple● MediumFULLTIMEPHONE SCREENProblem statement
Apple tracks iPhone orders by country. For every country and every model that names a previous-generation model, compare the two models' shares of that country's total iPhone units sold.
Return one row when either the current model or its previous model sold at least one unit in the country. A missing side of the comparison has zero units.
Result
Return country_name, product_name, current_share_percent, previous_share_percent, and share_difference_percentage_points. A model share is its units divided by all units sold in the same country, multiplied by 100 and rounded to two decimal places. The difference is current share minus previous share.
Order rows by country_name ascending, then product_name ascending.
Table schema
Use the same input data with any supported language. Open the Schema tab in the editor to see the generated SQL setup or Pandas DataFrames.
products
| Column | Type | Nullable | Description |
|---|---|---|---|
| product_idPK | Integer | No | — |
| product_name | Text | No | — |
| previous_model_id | Integer | Yes | — |
Foreign key: previous_model_id → products(product_id)
countries
| Column | Type | Nullable | Description |
|---|---|---|---|
| country_idPK | Integer | No | — |
| country_name | Text | No | — |
orders
| Column | Type | Nullable | Description |
|---|---|---|---|
| order_idPK | Integer | No | — |
| customer_id | Integer | No | — |
| country_id | Integer | No | — |
| order_date | Date | No | — |
Foreign key: country_id → countries(country_id)
order_items
| Column | Type | Nullable | Description |
|---|---|---|---|
| order_idPK | Integer | No | — |
| product_idPK | Integer | No | — |
| quantity | Integer | No | — |
| unit_price | Decimal | No | — |
Foreign key: order_id → orders(order_id)
Foreign key: product_id → products(product_id)
Expected result
Your query or function must return these columns.
| Column | Type | Nullable | Description |
|---|---|---|---|
| country_name | Text | No | — |
| product_name | Text | No | — |
| current_share_percent | Decimal | No | — |
| previous_share_percent | Decimal | No | — |
| share_difference_percentage_points | Decimal | No | — |
Row order: must match exactly. Numeric tolerance: 0.
Constraints
- Every
quantityis a positive integer. - Every row in
productsrepresents one iPhone model, andproduct_namevalues are unique. - Every
previous_model_idis eitherNULLor identifies an older product. - Every
country_nameis unique. - Each input table contains at most
2,000rows.