SQL Window Functions for Analytics

-- Running total
SELECT
  date,
  revenue,
  SUM(revenue) OVER (ORDER BY date) AS running_total
FROM daily_sales;

-- 7-day moving average
SELECT
  date,
  revenue,
  AVG(revenue) OVER (
    ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7d
FROM daily_sales;

-- Rank within partition
SELECT
  department, name, salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

-- Percent of total
SELECT
  product,
  revenue,
  ROUND(100.0 * revenue / SUM(revenue) OVER (), 2) AS pct_of_total
FROM product_sales;

-- Gap detection (find missing dates)
SELECT date, next_date, next_date - date AS gap_days
FROM (
  SELECT date, LEAD(date) OVER (ORDER BY date) AS next_date
  FROM daily_sales
) t
WHERE next_date - date > 1;