LEARN. PRACTICE. GROW.Choose the unit of each row
Before writing a function, define what one result row represents: movement, account, day, or incident. A window adds a calculation to existing detail. GROUP BY reduces rows to group grain. In a fictional report, an account with movements 7 and 5 can appear twice with total 12 on each row; that is correct for enriched detail but not for a list of account totals. Write the expected row count before execution. In a multitenant environment, identity may include tenant_id and account_id. Fixing the partition prevents mixed totals but does not grant authorization to read another tenant’s data.
Ties, selection, and presentation
ROW_NUMBER is useful for selecting one row when a complete tie-break rule exists. If the rule requests the latest event and then the highest ID, order by timestamp DESC and ID DESC. RANK and DENSE_RANK serve another need: retaining tied groups. For values 90,90,40, RANK gives 1,1,3 and DENSE_RANK gives 1,1,2. If you need the two highest distinct values, the second sequence expresses the contract. Do not add a unique ID to ranking order when that would destroy intended ties. Use an outer ORDER BY to present incidents consistently, keeping selection and presentation rules separate.
The frame defines the running calculation
An ordered sum does not necessarily mean a row-by-row sum. In PostgreSQL, the default frame with ORDER BY includes peers of the current ordering value. In the executable example, two movements share instant 10: both receive 40 in peer_total. For movement-by-movement progression, add ID to ordering and specify ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW; results are 20,40,45. The tie-break must match the contract, not merely an available column. LAST_VALUE also considers the frame’s end. If you need the partition’s last value on every row, explicitly use a frame ending at UNBOUNDED FOLLOWING.
Sparse series and unknown values
LAG looks for a preceding position in the row sequence, not a missing date. With observations on days 1 and 4, the row before day 4 can be day 1. A three-row mean does not automatically mean three calendar days either. For a daily metric, construct the intended time population and join existing data. Substitute zero for absence only when the metric definition permits it: a no-movement day differs from collection failure. In PostgreSQL 18, LAG does not automatically skip NULL values. A preceding row containing NULL still represents unknown information that the analysis rule must address.
Place the filter at the right level
The population seen by a window has already passed that query level’s WHERE. If values 40 and 60 form the population and only values above 50 remain before summing, the 60 share becomes 100%. To compare with the original total, calculate the denominator first and filter in an outer query. The same distinction appears in an incident dashboard: the latest open event is not the same as the latest event having open status. Filtering open before selecting the latest event can resurrect an already resolved incident. Draw two stages and state the population each must retain before choosing syntax.
Lab: establish shape and values
Run the example in a learning database and compare four properties: three retained rows, peer totals 40,40,45, movement running totals 20,40,45, and final ID ordering. Then change the second row’s value to 8 and predict the result before repeating. Add another account and check whether PARTITION BY is needed; do not assume a one-account example establishes separation between accounts. For an opening balance, add the running movement sum to that balance once in each result, without turning opening balance into a repeated movement. Finish by explaining the difference between row, partition, frame, and presentation order to a teammate.
WITH movements(id, instant, amount) AS (
VALUES (1,10,20), (2,10,20), (3,11,5)
)
SELECT id,
SUM(amount) OVER (ORDER BY instant) AS peer_total,
SUM(amount) OVER (
ORDER BY instant, id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS movement_total
FROM movements
ORDER BY idThree movements produce peer_total 40,40,45 and movement_total 20,40,45. The difference follows from frame and tie-breaking, not changed amounts.
Common pitfalls
Treating window order as final order, adding ID to a ranking that should retain ties, or treating a preceding row as a preceding day.
Related topics: Join without multiplying mistakes · Count and group carefully · Indexes and execution plans
Define row grain, population, and tie-breaking first; then choose the window and frame that reproduce that contract.
Reference: PostgreSQL 18: Window Functions · PostgreSQL 18 reference semantics; DR SQL 2026.2