Your question is SQL for Cohort Ranking. 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.
Write a SQL query to rank users within each cohort by spend and return the top 3 per cohort.
Use the users and purchases tables. Treat users with no purchases as having zero spend, exclude users without a cohort, and use user_id as the tie-breaker.
cohort, user_id, total_spend, and user_rank.cohort, total_spend descending, and user_id ascending.| Column | Type | Description |
|---|---|---|
| user_idPK | INT | Unique user identifier |
| cohort | VARCHAR(20) | Cohort identifier assigned to the user |
| Column | Type | Description |
|---|---|---|
| purchase_idPK | INT | Unique purchase identifier |
| user_id | INT | User associated with the purchase |
| amount | NUMERIC(10,2) | Purchase amount |