Условие

Дано две таблицы:

items(item_id, name, price, update_date) — история цен на товары. По одному item_id бывает несколько строк.

orders(order_id, user_id, item_id, order_date).

Написать запросы:

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

Решение

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

«Актуальная» цена — последняя update_date ≤ 2020-06-01.

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

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

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

WITH order_with_price AS (
  SELECT
    o.order_id, o.item_id, o.order_date,
    (SELECT i.price FROM items i
     WHERE i.item_id = o.item_id AND i.update_date <= o.order_date
     ORDER BY i.update_date DESC LIMIT 1) AS price_at_order
  FROM orders o
)
SELECT DISTINCT item_id
FROM order_with_price
WHERE price_at_order >= 3;

Или через JOIN с LATERAL (PostgreSQL):

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

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

Сумма за заказ = цена на момент заказа.

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

4. Сумма всех покупок до 2020-05-01 включительно

WITH priced AS (
  SELECT o.order_id, o.order_date,
    (SELECT i.price FROM items i
     WHERE i.item_id = o.item_id AND i.update_date <= o.order_date
     ORDER BY i.update_date DESC LIMIT 1) AS price
  FROM orders o
  WHERE o.order_date <= DATE '2020-05-01'
)
SELECT SUM(price) AS total
FROM priced;

5. Сумма всех заказов и средняя цена заказа по кварталам

WITH priced AS (
  SELECT o.order_id, o.order_date,
    (SELECT i.price FROM items i
     WHERE i.item_id = o.item_id AND i.update_date <= o.order_date
     ORDER BY i.update_date DESC LIMIT 1) AS price
  FROM orders o
)
SELECT
  EXTRACT(YEAR    FROM order_date)        AS year,
  EXTRACT(QUARTER FROM order_date)        AS qtr,
  SUM(price)                              AS total_revenue,
  AVG(price)                              AS avg_order_price
FROM priced
GROUP BY 1, 2
ORDER BY 1, 2;

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

«Актуальная цена» — это <= дата, не =. Если на запрашиваемую дату нет апдейта, нужна последняя предыдущая.
Цена по order_date — на момент покупки, а не текущая. Иначе при последующих изменениях цен ретроспектива испортится.
update_date в будущем. Если в данных есть будущая цена, фильтр <= обязательно с учётом.
Производительность LATERAL. На больших объёмах подзапрос на каждую строку дорог. В прод-системах хранят prepared prices_history(item_id, valid_from, valid_to, price) (SCD2) и делают range-join.
AVG(price) — это средняя цена позиции. Если в orders хранятся «итоги заказа» (несколько позиций → одна строка), формула не сходится. В этой задаче считаем 1 позицию = 1 строка orders.
Кварталы. В разных СУБД синтаксис разный: EXTRACT(QUARTER ...), DATEPART(qq, ...), QUARTER(...).

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

Везде, где нужна «цена на момент»: подзапрос/LATERAL берёт MAX update_date <= нужной даты. Для квартальной сводки — EXTRACT(QUARTER ...) с GROUP BY year, qtr. Не путать «текущая цена» и «цена на дату заказа».

Магнит SQL — актуальные цены, фильтры по сумме и квартальная сводка