## Контекст (таблицы и пример данных) ### Таблицы - `user_events(user_id, event_date)` ### Пример
SQL Developer
Senior
## Контекст (таблицы и пример данных)
### Таблицы
- `user_events(user_id, event_date)`
### Пример данных
**user_events**
| user_id | event_date |
|-------:|------------|
| 1 | 2025-01-01 |
| 1 | 2025-01-08 |
| 2 | 2025-01-01 |
| 2 | 2025-01-03 |
---
## Задача
Считать D7 retention для когорты `2025-01-01`:
- пользователи, активные в день `2025-01-01`
- из них те, кто активен в `2025-01-08`
Вернуть: `cohort_day`, `cohort_users`, `returned_users`, `d7_retention`.
Ответы
```sql
WITH cohort AS (
SELECT DISTINCT user_id
FROM user_events
WHERE event_date = DATE '2025-01-01'
), returned AS (
SELECT DISTINCT user_id
FROM user_events
WHERE event_date = DATE '2025-01-08'
)
SELECT
DATE '2025-01-01' AS cohort_day,
(SELECT COUNT(*) FROM cohort) AS cohort_users,
(SELECT COUNT(*) FROM cohort c JOIN returned r USING (user_id)) AS returned_users,
(SELECT COUNT(*) FROM cohort c JOIN returned r USING (user_id))::numeric
/ NULLIF((SELECT COUNT(*) FROM cohort), 0) AS d7_retention;
```