Портфолио аналитика
Главная / SQL Stroy Analytics / sql-tasks-and-approach

SQL-задачи и подход к решению

Описание

В итоговом проекте по базе данных stroy было необходимо подготовить единый SQL-файл с решениями 10 задач.

Задачи охватывают разные уровни сложности:

– простые агрегаты и фильтрацию;

– расчёты с датами;

– работу с несколькими связанными таблицами;

– подзапросы и исключающую логику;

– расчёт бонусов и агрегатов по группам;

– оконные функции;

– рекурсивные CTE;

– материализованные представления.

В этом документе описана логика решения каждой задачи и ключевые SQL-приёмы, которые использовались.

Задача 1. Количество проектов, подписанных в 2023 году

Суть задачи

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

Подход

Используется таблица project, поле sign\_date и агрегатная функция count.

Фильтрация выполняется по году подписания проекта.

Ключевые элементы решения

– count(\*);

– фильтрация по дате подписания;

– работа только с таблицей project.

Что демонстрирует

– базовую агрегацию;

– корректную фильтрацию по временным условиям;

– умение выбрать минимально необходимый набор данных.

Задача 2. Общий возраст сотрудников, нанятых в 2022 году

Суть задачи

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

Результат должен быть представлен интервальным значением вида:


... years ... mons ... days

Подход

Для расчёта возраста используются:

– дата рождения из person;

– дата текущего дня;

– функция age.

После этого интервальные значения суммируются.

Сотрудники отбираются по дате найма из employee.

Ключевые элементы решения

– соединение employee и person;

– age(current\_date, birthdate);

– sum по интервальным значениям;

– фильтрация по интервалу дат найма.

Что демонстрирует

– работу с типами даты и интервалами;

– выполнение ограничения по количеству функций для дат;

– использование связки кадровых и персональных данных.

Задача 3. Сотрудник с фамилией по шаблону и максимальным сроком работы

Суть задачи

Нужно найти сотрудника:

– фамилия начинается на букву М;

– в фамилии ровно 8 букв;

– сотрудник работает дольше остальных среди подходящих.

Если таких сотрудников несколько, разрешается вывести одного случайного.

Подход

Используется таблица person для фильтрации фамилии и таблица employee для даты найма.

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

При совпадении даты найма применяется случайная сортировка.

Ключевые элементы решения

– like 'М%';

– length(last\_name) = 8;

– сортировка по hire\_date;

– random() для выбора случайного сотрудника при совпадении;

– limit 1.

Что демонстрирует

– строковую фильтрацию;

– понимание логики «работает дольше» через дату найма;

– контроль количества возвращаемых строк.

Задача 4. Среднее количество полных лет уволенных сотрудников, не задействованных на проектах

Суть задачи

Нужно вычислить средний возраст в полных годах для сотрудников, которые:

– уволены;

– не участвуют ни в одном проекте;

– не являются руководителями проектов.

Если подходящих сотрудников нет, нужно вернуть 0.

Подход

Используется исключающая логика через not exists.

Проверяются два варианта участия сотрудника в проекте:

– сотрудник указан в массиве employees\_id;

– сотрудник указан как project\_manager\_id.

Полные годы рассчитываются через возраст, извлечённый в годах.

Ключевые элементы решения

– dismissal\_date is not null;

– not exists;

– employee\_id = any(project.employees\_id);

– проверка на руководителя проекта;

– extract(year from age(...));

– avg;

– coalesce.

Что демонстрирует

– сложную фильтрацию по отсутствию связи;

– работу с массивом идентификаторов;

– защиту результата от null;

– корректную агрегацию после исключения неподходящих сотрудников.

Задача 5. Сумма полученных платежей от контрагентов из Жуковского, Россия

Суть задачи

Нужно вычислить сумму фактически полученных платежей от контрагентов, находящихся в городе Жуковский, Россия.

Подход

Запрос объединяет цепочку сущностей:


project\_payment → project → customer → address → city → country

Учитываются только фактические платежи, для которых заполнено поле fact\_transaction\_timestamp.

Ключевые элементы решения

– несколько join;

– фильтрация по городу и стране;

– проверка фактического поступления платежа;

– sum(amount);

– coalesce.

Что демонстрирует

– умение проходить через несколько уровней связей;

– различение плановых и фактических платежей;

– формирование финансового агрегата.

Задача 6. Руководитель проекта с максимальным бонусом

Суть задачи

Руководитель проекта получает премию в размере 1% от стоимости завершённых проектов.

Нужно определить руководителя или руководителей с максимальным размером бонуса.

Подход

Сначала формируется CTE, в котором:

– отбираются завершённые проекты;

– группируются проекты по руководителю;

– суммируется стоимость проектов;

– рассчитывается бонус.

Затем выбираются строки, в которых бонус равен максимальному бонусу среди всех руководителей.

Ключевые элементы решения

– with manager\_bonus as (...);

– фильтр status = 'Завершен';

– sum(project\_cost) \* 0.01;

– группировка по руководителю;

– подзапрос с max(bonus).

Что демонстрирует

– работу с CTE;

– расчёт производного показателя;

– обработку ситуации с несколькими лидерами;

– соединение проектов, сотрудников и физических лиц.

Задача 7. Дата превышения накопительного итога авансовых платежей

Суть задачи

Нужно рассчитать накопительный итог планируемых авансовых платежей внутри каждого месяца и найти дату, на которой сумма впервые превысила 30 000 000.

Подход

Решение разбито на два CTE:

  1. рассчитывается накопительная сумма авансовых платежей внутри каждого месяца;

  2. через lag определяется предыдущее значение накопления.

В итог выводятся только те строки, где:

– текущее накопление уже больше 30 000 000;

– предыдущее накопление ещё не превышало 30 000 000.

Ключевые элементы решения

– sum(amount) over (...);

– partition by date\_trunc('month', plan\_payment\_date);

– порядок по дате и идентификатору платежа;

– lag(accumulation) over (...);

– фильтр на переход через порог.

Что демонстрирует

– расчёт накопительного итога;

– корректную фиксацию первого момента превышения порога;

– использование нескольких оконных функций в связанной логике.

Задача 8. Сумма окладов сотрудников подразделения и дочерних подразделений

Суть задачи

Нужно посчитать сумму фактических окладов сотрудников структурного подразделения с unit\_id = 17 и всех его дочерних подразделений.

Подход

Используется рекурсивный CTE:

– базовая часть выбирает подразделение 17;

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

После этого выбираются должности, относящиеся к найденным подразделениям, и рассчитывается сумма фактических окладов сотрудников.

Фактический оклад считается как:


salary × rate

Ключевые элементы решения

– with recursive;

– таблица company\_structure;

– связь через parent\_id;

– position;

– employee\_position;

– sum(salary \* rate).

Что демонстрирует

– обход иерархических данных;

– понимание структуры организации;

– применение рекурсии в аналитическом запросе.

Задача 9. Комплексный аналитический запрос с нумерацией платежей и скользящим средним

Суть задачи

В одном запросе необходимо:

  1. пронумеровать фактические платежи отдельно внутри каждого года;

  2. отобрать платежи, номер которых кратен 5;

  3. посчитать скользящее среднее платежей с окном 2 строки до и 2 строки после;

  4. получить сумму скользящих средних;

  5. рассчитать сумму стоимости проектов по годам;

  6. вывести годы, где сумма стоимости проектов меньше суммы скользящих средних.

Подход

Решение строится через несколько CTE:

– numbered\_payments;

– fifth\_payments;

– moving\_avg\_payments;

– moving\_avg\_sum;

– project\_sum\_by\_year.

Каждый CTE отвечает за отдельный этап вычислений.

Ключевые элементы решения

– row\_number() over (...);

– деление номера строки по модулю % 5 = 0;

– avg(amount) over (...);

– rows between 2 preceding and 2 following;

– суммирование результатов оконного расчёта;

– годовая агрегация стоимости проектов;

– сравнение агрегированных значений.

Что демонстрирует

– умение декомпозировать сложный запрос;

– работу с несколькими уровнями аналитических преобразований;

– использование оконных функций и CTE в одном решении;

– внимательность к требованиям «один запрос».

Задача 10. Материализованное представление с отчётом по проектам

Суть задачи

Нужно создать материализованное представление, которое хранит отчёт со следующими полями:

– идентификатор проекта;

– название проекта;

– дата последней фактической оплаты;

– сумма последней фактической оплаты;

– ФИО руководителя проекта;

– название контрагента;

– типы работ контрагента одной строкой.

Подход

Решение использует два CTE:

  1. last\_project\_payment — определяет последний фактический платёж по каждому проекту через row\_number;

  2. customer\_work\_types — агрегирует типы работ контрагента в одну строку через string\_agg.

Далее данные объединяются с проектом, руководителем и контрагентом в итоговом запросе create materialized view.

Ключевые элементы решения

– drop materialized view if exists;

– create materialized view;

– row\_number() для выбора последней оплаты;

– string\_agg;

– left join для необязательных данных;

– объединение проектной, финансовой и клиентской информации.

Что демонстрирует

– формирование отчётной витрины;

– подготовку результата для повторного использования;

– соединение нескольких аналитических подзадач в одном объекте БД;

– работу с материализованными представлениями.

Общая логика проекта

Все запросы были построены с учетом требований задания:

– возвращать ровно тот формат результата, который был указан;

– избегать необоснованного использования лишних таблиц;

– соблюдать условия по функциям и логике;

– использовать запросы, читаемые и пригодные для проверки;

– декомпозировать сложные расчёты через CTE;

– обеспечивать корректность расчётов для финансовых и кадровых показателей.