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

bef3fdfb2ddaa64c5cde2bb77595ba05.png

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

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

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

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

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

d1a9c56f0266ca9fd02c9fcf1d659a8d.png

Таблица 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 DISTINCT 
    last_name, 
    first_name
FROM clients
WHERE client_id IN (
    SELECT DISTINCT client_id
    FROM drives
    WHERE start_time >= CURRENT_DATE - INTERVAL '30 days'
);

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

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

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

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

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

SELECT 
       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;

Кейс 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);

Кейс 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
);

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

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

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

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

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

Кейс 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, где делюсь рабочими кейсами, примерами задач и опытом в этой области. Контент будет особенно полезен для начинающих, но всегда рад пообщаться и с более опытными инженерами. Надеюсь, что материалы на канале окажутся вам полезными.

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

© Habrahabr.ru