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

240. Как часто в городе повторяются одни и те же работы

Средняя

Задача из тестового Домклик (Сбер). В cities — города с регионом и населением, в events — работы городского хозяйства: уборка снега, вывоз мусора, ремонт дорог, с датой и затратами.

Для каждого города и типа события посчитайте, сколько было событий и сколько в среднем дней проходит между соседними событиями этого типа в этом городе. Два события в один день дают интервал 0. Пары, где событие одно, не выводите.

Названия городов повторяются: Киров есть и в Кировской, и в Калужской области — это разные города. Верните city_name, region, event_type, events, avg_gap_days (до 0,1), порядок — по этим трём первым колонкам.

В работе: Оценка регулярности городских работ для сравнения районов в сервисе недвижимости.

Таблицы

cities — справочник городов

КолонкаТипЧто значит
city_idintключ
city_nametextназвание
regiontextрегион
populationintнаселение

events — одна строка — одна работа в городе

КолонкаТипЧто значит
event_idintключ
city_idintгород
event_typetextтип работ
event_datedateдата
cost_rubnumericзатраты

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

cities · 3 строки

city_idcity_nameregionpopulation
1КировКировская область470000
2КировКалужская область29000
3ПермьПермский край1030000

events · 11 строк

event_idcity_idevent_typeevent_datecost_rub
11уборка снега2025-12-011800000
21уборка снега2025-12-05950000
31уборка снега2025-12-151200000
41ремонт дорог2025-06-1014000000
52уборка снега2025-12-03120000
62уборка снега2025-12-0480000
73уборка снега2025-12-023100000
83уборка снега2025-12-022900000
93уборка снега2025-12-083300000
103вывоз мусора2025-11-015400000
113вывоз мусора2025-11-115100000

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

city_nameregionevent_typeeventsavg_gap_days
КировКалужская областьуборка снега21
КировКировская областьуборка снега37
ПермьПермский крайвывоз мусора210
ПермьПермский крайуборка снега33
Как проверяем Ваш запрос выполняется в PostgreSQL 16 прямо в браузере на 5 наборах данных: пример выше и скрытые — с NULL, повторами и пустыми таблицами. Нужен один SELECT, можно с WITH. Сравниваем значения по порядку колонок, названия колонок могут быть свои. Порядок строк важен — как задано в условии. 
city_name, region, event_type, events, avg_gap_days
оконные функциидатыGROUP BYHAVING

Источник: тестовое BI-аналитика (Middle) в Домклик (Сбер), сборник No Data No Growth. Условие пересказано своими словами.