8 SQL-задач,
которые дают на собесе
в BigTech

Как формулируют

Решение

SELECT category, name, price
FROM (
  SELECT category, name, price,
         ROW_NUMBER() OVER (
           PARTITION BY category
           ORDER BY price DESC
         ) AS rn
  FROM products
) t
WHERE rn <= 3;

Подвох

Главный — «у двух товаров одинаковая цена, какой попадёт в топ?». Это вопрос про разницу между ROW_NUMBER, RANK и DENSE_RANK.

• ROW_NUMBER — гарантирует ровно 3 строки, но порядок ничьих неопределён.
• RANK — при ничьей все получают одинаковый ранг, следующий пропускается (1, 1, 3, 4). На выходе может быть больше 3 строк.
• DENSE_RANK — ничьи получают одинаковый ранг, следующий идёт подряд (1, 1, 2, 3). Тоже может вернуть больше 3 строк.

На собесе ожидают, что аналитик уточнит у собеседника: «А что делать с ничьими? Если ровно 3 — это ROW_NUMBER с tie-breaker'ом (например, name). Если можно больше — DENSE_RANK». Просто написать ROW_NUMBER и не объяснить ничьи — это junior-ответ.

Как формулируют

Решение (важно — уникальные пользователи)

-- Уникальные пользователи за 7-дневное окно — через self-join
WITH dates AS (
  SELECT DISTINCT event_date
  FROM events
)
SELECT d.event_date,
       COUNT(DISTINCT e.user_id) AS rolling_dau_7d
FROM dates d
JOIN events e
  ON e.event_date BETWEEN d.event_date - INTERVAL '6 days'
                       AND d.event_date
GROUP BY d.event_date
ORDER BY d.event_date;

Подвох

Самая частая ошибка — написать через SUM(dau) OVER (ROWS BETWEEN 6 PRECEDING AND CURRENT ROW). Это не уникальные юзеры за 7 дней, а сумма дневных DAU. Один и тот же юзер, заходивший все 7 дней, посчитается семь раз. На собесе это типичный «троп» — кандидат пишет красивое window-решение, а оно даёт неверный ответ.

Window-функция работает для аддитивных метрик (sum, avg, count без distinct). Для distinct count за окно нужен либо self-join (как выше), либо более тяжёлые приёмы — массивные window-агрегаты в ClickHouse, или approximate count distinct (HyperLogLog).

Как формулируют

Решение

WITH cohort AS (
  SELECT user_id,
         DATE_TRUNC('month', MIN(event_date)) AS cohort_m
  FROM events
  GROUP BY user_id
),
activity AS (
  SELECT c.cohort_m,
         DATE_TRUNC('month', e.event_date) AS active_m,
         e.user_id
  FROM cohort c
  JOIN events e USING (user_id)
),
sizes AS (
  SELECT cohort_m, COUNT(DISTINCT user_id) AS cohort_size
  FROM cohort
  GROUP BY cohort_m
)
SELECT
  a.cohort_m,
  EXTRACT(YEAR FROM AGE(a.active_m, a.cohort_m)) * 12 +
  EXTRACT(MONTH FROM AGE(a.active_m, a.cohort_m)) AS months_since,
  COUNT(DISTINCT a.user_id) AS retained,
  COUNT(DISTINCT a.user_id) * 100.0 / s.cohort_size AS retention_pct
FROM activity a
JOIN sizes s ON s.cohort_m = a.cohort_m
GROUP BY a.cohort_m, months_since, s.cohort_size
ORDER BY a.cohort_m, months_since;

Подвох

Часто кандидат забывает делить на cohort_size и оставляет абсолютные числа. Или путает cohort_m с active_m в groupby и получает «когорту последнего возвращения» вместо «когорты регистрации».

Также важно проговорить, что таблица retention — треугольная: свежие когорты успели прожить меньше месяцев, у них вторая половина строк пустая. Это нормально, не баг.

Как формулируют

Решение

WITH first_purchase AS (
  SELECT user_id, MIN(created_at) AS first_p
  FROM orders
  GROUP BY user_id
)
SELECT
  AVG(EXTRACT(EPOCH FROM (p.first_p - u.signup_at)) / 86400) AS avg_days,
  PERCENTILE_CONT(0.5) WITHIN GROUP (
    ORDER BY EXTRACT(EPOCH FROM (p.first_p - u.signup_at)) / 86400
  ) AS median_days
FROM users u
JOIN first_purchase p USING (user_id);

Подвох

Главный вопрос — что делать с юзерами, которые не сделали покупку. JOIN их отфильтрует, AVG посчитается только по «купившим». В реальной задаче это часто не то, что хотел продакт: правильный ответ — «среди купивших среднее 12 дней, но 60% базы не купила ни разу». Уточняй у собеседника, какая постановка.

Второй подвох — AVG vs медиана. Time-to-event обычно сильно скошен (большинство покупает в первые 1–3 дня, длинный хвост). Среднее завышено. Медиана и P90 — более содержательные. Сильный middle сразу считает оба.

Как формулируют

Решение

WITH flagged AS (
  SELECT user_id, event_at,
         CASE
           WHEN LAG(event_at) OVER w IS NULL
             OR event_at - LAG(event_at) OVER w
                > INTERVAL '30 minutes'
           THEN 1 ELSE 0
         END AS is_new_session
  FROM events
  WINDOW w AS (PARTITION BY user_id ORDER BY event_at)
)
SELECT user_id, event_at,
       SUM(is_new_session) OVER (
         PARTITION BY user_id
         ORDER BY event_at
       ) AS session_num
FROM flagged
ORDER BY user_id, event_at;

Подвох

Двух-этапная логика: сначала LAG для разметки «новая сессия / нет», потом cumulative SUM по флагу — получаем номер сессии. Кандидат, который пишет это в одном выражении или забывает PARTITION BY user_id во втором OVER, размечает сессии «через юзеров».

Дополнительный вопрос: «А если бы сессии надо было считать вообще по всем юзерам (например, для оценки общей нагрузки)?». Тогда убираем PARTITION BY в обоих OVER, считаем по timestamp и получаем глобальные сессии. Чаще нужны per-user — но проговорить разницу полезно.

Как формулируют

Решение

SELECT
  DATE(created_at) AS dt,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) AS median,
  PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY amount) AS p90,
  PERCENTILE_DISC(0.9) WITHIN GROUP (ORDER BY amount) AS p90_disc
FROM orders
GROUP BY dt
ORDER BY dt;

Подвох

Разница между PERCENTILE_CONT и PERCENTILE_DISC:

Подвох #2 — кандидат может предложить NTILE(10) для перцентилей. NTILE делит на N равных групп, и P90 = «99-я группа из 100». На сегменте 1000 строк всё ровно, на маленькой выборке (10–50) NTILE сильно врёт, потому что округляет. Для точных перцентилей — всегда PERCENTILE_CONT.

Как формулируют

Решение (Wald-интервал для пропорции)

WITH agg AS (
  SELECT DATE('date') AS dt,
         SUM(has_purchase::int) AS x,
         COUNT(*)::float   AS n
  FROM sessions
  GROUP BY dt
)
SELECT dt,
       x / n AS cr,
       x / n - 1.96 * SQRT((x / n) * (1 - x / n) / n) AS lo,
       x / n + 1.96 * SQRT((x / n) * (1 - x / n) / n) AS hi
FROM agg
ORDER BY dt;

Подвох

Wald-интервал хорошо работает на больших выборках и центральных конверсиях (10–90%). Ломается:

• На маленьких выборках (n < 100) — нижняя граница может уйти в минус. Заменяй на Wilson или Clopper-Pearson.
• На крайних значениях (CR < 1% или > 99%) — интервал получается асимметричный и Wald врёт. Тот же Wilson или Clopper-Pearson.
• На ratio-метриках, которые не пропорции (средний чек = revenue/orders) — Wald вообще неприменим. Нужен delta-method: дисперсия ratio считается как (σ²X / Y²) + (X² σ²Y / Y⁴) − 2·X·Cov(X,Y) / Y³.

На собесе ожидают, что middle различает «конверсия как пропорция» (Wald / Wilson) и «средний чек как ratio» (delta-method). Это и есть проверка глубины понимания статистики.

Как формулируют

Решение (пороговая логика)

-- Юзеры, у которых было > 100 кликов за любые 10 минут
WITH buckets AS (
  SELECT user_id,
         DATE_TRUNC('minute', event_at)
           - INTERVAL '1 minute'
             * (EXTRACT(MINUTE FROM event_at)::int % 10) AS bucket_10min,
         COUNT(*) AS clicks
  FROM events
  WHERE event_type = 'click'
  GROUP BY user_id, bucket_10min
)
SELECT DISTINCT user_id
FROM buckets
WHERE clicks > 100;

Альтернатива через перцентили

-- Юзеры выше 99-го перцентиля по кликам/день
WITH daily AS (
  SELECT user_id, DATE(event_at) AS dt, COUNT(*) AS clicks
  FROM events
  WHERE event_type = 'click'
  GROUP BY user_id, dt
),
threshold AS (
  SELECT PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY clicks) AS p99
  FROM daily
)
SELECT DISTINCT d.user_id
FROM daily d, threshold t
WHERE d.clicks > t.p99;

Подвох

Главный вопрос — пороговая константа vs относительный перцентиль. Константа («> 100 кликов») — простая, но привязана к продукту: что нормально на Facebook — фрод на B2B-портале. Перцентильный подход универсальнее, но даёт 1% подозреваемых всегда, даже если фрода нет.

В продакшене обычно используют комбинацию: (пороговый «явный фрод-уровень») OR (перцентиль 99.5 + дополнительные признаки). На собесе достаточно проговорить trade-off и предложить одну из реализаций.

Дополнительный пласт — обозначить, что одна метрика не ловит фрод полностью. В реальности добавляют скорость кликов (clicks/сек), географию (IP), user-agent, время суток, и моделируют через ансамбль признаков. На SQL-собесе это не требуется — но упомянуть это в одном предложении показывает зрелость.