Window Functions
A 7-day moving average, a cumulative total, and a day-over-day change — all three, plus the raw daily number, without collapsing a single row.
GROUP BY and a window function both compute aggregates, but they solve genuinely different problems. GROUP BY reduces many rows to one per group — exactly right for "total revenue per category," wrong for "each day's own revenue, plus how it compares to the last 7 days," since that needs every individual day's row to still exist in the output. A window function, written with OVER (...), computes its aggregate across a set of related rows — the whole table, a PARTITION BY group, or an explicit ROWS BETWEEN frame around the current row — while returning one row of output per input row, untouched.\n\nThis is what makes "daily revenue, plus a 7-day moving average, plus a running cumulative total, plus the day-over-day percentage change" answerable in one single query with no subqueries at all: every one of those four numbers is itself a window function evaluated once per row, each with its own ORDER BY or frame, but none of them collapsing the result set. RANK() and DENSE_RANK() extend the same idea to ranking within groups, and diverge from each other in a specific, learnable way exactly when a real tie occurs.
Four Numbers, One Query, Zero Collapsed Rows
SELECT
sale_date, revenue,
round(avg(revenue) OVER (ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 2) AS moving_avg_7d,
sum(revenue) OVER (ORDER BY sale_date) AS cumulative_total,
round(100.0 * (revenue - lag(revenue) OVER (ORDER BY sale_date)) / lag(revenue) OVER (ORDER BY sale_date), 2) AS pct_change
FROM sql_daily_sales ORDER BY sale_date LIMIT 10;
sale_date | revenue | moving_avg_7d | cumulative_total | pct_change
------------+---------+---------------+------------------+------------
2025-01-01 | 500 | 500.00 | 500 |
2025-01-02 | 513 | 506.50 | 1013 | 2.60
2025-01-08 | 591 | 552.00 | 4364 | 2.25
Every row from the table is still here — 30 days in, 30 days out — with 3 independently-computed window functions added alongside the raw revenue column. The first day's pct_change is NULL, honestly: there is no previous day for lag() to compare against.
RANK() vs DENSE_RANK(), on a Real Tie
SELECT category, id, revenue,
RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS rnk,
DENSE_RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS dense_rnk
FROM sql_products ORDER BY category, revenue DESC;
category | id | revenue | rnk | dense_rnk
----------+----+---------+-----+-----------
beer | 1 | 5000 | 1 | 1
beer | 2 | 5000 | 1 | 1
beer | 3 | 3000 | 3 | 2
wine | 4 | 4000 | 1 | 1
wine | 5 | 2000 | 2 | 2
Two beer products genuinely tie at 5000 revenue, and both correctly rank 1st. The next distinct value is where they diverge: RANK() jumps to 3 — counting the two tied rows that came before it — while DENSE_RANK() continues at 2, treating "how many distinct values came before" rather than "how many rows came before."
LEAD(): Looking Forward Instead of Back
SELECT sale_date, revenue, lead(revenue) OVER (ORDER BY sale_date) AS next_day_revenue
FROM sql_daily_sales ORDER BY sale_date LIMIT 5;
sale_date | revenue | next_day_revenue
------------+---------+-------------------
2025-01-01 | 500 | 513
2025-01-02 | 513 | 526
LAG() looks at the previous row in the window's order; LEAD() looks at the next one — the same mechanism, pointed the other direction.
Window frame (ROWS BETWEEN)
The specific subset of rows, relative to the current row, that a window function aggregates over — ROWS BETWEEN 6 PRECEDING AND CURRENT ROW means "the current row plus the 6 rows immediately before it in the window's ORDER BY," a sliding 7-row frame that moves with each row. Without an explicit frame, ORDER BY alone defaults to RANGE UNBOUNDED PRECEDING AND CURRENT ROW — everything from the start up to (and including peers of) the current row, which is exactly what makes a bare sum(...) OVER (ORDER BY ...) a running cumulative total.
RANK() vs DENSE_RANK()
Both assign rank 1 to the first row(s) in a PARTITION BY group's ORDER BY, and both give tied rows the identical rank. They diverge immediately after a tie: RANK() leaves a gap equal to the number of tied rows before continuing (1, 1, 3), while DENSE_RANK() continues with the very next integer (1, 1, 2) — the difference between counting rows already seen and counting distinct values already seen.
📈 Four Analytics Columns, One Query, No Subqueries
Compute daily revenue, a 7-day moving average, a cumulative total, and day-over-day percentage change in a single query.
psql -U postgres -d beer_db -c "SELECT sale_date, revenue, round(avg(revenue) OVER (ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW),2) AS moving_avg_7d, sum(revenue) OVER (ORDER BY sale_date) AS cumulative_total, round(100.0*(revenue - lag(revenue) OVER (ORDER BY sale_date))/lag(revenue) OVER (ORDER BY sale_date),2) AS pct_change FROM sql_daily_sales ORDER BY sale_date LIMIT 10;"student@lab:~$ psql -U postgres -d beer_db -c "SELECT sale_date, revenue, round(avg(revenue) OVER (ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW),2) AS moving_avg_7d, sum(revenue) OVER (ORDER BY sale_date) AS cumulative_total, round(100.0*(revenue - lag(revenue) OVER (ORDER BY sale_date))/lag(revenue) OVER (ORDER BY sale_date),2) AS pct_change FROM sql_daily_sales ORDER BY sale_date LIMIT 10;" SET sale_date | revenue | moving_avg_7d | cumulative_total | pct_change ------------+---------+---------------+------------------+------------ 2025-01-01 | 500 | 500.00 | 500 | 2025-01-02 | 513 | 506.50 | 1013 | 2.60 2025-01-03 | 526 | 513.00 | 1539 | 2.53 2025-01-04 | 539 | 519.50 | 2078 | 2.47 2025-01-05 | 552 | 526.00 | 2630 | 2.41 2025-01-06 | 565 | 532.50 | 3195 | 2.36 2025-01-07 | 578 | 539.00 | 3773 | 2.30 2025-01-08 | 591 | 552.00 | 4364 | 2.25 2025-01-09 | 604 | 565.00 | 4968 | 2.20 2025-01-10 | 617 | 578.00 | 5585 | 2.15 (10 rows)
🏅 Watch RANK() and DENSE_RANK() Diverge on a Real Tie
Rank products by revenue within their category, where two products genuinely tie for first place.
psql -U postgres -d beer_db -c "SELECT category, id, revenue, RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS rnk, DENSE_RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS dense_rnk FROM sql_products ORDER BY category, revenue DESC;"student@lab:~$ psql -U postgres -d beer_db -c "SELECT category, id, revenue, RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS rnk, DENSE_RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS dense_rnk FROM sql_products ORDER BY category, revenue DESC;" SET category | id | revenue | rnk | dense_rnk ----------+----+---------+-----+----------- beer | 1 | 5000 | 1 | 1 beer | 2 | 5000 | 1 | 1 beer | 3 | 3000 | 3 | 2 wine | 4 | 4000 | 1 | 1 wine | 5 | 2000 | 2 | 2 (5 rows)
👉 LEAD(): The Same Mechanism, Pointed Forward
Compare each day's revenue against the following day instead of the previous one.
psql -U postgres -d beer_db -c "SELECT sale_date, revenue, lead(revenue) OVER (ORDER BY sale_date) AS next_day_revenue FROM sql_daily_sales ORDER BY sale_date LIMIT 5;"student@lab:~$ psql -U postgres -d beer_db -c "SELECT sale_date, revenue, lead(revenue) OVER (ORDER BY sale_date) AS next_day_revenue FROM sql_daily_sales ORDER BY sale_date LIMIT 5;" SET sale_date | revenue | next_day_revenue ------------+---------+------------------- 2025-01-01 | 500 | 513 2025-01-02 | 513 | 526 2025-01-03 | 526 | 539 2025-01-04 | 539 | 552 2025-01-05 | 552 | 565 (5 rows)
Lab 3.3.2 complete. Window functions, proven against GROUP BY's structural limit:\n\n\n 4 analytics columns, 0 rows collapsed : ✅ moving avg, cumulative, pct change\n RANK vs DENSE_RANK on a real tie : ✅ 1,1,3 vs 1,1,2 — genuinely diverging\n LEAD() proven as LAG()'s mirror : ✅ same mechanism, opposite direction\n
Enable JavaScript to run the live terminal and track your progress.