Task
Facebook wants a weekly retention curve showing how many users active in week N were also active in week N+1. Write a PostgreSQL query to calculate week-over-week retention.
Requirements
- Treat a user as active in a week if they generated at least one event in that calendar week.
- Deduplicate multiple events from the same user in the same week.
- For each
week_start, compute:
- the number of active users in that week
- the number of those users also active in the following week
- the retention rate as
retained_users / active_users, rounded to 4 decimal places
- Return one row per week that has at least one active user.
- Order results by
week_start ascending.
Table Definitions
facebook_events
| column | type | description |
|---|
| event_id | INT | Unique event row identifier |
| user_id | INT | Facebook user identifier |
| event_name | VARCHAR(50) | Event type recorded on Facebook |
| event_time | TIMESTAMP | Time the event occurred |
| device_type | VARCHAR(20) | Device used for the event |
Sample Data
facebook_events
| event_id | user_id | event_name | event_time | device_type |
|---|
| 1 | 101 | login | 2024-01-03 09:00:00 | iOS |
| 2 | 101 | like | 2024-01-04 10:15:00 | iOS |
| 3 | 102 | comment | 2024-01-05 12:00:00 | Android |
| 4 | 101 | share | 2024-01-10 08:30:00 | Web |
| 5 | 103 | login | 2024-01-11 14:00:00 | Android |
| 6 | 104 | login | 2024-01-16 09:45:00 | Web |
Expected Output
| week_start | active_users | retained_users | retention_rate |
|---|
| 2024-01-01 | 3 | 1 | 0.3333 |
| 2024-01-08 | 2 | 1 | 0.5000 |
| 2024-01-15 | 2 | 1 | 0.5000 |
| 2024-01-22 | 2 | 0 | 0.0000 |
