Top Five Countries by Product Revenue
Apple● MediumFULLTIMEPHONE SCREENProblem statement
For each Apple product, find the five countries with the highest total sales revenue.
A line item contributes quantity * unit_price to its order country. Sum all line-item revenue for the same product and country.
Result
Return product_id, product_name, country_id, country_name, and sales_amount. Rank countries independently for each product by sales_amount descending. When amounts are equal, rank the country whose English country_name is alphabetically smaller first.
Return at most five rows per product. Order the final result by product_id ascending, then sales_amount descending, then country_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 |
|---|---|---|---|
| product_id | Integer | No | — |
| product_name | Text | No | — |
| country_id | Integer | No | — |
| country_name | Text | No | — |
| sales_amount | Decimal | No | — |
Row order: must match exactly. Numeric tolerance: 0.
Constraints
- Every
quantityis a positive integer. - Every
unit_priceis a non-negative exact decimal. - Every
product_nameandcountry_nameis unique. - Only product-country pairs with at least one line item appear in the result.
- Each input table contains at most
2,000rows.