Используя clicks и applications, для каждого продукта вычислите CTR: число уникальных пользователей, подавших заявку…
SQLУровень: ПрактикаCore interview patterns
Используя clicks и applications, для каждого продукта вычислите CTR: число уникальных пользователей, подавших заявку после клика, делённое на число уникальных кликнувших. Выведите product_name и ctr, округлённый до двух знаков. Пользователя, подавшего заявку без клика, учитывать нельзя.
Определения таблиц из исходного задания:
CREATE TABLE clicks(click_id INTEGER PRIMARY KEY, user_id INTEGER, product_id INTEGER, product_name TEXT, click_date TEXT);
CREATE TABLE applications(application_id INTEGER PRIMARY KEY, user_id INTEGER, product_id INTEGER, app_date TEXT, approved INTEGER);
Подсказки
- Определите, что означает одна строка результата и каковы ключи исходных таблиц.
- Проверьте дубликаты, NULL и порядок применения фильтрации и агрегации.
Ответ
Подход: Начните с clicks и выполните LEFT JOIN applications по пользователю и продукту. Используйте COUNT(DISTINCT) с обеих сторон: выбранное направление соединения исключает заявку без клика.
SELECT c.product_name,
ROUND(1.0 * COUNT(DISTINCT ap.user_id) / COUNT(DISTINCT c.user_id), 2) AS ctr
FROM clicks c
LEFT JOIN applications ap ON ap.user_id = c.user_id
AND ap.product_id = c.product_id
GROUP BY c.product_id, c.product_name;
В источнике используется SQLite со слоем совместимости с MySQL. Функции дат и строк зависят от диалекта; запрос сохранён в исходном виде.
Источник и лицензия: SQL Practice Lab — Ebrahim Sharifi · MIT · перевод: Quantoros