SQL для Junior Data Engineers: примеры бизнес-задач

Вход в профессию Data Engineer требует не только владения инструментами для построения данных, но и уверенного знания SQL для решения задач различной сложности. Несмотря на то, что многие SQL-запросы могут казаться «аналитическими», на практике именно Data Engineers часто отвечают за их написание и оптимизацию. Ведь аналитикам и специалистам по продукту требуется быстрый и точный доступ к данным для их анализа, а это означает, что DE должны обеспечить доступ к нужным данным и помочь в создании запросов для обработки больших объемов информации.

SQL в роли Data Engineer и взаимодействие с бизнес-командами

Роль Data Engineer выходит за рамки технической поддержки, поскольку именно эти специалисты часто работают на стыке данных и бизнеса. Они взаимодействуют с аналитиками, маркетологами и другими бизнес-командами, чтобы предоставить точные, агрегированные данные для принятия решений. Data Engineers не только создают пайплайны для передачи данных, но и оптимизируют SQL-запросы, чтобы сделать доступ к данным максимально быстрым и эффективным. Это напрямую влияет на то, как быстро и точно бизнес может реагировать на изменения.

Data Engineers также помогают построить сложные аналитические запросы, которые используются в различных бизнес-кейсах, начиная от маркетинговых кампаний до оптимизации логистики. В следующих разделах представлены задачи, которые регулярно встречаются в практике Data Engineer и соответствуют уровню Junior.

Структура базы данных и описание таблиц

Таблица clients
Описание: Хранит данные о клиентах компании.
Столбцы:

• client_id — идентификатор клиента

• first_name — имя клиента

• last_name — фамилия клиента

Таблица drives
Описание: Хранит данные о поездках.
Столбцы:

• drive_id — идентификатор поездки

• client_id — идентификатор клиента

• start_location — начальная точка поездки

• end_location — конечная точка поездки

• start_time — дата и время начала поездки

• end_time — дата и время окончания поездки

• fare — стоимость поездки

Примеры бизнес-задач

Примеры задач, рассмотренные ниже, охватывают анализ активности клиентов, сегментацию по расходам, построение профилей для маркетинговых стратегий и другие кейсы. Эти SQL-запросы — неотъемлемая часть работы DE, позволяя оптимизировать работу с данными и обеспечивать поддержание бизнес-логики на каждом этапе.

Кейс 1: Анализ активности клиентов для маркетинговой стратегии

Ситуация: Команда маркетинга хочет узнать, какие клиенты были активны в последний месяц, чтобы сформировать целевые предложения и акционные кампании. Активные клиенты — это те, кто совершал поездки за последние 30 дней.

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

Решение: Использование подзапроса для фильтрации client_id, соответствующих последним поездкам, с динамическим фильтром, чтобы отсчитывать последние 30 дней от текущей даты.

SELECT      last_name,      first_name FROM clients WHERE client_id IN (     SELECT DISTINCT client_id     FROM drives     WHERE start_time >= CURRENT_DATE - INTERVAL '30 days' );

Этот запрос позволяет быстро выделить тех, кто активно пользуется услугой, и сосредоточить маркетинговые усилия на актуальной аудитории.

Для оптимизации на больших данных можно использовать:

SELECT DISTINCT      last_name,      first_name FROM clients c WHERE EXISTS (     SELECT 1     FROM drives d     WHERE d.client_id = c.client_id       AND d.start_time >= CURRENT_DATE - INTERVAL '30 days' );

Кейс 2: Сегментация поездок по стоимости для оптимизации предложений

Ситуация: Компания хочет сегментировать клиентов в зависимости от их расходов, чтобы предлагать более выгодные условия клиентам с высоким средним чеком. Клиенты, которые совершают дорогие поездки, могут быть заинтересованы в пакетах премиум-обслуживания.

Задача: Классифицировать поездки по категориям «дорогая» и «дешевая» в зависимости от стоимости, а также вывести место начала поездки для последующего анализа.

Решение: Использование JOIN и CASE, чтобы автоматически классифицировать поездки по стоимости.

SELECT      c.client_id,     c.first_name,      c.last_name,      d.start_location,      d.end_location,     d.fare,     CASE         WHEN d.fare > 3000 THEN 'Дорогая'         ELSE 'Дешевая'     END AS fare_category FROM clients AS c JOIN drives AS d ON c.client_id = d.client_id ORDER BY d.fare DESC;

Кейс 3: Определение VIP-клиентов для улучшенного сервиса

Ситуация: Компания планирует внедрить VIP-программу для клиентов, которые регулярно тратят большие суммы на поездки. Программа позволит улучшить опыт для этих клиентов и увеличить их лояльность.

Задача: Найти всех клиентов, чьи суммарные траты на поездки превышают определенную сумму, например, 5000.

Решение: Использование группировки и HAVING, чтобы фильтровать клиентов на основе их общих затрат.

SELECT      c.first_name,      c.last_name,      SUM(d.fare) AS total_fare FROM clients AS c JOIN drives AS d ON c.client_id = d.client_id GROUP BY c.first_name, c.last_name HAVING SUM(d.fare) > 5000; 

Кейс 4: Логистика — анализ последовательности поездок

Ситуация: Логистическая команда компании хочет понять, в каком порядке клиенты чаще всего используют сервис, чтобы оптимизировать маршруты и предложения.

Задача: Определить порядок поездок для каждого клиента, отсортировав их по времени.

Решение: Использование оконной функции ROW_NUMBER() для нумерации поездок каждого клиента по порядку.

SELECT      c.first_name,      c.last_name,      d.start_location,      d.end_location,      d.start_time,     ROW_NUMBER() OVER(PARTITION BY c.client_id ORDER BY d.start_time) AS trip_number FROM clients AS c JOIN drives AS d ON c.client_id = d.client_id; 

Кейс 5: Выделение поездок с высокой стоимостью для анализа ценообразования

Ситуация: Команда по ценообразованию хочет видеть поездки, которые стоят дороже среднего, чтобы понять, какие маршруты и условия приводят к более высоким затратам.

Задача: Найти поездки, стоимость которых выше среднего.

Решение: Использование подзапроса с AVG, чтобы выбрать поездки выше средней стоимости.

SELECT      d.drive_id,      client_id,      fare FROM drives d WHERE fare > (SELECT AVG(fare) FROM drives);  -- Другой вариант  WITH avg_fare AS (     SELECT AVG(fare) AS avg_fare     FROM drives ) SELECT      d.drive_id,      client_id,      fare FROM drives d, avg_fare WHERE d.fare > avg_fare.avg_fare; 

Кейс 6: Поиск клиентов с большим количеством поездок для лояльности

Ситуация: Маркетинг планирует программу лояльности для активных клиентов. Необходимо определить клиентов, которые часто пользуются услугами.

Задача: Найти клиентов, которые совершили больше поездок, чем среднее количество поездок среди всех клиентов.

Решение: Использование подзапроса и HAVING для вычисления среднего количества поездок и фильтрации клиентов с количеством поездок выше среднего.

SELECT      c.first_name,      c.last_name,      COUNT(d.drive_id) AS num_of_trips FROM clients AS c JOIN drives AS d ON c.client_id = d.client_id GROUP BY c.first_name, c.last_name HAVING COUNT(d.drive_id) > (     SELECT AVG(num_trips)      FROM (SELECT client_id, COUNT(drive_id) AS num_trips FROM drives GROUP BY client_id) AS avg_trips );  -- Вариант с CTE  WITH avg_trips AS (     SELECT AVG(num_trips) AS avg_num_trips     FROM (SELECT client_id, COUNT(drive_id) AS num_trips FROM drives GROUP BY client_id) AS trips ) SELECT      c.first_name,      c.last_name,      COUNT(d.drive_id) AS num_of_trips FROM clients AS c JOIN drives AS d ON c.client_id = d.client_id GROUP BY c.first_name, c.last_name HAVING COUNT(d.drive_id) > (SELECT avg_num_trips FROM avg_trips);  

Кейс 7: Построение профиля клиента для персонализированных предложений

Ситуация: Отдел маркетинга хочет построить профили клиентов для персонализации предложений.

Задача: Получить максимальную стоимость и общую сумму всех поездок каждого клиента.

Решение: Использование оконных функций MAX и SUM для агрегирования данных по каждому клиенту.

SELECT      c.first_name,      c.last_name,     MAX(d.fare) AS max_fare,     SUM(d.fare) AS total_fare FROM clients AS c JOIN drives AS d ON c.client_id = d.client_id GROUP BY c.first_name, c.last_name;

Кейс 8: Сегментация клиентов по региону и фамилии для точечного маркетинга

Ситуация: Отдел маркетинга запускает целевую кампанию в определенном регионе и хочет сегментировать клиентов.

Задача: Найти клиентов из Москвы, чьи фамилии начинаются с буквы «А».

Решение: Использование JOIN с несколькими условиями для фильтрации по местоположению и фамилии.

SELECT        c.first_name,        c.last_name,        d.start_location,        d.end_location,        d.fare FROM clients AS c JOIN drives AS d ON c.client_id = d.client_id WHERE 1 = 1       AND d.start_location = 'Москва'        AND c.last_name LIKE 'А%'; 

Советы по оптимизации запросов

SQL-запросы, используемые Data Engineers, часто требуют оптимизации для работы с большими объемами данных. Для ускорения, например, в кейсе 1, можно создать индекс на столбце start_time таблицы drives:

CREATE INDEX idx_drives_start_time ON drives (start_time);

Другой прием — замена подзапросов на JOIN в случаях, когда это улучшает производительность.

Заключение: SQL как основа для роста Data Engineer

Этот уровень SQL — необходимый минимум для Junior Data Engineers, который проверяется на собеседованиях и является основой в работе. Но для того чтобы развиваться в профессии, нужно совершенствоваться в оптимизации запросов, написании сложных аналитических запросов и разработке ETL-процессов.

Data Engineers, обладающие глубокими знаниями SQL, решают бизнес-задачи и активно влияют на процессы внутри компании, делая её более гибкой и ориентированной на данные.

Если вам интересна тема data engineering, приглашаю заглянуть в мой блог в Telegram, где делюсь рабочими кейсами, примерами задач и опытом в этой области. Контент будет особенно полезен для начинающих, но всегда рад пообщаться и с более опытными инженерами. Надеюсь, что материалы на канале окажутся вам полезными.

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