Войти Регистрация
Data base

61. Возвращаемость новых колонок по неделям активации

Сложная

В devices — колонки и приставки с датой активации, в device_activity — дни, когда устройство хотя бы раз обращалось к ассистенту.

Когорта — неделя активации (неделя начинается с понедельника). Устройство вернулось на D1, если было активно ровно на следующий день после активации, и на D7 — если ровно через 7 дней.

Для каждой недели от первой до последней когорты, включая недели без активаций, верните cohort_week (понедельник), devices, d1_devices, d7_devices, d1_pct, d7_pct (проценты с 1 знаком, в пустой неделе NULL). Порядок — по cohort_week.

В работе: Оценка онбординга: возвращаются ли к колонке после первого вечера и после первой недели.

Таблицы

devices — устройства

КолонкаТипЧто значит
device_idintключ
modeltextSberBox / SberPortal / SberBoom
activated_ondateдата активации

device_activity — один день — одна строка на устройство

КолонкаТипЧто значит
device_idintустройство
active_ondateдень с обращениями

Пример: данные

devices · 4 строки

device_idmodelactivated_on
1SberBoom2026-09-07
2SberBox2026-09-09
3SberPortal2026-09-13
4SberBoom2026-09-22

device_activity · 11 строк

device_idactive_on
12026-09-07
12026-09-08
12026-09-14
22026-09-09
22026-09-10
32026-09-13
32026-09-20
42026-09-22
42026-09-24
42026-09-28
42026-09-30

Ожидаемый ответ

cohort_weekdevicesd1_devicesd7_devicesd1_pctd7_pct
2026-09-0732266,766,7
2026-09-14000NULLNULL
2026-09-2110000
Как проверяем Ваш запрос выполняется в PostgreSQL 16 прямо в браузере на 4 наборах данных: пример выше и скрытые — с NULL, повторами и пустыми таблицами. Нужен один SELECT, можно с WITH. Сравниваем значения по порядку колонок, названия колонок могут быть свои. Порядок строк важен — как задано в условии. 
cohort_week, devices, d1_devices, d7_devices, d1_pct, d7_pct
когортыретеншнусловная агрегациядатыLEFT JOIN

Источник: на данных SberDevices: активация умных устройств и обращения к ассистенту. Условие пересказано своими словами.

Давали на собеседовании? Ответов пока мало — ваш поможет.