Black Friday Shopping Spree
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.
Schema
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
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
For Electronics, there are three unique customers: 101, 102, and 103. Their spend amounts are 200 for customer 101 (120 + 80), 150 for customer 102, and 300 for customer 103, giving an average spend of 216.67. Only customer 101 made more than one purchase, so the multi-purchase rate is 33.33%.
For Clothing, there are two unique customers: 102 and 103. Customer 102 spent a total of 50 (40 + 10), while customer 103 spent 50, resulting in an average of 50.00. Customer 102 is the only one with multiple purchases, leading to a multi-purchase rate of 50.00%.
.
.
.
.
Comments