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

227. Топ-3 запроса Картинок на телефонах и компьютерах

Средняя

В image_queries — запросы к поиску по картинкам: текст query, время ts и платформа platform (touch — телефоны, desktop — компьютеры).

Пользователи пишут как попало: «Котики», «котики » и «котики рыжие» с двумя пробелами. Перед подсчётом приведите текст к нижнему регистру, обрежьте пробелы по краям и замените несколько пробелов подряд одним.

Для каждой платформы верните запросы, занявшие места с 1-го по 3-е по числу повторов: platform, query, cnt, place. При равенстве у запросов одно место, следующее место пропускается (как в спорте: 1, 1, 3). Порядок — platform, place, query.

В работе: Проверка гипотезы, что на телефонах и компьютерах ищут разное: первый срез перед разбором тематик.

Таблицы

image_queries — одна строка — один запрос

КолонкаТипЧто значит
query_idintключ
querytextтекст как ввели
tstimestampвремя запроса
platformtexttouch или desktop

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

image_queries · 13 строк

query_idquerytsplatform
1Котики2025-11-01T09:00:00touch
2котики 2025-11-01T09:05:00touch
3обои на телефон2025-11-01T10:00:00touch
4обои на телефон2025-11-01T10:30:00touch
5КОТИКИ2025-11-01T11:00:00touch
6мемы2025-11-01T12:00:00touch
7торт на день рождения2025-11-01T13:00:00touch
8схема вязания2025-11-01T14:00:00desktop
9Схема вязания2025-11-01T15:00:00desktop
10чертёж табурета2025-11-01T16:00:00desktop
11карта метро2025-11-01T17:00:00desktop
12карта метро2025-11-01T18:00:00desktop
13логотип вектор2025-11-01T19:00:00desktop

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

platformquerycntplace
desktopкарта метро21
desktopсхема вязания21
desktopлоготип вектор13
desktopчертёж табурета13
touchкотики31
touchобои на телефон22
touchмемы13
touchторт на день рождения13
Как проверяем Ваш запрос выполняется в PostgreSQL 16 прямо в браузере на 4 наборах данных: пример выше и скрытые — с NULL, повторами и пустыми таблицами. Нужен один SELECT, можно с WITH. Сравниваем значения по порядку колонок, названия колонок могут быть свои. Порядок строк важен — как задано в условии. 
platform, query, cnt, place
оконные функциитоп-N в группестрокиGROUP BY

Источник: тестовое продуктового аналитика (Middle) в Яндекс Поиске, сборник No Data No Growth. Условие пересказано своими словами.