Практика: Оконные функции

Онлайн редактор кода для PostgreSQL

Здесь вы можете попробовать свои силы в программировании на языке SQL для работы с PostgreSQL. Для этого мы предоставляем вам онлайн редактор кода, в котором вы можете написать свой код и запустить его.

❗️ При обновлении страницы код пропадёт, по этому, сохраните свой код куда-нибудь, если он важный.

Задание 1 — Ранжирование (ROW_NUMBER, RANK, DENSE_RANK)

Задание 1: Ранжирование и топ-N в группах

⏱️ Примерное время: 15-25 минут

Используй ROW_NUMBER, RANK, DENSE_RANK для нумерации и выбора топ-N.

Требования:

• минимум 2 разных ранжирующих функции
• хотя бы один запрос с PARTITION BY
• хотя бы один топ-N (WHERE rank ≤ 3 / row_number ≤ 10)
• красивое оформление результата (имя, сумма, ранг)

Пример:

WITH ranked_products AS (
    SELECT
        p.category,
        p.product_name,
        SUM(s.quantity * s.unit_price) AS revenue,
        RANK() OVER (PARTITION BY p.category ORDER BY SUM(s.quantity * s.unit_price) DESC) AS rank
    FROM products p
    LEFT JOIN sales s ON p.product_id = s.product_id
    GROUP BY p.category, p.product_name
)
SELECT * FROM ranked_products
WHERE rank <= 3
ORDER BY category, rank;

Задание 2 — Смещение (LAG, LEAD, FIRST_VALUE, LAST_VALUE)

Задание 2: Сравнение с предыдущими/следующими строками

⏱️ Примерное время: 20-35 минут

Используй LAG/LEAD для расчёта изменений, разниц, трендов.

Требования:

• минимум LAG или LEAD
• расчёт процентного изменения или разницы
• обработка NULL (COALESCE, NULLIF)
• хотя бы один запрос с FIRST_VALUE / LAST_VALUE

Пример:

SELECT
    sale_date,
    daily_revenue,
    LAG(daily_revenue) OVER (ORDER BY sale_date) AS prev_day,
    daily_revenue - LAG(daily_revenue) OVER (ORDER BY sale_date) AS delta,
    ROUND(
        (daily_revenue - LAG(daily_revenue) OVER (ORDER BY sale_date)) /
        NULLIF(LAG(daily_revenue) OVER (ORDER BY sale_date), 0) * 100,
        2
    ) AS pct_change
FROM (
    SELECT sale_date, SUM(quantity * unit_price) AS daily_revenue
    FROM sales
    GROUP BY sale_date
) t
ORDER BY sale_date;

Задание 3 — Накопительные суммы и скользящие средние

Задание 3: Running totals, moving averages, кумулятивные показатели

⏱️ Примерное время: 25-40 минут

Работа с накопительными суммами и скользящими окнами.

Требования:

• SUM() / AVG() OVER с ORDER BY
• хотя бы одно скользящее окно (ROWS BETWEEN … PRECEDING AND CURRENT ROW)
• красивое округление + % где уместно

Пример:

SELECT
    sale_date,
    daily_revenue,
    SUM(daily_revenue) OVER (ORDER BY sale_date) AS running_total,
    ROUND(
        SUM(daily_revenue) OVER (ORDER BY sale_date) /
        SUM(daily_revenue) OVER () * 100,
        2
    ) AS cumulative_pct
FROM (
    SELECT sale_date, SUM(quantity * unit_price) AS daily_revenue
    FROM sales
    GROUP BY sale_date
) t
ORDER BY sale_date;

Задание 4 — Комбинированные аналитические запросы

Задание 4: Комбинированные оконные функции

⏱️ Примерное время: 35-60 минут

Смешивай ранжирование, смещение и агрегаты в одном запросе.

Требования:

• минимум 3 разных оконных функции в одном запросе
• CTE для промежуточных вычислений
• осмысленные имена столбцов
• сортировка и LIMIT где нужно

Пример структуры:

WITH monthly AS (
    SELECT DATE_TRUNC('month', sale_date) AS month, ...
),
ranked AS (
    SELECT ..., RANK() OVER (...) AS rank, LAG(...) OVER (...)
    FROM monthly
)
SELECT ..., moving_avg, pct_change
FROM ranked
ORDER BY month DESC, rank;

Задание 5 — Итоговый аналитический дашборд

Задание 5: Полноценный аналитический отчёт (итоговое)

⏱️ Примерное время: 60-120 минут

Создай отчёт уровня BI-дашборда с использованием только оконных функций.

Требования:

• 5–8 разных оконных функций
• несколько CTE
• комбинация ранжирования + смещения + running / moving
• красивое форматирование (ROUND, TO_CHAR, CASE для меток)
• можно добавить условное форматирование (🥇 🥈 🥉, ▲ ▼)
• результат должен быть понятен менеджеру без глубокого знания SQL

Это задание — отличный кандидат для портфолио или демонстрации навыков аналитического SQL.

подсказка

Оконные функции — это когда ты начинаешь думать как аналитик, а не как агрегатор.
Главные секреты успеха:

1. Всегда думай про PARTITION BY и ORDER BY
2. Для скользящих окон обязательно указывай frame (ROWS / RANGE)
3. Комбинируй несколько оконных функций в одном SELECT — это магия
4. CTE делает код читаемым — не бойся их использовать