SQL window functions: order, frames and ties

Keep the row grain

GROUP BY usually collapses input rows into one row per group. A window function with OVER attaches a result to each surviving row instead. If the task asks for every sale alongside its account’s running total, a window is a natural fit: you need both the sale grain and a calculation involving other sales.

PARTITION BY splits the calculation into independent groups. For an account running total, the total restarts for each account. The ORDER BY inside OVER determines calculation order. The final ORDER BY determines display order; it does not change how the window was calculated. Keeping these two roles separate prevents many interview mistakes.

Run a complete example

Account A has two sales on the same day and one on the next day. Account B has one sale. The query calculates a running amount, a position by amount and a rank by sale day. The complete VALUES dataset makes ties and restarts visible without loading a separate database.

WITH sales(sale_id, account_id, sold_on, cents) AS (
  VALUES (1, 'A', DATE '2026-01-01', 100),
         (2, 'A', DATE '2026-01-01', 200),
         (3, 'A', DATE '2026-01-02', 50),
         (4, 'B', DATE '2026-01-01', 80)
)
SELECT sale_id, account_id, cents,
       SUM(cents) OVER (
         PARTITION BY account_id ORDER BY sold_on, sale_id
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_cents,
       ROW_NUMBER() OVER (
         PARTITION BY account_id ORDER BY cents DESC, sale_id
       ) AS amount_position,
       DENSE_RANK() OVER (
         PARTITION BY account_id ORDER BY sold_on
       ) AS day_rank
FROM sales
ORDER BY account_id, sold_on, sale_id;

Expected output:

Follow the running total

For account A, the total moves from 100 to 300 to 350 cents. Account B starts its own total at 80. The ROWS frame begins at the first row in the partition and ends at the current row. Ordering by sold_on and sale_id gives each sale a reproducible position, even when two sales occur on the same date.

If you order only by sold_on, rows on the same date are peers. PostgreSQL’s default frame for an ordered aggregate window includes peers through the current ordering value, so both first-day sales can show a total of 300. That can be correct for a cumulative total by day, but it differs from a per-sale running total. Specify ROWS and a stable tie-breaker when the question requires row-by-row accumulation.

Choose a ranking rule

ROW_NUMBER assigns different positions to each row. In the amount_position column, account A’s 200-cent sale is first, its 100-cent sale second and its 50-cent sale third. Adding sale_id makes equal amounts deterministic; it does not make them share a rank.

RANK gives tied values the same rank and leaves gaps afterward. DENSE_RANK gives tied values the same rank without gaps. The example’s day_rank is 1, 1, 2 for account A because the first two sales share a date. Choose the rule from the question: “exactly three rows” often calls for ROW_NUMBER, while “all rows in the three highest distinct values” often calls for DENSE_RANK. Clarify tie behavior instead of guessing.

Filter after calculating the window

To select the highest-value sale per account, put the ranking query in a common table expression or subquery, then filter amount_position = 1 in the outer SELECT. A WHERE clause in the same SELECT cannot reference a window result. PostgreSQL does not support QUALIFY, even though some other SQL systems do.

Ordinary WHERE filters also affect which rows enter the window. If you filter to the last week before calculating a running total, the total starts within that week. If you want a lifetime total but only show last week’s rows, calculate the lifetime window first and filter its result outside. The required time boundary is a business question, not a syntax detail.

Test ties, missing values and ordering

Try equal amounts, equal timestamps, a single row, multiple accounts and an empty input. Decide where NULL values should sort, using NULLS FIRST or NULLS LAST when appropriate. For a running sum, clarify whether a NULL amount is unknown or should be treated as zero; SUM ignores NULL inputs, which may hide incomplete data.

A stable tie-breaker should represent the business sequence if the order matters; an arbitrary ID is only a reproducible fallback, not proof of event chronology. State your assumptions aloud and inspect intermediate rows. Continue with SQL practice or review aggregation and row grain before combining joins with windows.