LAG and LEAD — Comparing Current Row With Previous/Next

-- LAG: Get the previous day's revenue alongside today's
SELECT
  order_date,
  SUM(total_amount) AS daily_revenue,
  LAG(SUM(total_amount)) OVER(ORDER BY order_date) AS prev_day_revenue
FROM orders
GROUP BY order_date
ORDER BY order_date;

-- Result:
-- order_date  | daily_revenue | prev_day_revenue
-- 2024-01-01  | 15000         | NULL  (no previous day)
-- 2024-01-02  | 18000         | 15000
-- 2024-01-03  | 12000         | 18000
-- 2024-01-04  | 22000         | 12000

-- LEAD: See the next day's revenue
SELECT
  order_date,
  SUM(total_amount) AS daily_revenue,
  LEAD(SUM(total_amount)) OVER(ORDER BY order_date) AS next_day_revenue
FROM orders
GROUP BY order_date
ORDER BY order_date;

-- Daily revenue with change and percentage change
SELECT
  order_date,
  daily_revenue,
  prev_day_revenue,
  daily_revenue - prev_day_revenue AS change_amount,
  ROUND(
    (daily_revenue - prev_day_revenue) * 100.0 / prev_day_revenue, 2
  ) AS pct_change
FROM (
  SELECT
    order_date,
    SUM(total_amount) AS daily_revenue,
    LAG(SUM(total_amount)) OVER(ORDER BY order_date) AS prev_day_revenue
  FROM orders
  GROUP BY order_date
) daily_stats
ORDER BY order_date;

-- Find days where order count jumped by more than 200%
-- Could indicate a bot attack or a test data leak
SELECT * FROM (
  SELECT
    order_date,
    COUNT(*) AS order_count,
    LAG(COUNT(*)) OVER(ORDER BY order_date) AS prev_day_count,
    CASE
      WHEN LAG(COUNT(*)) OVER(ORDER BY order_date) > 0
      THEN ROUND(
        COUNT(*) * 100.0 / LAG(COUNT(*)) OVER(ORDER BY order_date), 2
      )
      ELSE NULL
    END AS pct_of_prev_day
  FROM orders
  GROUP BY order_date
) daily_summary
WHERE pct_of_prev_day > 200
ORDER BY order_date;

-- Compare each customer's current order with their previous one
SELECT
  customer_id,
  order_id,
  order_date,
  total_amount,
  LAG(total_amount) OVER(
    PARTITION BY customer_id ORDER BY order_date
  ) AS prev_order_amount,
  total_amount - LAG(total_amount) OVER(
    PARTITION BY customer_id ORDER BY order_date
  ) AS amount_change
FROM orders
ORDER BY customer_id, order_date;