SQLОконные функцииMediumБесплатно

Сравнение заказа с предыдущим

// Условие

Для каждого завершённого заказа вычислите разницу суммы с предыдущим завершённым заказом того же пользователя. Выведите user_id, order_date, amount, prev_amount и diff (amount - prev_amount). Только строки, где prev_amount не NULL (т.е. есть предыдущий заказ). Первые 20 строк по order_date.

// Таблицы

sandbox.orders
order_idINT
user_idINT
order_dateTIMESTAMP
amountNUMERIC(10,2)
statusVARCHAR(20)
delivery_daysINT
promo_codeVARCHAR(20)

// Что тренирует

LAGPARTITION BYWindow functions
▶ Решить в SQL-песочнице

// Решение

Подсказка

LAG(amount) OVER (PARTITION BY user_id ORDER BY order_date) — получает значение из предыдущей строки.

Сначала попробуй сам → Показать решение 
WITH with_prev AS (
  SELECT
    user_id,
    order_date,
    amount,
    LAG(amount) OVER (
      PARTITION BY user_id ORDER BY order_date
    ) AS prev_amount
  FROM sandbox.orders
  WHERE status = 'completed'
)
SELECT
  user_id,
  order_date,
  amount,
  prev_amount,
  amount - prev_amount AS diff
FROM with_prev
WHERE prev_amount IS NOT NULL
ORDER BY order_date
LIMIT 20;

LAG() — функция «предыдущее значение». PARTITION BY user_id значит, что каждый пользователь рассматривается отдельно: LAG не «залезет» в заказы другого пользователя. WHERE prev_amount IS NOT NULL отсекает первые заказы каждого пользователя (у них нет предыдущего). Альтернатива: LEAD() для «следующего» значения.

// Похожие задачи

// Разобраться в теме

→ Оконные функции SQL