SQL window functions: running totals, ranks, top-N

The ticket says "show each customer's three largest orders on the account page". The first version loads the customers, then runs one query per customer with ORDER BY amount DESC LIMIT 3: an N+1 with a LIMIT on it. The second version does it in one statement with a correlated subquery that counts how many of the customer's orders are larger than this one, which reads every order once per order in the same customer. The third version pulls every order into memory and sorts in C#. All three pass in the demo database. None of them is the operation SQL already has a name for.

The same shape hides in "running balance per account", "revenue compared with last month", "seven-day average", and "each region's share of total sales". Every one is a question about a row and the rows related to it, and every one gets solved in application code until someone on the team shows the others the OVER clause.

GROUP BY collapses, OVER does not

An aggregate with GROUP BY folds a group into one row. SUM(amount) ... GROUP BY customer_id gives you one total per customer, and the orders are gone from the result. A window function computes the same kind of aggregate over a set of related rows but attaches the answer to each row instead of replacing them. PostgreSQL's window function tutorial puts it plainly: the rows retain their separate identities.

The OVER clause decides what "related rows" means, in three parts:

SELECT customer_id, id, amount,
       SUM(amount) OVER (
           PARTITION BY customer_id            -- which rows belong together
           ORDER BY placed_at, id              -- the sequence inside the partition
           ROWS BETWEEN UNBOUNDED PRECEDING    -- the frame: how far back and forward
                    AND CURRENT ROW
       ) AS running_total
FROM orders;

PARTITION BY is the window equivalent of GROUP BY: it splits the rows into groups without merging them. ORDER BY gives each partition a sequence, which ranking functions and running aggregates need. The frame narrows the partition to a range around the current row. Leave all three out and SUM(amount) OVER () is the grand total repeated on every row, which is exactly what a "share of total" column wants.

Ranking functions disagree about ties

Three functions number the rows in a partition, and they differ only in what happens when two rows sort equal:

SELECT customer_id, amount,
       ROW_NUMBER() OVER w AS row_number,
       RANK()       OVER w AS rank,
       DENSE_RANK() OVER w AS dense_rank
FROM orders
WINDOW w AS (PARTITION BY customer_id ORDER BY amount DESC);

For one customer with a tie at 250:

amount  row_number  rank  dense_rank
   300           1     1           1
   250           2     2           2
   250           3     2           2
   100           4     4           3

ROW_NUMBER always counts 1, 2, 3, 4 and picks an arbitrary winner among the tied rows unless the ORDER BY has a tiebreaker. RANK gives tied rows the same number and skips the numbers they used up, so there is no third place. DENSE_RANK gives tied rows the same number and does not skip. "Top 3 orders" wants ROW_NUMBER with a tiebreaker. "A podium where everyone who tied for bronze stands on it" wants RANK or DENSE_RANK, and the two produce different podium sizes.

Top-N per group

WHERE is evaluated before window functions, so you cannot filter on the rank in the same SELECT. Rank in a CTE, then filter:

WITH ranked AS (
    SELECT customer_id, id, amount, placed_at,
           ROW_NUMBER() OVER (
               PARTITION BY customer_id
               ORDER BY amount DESC, id
           ) AS rn
    FROM orders
)
SELECT customer_id, id, amount, placed_at
FROM ranked
WHERE rn <= 3
ORDER BY customer_id, rn;

One pass over orders, one sort, no loop, and the id tiebreaker makes the result stable between runs. The same shape answers "latest reading per sensor" (rn = 1) and "first order per customer".

Running totals and the frame trap

The obvious running balance looks right and is subtly wrong:

SELECT posted_on, amount,
       SUM(amount) OVER (ORDER BY posted_on) AS balance
FROM ledger
WHERE account_id = 42;

With ORDER BY and no explicit frame, PostgreSQL uses the default frame, RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and in RANGE mode "current row" means the current row and all of its peers, the rows that sort equal to it. Two entries posted on the same day are peers, so each gets a balance that already includes the other:

posted_on   amount  balance (RANGE)  balance (ROWS)
2026-09-01     100              100             100
2026-09-02      40              200             140
2026-09-02      60              200             200
2026-09-03      25              225             225

The balance jumps from 100 to 200 and shows 200 twice. A report built on it is wrong on every day with more than one entry, and the bug is invisible in test data with one row per day. Two fixes belong together: an explicit ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW frame so the sum stops at the current physical row, and a tiebreaker in the ORDER BY (posted_on, id) so "current physical row" is deterministic. The frame clause reference spells out the RANGE, ROWS, and GROUPS modes.

Same rows, same ORDER BY, two frames

Running balance for account 42

current row = id 2, the first 09-02 entry

RANGE (the default)

jumps

SUM(amount) OVER (ORDER BY posted_on)

• 09-01 id 1 +100 in frame 100
• 09-02 id 2 +40 current 200
• 09-02 id 3 +60 peer 200
• 09-03 id 4 +25 after 225

the tied 09-02 row is a peer, so both rows read 200

ROWS, explicit

correct

SUM(amount) OVER (ORDER BY posted_on, id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

• 09-01 id 1 +100 in frame 100
• 09-02 id 2 +40 current 140
• 09-02 id 3 +60 after 200
• 09-03 id 4 +25 after 225

100, 140, 200, 225: one step per row

GROUP BY collapses the partition into one answer. OVER computes an answer for every row and hands every row back.

LAG and LEAD for month-over-month

LAG(value, offset, default) reads a column from an earlier row in the window order; LEAD reads a later one. Month-over-month growth is a subtraction against LAG:

SELECT month, revenue,
       revenue - LAG(revenue, 1, 0) OVER (ORDER BY month) AS delta,
       ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
             / NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 1) AS pct
FROM monthly_revenue;

LAG looks at the previous row, not the previous month. If March is missing from the table, April is compared with February. When gaps matter, join the data to a generated calendar first so every month has a row, then apply the window.

Moving averages and share of total

A bounded frame turns the same AVG into a trailing window. Seven days means the current row and the six before it:

SELECT day, region, orders,
       AVG(orders) OVER (
           PARTITION BY region
           ORDER BY day
           ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS avg_7d,
       orders * 100.0 / SUM(orders) OVER () AS pct_of_total,
       orders * 100.0 / SUM(orders) OVER (PARTITION BY region) AS pct_of_region
FROM daily_orders;

The first six rows of each region average fewer than seven days, because the frame is clipped at the partition start. Decide whether to show them, hide them, or switch to RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW so a missing day counts as missing instead of stretching the window. Share of total needs no join to a totals subquery: SUM() OVER () and SUM() OVER (PARTITION BY region) are computed in the same statement as the rows they describe.

What the planner does with a window

Each distinct window specification costs one sort. The planner puts a WindowAgg node above a Sort on the partition and order columns, then walks the sorted rows once, keeping the frame in memory. Two window functions with the same PARTITION BY and ORDER BY share that sort; a third with a different order adds another. Naming the spec with WINDOW w AS (...) keeps the shared ones visibly identical.

WindowAgg
  ->  Sort
        Sort Key: customer_id, amount DESC
        ->  Seq Scan on orders

The sort is where the time goes on a large table, and an index on (customer_id, amount DESC) removes it, because the rows come off the B-tree already in window order:

WindowAgg
  ->  Index Scan using ix_orders_customer_amount on orders

Both plans are illustrative shapes; run EXPLAIN (ANALYZE, BUFFERS) on your own table and look for the Sort node, as reading EF Core query plans walks through. Top-N per group is still a full pass over the partition even with the index, so when the page only needs one customer, put WHERE customer_id = @id inside the CTE and the window runs over that customer's rows rather than the whole table.

Where EF Core stops

LINQ has no window operator. There is no RowNumber or Lag the provider will translate, GroupBy becomes GROUP BY and collapses, and the nearest thing, a Select with a nested OrderBy().Take(3) over a navigation, becomes a lateral join that re-runs the ordered LIMIT for each outer row. Running totals, LAG, and moving averages have no LINQ shape at all. This is the place for raw SQL, and EF Core treats that as a first-class call rather than a bypass. Database.SqlQuery<T> maps a result set to a plain C# type with no entity mapping, and the interpolated values become parameters:

var top = await db.Database
    .SqlQuery<TopOrder>($"""
        SELECT customer_id AS "CustomerId", id AS "Id", amount AS "Amount"
        FROM (
            SELECT customer_id, id, amount,
                   ROW_NUMBER() OVER (
                       PARTITION BY customer_id ORDER BY amount DESC, id
                   ) AS rn
            FROM orders
            WHERE placed_at >= {since}
        ) ranked
        WHERE rn <= 3
        """)
    .ToListAsync();

FromSql does the same for a keyless entity type mapped to a view, which is the tidier option when the window query is reused in several places. The raw SQL guidance covers both. Keep the composition rules from IQueryable vs IEnumerable in mind: SqlQuery returns an IQueryable, so a Where composed on top still runs in the database, wrapped around the SQL you wrote. And once a ranked result is paged, keyset pagination on (customer_id, rn) beats OFFSET for the same reasons it does everywhere else.

Practice on a real PostgreSQL plan

The Katabench Database track has a raw-SQL subset built from exactly these shapes: "Top N per Group with a Window Function", "Running Total with a Window", "The Running Total That Jumped", "Month-over-Month Growth with LAG", "Podium with Ties", "The Seven-Day Window", and "Slice of the Pie". Each runs your SQL against a real PostgreSQL database and shows you the query plan it chose, so a window that sorts twice when once would do is visible before it ships. The EF Core practice page lists the track, and the tracks guide explains how the SQL subset and the LINQ katas fit together.