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

90. CTR поисковых запросов с повторными кликами

Лёгкая

В shows — показы поисковой выдачи, в clicks — клики по ним. Пользователь может кликнуть по одному показу несколько раз, а логгер иногда пишет клик дважды.

CTR запроса — доля показов, в которых был хотя бы один клик. Для каждого query верните shows (число показов) и ctr_pct в процентах с двумя знаками.

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

В работе: Разбор качества выдачи: по каким запросам люди не находят, на что кликнуть.

Таблицы

shows — один показ выдачи

КолонкаТипЧто значит
show_idintключ
querytextтекст запроса
shown_attimestampвремя показа

clicks — клики, по одному показу их может быть несколько

КолонкаТипЧто значит
click_idintключ
show_idintпоказ
clicked_attimestampвремя клика

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

shows · 6 строк

show_idqueryshown_at
1погода в казани2026-04-01T08:00:00
2погода в казани2026-04-01T08:05:00
3погода в казани2026-04-01T08:07:00
4билеты ржд2026-04-01T09:00:00
5билеты ржд2026-04-01T09:10:00
6курс юаня2026-04-01T10:00:00

clicks · 5 строк

click_idshow_idclicked_at
10012026-04-01T08:00:05
10112026-04-01T08:00:05
10242026-04-01T09:00:20
10342026-04-01T09:02:00
10452026-04-01T09:10:30

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

queryshowsctr_pct
билеты ржд2100
погода в казани333,33
курс юаня10
Как проверяем Ваш запрос выполняется в PostgreSQL 16 прямо в браузере на 4 наборах данных: пример выше и скрытые — с NULL, повторами и пустыми таблицами. Нужен один SELECT, можно с WITH. Сравниваем значения по порядку колонок, названия колонок могут быть свои. Порядок строк важен — как задано в условии. 
query, shows, ctr_pct
LEFT JOINGROUP BYDISTINCTдедупликация

Источник: на данных поиска: показы выдачи и клики. Условие пересказано своими словами.

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