Оптимизация тяжёлого ETL-запроса

Условие задания

**Компания:** Яндекс

**Контекст:** ETL-запрос для обновления витрины выполняется 4 часа вместо ожидаемых 30 минут. Нужно проанализировать и оптимизировать.

**Медленный запрос:**

[см. код в задании]

**Размеры таблиц:** users — 10 млн, events — 2 млрд, orders — 100 млн.

**Задание:**
1. Определите проблемы в запросе (минимум 3)
2. Перепишите запрос с оптимизациями
3. Предложите индексы и другие инфраструктурные улучшения

Пример данных

Структура для ориентира — реальные значения из эталонного решения.

INSERT INTO mart_user_activity
WITH event_stats AS (
    -- Предагрегация событий (только за 30 дней)
    SELECT
        user_id,
        COUNT(DISTINCT session_id) AS sessions_30d,
        COUNT(*) AS events_30d,
        MAX(event_ts) AS last_activity
    FROM events
    WHERE event_ts >= NOW() - INTERVAL '30 days'
    GROUP BY user_id
),
order_stats AS (
    -- Предагрегация заказов (убрали correlated subquery)
    SELECT
        user_id,
        COUNT(*) AS orders_30d,
        COALESCE(SUM(amount), 0) AS revenue_30d
    FROM orders
    WHERE created_at >= NOW() - INTERVAL '30 days'
    GROUP BY user_id
)
SELECT
    u.user_id,
    u.name,
    u.registration_date,
    COALESCE(e.sessions_30d, 0),
    COALESCE(e.events_30d, 0),
    e.last_activity,
    COALESCE(o.orders_30d, 0),
    COALESCE(o.revenue_30d, 0)
FROM users u
LEFT JOIN event_stats e ON u.user_id = e.user_id
LEFT JOIN order_stats o ON u.user_id = o.user_id;

Темы

EXPLAIN оптимизация производительность рефакторинг

Подсказки

• Correlated subqueries выполняются для каждой строки — это 10 млн подзапросов
• LEFT JOIN с 2 млрд строк без партиционирования — full scan
• Предагрегируйте events и orders отдельно, затем JOIN

Все тестовые задания →

Частые вопросы

Какой уровень знаний нужен для задачи "Оптимизация тяжёлого ETL-запроса"?

Это задание для уровня hard. Senior-уровень — глубокое понимание темы, опыт решения нестандартных задач, обсуждение trade-off на собеседовании.

На каких собеседованиях встречается такая задача?

Подобные задания в категории «data_engineering» регулярно дают на собеседованиях аналитика данных в Яндекс, Сбер, Ozon, Авито, Тинькофф, Wildberries, T-Bank, X5, ВТБ и других крупных IT-компаниях. Тематика: EXPLAIN, оптимизация, производительность, рефакторинг.

Сколько времени даётся на решение?

На реальном собеседовании на подобную задачу отводится 30-60 минут с обсуждением подходов, оптимизаций и trade-off. Для тренировки рекомендуем сначала решить самостоятельно, потом сверить с эталонным решением и подсказками.

Где ещё потренироваться по теме «data_engineering»?

На zasqlpython.ru есть другие задания в категории «data_engineering», продуктовые кейсы, справочник метрик, AI мок-собеседование с разбором ваших ответов.

← Все задания