Работа с датами в SQL на собеседовании
Почему даты — ключевая тема для аналитика
Почти каждая аналитическая задача связана с временем: динамика по дням, сравнение периодов, когортный анализ, retention. На собеседовании вопросы по датам проверяют, умеете ли вы работать с реальными аналитическими данными, а не просто писать SELECT из учебника.
Типичный формат: дана таблица с колонкой created_at типа timestamp. Нужно посчитать метрику по дням, неделям или месяцам. Кажется просто — но ловушек достаточно, чтобы запутать даже опытного кандидата.
Работа с датами — обязательный навык аналитика. Если вы не можете сгруппировать данные по неделям или посчитать разницу между датами, интервьюер усомнится в вашем практическом опыте.
Основные функции
DATE_TRUNC — округляет дату до указанного периода. DATE_TRUNC('month', '2024-03-15'::date) вернёт '2024-03-01'. Это основной инструмент для группировки по периодам. Работает с day, week, month, quarter, year. В MySQL аналог — DATE_FORMAT, в BigQuery — DATE_TRUNC с немного другим синтаксисом.
EXTRACT — извлекает часть даты: год, месяц, день, час, день недели. EXTRACT(dow FROM created_at) вернёт номер дня недели. Используется для анализа сезонности, распределения по часам, фильтрации по дням недели.
INTERVAL — арифметика с датами. created_at + INTERVAL '7 days' сдвигает дату на неделю вперёд. NOW() - INTERVAL '30 days' — дата 30 дней назад. Синтаксис зависит от СУБД, но принцип одинаков.
DATE_DIFF / AGE — разница между датами. В PostgreSQL AGE(date1, date2) возвращает интервал, в BigQuery DATE_DIFF(date1, date2, DAY) — число дней. На собеседовании важно знать синтаксис своей СУБД.
Типичные аналитические задачи
Задача 1: DAU по дням за последний месяц. Подход — DATE_TRUNC('day', event_time) для группировки, COUNT(DISTINCT user_id) для подсчёта уникальных пользователей, WHERE event_time >= NOW() - INTERVAL '30 days' для фильтрации.
Задача 2: Сравнение метрики за текущую и предыдущую неделю. Подход — два CTE или подзапроса: один для текущей недели, другой для предыдущей. Или LAG с предварительной группировкой по неделям. Интервьюер оценит, если вы предложите оба варианта.
Задача 3: Retention по когортам. Подход — определить когорту как DATE_TRUNC('week', first_activity_date), затем посчитать долю вернувшихся через 7, 14, 30 дней. Здесь критично правильно вычислить разницу между датами.
Задача 4: Заполнить пропущенные даты нулями. Подход — сгенерировать ряд дат через generate_series (PostgreSQL) или CROSS JOIN с таблицей календаря, затем LEFT JOIN к данным. Без этого приёма графики с пропущенными днями вводят в заблуждение.
Ловушки и нюансы
- Часовые пояса — timestamp without time zone и timestamp with time zone ведут себя по-разному. Если данные хранятся в UTC, а отчёт нужен по Москве, DATE_TRUNC без конвертации даст неправильные группировки. Всегда уточняйте у интервьюера, в каком часовом поясе данные.
- Начало недели — в PostgreSQL неделя начинается с понедельника (ISO), в MySQL — с воскресенья по умолчанию. Это влияет на группировку по неделям и результат EXTRACT(dow).
- Високосные годы и месяцы разной длины — INTERVAL '1 month' от 31 января даст 28 или 29 февраля. Арифметика с месяцами и годами неточна — для критичных расчётов используйте дни.
- Индексы и DATE_TRUNC — WHERE DATE_TRUNC('day', created_at) = '2024-03-15' не использует индекс на created_at. Правильно: WHERE created_at >= '2024-03-15' AND created_at < '2024-03-16'.
Совет: на собеседовании всегда уточняйте часовой пояс данных и определение «недели». Это показывает, что вы работали с реальными данными, а не только с учебными.
FAQ
Какие функции для дат нужно знать для собеседования?
Минимум: DATE_TRUNC, EXTRACT, INTERVAL, CURRENT_DATE / NOW(). Для middle-позиции — generate_series для генерации дат, AGE или DATE_DIFF для разницы между датами. Синтаксис зависит от СУБД — если компания использует BigQuery, почитайте их документацию.
Как правильно фильтровать по дате, чтобы использовался индекс?
Используйте диапазонные условия: WHERE created_at >= '2024-03-01' AND created_at < '2024-04-01'. Не оборачивайте колонку в функции — DATE_TRUNC или CAST в WHERE блокируют использование индекса. Подробнее об индексах — в разделе индексы и оптимизация.
Чем timestamp отличается от date?
date хранит только дату (год, месяц, день). timestamp хранит дату и время с точностью до микросекунд. Для аналитических задач с группировкой по дням разницы нет — DATE_TRUNC('day', timestamp_col) вернёт дату. Но при точных сравнениях разница критична.