Сумма продаж магазина за день с учётом возвратов

С виду простой SUM — а на собеседовании тут две ловушки: отрицательные количества и дубли в справочнике цен, из-за которых сумма завышается.

Задача

Считаем сумму продаж по магазину за день. Таблицы:

• sales(datetime, shop, art, quantity) — продажи; quantity может быть отрицательным (это возврат).
• prices(art, price) — цены без дат; артикул может встречаться несколько раз.

Посчитай сумму продаж по магазину 100 за 1 января 2013 года.

Схема данных

CREATE TABLE sales (datetime timestamp, shop int, art text, quantity int);
CREATE TABLE prices (art text, price numeric);

Обрати внимание: в prices один артикул повторяется, а в sales есть строка с отрицательным quantity (возврат) и продажи других магазинов и дней.

Решение

WITH p AS (
  SELECT art, MAX(price) AS price
  FROM prices
  GROUP BY art
)
SELECT SUM(s.quantity * p.price) AS total_sales
FROM sales s
JOIN p ON p.art = s.art
WHERE s.shop = 100
  AND s.datetime >= '2013-01-01'
  AND s.datetime < '2013-01-02';

Разбор

Две ловушки, ради которых задачу и дают.

Первая — отрицательный quantity. Это возврат, а не «мусор». Если считаем чистые продажи, его нужно учесть со знаком: SUM(quantity * price) сам вычтет возврат из выручки. Выкидывать такие строки как ошибочные нельзя — стоит проговорить с интервьюером, что именно считаем.

Вторая, более коварная — дубли в prices. Артикул встречается несколько раз, и если соединить sales с prices в лоб, каждая строка продажи размножится на число дублей цены, а сумма завысится в разы. Поэтому сначала схлопываю цены до одной на артикул: CTE p с GROUP BY art. Правило выбора цены (MAX, MIN или что-то по бизнесу) стоит согласовать — тут беру MAX. И только потом джойню уже уникальный справочник.

Фильтр по дню — полуинтервалом >= '2013-01-01' AND < '2013-01-02', а не DATE(datetime) = '2013-01-01': функция на колонке убивает индекс. Условие по магазину и дню отсекает чужие продажи и соседний день. На данных песочницы ответ — 570.

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

total_sales
---
570