Условие

Даны три таблицы programmatic-рекламы:

impressions (показы):

impression_id: string, user_id: string, url_address: string, request_country: string, tracking_type: string, 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.

Для каждого пользователя, у которого была конверсия, найдите:

сколько всего показов он получил;
timestamp первого и последнего показа.

Решение

Подход

«Конвертившийся пользователь» = user_id, встречающийся в conversions. Для них фильтруем показы и агрегируем MIN/MAX/COUNT.

Реализация

SELECT
    i.user_id,
    COUNT(*)              AS impressions_count,
    MIN(i.timestamp)      AS first_impression_ts,
    MAX(i.timestamp)      AS last_impression_ts
FROM impressions i
WHERE i.user_id IN (SELECT DISTINCT user_id FROM conversions)
GROUP BY i.user_id
ORDER BY impressions_count DESC;

Альтернатива через JOIN (часто быстрее на индексах):

SELECT
    i.user_id,
    COUNT(*)         AS impressions_count,
    MIN(i.timestamp) AS first_impression_ts,
    MAX(i.timestamp) AS last_impression_ts
FROM impressions i
JOIN (SELECT DISTINCT user_id FROM conversions) c
  ON c.user_id = i.user_id
GROUP BY i.user_id;

Если хочется ещё сразу узнать, было ли это до или после конверсии:

WITH conv_first AS (
    SELECT user_id, MIN(timestamp) AS first_conv_ts
    FROM conversions
    GROUP BY user_id
)
SELECT
    i.user_id,
    COUNT(*) AS imp_total,
    SUM(CASE WHEN i.timestamp <= cf.first_conv_ts THEN 1 ELSE 0 END) AS imp_before_conv,
    SUM(CASE WHEN i.timestamp >  cf.first_conv_ts THEN 1 ELSE 0 END) AS imp_after_conv,
    MIN(i.timestamp) AS first_imp,
    MAX(i.timestamp) AS last_imp
FROM impressions i
JOIN conv_first cf USING (user_id)
GROUP BY i.user_id;

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

IN (subquery) vs JOIN: на современных оптимизаторах разница невелика, но JOIN с DISTINCT обычно стабильнее.
Если конверсия одна — это число. Если их может быть несколько — задача про «первую», поэтому в conv_first берём MIN.
Колонка last_impression_ts интересна для аттрибуции — последний показ перед конверсией = «last touch».

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

Типы. В исходной схеме clicks.user_id это int, а у impressions/conversions — string. Кастуйте.
Дубли. Один и тот же impression_id может встречаться дважды в логах из-за SDK retry. COUNT(DISTINCT impression_id), если важно «уникальные показы».
Конверсия до показа. Технически возможна — если идентификация пользователя сменилась. Делите показы на «до» и «после» первой конверсии.
Time zones. timestamp в разных таблицах может быть в UTC и локальной TZ. На сравнении i.timestamp <= cf.first_conv_ts это критично.
Аттрибуция. Задача просит «общее число показов», а не «показы до конверсии». Это разные числа; в маркетинговом отчёте обычно нужно второе.
Производительность. Таблица показов огромна; партиционирование по дате обязательно, иначе MIN/MAX/COUNT будут сканировать всё.

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

SELECT
    i.user_id,
    COUNT(*)         AS impressions_count,
    MIN(i.timestamp) AS first_impression_ts,
    MAX(i.timestamp) AS last_impression_ts
FROM impressions i
JOIN (SELECT DISTINCT user_id FROM conversions) c USING (user_id)
GROUP BY i.user_id;

В реальном маркетинговом отчёте — расширяем CTE'ой first_conv_ts и считаем показы до конверсии отдельно.

Sportradar: количество показов и первый/последний таймстемп для конвертившихся