Window Functions with RANK and LAG

-- Rank users by total spend within each region
SELECT
  user_id,
  region,
  total_spend,
  RANK() OVER (PARTITION BY region ORDER BY total_spend DESC) AS rank,
  DENSE_RANK() OVER (PARTITION BY region ORDER BY total_spend DESC) AS dense_rank
FROM user_stats;

-- Running total of daily revenue
SELECT
  date,
  revenue,
  SUM(revenue) OVER (ORDER BY date ROWS UNBOUNDED PRECEDING) AS running_total,
  AVG(revenue) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7day
FROM daily_revenue;

-- Compare each row with previous (month-over-month)
SELECT
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_month,
  revenue - LAG(revenue) OVER (ORDER BY month) AS change,
  ROUND(
    (revenue - LAG(revenue) OVER (ORDER BY month))::numeric /
    NULLIF(LAG(revenue) OVER (ORDER BY month), 0) * 100, 1
  ) AS pct_change
FROM monthly_revenue;

-- Top 3 products per category
SELECT * FROM (
  SELECT
    product_id, category, sales,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) AS rn
  FROM products
) ranked
WHERE rn <= 3;