SQL Server Window Functions

-- Running total in SQL Server (2012+)
SELECT
  order_date,
  daily_revenue,
  SUM(daily_revenue) OVER (
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM daily_sales;

-- Moving 7-day average
SELECT
  order_date,
  daily_revenue,
  AVG(daily_revenue) OVER (
    ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS seven_day_avg
FROM daily_sales;

Running totals are the most common window aggregate in interviews. SQL Server requires an explicit frame clause for cumulative sums. Without one, the default depends on whether ORDER BY is present.

-- Default frame (RANGE) with ties: may surprise you
SELECT
  order_date,
  amount,
  SUM(amount) OVER (ORDER BY order_date) AS running_sum
FROM orders;
-- If two rows have the same order_date,
-- both see the sum INCLUDING the other tied row.

-- Fix: specify ROWS explicitly
SELECT
  order_date,
  amount,
  SUM(amount) OVER (
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_sum
FROM orders;
-- Each row sees only rows up to and including itself

Both SQL Server and PostgreSQL default to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW when ORDER BY is present. Tied rows are included in the frame together, producing unexpected running totals. Always specify ROWS BETWEEN explicitly.