Условие

В programmatic-рекламе показы (impressions) выводятся на сайтах через DSP, кликом мы фиксируем переход на сайт рекламодателя, конверсией — депозит. Даны три таблицы:

impressions

impression_id: string
url_address: string
user_id: string
request_country: string
tracking_type: string (cookie/fingerprint)
dynamic_display: boolean
dynamic_display_variables: string
request_browser_name: string
timestamp: date

clicks

impression_id: int
user_id: int
timestamp: string

conversions

conversion_id: string
user_id: string
dval: integer (deposit value)
curr: string
timestamp: date

Посчитайте CTR (%) по показам в стране Austria.

Решение

Подход

CTR = clicks / impressions × 100%. «Кликом» считаем такой clicks.impression_id, который ссылается на показ в Austria. Самый чистый способ — LEFT JOIN от impressions:

Реализация (PostgreSQL)

SELECT
    100.0 * COUNT(c.impression_id)::numeric / COUNT(*)::numeric AS ctr_pct
FROM impressions i
LEFT JOIN clicks c
       ON c.impression_id::text = i.impression_id
WHERE i.request_country = 'Austria';

Альтернатива — через подзапрос:

SELECT
    100.0 *
    (SELECT COUNT(*)
     FROM clicks c
     JOIN impressions i ON i.impression_id = c.impression_id::text
     WHERE i.request_country = 'Austria')::numeric
    /
    NULLIF((SELECT COUNT(*) FROM impressions WHERE request_country = 'Austria'), 0) AS ctr_pct;

Анализ результата

Ключевая проверка — типы данных: clicks.impression_id объявлен как int, а в impressions — string. На реальной БД это либо нужно кастовать (::text), либо оба типа должны совпадать. Если у части импрешенов id не numeric — ::int развалится.
LEFT JOIN против INNER JOIN: при INNER JOIN мы потеряем показы без кликов и не сможем посчитать знаменатель. Нужен LEFT.
Если у одного impression_id может быть несколько кликов (двойной клик, ретрай), числитель раздуется. Стандарт: COUNT(DISTINCT c.impression_id).

Усиленная версия

SELECT
    100.0 * COUNT(DISTINCT c.impression_id)::numeric
          / NULLIF(COUNT(DISTINCT i.impression_id), 0) AS ctr_pct
FROM impressions i
LEFT JOIN clicks c
       ON c.impression_id::text = i.impression_id
WHERE i.request_country = 'Austria';

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

Несовместимые типы id. int vs string — ловите на проде через CAST и проверяйте, что нет non-numeric id.
Двойные клики. COUNT(DISTINCT c.impression_id) — стандарт; иначе CTR > 100% возможен.
COUNT(c.impression_id) vs COUNT(*). Первый считает не-NULL после LEFT JOIN; второй посчитал бы все строки (включая показы без кликов). Используем разные подсчёты.
NULL-страна. Если request_country может быть NULL — не учтётся в WHERE. ОК, но стоит явно отметить.
Целочисленное деление. В строгих диалектах 1/2 = 0. Кастуем в numeric или умножаем на 100.0.
Период. Часто CTR считают за конкретное окно (WHERE timestamp BETWEEN ...); запрос без фильтра — за всё время.

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

SELECT 100.0 * COUNT(DISTINCT c.impression_id)::numeric
              / NULLIF(COUNT(DISTINCT i.impression_id), 0) AS ctr_pct
FROM impressions i
LEFT JOIN clicks c ON c.impression_id::text = i.impression_id
WHERE i.request_country = 'Austria';

LEFT JOIN сохраняет знаменатель, DISTINCT защищает от двойных кликов, фильтр по стране — в WHERE.

Sportradar: SQL — CTR показов в Австрии