Task
ShopWave wants to track how its active purchaser base changes week over week. Write a PostgreSQL query to calculate the weekly count of active purchasers and the week-over-week growth rate.
An active purchaser is a user who has at least one completed transaction in a given calendar week. Use the transaction date to assign each purchase to a week.
Requirements
- Count distinct purchasers per week using only transactions with
status = 'completed'.
- Return one row per week, even if there are zero active purchasers that week.
- Calculate the prior week's active purchaser count.
- Compute week-over-week growth rate as:
((current_week_active_purchasers - previous_week_active_purchasers) / previous_week_active_purchasers) * 100
Return NULL when the previous week is 0 or missing.
- Order the output by week start date ascending.
Table Definitions
transactions
| column | type | description |
|---|
| transaction_id | INT | Unique transaction ID |
| user_id | INT | Purchasing user |
| transaction_date | DATE | Date of the transaction |
| amount | DECIMAL(10,2) | Transaction amount |
| status | VARCHAR(20) | Transaction status |
| store_id | INT | Store where the purchase happened |
users
| column | type | description |
|---|
| user_id | INT | Unique user ID |
| user_name | VARCHAR(100) | User name |
| signup_date | DATE | User signup date |
| country | VARCHAR(50) | User country |
Sample Data
transactions
| transaction_id | user_id | transaction_date | amount | status | store_id |
|---|
| 101 | 1 | 2024-01-02 | 45.00 | completed | 10 |
| 102 | 1 | 2024-01-03 | 20.00 | completed | 10 |
| 103 | 2 | 2024-01-04 | 60.00 | completed | 11 |
| 104 | 3 | 2024-01-09 | 30.00 | completed | 10 |
| 105 | 2 | 2024-01-10 | 25.00 | failed | 11 |
| 106 | 4 | 2024-01-16 | 80.00 | completed | 12 |
| 107 | 4 | 2024-01-17 | 10.00 | completed | 12 |
| 108 | 5 | 2024-01-23 | 50.00 | pending | 10 |
| 109 | 6 | 2024-01-24 | 70.00 | completed | 11 |
| 110 | NULL | 2024-01-25 | 15.00 | completed | 11 |
users
| user_id | user_name | signup_date | country |
|---|
| 1 | Ava Chen | 2023-12-15 | US |
| 2 | Liam Patel | 2023-12-20 | CA |
| 3 | Noah Kim | 2024-01-05 | US |
| 4 | Emma Lopez | 2024-01-08 | UK |
| 5 | Mia Johnson | 2024-01-12 | US |
| 6 | Ethan Brown | 2024-01-18 | CA |
| 7 | Sophia Davis | 2024-01-22 | UK |
| 8 | Lucas Martin | 2024-01-28 | NULL |
Expected Output
| week_start | active_purchasers | previous_week_active_purchasers | wow_growth_rate_pct |
|---|
| 2024-01-01 | 2 | NULL | NULL |
| 2024-01-08 | 1 | 2 | -50.00 |
| 2024-01-15 | 1 | 1 | 0.00 |
| 2024-01-22 | 1 | 1 | 0.00 |
