Product wants to measure how many newly acquired users return the day after signup (Day-1 retention). Using the user_events table, return total_signups, retained_day1 (users who had an event on signup_date + 1), and retention_pct (retained_day1 / total_signups × 100, rounded to 2 decimal places).
user_events
| column | type |
|---|---|
| id | INTEGER |
| user_id | INTEGER |
| event_type | TEXT |
| event_date | DATE |
user_events
| id | user_id | event_type | event_date |
|---|---|---|---|
| 1 | 1 | signup | 2024-01-01 |
| 2 | 2 | signup | 2024-01-01 |
| 3 | 3 | signup | 2024-01-01 |
| 4 | 1 | login | 2024-01-02 |
| 5 | 3 | login | 2024-01-05 |
| total_signups | retained_day1 | retention_pct |
|---|---|---|
| 3 | 1 | 33.33 |
All three users signed up on 2024-01-01. User 1 returned the very next day (2024-01-02) — that is Day-1 retention. User 3 returned four days later, which does not count. User 2 never returned. One of three users was retained, so retention_pct = 33.33.