Customers with repeat orders
mediumCustomers with repeat orders
Target SQL Interview Question
Target's loyalty team wants to reward repeat buyers, which it defines as customers who have placed more than four online orders.
Find every repeat buyer, showing their customer_id and their number of orders as order_count. Count every order, whatever its status.
Asked of
- Data Analyst
- Business Analyst
- Product Analyst
- Analytics 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 |
| 1003 | 1 | 2024-01-11 | completed | 89.99 |
| 1004 | 3 | 2024-01-15 | canceled | 45 |
| 1008 | 1 | 2024-02-02 | refunded | 75 |
| 1010 | 3 | 2024-02-09 | completed | 132.6 |
| 1015 | 1 | 2024-03-01 | completed | 210 |
| 1018 | 3 | 2024-03-12 | completed | 178.25 |
| 1024 | 1 | 2024-04-05 | completed | 95.25 |
| 1026 | 3 | 2024-04-13 | refunded | 88 |
| 1032 | 1 | 2024-05-07 | completed | 143 |
Example Output
| customer_id | order_count |
|---|---|
| 1 | 6 |
Explanation
Customer 1 placed six orders in the example, including one that was refunded, so they qualify with 6. Customer 3 placed four orders, which is not more than four, so they are left out.
The example above is a small slice of the data. Your query runs against the full tables.
Company
Target
Difficulty
medium
Topic
aggregations
Your query
Starting the SQL engine...