Task
You are given the task of analyzing repeat purchase behavior for HelloFresh meal kits by joining users, orders, and products. Write a PostgreSQL query that returns one row per product showing how many distinct users bought that product at least once, how many of those users placed more than one delivered order containing the same product, and the repeat purchase rate as a percentage. Only include delivered orders, and keep products even if they have never appeared in a delivered order.
Schema
users
| column | type | description |
|---|
| user_id | INT | Unique user identifier |
| full_name | VARCHAR(100) | User name |
| signup_channel | VARCHAR(50) | Acquisition channel |
| country_code | VARCHAR(2) | User country |
orders
| column | type | description |
|---|
| order_id | INT | Unique order identifier |
| user_id | INT | User who placed the order |
| order_date | DATE | Order date |
| order_status | VARCHAR(20) | Order lifecycle status |
| product_id | INT | Ordered product |
products
| column | type | description |
|---|
| product_id | INT | Unique product identifier |
| product_name | VARCHAR(100) | HelloFresh product name |
| category | VARCHAR(50) | Product category |
Sample data
orders
| order_id | user_id | order_date | order_status | product_id |
|---|
| 102 | 1 | 2024-01-20 | delivered | 101 |
| 104 | 2 | 2024-02-05 | delivered | 102 |
| 108 | 4 | 2024-03-01 | cancelled | 104 |
| 111 | 7 | 2024-03-15 | delivered | NULL |
Expected output
| product_id | product_name | unique_buyers | repeat_buyers | repeat_purchase_rate |
|---|
| 101 | Chicken Teriyaki | 2 | 1 | 50.00 |
| 102 | Veggie Burrito Bowl | 2 | 1 | 50.00 |
| 103 | Creamy Mushroom Pasta | 2 | 1 | 50.00 |
| 104 | Salmon Rice Bowl | 1 | 0 | 0.00 |
| 105 | Beef Tacos | 0 | 0 | 0.00 |
