Your question is SQL Cohort Metrics Query. Start with the requirements and the three 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.
PlayStation Network analysts want to compare engagement and purchase behavior across monthly user signup cohorts. Write a PostgreSQL query that reports activity and revenue metrics for each cohort month and subsequent active month.
cohort_month and calculate the number of months since signup as month_number.platform = 'PS5' and purchases with status = 'completed' count.users: (1, 'Aiko Tanaka', '2025-01-05'), (3, 'Maya Singh', '2025-02-01'), (5, 'Leo Martin', '2025-03-03'), (10, 'Nora Evans', '2025-04-15')
sessions: (101, 1, '2025-01-06', 'PS5'), (102, 1, '2025-01-20', 'PS5'), (106, 3, '2025-03-05', 'PS5'), (109, 6, '2025-03-30', NULL)
purchases: (201, 1, '2025-01-06', 59.99, 'completed'), (204, 3, '2025-02-02', 69.99, 'completed'), (205, 3, '2025-03-05', 69.99, 'refunded'), (210, 10, '2025-04-16', 15.00, NULL)
| Column | Type | Description |
|---|---|---|
| user_idPK | INT | PlayStation Network user identifier |
| display_name | VARCHAR(100) | PlayStation Network display name |
| signup_date | DATE | Account signup date |
| Column | Type | Description |
|---|---|---|
| session_idPK | INT | Gameplay or PlayStation Network session identifier |
| user_id | INT | User who initiated the session |
| session_date | DATE | Date of the session |
| platform | VARCHAR(20) | PlayStation platform used |
| Column | Type | Description |
|---|---|---|
| purchase_idPK | INT | Purchase identifier |
| user_id | INT | Purchasing user |
| purchase_date | DATE | Date of purchase |
| amount | NUMERIC(10,2) | Purchase amount |
| status | VARCHAR(20) | Purchase processing status |