Условие

Задача 1. Дана таблица Sales(datetime, shop, art, quantity) (5 млн строк) и справочник Prices(art, price) (5 млн строк). В Prices у одного артикула может быть несколько цен (исторические, по магазинам).

Написать запрос: сумма продаж по магазину 100 за 1 января 2013 года.

Особенность: Prices имеет дубликаты по art (A1 встречается с разными ценами). Нужно подумать, какую цену брать.

Задача 2. Найти средний оборот на одного клиента и среднюю частоту покупок в течение 1 года с момента первого указания email в личном кабинете. Нас интересуют только те, кто впервые указал email в сентябре 2019.

Таблицы:

table_ident(id_client, ident_type, dat_inserted, ident) — где ident_type ∈ {'Телефон','Email'}.
table_sales(id_client, id_check, sales, channel, dat_check).

Решение

Задача 1

-- Если в Prices последняя цена считается актуальной (предположение), берём её
WITH actual_price AS (
  SELECT art,
         price,
         ROW_NUMBER() OVER (PARTITION BY art ORDER BY price_date DESC) AS rn
  FROM Prices
)
SELECT s.shop,
       SUM(s.quantity * p.price) AS revenue
FROM Sales s
JOIN actual_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;

Важно: в исходных данных нет price_date. Если справочник реально содержит несколько цен без даты, надо уточнить логику. Возможные варианты: max/min/avg; обычно — последняя действующая на дату продажи.

Если Prices имеет дату:

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

Задача 2

WITH first_email AS (
  SELECT id_client,
         MIN(dat_inserted) AS email_first_date
  FROM table_ident
  WHERE ident_type = 'Email'
  GROUP BY id_client
),
sept_clients AS (
  SELECT id_client, email_first_date
  FROM first_email
  WHERE email_first_date BETWEEN DATE '2019-09-01'
                              AND DATE '2019-09-30'
),
year_after AS (
  SELECT s.id_client,
         COUNT(*)        AS purchases,
         SUM(s.sales)    AS revenue
  FROM table_sales s
  JOIN sept_clients sc USING (id_client)
  WHERE s.dat_check >= sc.email_first_date
    AND s.dat_check <  sc.email_first_date + INTERVAL '1 year'
  GROUP BY s.id_client
)
SELECT AVG(revenue)         AS avg_revenue_per_client,
       AVG(purchases)       AS avg_frequency
FROM year_after;

Задача 3 (бонус из xlsx) — пересечение лояльности

70% клиентов любят бонусы, 80% — промокоды. Найти % любящих обоих.

Если события независимы: P(A ∧ B) = 70% × 80% = 56%. Если полностью наложены: P(A ∧ B) = min(70%, 80%) = 70%. Без доп. данных корректный диапазон: P(A ∧ B) ∈ [50%, 70%].

Минимум: P(A) + P(B) − 1 = 70% + 80% − 100% = 50%.

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

Prices с дубликатами: важно понять структуру. Возможно, разные магазины имеют разные цены — тогда join должен включать shop. В задании это не указано — это первый вопрос интервьюеру.
Дата '2013-01-01': использовать datetime field, не =, а BETWEEN. На границе может быть 01-02 00:00:00 — исключите через <, не <=.
Quantity отрицательные (возвраты) — учитывать в продажах? SUM(quantity * price) уже корректно учтёт возвраты как минус.
«Впервые указал email» — может быть, что один и тот же email указан несколько раз; берём MIN(dat_inserted).
AVG(revenue) vs SUM(revenue) / n_clients: если у клиента нет покупок за год — он попадает или нет в делитель? Уточняйте определение «среднего оборота».

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

(1) Join Sales + Prices с разрешением дубликатов цен через ROW_NUMBER (или max/min/avg по бизнес-смыслу), фильтр shop=100, дата=01.01.2013, SUM(qty × price). (2) CTE first_email → sept_clients → year_after с суммой и количеством продаж в окне 1 год от email-указания → финальные AVG. (3) P(A∧B) ∈ [50%, 70%]; точное значение требует доп. данных.

Спортмастер — сумма продаж по магазину за конкретный день и оборот по email-клиенту