Aafreen29/SQL-Interview-Prep: CTR постов в ленте

Условие

Таблица событий events(user_id, event_type, post_id, ts) содержит события типа 'impression' (пост показан пользователю) и 'click' (пользователь кликнул). Посчитайте CTR (click-through-rate) для каждого поста.

Решение

Подход

Используем условную агрегацию: COUNT(*) FILTER (WHERE event_type = 'click') и COUNT(*) FILTER (WHERE event_type = 'impression'). Делим первое на второе. CTR корректно считать по уникальным пользователям, если нас интересует «доля показанных, что кликнули хоть раз», или по событиям, если нас интересует «частота кликов на показ».

Реализация

-- CTR по событиям (стандартный)
SELECT
    post_id,
    COUNT(*) FILTER (WHERE event_type = 'impression') AS impressions,
    COUNT(*) FILTER (WHERE event_type = 'click')      AS clicks,
    ROUND(
        100.0 * COUNT(*) FILTER (WHERE event_type = 'click')
        / NULLIF(COUNT(*) FILTER (WHERE event_type = 'impression'), 0),
        2
    ) AS ctr_pct
FROM events
GROUP BY post_id
ORDER BY impressions DESC;
 
-- CTR по пользователям (sklearn-like unique-CTR)
SELECT
    post_id,
    COUNT(DISTINCT user_id) FILTER (WHERE event_type = 'impression') AS uniq_imps,
    COUNT(DISTINCT user_id) FILTER (WHERE event_type = 'click')      AS uniq_clicks,
    ROUND(
        100.0 * COUNT(DISTINCT user_id) FILTER (WHERE event_type = 'click')
        / NULLIF(COUNT(DISTINCT user_id) FILTER (WHERE event_type = 'impression'), 0),
        2
    ) AS uniq_ctr_pct
FROM events
GROUP BY post_id;

Подводные камни

1. Клики без impression: иногда события приходят не в порядке, или impression потерялся. Тогда CTR > 100%. На проде делайте WHERE clicks <= impressions или хотя бы кэп на 100%.
2. CTR vs unique CTR — разные метрики. На собеседовании уточните, какая нужна.
3. CASE WHEN ... THEN 1 END внутри COUNT() тоже работает (COUNT считает не-NULL), но FILTER читается лучше; для MySQL до 8.0 FILTER нет — используйте SUM(event_type = 'click').
4. NULL-в знаменателе. Без NULLIF для постов с 0 impressions упадёте в деление на ноль.

Эталонный ответ

COUNT(*) FILTER (WHERE event_type = 'click') / NULLIF(COUNT(*) FILTER (WHERE event_type = 'impression'), 0) по post_id. Уточняйте: считать ли по событиям или по уникальным пользователям.