Вопросы на собеседовании: Дата-инженер

У SELECT порядок записи отличается от логического порядка вычисления.

• Части запроса записывают так: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY и LIMIT.
• Логическое вычисление начинается с FROM и соединений, после чего выполняются WHERE, GROUP BY, HAVING, SELECT, ORDER BY и LIMIT.
• Поскольку WHERE выполняется до SELECT и агрегации, псевдоним из SELECT обычно нельзя использовать в WHERE, а результаты агрегатов нужно фильтровать через HAVING, а не WHERE.

WHERE и HAVING фильтруют данные на разных этапах запроса с группировкой.

• WHERE отбирает отдельные строки до группировки, например оставляет только строки с country = 'DE'.
• HAVING отбирает группы после агрегации, например оставляет группы с SUM(amount) > 1000.
• Условие, которое можно применить в WHERE, обычно стоит размещать именно там, поскольку сокращение числа строк до агрегации уменьшает объём работы.

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

• INNER JOIN оставляет только строки с совпадением в обеих таблицах, поэтому подходит для записей, которые обязаны присутствовать с двух сторон.
• LEFT JOIN сохраняет все строки левой таблицы и заполняет правые колонки значением NULL при отсутствии пары, поэтому подходит для полного списка клиентов с заказами и без них.
• FULL JOIN сохраняет несовпавшие строки с обеих сторон, что удобно при сверке источника и приёмника.

Соединение размножает строки, когда его ключ не уникален в одной или обеих таблицах.

• Каждая совпавшая пара создаёт строку результата, поэтому связь один-ко-многим или многие-ко-многим может незаметно раздуть количество строк и итоговые суммы.
• До соединения нужно сравнить COUNT с COUNT(DISTINCT key) или выполнить аналогичную проверку уникальности на стороне, где она ожидается.
• После соединения нужно сверить число строк с ожидаемым типом связи и автоматизировать проверки уникальности и количества, чтобы пайплайн остановился до публикации задвоенных данных.

GROUP BY задаёт детализацию результата, а агрегатные функции вычисляют значение для каждой группы.

• Строки с одинаковыми значениями в перечисленных колонках группировки образуют одну группу результата.
• SUM, COUNT, AVG, MIN и MAX вычисляют отдельное значение для каждой группы.
• Каждая выбранная колонка обычно должна либо входить в GROUP BY, либо находиться внутри агрегатной функции, поэтому колонки группировки должны соответствовать нужной детализации отчёта.

Варианты COUNT и другие агрегатные функции по-разному обрабатывают NULL.

• COUNT(*) считает все строки, а COUNT(column) только строки, где значение этой колонки не является NULL.
• SUM, AVG, MIN и MAX пропускают NULL, поэтому AVG учитывает только заполненные значения и может описывать не ту совокупность, которую ожидали.
• COALESCE(column, 0) следует применять лишь тогда, когда пропуск действительно означает ноль, а перед агрегацией стоит проверить долю NULL.

COUNT(DISTINCT column) считает уникальные значения без NULL, а не строки.

• COUNT(DISTINCT customer_id) позволяет отличить число уникальных клиентов от числа заказов.
• NULL не учитывается, а точный подсчёт уникальных значений на очень больших таблицах может быть дорогим, поэтому для оценки иногда подходит приближённая функция.
• После соединения размножение строк может не изменить число уникальных значений и скрыть ошибку, поэтому нужно также проверять количество строк и связи между ключами.

Подзапросы и CTE создают промежуточные результаты, поэтому выбор прежде всего зависит от читаемости и структуры.

• Короткий подзапрос в WHERE или FROM удобен для одной локальной задачи, например для сравнения значения со средним.
• CTE, объявленный через WITH, даёт каждому этапу имя и позволяет читать многошаговое преобразование сверху вниз.
• CTE часто используют в моделях dbt и аналитическом SQL, поскольку именованные этапы проще проверять и при необходимости можно использовать повторно.

Оконная функция выполняет расчёт по связанным строкам, не схлопывая исходные строки.

• Каждая входная строка остаётся в результате и получает дополнительное вычисленное значение.
• PARTITION BY задаёт связанную группу, а ORDER BY внутри OVER определяет порядок, например в ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at).
• GROUP BY возвращает одну строку на группу, а оконные функции могут показать ранг, предыдущее или накопленное значение для каждой строки внутри группы.

Нарастающий итог дневной выручки сочетает агрегацию по дням с оконной суммой.

• Один из вариантов: SELECT order_date, SUM(amount) AS daily_revenue, SUM(SUM(amount)) OVER (ORDER BY order_date) AS running_total FROM orders GROUP BY order_date ORDER BY order_date.
• Внутренний SUM считает выручку за день, а внешний оконный SUM накапливает дневные значения от первой даты до текущей.
• Если добавить в окно PARTITION BY region, итог начнётся заново для каждого региона, при этом region также нужно включить в дневную группировку.

Обычный способ дедупликации ранжирует строки по свежести внутри бизнес-ключа и оставляет первую.

• Можно создать CTE: WITH ranked AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) AS rn FROM customers_raw).
• Затем нужно выбрать из CTE строки с WHERE rn = 1 и добавить в ORDER BY стабильный дополнительный критерий, если время у двух записей может совпасть.
• В отличие от DISTINCT, этот способ удаляет повторы по выбранному бизнес-ключу и явно определяет, какая запись победит.

UNION удаляет одинаковые строки результата, а UNION ALL объединяет результаты, сохраняя все строки.

• Удаление дублей требует дополнительной работы, поэтому UNION обычно медленнее UNION ALL.
• В пайплайнах обычно стоит выбирать UNION ALL, если входные наборы не пересекаются или повторы имеют смысл.
• Если нежелательные дубли возможны, явное правило по бизнес-ключу понятнее и безопаснее, чем неявная дедупликация целых строк через UNION.

Условная агрегация помещает CASE WHEN внутрь агрегатной функции и считает метрики по сегментам в одном запросе.

• SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END) AS paid_revenue суммирует только оплаченные суммы.
• COUNT(CASE WHEN channel = 'mobile' THEN 1 END) AS mobile_orders считает строки, в которых условие возвращает значение, отличное от NULL.
• Несколько таких выражений позволяют рассчитать разные сегменты отчёта за одно чтение таблицы вместо отдельных запросов с фильтрами.

NULL обозначает неизвестное значение, поэтому для него нужны специальные операторы и функции SQL.

• Сравнения column = NULL и NULL = NULL дают UNKNOWN, а не TRUE, поэтому нужно использовать IS NULL или IS NOT NULL.
• NOT IN может не вернуть ни одной строки, если его подзапрос содержит NULL, поэтому для исключающего соединения часто безопаснее NOT EXISTS.
• COALESCE подставляет явно заданное запасное значение, а NULLIF может заменить нулевой делитель на NULL и предотвратить ошибку деления на ноль.

События по месяцам нужно группировать по дате начала месяца с учётом года и нужного часового пояса.

• Типичный запрос вычисляет DATE_TRUNC('month', created_at) AS month, группирует по этому значению и сортирует по нему.
• Группировка только по номеру месяца объединит одинаковые месяцы разных лет, а текстовая подпись месяца может нарушить хронологическую сортировку.
• При необходимости время нужно перевести в бизнес-часовой пояс перед применением DATE_TRUNC, поскольку UTC и местное время могут отнести пограничное событие к разным месяцам.

Последнюю запись для каждой сущности обычно выбирают с помощью оконной функции ROW_NUMBER.

• В CTE или подзапросе нужно вычислить ROW_NUMBER() OVER (PARTITION BY entity_id ORDER BY event_time DESC), а снаружи оставить rn = 1.
• В ORDER BY стоит добавить стабильный дополнительный критерий, чтобы при одинаковом времени двух событий победитель выбирался однозначно.
• Соединение с MAX(event_time) тоже возможно, но без отдельной обработки совпадений оно может вернуть несколько строк для одной сущности.

Основные строковые функции SQL помогают привести грязный текст к единому виду до сравнения, соединения или публикации.

• TRIM убирает пробелы по краям, а LOWER и UPPER выравнивают регистр букв.
• REPLACE удаляет или заменяет нежелательные символы, а SUBSTRING вместе с POSITION либо SPLIT_PART извлекает части составного значения.
• Например, email можно один раз нормализовать при загрузке через LOWER(TRIM(email)), а не повторять ту же очистку во всех последующих запросах.

Перед публикацией датасет нужно сверить с надёжными источниками, ожиданиями от преобразований и отдельными примерами записей.

• Итоги следует сравнить с системой-источником или проверенным отчётом, а число строк контролировать на каждом шаге, чтобы найти место неожиданной потери или раздувания данных.
• Отдельно нужно проверить крайние случаи, например NULL в ключах, дубликаты и границы дат.
• Две-три записи стоит вручную проследить от источника до результата, а подозрительно хорошие или плохие показатели исследовать, а не считать правильными по умолчанию.

При медленном запросе сначала нужно измерить, где тратятся ресурсы, а не угадывать причину.

• Через EXPLAIN следует проверить полные сканирования таблиц, использование индексов или партиций, способ соединения и число строк на каждом этапе.
• Объём данных стоит уменьшать подходящими ранними фильтрами и выбирать только нужные колонки вместо SELECT *.
• Также нужно проверить полезные индексы на колонках соединения и фильтрации в строчно-ориентированной базе либо использование колонки партиционирования в WHERE для партиционированной системы.

DDL определяет структуры базы данных, а DML работает с данными внутри этих структур.

• К DDL относятся CREATE, ALTER и DROP для таких объектов, как таблицы, схемы и индексы.
• К DML относятся INSERT, UPDATE, DELETE и MERGE, а читающий данные SELECT иногда выделяют отдельно как DQL.
• Разрушительные команды DDL, например DROP или TRUNCATE, требуют особой осторожности, поскольку возможность отката зависит от базы, а восстановление может быть сложным или невозможным.