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 |