⚽️🥊 SQL Gym Pro 12- Разбор задачи

Если бы мы решали задачу с помощью подзапросов , у нас бы получился огромный вложенный запрос, в котором через пять минут будет невозможно разобраться. Пишем по-человечески - через CTE.

1. Мы не пытаемся запихнуть фильтрацию по среднему (AVG) в HAVING основного запроса. Мы вынесли это в отдельный блок global_stats.

2. Использование кросс-джойна для подключения константы (среднего значения) к данным.

3. Средний чек мы считаем как SUM(выручки) / SUM(заказов) внутри группы. Новички часто делают AVG(avg_user_check), что математически неверно (ошибка «среднего от средних»).

WITH user_aggregates AS (
    -- Шаг 1: Собираем базу по каждому юзеру
    SELECT 
        user_id,
        SUM(amount) AS total_revenue,
        COUNT(order_id) AS orders_count
    FROM orders
    GROUP BY user_id
),

global_stats AS (
    -- Шаг 2: Вычисляем среднюю выручку по всей базе (одно число)
    SELECT AVG(total_revenue) AS avg_total_revenue
    FROM user_aggregates
),

top_users_categorized AS (
    -- Шаг 3: Фильтруем "топов" и вешаем ярлыки
    SELECT 
        u.user_id,
        u.total_revenue,
        u.orders_count,
        CASE 
            WHEN u.total_revenue > 50000 THEN 'Diamond'
            ELSE 'Gold'
        END AS category
    FROM user_aggregates u
    CROSS JOIN global_stats g -- Приклеиваем среднее значение к каждой строке
    WHERE u.total_revenue > g.avg_total_revenue
)

-- Шаг 4: Финальная агрегация по категориям
SELECT 
    category,
    COUNT(user_id) AS users_count,
    ROUND(SUM(total_revenue) / SUM(orders_count), 2) AS avg_order_value
FROM top_users_categorized
GROUP BY category;

Признавайтесь, кто по привычке потянулся писать WHERE total_spent > (SELECT AVG...)? 😉

На самом деле, это не смертельно, но на больших данных ваш сервак скажет досвидос!