Практический проект: аналитический отчёт

Собираем всё вместе в реальную аналитическую задачу.

Постановка задачи

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

1. Общая выручка и количество заказов за последний месяц
2. Топ-5 товаров по выручке
3. Выручка по регионам
4. Клиенты без покупок за 90 дней (группа риска)
5. Динамика выручки по неделям

Запрос 1: Общая статистика

SELECT
    COUNT(*)                          AS total_orders,
    COUNT(DISTINCT customer_id)       AS unique_customers,
    SUM(amount)                       AS total_revenue,
    ROUND(AVG(amount), 0)             AS avg_order,
    MAX(amount)                       AS max_order
FROM orders
WHERE created_at >= DATE_TRUNC('month', CURRENT_DATE)
  AND status = 'completed';

Запрос 2: Топ-5 товаров

SELECT
    p.name          AS product,
    COUNT(*)        AS orders_count,
    SUM(oi.price * oi.quantity) AS revenue
FROM order_items oi
JOIN products p ON oi.product_id = p.id
JOIN orders o   ON oi.order_id = o.id
WHERE o.created_at >= DATE_TRUNC('month', CURRENT_DATE)
  AND o.status = 'completed'
GROUP BY p.id, p.name
ORDER BY revenue DESC
LIMIT 5;

Запрос 3: Выручка по регионам

SELECT
    c.city                           AS region,
    COUNT(DISTINCT o.customer_id)    AS customers,
    SUM(o.amount)                    AS revenue,
    ROUND(AVG(o.amount), 0)          AS avg_order
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.created_at >= DATE_TRUNC('month', CURRENT_DATE)
  AND o.status = 'completed'
GROUP BY c.city
ORDER BY revenue DESC;

Запрос 4: Клиенты группы риска (не покупали 90 дней)

WITH last_orders AS (
    SELECT
        customer_id,
        MAX(created_at) AS last_order_date
    FROM orders
    WHERE status = 'completed'
    GROUP BY customer_id
)
SELECT
    c.name,
    c.email,
    lo.last_order_date,
    CURRENT_DATE - lo.last_order_date::date AS days_inactive
FROM customers c
JOIN last_orders lo ON c.id = lo.customer_id
WHERE lo.last_order_date < CURRENT_DATE - INTERVAL '90 days'
  AND c.is_active = true
ORDER BY days_inactive DESC;

Запрос 5: Динамика по неделям

SELECT
    DATE_TRUNC('week', created_at)::date    AS week_start,
    COUNT(*)                                AS orders,
    SUM(amount)                             AS revenue,
    SUM(SUM(amount)) OVER (ORDER BY DATE_TRUNC('week', created_at)) AS cumulative
FROM orders
WHERE created_at >= CURRENT_DATE - INTERVAL '3 months'
  AND status = 'completed'
GROUP BY DATE_TRUNC('week', created_at)
ORDER BY week_start;

Здесь используются GROUP BY, CTE, оконная функция и фильтрация по дате, всё что мы изучили.

💡 Задание: Попробуй адаптировать эти запросы под свои данные. Любой отчёт можно разложить на такие же блоки.