Спортмастер стажировка: SQL — сумма продаж по магазину 100 за 01.01.2013

Условие

Две таблицы по 5 млн строк:

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

Написать запрос, который посчитает сумму продаж по 100-му магазину за 1 января 2013 года.

Решение

Подход

«Сумма продаж» = SUM(quantity × price). Поскольку справочник цен — история, нужно решить, какую цену брать. Самые вероятные интерпретации:

1. Текущая последняя цена артикула (часто на собеседованиях).
2. Цена на момент продажи (для этого нужна update_date в Prices, которой здесь нет).

Без даты в Prices корректно — взять последнюю цену по артикулу. Если бы интерпретировать «среднюю», это была бы редкая практика.

SQL

WITH last_price AS (
  SELECT art, price,
         ROW_NUMBER() OVER (PARTITION BY art ORDER BY rowid DESC) AS rn  -- или ORDER BY update_date DESC, если есть
  FROM Prices
)
SELECT
  s.shop,
  DATE(s.datetime) AS day,
  SUM(s.quantity * p.price) AS total_sales
FROM Sales s
JOIN last_price p ON p.art = s.art AND p.rn = 1
WHERE s.shop = 100
  AND s.datetime >= TIMESTAMP '2013-01-01 00:00:00'
  AND s.datetime <  TIMESTAMP '2013-01-02 00:00:00'
GROUP BY s.shop, DATE(s.datetime);

Альтернатива (если Prices имеет update_date)

SELECT
  SUM(s.quantity * p.price) AS total_sales
FROM Sales s
JOIN LATERAL (
  SELECT price
  FROM Prices
  WHERE Prices.art = s.art
    AND Prices.update_date <= s.datetime
  ORDER BY Prices.update_date DESC
  LIMIT 1
) p ON TRUE
WHERE s.shop = 100
  AND s.datetime::date = DATE '2013-01-01';

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

1. Возвраты (quantity = -2): SUM(quantity × price) корректно их учтёт как уменьшение выручки.
2. TZ: datetime может быть в UTC, а «1 января» — в МСК. Корректировать.
3. Дубли в Prices без даты: MAX(price) вместо last — некорректно.
4. Производительность 5М строк: индекс на (shop, datetime); на art в Prices.
5. Артикулы без цены — потеряются при INNER JOIN. Если ожидается, что должны быть все, использовать LEFT JOIN + проверить NULL.

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

SUM(quantity * price) с join по art, фильтрацией shop=100 и диапазоном 2013-01-01. Цену брать как последнюю в справочнике (или на дату продажи, если есть update_date).