К содержанию
Quantoros

Используя 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

Похожие вопросы