Orders by status
easyOrders by status
Target SQL Interview Question
Target's operations team tracks how online orders end up: completed, canceled or refunded.
For each status, find the number of orders as order_count and the average order amount as avg_amount. Do not round the average.
Asked of
- Data Analyst
- Business Analyst
- BI Analyst
- Product Analyst
- ML Engineer
ordersTable35 rows
| Column Name | Type |
|---|---|
| order_id | BIGINT |
| customer_id | BIGINT |
| order_date | DATE |
| status | VARCHAR |
| amount | DOUBLE |
ordersExample Input
| order_id | customer_id | order_date | status | amount |
|---|---|---|---|---|
| 1001 | 1 | 2024-01-05 | completed | 120.5 |
| 1002 | 2 | 2024-01-07 | completed | 2400 |
| 1003 | 1 | 2024-01-11 | completed | 89.99 |
| 1004 | 3 | 2024-01-15 | canceled | 45 |
| 1005 | 4 | 2024-01-18 | completed | 610.75 |
| 1006 | 2 | 2024-01-22 | completed | 1850.25 |
| 1007 | 5 | 2024-01-29 | completed | 3200 |
| 1008 | 1 | 2024-02-02 | refunded | 75 |
| 1009 | 6 | 2024-02-06 | completed | 55.4 |
| 1010 | 3 | 2024-02-09 | completed | 132.6 |
Example Output
| status | order_count | avg_amount |
|---|---|---|
| canceled | 1 | 45 |
| completed | 8 | 1057.4362 |
| refunded | 1 | 75 |
Explanation
Eight of the ten orders in the example were completed, and they average about 1,057.44. There is one canceled order worth 45 and one refunded order worth 75, so each of those averages equals that single amount.
The example above is a small slice of the data. Your query runs against the full tables.
Company
Target
Difficulty
easy
Topic
aggregations
Your query
Starting the SQL engine...