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

233. Доля абонентов без начислений по регионам и тарифам

Средняя

В abonents — срез базы на каждый отчётный месяц tbm (первое число месяца): номер app_n, дата активации act_day, регион и тарифная группа. В charges — начисления за месяц, по номеру может быть несколько строк (разные услуги), revenue бывает NULL. Суммы не отрицательные.

Для каждого месяца, региона и тарифной группы посчитайте: subscribers — абонентов в срезе; no_charge_pct — процент абонентов, у которых за этот месяц начислений нет совсем, все они NULL или их сумма 0 (округлить до 0,1); activated — сколько номеров активировано в том же месяце, что и отчётный.

Названия региона и группы — из справочников regions и tp_groups. Порядок — tbm, region_name, tp_group_name.

В работе: Ежемесячный отчёт по базе: где номера висят без трафика и платежей и откуда идут новые подключения.

Таблицы

abonents — одна строка — номер в срезе отчётного месяца

КолонкаТипЧто значит
tbmdateотчётный месяц, первое число
app_nbigintномер
act_daydateдата активации
region_idintрегион
tp_group_idintтарифная группа

charges — начисления за месяц, несколько строк на номер

КолонкаТипЧто значит
tbmdateотчётный месяц
app_nbigintномер
servicetextуслуга
revenuenumericсумма, может быть NULL

regions — справочник регионов

КолонкаТипЧто значит
region_idintключ
region_nametextназвание

tp_groups — справочник тарифных групп

КолонкаТипЧто значит
tp_group_idintключ
tp_group_nametextназвание

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

abonents · 7 строк

tbmapp_nact_dayregion_idtp_group_id
2018-01-0191600010012018-01-1211
2018-01-0191600010022017-01-2011
2018-01-0191600010032016-05-0211
2018-01-0191300020042018-01-0322
2018-01-0191300020052017-11-3022
2018-02-0191600010012018-01-1211
2018-02-0191600010022017-01-2011

charges · 7 строк

tbmapp_nservicerevenue
2018-01-019160001001абонплата450
2018-01-019160001001роуминг120
2018-01-019160001003абонплата0
2018-01-019130002004абонплатаNULL
2018-01-019130002005абонплата990
2018-02-019160001001абонплата570
2018-02-019160001002абонплата300

regions · 2 строки

region_idregion_name
1Москва
2Новосибирская область

tp_groups · 2 строки

tp_group_idtp_group_name
1Безлимит
2Корпоративный

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

tbmregion_nametp_group_namesubscribersno_charge_pctactivated
2018-01-01МоскваБезлимит366,71
2018-01-01Новосибирская областьКорпоративный2501
2018-02-01МоскваБезлимит200
Как проверяем Ваш запрос выполняется в PostgreSQL 16 прямо в браузере на 4 наборах данных: пример выше и скрытые — с NULL, повторами и пустыми таблицами. Нужен один SELECT, можно с WITH. Сравниваем значения по порядку колонок, названия колонок могут быть свои. Порядок строк важен — как задано в условии. 
tbm, region_name, tp_group_name, subscribers, no_charge_pct, activated
LEFT JOINусловная агрегацияNULLдатыGROUP BY

Источник: техническое задание МТС из портфолио аналитика на GitHub, 2018. Условие пересказано своими словами.