Aggregate Window Functions in PostgreSQL

-- Running total
SELECT
  date,
  amount,
  SUM(amount) OVER (ORDER BY date) as running_total
FROM sales;

-- Moving average (3-row window)
SELECT
  date,
  price,
  AVG(price) OVER (
    ORDER BY date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) as moving_avg_3
FROM stock_prices;

-- Cumulative percentage
SELECT
  product,
  revenue,
  SUM(revenue) OVER (ORDER BY revenue DESC) as cumulative,
  100.0 * SUM(revenue) OVER (ORDER BY revenue DESC) / 
    SUM(revenue) OVER () as cumulative_pct
FROM product_sales;

-- DETAILED_TAB:
-- Complex window frames
SELECT
  date,
  sales,
  -- Previous row
  LAG(sales, 1) OVER (ORDER BY date) as prev_sales,
  -- Next row
  LEAD(sales, 1) OVER (ORDER BY date) as next_sales,
  -- First value in window
  FIRST_VALUE(sales) OVER (
    PARTITION BY EXTRACT(YEAR FROM date)
    ORDER BY date
  ) as year_first,
  -- Difference from previous
  sales - LAG(sales, 1) OVER (ORDER BY date) as growth
FROM daily_sales;