SUM/AVG/COUNT as Window Functions — Running Totals and Moving Averages

-- Running total of daily revenue
SELECT
  order_date,
  SUM(total_amount) AS daily_revenue,
  SUM(SUM(total_amount)) OVER(ORDER BY order_date) AS cumulative_revenue
FROM orders
GROUP BY order_date
ORDER BY order_date;

-- Result:
-- order_date  | daily_revenue | cumulative_revenue
-- 2024-01-01  | 15000         | 15000
-- 2024-01-02  | 18000         | 33000
-- 2024-01-03  | 12000         | 45000
-- 2024-01-04  | 22000         | 67000

-- Per-customer running total of spending
SELECT
  customer_id,
  order_id,
  order_date,
  total_amount,
  SUM(total_amount) OVER(
    PARTITION BY customer_id
    ORDER BY order_date
  ) AS customer_running_total
FROM orders
ORDER BY customer_id, order_date;

-- Cumulative order count per day
SELECT
  order_date,
  COUNT(*) AS daily_orders,
  SUM(COUNT(*)) OVER(ORDER BY order_date) AS total_orders_so_far
FROM orders
GROUP BY order_date
ORDER BY order_date;

-- QA scenario: Verify user signup growth
SELECT
  DATE_TRUNC('month', created_at) AS signup_month,
  COUNT(*) AS new_users,
  SUM(COUNT(*)) OVER(ORDER BY DATE_TRUNC('month', created_at)) AS total_users
FROM users
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY signup_month;

-- 7-day moving average of daily revenue
SELECT
  order_date,
  SUM(total_amount) AS daily_revenue,
  ROUND(
    AVG(SUM(total_amount)) OVER(
      ORDER BY order_date
      ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ), 2
  ) AS seven_day_avg
FROM orders
GROUP BY order_date
ORDER BY order_date;

-- 3-day moving average of API response times
SELECT
  log_date,
  ROUND(AVG(response_time_ms), 2) AS daily_avg_ms,
  ROUND(
    AVG(AVG(response_time_ms)) OVER(
      ORDER BY log_date
      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ), 2
  ) AS three_day_moving_avg
FROM api_logs
GROUP BY log_date
ORDER BY log_date;