✅ Решения

📁 Блок: SQL / MySQL / Оконные функции ⏱️ Время изучения: ~90 мин 🎯 Сложность: Практикум

⚡ Кратко: решения

Практикум 10 закрепляет оконные функции ранжирования, смещения и выбора на таблице purchase_order_details.

Ключевые конструкции: LAG, LEAD, FIRST_VALUE, DENSE_RANK, ROW_NUMBER, CTE.

Минимальный пример: LAG(date_received) OVER (PARTITION BY product_id ORDER BY date_received) показывает предыдущую дату получения продукта.

Практический фокус — сначала получить аналитический столбец, а затем фильтровать результат через CTE или подзапрос.

Топ-3 ошибки: не задать сортировку для LAG/LEAD, фильтровать ROW_NUMBER в том же WHERE, забыть убрать строки с пустым date_received.

✅ Решения и разбор

Задание 1

SELECT
    product_id,
    date_received,
    LAG(date_received) OVER (
        PARTITION BY product_id
        ORDER BY date_received
    ) AS previous_date_received,
    LEAD(date_received) OVER (
        PARTITION BY product_id
        ORDER BY date_received
    ) AS next_date_received
FROM purchase_order_details
WHERE date_received IS NOT NULL;

Добавляем ORDER BY date_received, чтобы предыдущая и следующая даты были календарными соседями.

Задание 2

WITH received_orders AS (
    SELECT DISTINCT
        purchase_order_id,
        date_received
    FROM purchase_order_details
    WHERE date_received IS NOT NULL
)
SELECT
    purchase_order_id,
    date_received,
    LAG(date_received) OVER (
        ORDER BY date_received
    ) AS previous_date_received
FROM received_orders;

CTE убирает повторяющиеся строки деталей заказа и оставляет одну дату на заказ.

Задание 3

SELECT
    inventory_id,
    quantity,
    unit_cost,
    FIRST_VALUE(quantity) OVER (
        PARTITION BY inventory_id
        ORDER BY quantity DESC
    ) AS max_quantity,
    FIRST_VALUE(unit_cost) OVER (
        PARTITION BY inventory_id
        ORDER BY unit_cost ASC
    ) AS min_unit_cost
FROM purchase_order_details;

Максимум получается сортировкой по убыванию, минимум — сортировкой по возрастанию.

Задание 4

WITH costs AS (
    SELECT
        inventory_id,
        unit_cost,
        FIRST_VALUE(unit_cost) OVER (
            PARTITION BY inventory_id
            ORDER BY unit_cost DESC
        ) AS max_unit_cost
    FROM purchase_order_details
)
SELECT AVG(max_unit_cost - unit_cost) AS avg_cost_diff
FROM costs;

Разница считается как максимум минус текущая цена, поэтому среднее отличие получается неотрицательным.

Задание 5

WITH ranked_products AS (
    SELECT
        product_id,
        quantity,
        DENSE_RANK() OVER (
            ORDER BY quantity DESC
        ) AS quantity_rank
    FROM purchase_order_details
)
SELECT *
FROM ranked_products
WHERE quantity_rank <= 5;

Фильтр по рангу вынесен во внешний запрос.

Задание 6

WITH numbered_rows AS (
    SELECT
        *,
        ROW_NUMBER() OVER (
            ORDER BY inventory_id DESC
        ) AS row_num
    FROM purchase_order_details
)
SELECT *
FROM numbered_rows
WHERE row_num = 13;

ROW_NUMBER даёт уникальную позицию строки после сортировки.