Условие

Таблицы те же, что в стажировочной задаче (orders, warehouses).

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

На выходе: name (склад), city, avg_order_warehouse, avg_order_city.

Решение

Подход

Считаем avg(paid_amount) на уровне склада. Через оконку OVER (PARTITION BY city) — средний по всему городу. Потом фильтр и ORDER BY name.

Реализация

WITH last14 AS (
  SELECT
    o.warehouse_id,
    AVG(o.paid_amount) AS avg_order
  FROM orders o
  WHERE o.date >= CURRENT_DATE - INTERVAL '14 day'
  GROUP BY o.warehouse_id
),
joined AS (
  SELECT
    w.name,
    w.city,
    l.avg_order                                                      AS avg_order_warehouse,
    AVG(l.avg_order) OVER (PARTITION BY w.city)                      AS avg_order_city_simple,
    -- более честно: средний заказ ВСЕХ заказов в городе, а не среднее средних
    SUM(SUM(o.paid_amount)) OVER (PARTITION BY w.city)
      / NULLIF(SUM(COUNT(o.paid_amount)) OVER (PARTITION BY w.city), 0) AS avg_order_city
  FROM last14 l
  JOIN warehouses w USING (warehouse_id)
  LEFT JOIN orders o
    ON o.warehouse_id = w.warehouse_id
   AND o.date >= CURRENT_DATE - INTERVAL '14 day'
  GROUP BY w.warehouse_id, w.name, w.city, l.avg_order
)
SELECT name, city, avg_order_warehouse, avg_order_city
FROM joined
WHERE avg_order_warehouse > avg_order_city
ORDER BY name;

Простая версия

Если допустимо считать «среднее по городу = среднее средних по складам» (микро-отличие от взвешенного):

WITH wh AS (
  SELECT
    w.warehouse_id, w.name, w.city,
    AVG(o.paid_amount) AS avg_order_wh
  FROM warehouses w
  LEFT JOIN orders o
    ON o.warehouse_id = w.warehouse_id
   AND o.date >= CURRENT_DATE - INTERVAL '14 day'
  GROUP BY w.warehouse_id, w.name, w.city
)
SELECT name, city, avg_order_wh, avg_order_city
FROM (
  SELECT *,
    AVG(avg_order_wh) OVER (PARTITION BY city) AS avg_order_city
  FROM wh
) t
WHERE avg_order_wh > avg_order_city
ORDER BY name;

Разница «среднее средних» vs взвешенное среднее

Допустим, в городе:

склад A: 10 заказов по 1000.
склад B: 100 заказов по 500.

«Среднее средних» = (1000 + 500) / 2 = 750. Взвешенное (по числу заказов) = (10·1000 + 100·500) / 110 ≈ 545. На собесе обязательно проговорить разницу.

Подводные камни

«Среднее по городу» — в задаче не уточнено, какое именно. По умолчанию правильнее взвешенное (среднее по всем заказам города), но «среднее средних» проще выразить.
Склады без заказов за 14 дней. В простой версии LEFT JOIN оставит их с avg_order_wh = NULL — фильтр > avg_city отбросит. Это нормально.
COUNT(o.paid_amount) игнорирует NULL — корректно для пустых заказов.
«В алфавитном порядке». Локаль БД важна: ORDER BY name для кириллицы зависит от COLLATION.
Мульти-городные склады. Бывает редко, но в реальной БД проверьте, что у склада ровно один city.
Часовой пояс при INTERVAL '14 day'. Если o.date — таймстамп, граница может «съехать». Использовать CAST(o.date AS DATE) для безопасности.

Эталонный ответ

AVG(paid_amount) GROUP BY warehouse_id за 14 дней → оконка AVG(...) OVER (PARTITION BY city) (для среднего средних) или взвешенное → фильтр wh > city, ORDER BY name. Уточните у заказчика, какое «среднее по городу» нужно.

Самокат SQL — склады со средним заказом выше городского за 14 дней