Add ORDER BY and Your SUM Means Something Completely Different

Same SUM function. With ORDER BY it is a running total; without it, a group total. Nobody tells you this.

The problem

The chart needs a cumulative revenue line from the start of the month, plus a 3-day moving average to smooth it.

Running total

SELECT
  day,
  revenue,
  SUM(revenue) OVER (ORDER BY day) AS running_total
FROM daily_revenue;

Moving average

SELECT
  day,
  ROUND(AVG(revenue) OVER (
    ORDER BY day
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ), 1) AS avg_3_day
FROM daily_revenue;

ROWS BETWEEN defines the frame — how many rows are included:

Common mistakes

1. Confusing ROWS with RANGE

ROWS counts rows. RANGE counts by value, so every row sharing a date collapses into one step. With duplicate dates the two give different answers. The default is RANGE — the source of many baffling numbers.

2. Forgetting PARTITION BY

-- WRONG: the running total bleeds across cities
SUM(revenue) OVER (ORDER BY day)

-- RIGHT
SUM(revenue) OVER (PARTITION BY city ORDER BY day)

3. Missing days breaking the moving average

ROWS 2 PRECEDING takes 3 rows, not 3 days. If days are missing, the window slides across the gap unnoticed. Build a complete date range first.

A shortcut when you reuse the same frame

SELECT
  day,
  SUM(revenue) OVER w      AS running_total,
  AVG(revenue) OVER w      AS running_avg,
  MAX(revenue) OVER w      AS running_max
FROM daily_revenue
WINDOW w AS (ORDER BY day);

Practice

• LeetCode 1321 — Restaurant Growth (7-day moving average)
• LeetCode 579 — Find Cumulative Salary of an Employee
• LeetCode 1204 — Last Person to Fit in the Bus
• LeetCode 534 — Game Play Analysis III
• HackerRank — Weather Observation Station 20 (median)