SQL для Data Engineer на собеседовании

Зачем DE спрашивают SQL

SQL для Data Engineer — это не только «написать запрос», но и оптимизация: как ускорить запрос с 30 минут до 30 секунд, как использовать индексы, EXPLAIN PLAN, materialized views, partitioning.

На собесе Data Engineer SQL — отдельный раунд 45-60 минут. Уровень: уверенный middle — JOIN, оконные функции, оптимизация. На senior — explain plans, индексы, performance tuning на масштабе.

Базовый набор

Каждый DE должен знать вслепую:

  • JOIN-ы: INNER, LEFT, RIGHT, FULL, CROSS, SELF. Когда какой.
  • GROUP BY + HAVING. Агрегатные функции (SUM, COUNT, AVG, MIN, MAX, ARRAY_AGG, STRING_AGG).
  • Подзапросы и CTE. Когда CTE проще, когда подзапросы.
  • Оконные функции: ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, SUM OVER, AVG OVER.
  • CASE WHEN. Условная логика.
  • UNION vs UNION ALL. Дедупликация.
  • NULL handling: COALESCE, NULLIF, IS NULL, IS NOT NULL.
  • Тип конверсия: CAST, тип-зависимые операции.
  • DATE-операции: EXTRACT, DATE_TRUNC, INTERVAL, AGE.

Подробнее — SQL на собеседовании, оконные функции, CTE.

Оконные функции глубоко

DE часто решает задачи через window functions, где аналитик использовал бы JOIN.

ROW_NUMBER vs RANK vs DENSE_RANK:

  • ROW_NUMBER — уникальный номер
  • RANK — пропускает при ties (1, 1, 3)
  • DENSE_RANK — не пропускает (1, 1, 2)

LAG/LEAD — предыдущее / следующее значение. Полезно для разностей по времени.

SUM OVER PARTITION BY — running total / агрегат по группе без коллапсирования строк.

Окна с фреймом:

SUM(amount) OVER (PARTITION BY user_id ORDER BY DATE
                  ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

Скользящее окно по 7 строкам.

Range vs Rows: ROWS — физические N строк. RANGE — по значению (полезно для дат).

Подробнее — LAG, LEAD в SQL, оконные функции — шпаргалка.

Оптимизация запросов

EXPLAIN PLAN — главный инструмент. Показывает, как СУБД выполняет запрос.

В Postgres:

EXPLAIN ANALYZE
SELECT ...

Покажет: тип операций (Seq Scan / Index Scan / Hash Join / Merge Join), стоимость, фактическое время, количество строк.

Признаки проблем:

  • Seq Scan на большой таблице (нет индекса)
  • Hash Join вместо Merge Join на сортированных данных
  • Большие estimated rows vs actual rows (плохая статистика)
  • Subquery executed multiple times

Оптимизации:

  • Индексы. B-tree для equality / range, GIN для arrays / JSONB, BRIN для time-series.
  • Партиционирование. Pruning лишних partitions.
  • Materialized views. Предагрегаты для частых запросов.
  • Денормализация. Если JOIN-ы стоят дороже storage.
  • Перепись запроса. Avoiding subquery in WHERE, использование CTE с MATERIALIZED hint.

Индексы

B-tree — default. Хорошо для equality (=), range (<, >, BETWEEN), LIKE с префиксом.

Hash — только equality. С Postgres 10+ поддерживает WAL и пригоден для production, но B-tree обычно достаточен.

GIN — для arrays, JSONB, full-text search.

BRIN — Block Range Index. Для огромных таблиц по time/sequential колонкам. Очень компактный.

Partial index — индекс только на subset строк (WHERE status = 'active').

Composite index — несколько колонок. Order matters: индекс (a, b) работает для WHERE a = ... AND b = ..., но не для WHERE b = ... only.

Covering index — INCLUDE дополнительные колонки, чтобы query был index-only scan.

Materialized views

Предвычисленные результаты запроса. В отличие от обычного VIEW (виртуальный), MV хранит данные на диске.

CREATE MATERIALIZED VIEW mv_daily_revenue AS
SELECT DATE, SUM(amount) AS revenue
FROM orders
GROUP BY DATE;

REFRESH MATERIALIZED VIEW mv_daily_revenue;

Use case: медленные аналитические запросы, которые много раз вызываются.

Trade-off: надо обновлять. CONCURRENTLY для refresh без блокировки.

Подробнее — materialized views в SQL.

Типичные задачи на собесе

1. Top-K per group

«Для каждого user найди топ-3 транзакции по сумме».

WITH ranked AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rn
    FROM transactions
)
SELECT * FROM ranked WHERE rn <= 3;

2. Retention cohort

«Посчитай weekly cohort retention: сколько пользователей из когорты вернулось на следующей неделе».

WITH first_event AS (
    SELECT user_id,
           DATE_TRUNC('week', MIN(event_date)) AS cohort_week
    FROM events
    GROUP BY user_id
),
activity AS (
    SELECT f.cohort_week,
           f.user_id,
           DATE_TRUNC('week', e.event_date) AS active_week
    FROM first_event f
    JOIN events e USING (user_id)
)
SELECT cohort_week,
       COUNT(DISTINCT user_id) AS cohort_size,
       COUNT(DISTINCT CASE
             WHEN active_week = cohort_week + INTERVAL '7 days' THEN user_id
             END) AS retained_w1
FROM activity
GROUP BY 1
ORDER BY 1;

3. Funnel

«Конверсия из view в add_to_cart в purchase».

WITH user_events AS (
    SELECT user_id,
           MAX(CASE WHEN event = 'view' THEN 1 ELSE 0 END) AS viewed,
           MAX(CASE WHEN event = 'add_to_cart' THEN 1 ELSE 0 END) AS added,
           MAX(CASE WHEN event = 'purchase' THEN 1 ELSE 0 END) AS purchased
    FROM events
    GROUP BY user_id
)
SELECT SUM(viewed) AS viewed,
       SUM(added) AS added,
       SUM(purchased) AS purchased,
       ROUND(100.0 * SUM(added) / NULLIF(SUM(viewed), 0), 2) AS view_to_cart_pct,
       ROUND(100.0 * SUM(purchased) / NULLIF(SUM(added), 0), 2) AS cart_to_purchase_pct
FROM user_events;

4. Дубликаты

«Найди и удали дубликаты, оставив самую свежую запись».

WITH ranked AS (
    SELECT id,
           ROW_NUMBER() OVER (PARTITION BY user_id, amount, DATE
                              ORDER BY created_at DESC) AS rn
    FROM transactions
)
DELETE FROM transactions
WHERE id IN (SELECT id FROM ranked WHERE rn > 1);

Используем IN с подзапросом «что удалить», а не NOT IN. NOT IN (subquery) ломается при NULL значениях в подзапросе и читается тяжелее.

5. Gap analysis

«Найди пропуски в sequential ID».

SELECT id + 1 AS gap_start,
       LEAD(id) OVER (ORDER BY id) - 1 AS gap_end
FROM events
WHERE LEAD(id) OVER (ORDER BY id) - id > 1;

Типичные ошибки в SQL

Integer division. 5 / 20 = 0 в Postgres. Используй 5.0 / 20 или CAST AS NUMERIC.

NULL в агрегатах. SUM, AVG игнорируют NULL. COUNT(*) считает все, COUNT(col) — только не-NULL.

EXTRACT(MONTH FROM AGE()) возвращает 0-11, не общее число месяцев. Нужно YEAR × 12 + MONTH.

JOIN множит строки. Если в правой таблице несколько матчей — строки умножаются. Часто баг.

Forgot NULLIF. Деление на ноль ломает. amount / NULLIF(quantity, 0).

Оконные функции в WHERE. Не работают. Нужно CTE + фильтр.

FAQ

Какую СУБД учить?

PostgreSQL — фундамент, синтаксис близок к SQL standard. ClickHouse — для OLAP. Spark SQL — для big data. BigQuery / Snowflake — для cloud.

Нужно ли знать MySQL?

Редко критично. Если попадёшь — выучишь различия за неделю.

Каков уровень SQL для DE?

Уверенный middle: оконные функции, CTE, оптимизация, индексы. На Senior — глубокая оптимизация, понимание execution plans, опыт с большими таблицами.

Сколько готовиться?

С нуля — 2-3 месяца до middle. Уже работал — 2-4 недели для систематизации.

Какие книги?

«SQL для простых смертных» — для базы. «PostgreSQL: From Novice to Professional» — глубоко. «High Performance SQL» — оптимизация.

Смотрите также

Тренироваться в Telegram

Ещё по теме «data-engineer-sobesedovanie»

airflow-patternsclickhouse-i-olapdata-modelingkafka-streamingspark-deep-dive