Заказы, где товар куплен по цене от 3 и выше

Задача с собеседования, на которой заваливается наивный JOIN. Цена товара версионная, и если соединить таблицы в лоб, строки размножатся, а сумма поедет.

Задача

В магазине цена товара меняется во времени — таблица items хранит историю (каждая строка это цена товара с определённой даты):

item_id | name          | price | update_date
3       | Ластик        | 7     | 2020-04-01
1       | Ручка гелевая | 10    | 2020-02-01
2       | Карандаш 1HH  | 3     | 2020-01-01
1       | Ручка гелевая | 10    | 2020-03-01
3       | Ластик        | 5     | 2020-07-01
2       | Карандаш 1HH  | 2     | 2020-04-01
1       | Ручка гелевая | 5     | 2020-05-01
2       | Карандаш 1HH  | 7     | 2020-06-01

Есть таблица заказов orders(order_id, user_id, item_id, order_date). Выведи заказы, в которых товар был куплен по цене 3 и выше. Важно: цена покупки — та, что действовала на дату заказа, а не любая строка items.

Схема данных

CREATE TABLE items (item_id int, name text, price numeric, update_date date);
CREATE TABLE orders (order_id int, user_id int, item_id int, order_date date);

У одного товара несколько версий цены. «Цена на дату заказа» — это последняя запись items с update_date <= order_date.

Решение

SELECT o.order_id, o.user_id, i.name, i.price, to_char(o.order_date, 'YYYY-MM-DD') AS order_date
FROM orders o
JOIN LATERAL (
  SELECT it.name, it.price
  FROM items it
  WHERE it.item_id = o.item_id
    AND it.update_date <= o.order_date
  ORDER BY it.update_date DESC
  LIMIT 1
) i ON TRUE
WHERE i.price >= 3
ORDER BY o.order_id;

Разбор

Соблазн — просто JOIN orders o JOIN items i ON i.item_id = o.item_id. Но у товара несколько строк цены, и такой джойн приклеит к каждому заказу все версии цены разом: одна строка заказа превратится в несколько, и любая последующая сумма будет завышена. Про сам эффект размножения — в вопросе может ли JOIN вернуть больше строк.

Правильно — для каждого заказа взять ровно одну цену: последнюю, что действовала на order_date. В Postgres это красиво делает JOIN LATERAL: подзапрос справа видит текущую строку заказа (o.item_id, o.order_date) и для неё выбирает top-1 — WHERE update_date <= order_date ORDER BY update_date DESC LIMIT 1. То есть «самая свежая цена, не позже даты заказа». ON TRUE тут — просто формальность: вся логика соединения уже внутри подзапроса.

Дальше WHERE i.price >= 3 отсекает дешёвые покупки. На данных песочницы отсеивается заказ на Карандаш с более поздней датой: на тот момент действовала цена 2 (от 2020-04-01), она ниже порога, а строка с более ранней датой заказа отфильтруется. Остальные заказы проходят.

Тот же результат даёт ROW_NUMBER() OVER (PARTITION BY o.order_id ORDER BY it.update_date DESC) по джойну заказов с историей цен и фильтром update_date <= order_date, затем rn = 1. В SELECT дату заказа оборачиваю в to_char(o.order_date, 'YYYY-MM-DD') — только для читаемого вывода, вся логика подбора цены работает с исходной колонкой order_date.

Ожидаемый результат

order_id | user_id | name | price | order_date
--- | --- | --- | --- | ---
1 | 1 | Ручка гелевая | 10 | 2020-02-01
3 | 1 | Ластик | 5 | 2020-07-01
4 | 3 | Карандаш 1HH | 7 | 2020-07-01
5 | 2 | Ручка гелевая | 10 | 2020-04-01
6 | 1 | Ручка гелевая | 5 | 2020-06-01