Black Friday Shopping Spree
Start Timer
0:00:00
You’re a data analyst working for Burlington studying customer behavior during the annual Black Friday shopping spree. Each customer may visit the store multiple times throughout the day and make several purchases across different product categories. The marketing team wants to understand which customer segments were most engaged.
Write a SQL query to compute the following metrics for each product category:
- Total number of unique customers who purchased from that category
- Average spend per customer (rounded to 2 decimal places)
- Percentage of customers who made more than one purchase in that category (rounded to 2 decimal places)
Sort the final results in descending order by average spend per customer.
Notes:
- A “purchase” means a single row in the purchases table.
- A customer who buys multiple items in one visit still counts as multiple purchases.
- If a customer purchased from a category only once, they should not count toward the “multiple purchase” percentage.
- Every category present in the purchases table should appear in the output.
Example 1
Input
purchases table
| Column | Type |
|---|---|
| purchase_id | INTEGER |
| customer_id | INTEGER |
| category | VARCHAR |
| amount | DECIMAL |
| purchase_time | DATETIME |
Output
| Column | Type |
|---|---|
| category | VARCHAR |
| unique_customers | INTEGER |
| avg_spend_per_customer | DECIMAL |
| pct_multi_purchase_customers | DECIMAL |
Example 2
Input
purchases table
| purchase_id | customer_id | category | amount | purchase_time |
|---|---|---|---|---|
| 1 | 101 | Electronics | 120 | 2025-11-28 09:10:00 |
| 2 | 101 | Electronics | 80 | 2025-11-28 10:00:00 |
| 3 | 102 | Clothing | 40 | 2025-11-28 11:15:00 |
| 4 | 103 | Electronics | 300 | 2025-11-28 13:30:00 |
| 5 | 102 | Electronics | 150 | 2025-11-28 14:10:00 |
| 6 | 102 | Clothing | 10 | 2025-11-28 16:20:00 |
| 7 | 103 | Clothing | 50 | 2025-11-28 17:20:00 |
Output
| category | unique_customers | avg_spend_per_customer | pct_multi_purchase_customers |
|---|---|---|---|
| Electronics | 3 | 216.67 | 33.33 |
| Clothing | 2 | 50.00 | 50.00 |
Explanation
Electronics
- Customers: 101, 102, 103 → 3 unique
- Spend per customer:
- 101 → 120 + 80 = 200
- 102 → 150
- 103 → 300
- Average = (200 + 150 + 300) / 3 = 216.67
- Multi-purchase customers:
- Only customer 101 has more than one purchase → 1⁄3 = 33.33%
Clothing
- Customers: 102, 103 → 2 unique
- Spend per customer:
- 102 → 40 + 10 = 50
- 103 → 50
- Average = (50 + 50) / 2 = 50.00
- Multi-purchase customers:
- Customer 102 made 2 purchases
- Customer 103 made 1
- Multi-purchase rate = 1⁄2 = 50.00%
.
.
.
.
.
.
.
.
.
Comments