В activity — ежедневные срезы по клиентам Альфа-Банка за сентябрь 2021 — февраль 2022: digital_30_cnt — входы в приложение за 30 дней до value_day, tran_active_30_cnt — дни с картой за те же 30 дней. Счётчики бывают NULL, это 0. Строка в срезе есть и у клиента, который ничего не делал.
Клиент диджитал-активен в месяце, если хотя бы в один день месяца digital_30_cnt ≥ 1, и транзакционно активен, если хотя бы в один день tran_active_30_cnt ≥ 1. Когорта — месяц, когда клиент впервые стал диджитал-активным.
Для каждой когорты и номера месяца от неё (0 — сам месяц когорты) верните cohort_month, month_no, digital_clients — сколько клиентов когорты диджитал-активны в этом месяце, retention_pct — их процент от размера когорты (до 0,1), tran_clients — сколько из них в том же месяце ещё и транзакционно активны. Месяцы, где из когорты не активен никто, не выводите. Порядок — cohort_month, month_no.
В работе: Оценка, переходит ли активность в приложении в реальные платежи по карте у новых пользователей.
Таблицы
activity — одна строка — срез клиента на дату
| Колонка | Тип | Что значит |
|---|---|---|
client_id | int | клиент |
value_day | date | отчётная дата |
digital_30_cnt | int | входы за 30 дней, может быть NULL |
tran_active_30_cnt | int | дни с транзакциями за 30 дней, может быть NULL |
Пример: данные
activity · 11 строк
| client_id | value_day | digital_30_cnt | tran_active_30_cnt |
|---|---|---|---|
| 1 | 2021-11-05 | 0 | 0 |
| 1 | 2021-11-20 | 2 | 1 |
| 1 | 2021-12-10 | 0 | 3 |
| 1 | 2022-01-15 | 1 | 0 |
| 2 | 2021-11-01 | 1 | 0 |
| 2 | 2021-12-31 | 4 | 2 |
| 3 | 2021-10-30 | 0 | 0 |
| 3 | 2021-12-01 | 1 | 1 |
| 3 | 2022-01-03 | 2 | 0 |
| 4 | 2021-09-15 | 0 | 2 |
| 4 | 2021-10-15 | NULL | 1 |
Ожидаемый ответ
| cohort_month | month_no | digital_clients | retention_pct | tran_clients |
|---|---|---|---|---|
| 2021-11-01 | 0 | 2 | 100 | 1 |
| 2021-11-01 | 1 | 1 | 50 | 1 |
| 2021-11-01 | 2 | 1 | 50 | 0 |
| 2021-12-01 | 0 | 1 | 100 | 1 |
| 2021-12-01 | 1 | 1 | 100 | 0 |
SELECT, можно с WITH. Сравниваем значения по порядку колонок, названия колонок могут быть свои. Порядок строк важен — как задано в условии.
cohort_month, month_no, digital_clients, retention_pct, tran_clients
Источник: тестовое аналитика данных в Альфа-Банке, сборник No Data No Growth, 2022. Условие пересказано своими словами.
Посмотреть данные, проверить кусок запроса. На зачёт не влияет.
Здесь появится результат отправки.