Compare rows with window functions

Estimated study time: 75–105 minutes. Read, predict, run, explain, then attempt the independent work.

A monthly total compresses orders into one row per month. The manager now wants to keep those month rows while adding a running total and the preceding month's sales. A window function performs a calculation across related result rows without collapsing them. The presence of OVER distinguishes it from an ordinary grouped aggregate.

Start by producing the monthly totals at the correct grain. In the worked query, GROUP BY 1 refers to the first expression in its SELECT list, the month key. This positional shorthand is valid PostgreSQL, but repeating the explicit month expression is often clearer when a query is likely to be edited. Then SUM(net_sales) OVER (...) can add a cumulative figure beside each month. The window's ORDER BY determines the sequence used in that calculation. The final ORDER BY controls presentation. These are separate roles; one should not be assumed to imply the other.

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW explicitly defines the cumulative frame: begin with the first ordered row and end at this row. When ordering contains ties, PostgreSQL's default frame can include peers with the same ordering value. An explicit ROWS frame plus a deterministic ordering removes surprises for a row-by-row cumulative measure. Here the monthly aggregation makes the month key unique, which also makes the sequence clear.

Open the diagram at full size

Read the diagram explanation

Monthly rows remain January 1620, February 1575, March 2450. Running sales become 1620, 3195, 5645. Previous month sales are null, 1620, 1575.

Compare with a previous result row

LAG(net_sales) retrieves the preceding row's amount in the window order. The first row has no predecessor, so the result is null. That is a boundary fact, not a zero-sales month. The difference between current and prior sales gives an absolute change. Dividing that difference by the prior value gives a relative change, with NULLIF protecting a zero denominator.

LAG means “previous row,” not necessarily “previous calendar month.” Our fixture contains January, February and March. If February were absent, March's previous row would be January. A complete calendar series and an agreed missing-month policy are needed before claiming true month-over-month change on sparse data. Do not silently replace a missing month with zero if the source might simply be incomplete.

Rank customers with a tie policy

ROW_NUMBER assigns different sequential positions even when scores tie; add customer_id to make ties deterministic. DENSE_RANK assigns the same rank to equal scores and leaves no gaps after a tie. RANK also shares a rank but leaves gaps. Choose the function that matches the question. “Exactly two rows for display” differs from “everyone tied within the top two ranks.”

The worked query uses net_sales alone inside DENSE_RANK so equal amounts would share a rank. It uses net_sales and customer_id inside ROW_NUMBER to guarantee a stable display order. Adding the identifier to the ranking expression would accidentally make every row distinct and defeat the business tie policy.

A window expression cannot be filtered by WHERE at the same query level because the window calculation occurs later in the logical reasoning sequence. To keep only the top two positions, calculate positions in a CTE and filter that CTE in an outer SELECT. You already know this staged pattern from module 8; now the intermediate result includes a window value.

Use PARTITION BY inside OVER when the comparison should restart for each group, such as a separate ranking within every channel. Without it, the whole input result is one partition. The grouped aggregate, the partition and the final ordering answer different questions. Draw the rows and label each boundary before choosing syntax.

Worked example 09 running

Three monthly rows remain three rows. The running total reaches 5645 in March, which reconciles to the full-quarter net total.

WITH line_totals AS (
 SELECT order_id,SUM(quantity*unit_price-line_discount) AS line_sales
 FROM aabha_sql.order_items GROUP BY order_id
), refund_totals AS (
 SELECT order_id,SUM(amount) AS refunded
 FROM aabha_sql.refunds WHERE refund_date < DATE '2025-04-01' GROUP BY order_id
), order_net AS (
 SELECT o.order_id,o.customer_id,o.order_date,o.channel,
 l.line_sales,COALESCE(r.refunded,0) AS refunded,
 l.line_sales-COALESCE(r.refunded,0) AS net_sales
 FROM aabha_sql.orders AS o
 JOIN line_totals AS l ON l.order_id=o.order_id
 LEFT JOIN refund_totals AS r ON r.order_id=o.order_id
 WHERE o.status='completed' AND o.order_date >= DATE '2025-01-01'
   AND o.order_date < DATE '2025-04-01'
), monthly AS (
 SELECT date_trunc('month',order_date)::date AS month,SUM(net_sales) AS net_sales
 FROM order_net GROUP BY 1
)
SELECT month::text AS month,net_sales,
 SUM(net_sales) OVER (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_sales,
 LAG(net_sales) OVER (ORDER BY month) AS previous_month_sales
FROM monthly ORDER BY month;

Expected result from the bundled dataset:

The runnable file is sql/examples/09_running.sql.

Worked example 09 rank

The fixture has no sales ties, so the two position columns agree. The validator includes a separate tied-score case to check the intended distinction.

WITH line_totals AS (
 SELECT order_id,SUM(quantity*unit_price-line_discount) AS line_sales
 FROM aabha_sql.order_items GROUP BY order_id
), refund_totals AS (
 SELECT order_id,SUM(amount) AS refunded
 FROM aabha_sql.refunds WHERE refund_date < DATE '2025-04-01' GROUP BY order_id
), order_net AS (
 SELECT o.order_id,o.customer_id,o.order_date,o.channel,
 l.line_sales,COALESCE(r.refunded,0) AS refunded,
 l.line_sales-COALESCE(r.refunded,0) AS net_sales
 FROM aabha_sql.orders AS o
 JOIN line_totals AS l ON l.order_id=o.order_id
 LEFT JOIN refund_totals AS r ON r.order_id=o.order_id
 WHERE o.status='completed' AND o.order_date >= DATE '2025-01-01'
   AND o.order_date < DATE '2025-04-01'
), customer_sales AS (
 SELECT customer_id,SUM(net_sales) AS net_sales FROM order_net GROUP BY customer_id
)
SELECT customer_id,net_sales,DENSE_RANK() OVER (ORDER BY net_sales DESC) AS sales_rank,
 ROW_NUMBER() OVER (ORDER BY net_sales DESC,customer_id) AS display_position
FROM customer_sales ORDER BY display_position;

Expected result from the bundled dataset:

The runnable file is sql/examples/09_rank.sql.

Independent practice

09a

Return exactly the top two purchasing customers with customer_id, net_sales and position. Break amount ties with customer_id.

Hint: Calculate ROW_NUMBER in a CTE, then apply the position filter outside it.

09b

Return month, absolute_change and percent_change in net sales from the preceding month. Keep the first month’s change null and round percentage change to two decimals.

Hint: Calculate LAG in one stage and the differences in the next. A missing or zero denominator must not become a claimed zero percent change.

Check before moving on

Explain why window SUM retains rows, why a deterministic tie-breaker matters, and why LAG alone does not fill gaps in a calendar.