Your question is Window Functions for Purchase Rankings. Start with the requirements and the two tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
Amazon’s analytics team wants to monitor how frequently customers purchase over time. Write a PostgreSQL query that summarizes completed purchases by user and calendar month, then uses window functions to calculate running averages and rank users by purchase frequency.
Return one row per user-month with user_name, purchase_month, monthly_purchase_count, running_avg_purchase_frequency, total_purchases, and frequency_rank.
| Column | Type | Description |
|---|---|---|
| user_idPK | INTEGER | Unique Amazon customer identifier |
| user_name | VARCHAR(100) | Customer display name |
| Column | Type | Description |
|---|---|---|
| transaction_idPK | BIGINT | Unique transaction identifier |
| user_id | INTEGER | Customer who made the transaction |
| purchased_at | TIMESTAMP | Transaction timestamp |
| status | VARCHAR(20) | Transaction state |