Условие

Даны таблицы:

items(item_id, name, price, update_date) — история цен на товары.
orders(order_id, user_id, item_id, order_date).

Задачи (5 штук):

Актуальное состояние товаров на 2020-06-01.
Товары, купленные по цене ≥ 3.
Сумма покупок клиента 1.
Сумма всех покупок до 2020-05-01 включительно.
Сумма всех заказов и средняя цена заказа поквартально.

Решение

1. Актуальное состояние на 2020-06-01

«Актуальная» цена = последняя update_date <= 2020-06-01 для каждого товара.

WITH last_price AS (
  SELECT item_id, name, price, update_date,
         ROW_NUMBER() OVER (PARTITION BY item_id ORDER BY update_date DESC) AS rn
  FROM items
  WHERE update_date <= DATE '2020-06-01'
)
SELECT item_id, name, price
FROM last_price
WHERE rn = 1;

2. Товары, купленные по цене ≥ 3

«Цена покупки» = цена, актуальная на дату заказа.

SELECT DISTINCT o.order_id, o.item_id, i.name, p.price
FROM orders o
JOIN LATERAL (
  SELECT name, price
  FROM items
  WHERE items.item_id = o.item_id AND items.update_date <= o.order_date
  ORDER BY update_date DESC LIMIT 1
) p ON TRUE
JOIN items i ON i.item_id = o.item_id AND i.update_date = (
  SELECT MAX(update_date) FROM items WHERE items.item_id = o.item_id
                                      AND update_date <= o.order_date
)
WHERE p.price >= 3;

3. Сумма покупок клиента 1

WITH price_at AS (
  SELECT o.order_id, o.user_id,
    (SELECT price FROM items
     WHERE items.item_id = o.item_id AND items.update_date <= o.order_date
     ORDER BY update_date DESC LIMIT 1) AS price
  FROM orders o
)
SELECT user_id, SUM(price) AS total
FROM price_at
WHERE user_id = 1
GROUP BY user_id;

4. Сумма покупок до 2020-05-01

SELECT SUM(p.price) AS total
FROM orders o
JOIN LATERAL (
  SELECT price FROM items
  WHERE items.item_id = o.item_id AND update_date <= o.order_date
  ORDER BY update_date DESC LIMIT 1
) p ON TRUE
WHERE o.order_date <= DATE '2020-05-01';

5. Сумма заказов и средний чек поквартально

WITH order_price AS (
  SELECT o.*, (SELECT price FROM items
               WHERE items.item_id = o.item_id AND update_date <= o.order_date
               ORDER BY update_date DESC LIMIT 1) AS price
  FROM orders o
)
SELECT
  EXTRACT(YEAR  FROM order_date) AS y,
  EXTRACT(QUARTER FROM order_date) AS q,
  SUM(price) AS total,
  AVG(price) AS aov
FROM order_price
GROUP BY y, q
ORDER BY y, q;

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

У товара несколько обновлений цены — нужна именно цена на дату заказа, не последняя.
Если товар обновляется до даты заказа в этот же день → используйте <= (включительно).
EXTRACT(QUARTER...) поддерживается не везде; альтернатива — DATE_TRUNC('quarter', order_date).
Без обновления цены до даты заказа LATERAL вернёт NULL → пропуск товара. Решить через COALESCE со средней или предупреждением.

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

Везде ключевой паттерн — взять price из items на момент order_date (LATERAL/ROW_NUMBER). Поквартально — EXTRACT(YEAR), EXTRACT(QUARTER) + SUM/AVG.

Магнит BI: SQL — сумма заказов и средний чек поквартально