Orders per customer
easyOrders per customer
Target SQL Interview Question
Target's customer success team wants to see how active each online customer is.
For each customer_id, count the orders they have placed as order_count. Count every order, whatever its status.
Asked of
- Data Analyst
- Business Analyst
- BI Analyst
- Product Analyst
- Data Scientist
- 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
| customer_id | order_count |
|---|---|
| 1 | 3 |
| 2 | 2 |
| 3 | 2 |
| 4 | 1 |
| 5 | 1 |
| 6 | 1 |
Explanation
Customer 1 placed orders 1001, 1003 and 1008, so their count is 3. Order 1004 was canceled, but it still counts toward customer 3's total of 2.
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...