LAG и LEAD на собеседовании Data Engineer

Зачем спрашивают на собесе DE

LAG/LEAD — оконные функции для доступа к соседним строкам. Без них приходится писать self-join, что и медленнее, и сложнее. На собесе DE: «как посчитать прирост week-over-week», «как найти разрывы в timeline», «как определить начало сессии». Senior-уровень — performance отличие LAG от self-join, поведение в распределённых движках.

Главная боль без понимания — DE пишет 30-строчный self-join, в плане две seq-scan'а, запрос работает 10 минут. С LAG было бы 3 строки и одна сортировка.

Базовый синтаксис

LAG(expression [, OFFSET [, default]]) OVER (
    [PARTITION BY ...]
    ORDER BY ...
)

• expression — что взять из соседней строки.
• offset — на сколько строк назад (LAG) или вперёд (LEAD). По умолчанию 1.
• default — что вернуть, если соседа нет (NULL по умолчанию).
• PARTITION BY — рамки независимых секций (например, по user_id).
• ORDER BY — обязательно, определяет «соседство».

Пример: предыдущая стоимость заказа того же пользователя.

SELECT
  order_id,
  user_id,
  order_at,
  amount,
  LAG(amount) OVER (PARTITION BY user_id ORDER BY order_at) AS prev_amount,
  LEAD(amount, 1, 0) OVER (PARTITION BY user_id ORDER BY order_at) AS next_amount
FROM orders;

LEAD(amount, 1, 0) — следующая сумма, если её нет — 0 (default).

Сравнение с прошлым: revenue growth

Рост MoM:

WITH monthly AS (
  SELECT
    DATE_TRUNC('month', created_at) AS month,
    SUM(amount) AS revenue
  FROM orders
  GROUP BY 1
)
SELECT
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
  ROUND(
    100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
    / NULLIF(LAG(revenue) OVER (ORDER BY month), 0),
    2
  ) AS growth_pct
FROM monthly
ORDER BY month;

NULLIF(..., 0) — защита от деления на ноль (первый месяц или нулевой revenue).

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

WITH monthly AS (...),
shifted AS (
  SELECT
    month, revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_revenue
  FROM monthly
)
SELECT month, revenue, prev_revenue,
       ROUND(100.0 * (revenue - prev_revenue) / NULLIF(prev_revenue, 0), 2) AS growth_pct
FROM shifted;

Читается лучше, не повторяет LAG.

Gap detection в datime-series

Найти даты, в которые не было заказов:

SELECT
  d.day,
  LAG(d.day) OVER (ORDER BY d.day) AS prev_day,
  d.day - LAG(d.day) OVER (ORDER BY d.day) AS gap_days
FROM (
  SELECT DISTINCT DATE(created_at) AS day FROM orders
) d
ORDER BY day;

Gap > 1 = пропуск. Если gap_days = 5, значит 4 дня были «тихими».

Альтернативный подход — left join с generate_series:

SELECT d.day
FROM generate_series('2026-01-01'::DATE, '2026-12-31'::DATE, '1 day') AS d(day)
LEFT JOIN (SELECT DISTINCT DATE(created_at) AS day FROM orders) o USING (day)
WHERE o.day IS NULL;

LAG-вариант полезен, когда нужно посчитать длину пропуска, не его наличие.

Sessionization: разрыв сессии

Стандартная задача DE — разбить события юзера на сессии (новая, если разрыв > 30 минут).

WITH events_with_gap AS (
  SELECT
    user_id,
    event_at,
    event_type,
    EXTRACT(EPOCH FROM (event_at - LAG(event_at) OVER (
      PARTITION BY user_id ORDER BY event_at
    ))) AS gap_seconds
  FROM events
),
sessions AS (
  SELECT
    *,
    SUM(CASE WHEN gap_seconds IS NULL OR gap_seconds > 1800 THEN 1 ELSE 0 END)
      OVER (PARTITION BY user_id ORDER BY event_at) AS session_id
  FROM events_with_gap
)
SELECT user_id, session_id, MIN(event_at), MAX(event_at), COUNT(*) AS events
FROM sessions
GROUP BY 1, 2;

Логика:

1. Для каждого события — gap до предыдущего того же юзера.
2. Если gap > 30 мин (или NULL — самое первое) — это начало новой сессии (1).
3. Накапливающая сумма 1-ок даёт уникальный session_id.

Этот паттерн «gap-based session» обязательный для собеса middle DE.

Распределённые движки: ClickHouse, Spark

ClickHouse — поддерживает LAG/LEAD через lagInFrame и leadInFrame в новых версиях. Старый синтаксис — через neighbor() (deprecated).

SELECT
  user_id,
  event_at,
  lagInFrame(amount) OVER (
    PARTITION BY user_id ORDER BY event_at
    ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING
  ) AS prev_amount
FROM events;

Spark SQL — синтаксис идентичен Postgres:

SELECT user_id, amount,
       lag(amount) OVER (PARTITION BY user_id ORDER BY ts) AS prev
FROM events;

Skewed partitions — узкое место. Если у одного юзера 100М событий — окно для него обрабатывается на одном executor'е. Решение: salted partition key или препроцессинг.

Greenplum — стандартный ANSI SQL window. Но distribution key должен совпадать с partition window для эффективности (иначе motion).

Частые ошибки

Без ORDER BY. LAG/LEAD требуют порядок — без него БД может выдать любой результат (или ошибку).

PARTITION BY пропущен, но нужен. «Разница с предыдущим заказом того же юзера» без PARTITION BY user_id смешает заказы разных юзеров.

Использовать LAG в WHERE. Окна не работают в WHERE. Нужно завернуть в CTE / подзапрос.

-- плохо
SELECT * FROM orders WHERE LAG(amount) OVER (...) > 100;

-- хорошо
WITH t AS (SELECT *, LAG(amount) OVER (...) AS prev FROM orders)
SELECT * FROM t WHERE prev > 100;

Деление на null prev_value. (curr - prev) / prev без NULLIF упадёт или вернёт null на первом ряду партиции.

Self-join вместо LAG. Self-join работает, но в 5-10× медленнее на больших данных.

Считать, что LAG — это «строка минус 1 в таблице». Это «предыдущая по сортировке окна», не физическая. Если порядок не указан — результат неопределён.

Frame в LAG/LEAD. LAG/LEAD не используют window frame (ROWS/RANGE), только offset. Если задать ROWS BETWEEN ... — синтаксически валидно (в PG), но не имеет эффекта.

Связанные темы

• Оконные функции на собесе DE
• Recursive CTE на собесе DE
• SQL для Data Engineer: собеседование
• EXPLAIN и план запроса для DE
• Подготовка к собесу Data Engineer

FAQ

LAG vs FIRST_VALUE — в чём разница?

LAG — N строк назад. FIRST_VALUE — первая строка в окне (зависит от frame). Для «значение на старте партиции» используется FIRST_VALUE с ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Можно ли передать выражение в offset?

В Postgres — нет, offset должен быть константой или параметром. Динамический offset — через CASE-ветки или процедуру.

LAG работает на NULL-ах?

Да. Возвращает NULL значения, если они есть в источнике. IGNORE NULLS (стандарт SQL:2008) поддерживается в Oracle, частично в Postgres 16+ через RESPECT NULLS / IGNORE NULLS.

Как обрабатывать дубликаты в ORDER BY?

Если две строки равны по ORDER BY, порядок между ними не определён. Добавь tie-breaker: ORDER BY event_at, event_id.

LAG быстрее self-join?

Обычно — да, потому что одно сканирование таблицы и одна сортировка вместо двух сканирований и hash-join. На больших данных разница в 5-10×.

Это официальная информация?

Нет. Статья основана на стандарте SQL:2003, документации Postgres / ClickHouse / Spark.

Тренируйте Data Engineering — откройте тренажёр с 1500+ вопросами для собесов.