Choose the grain before the function
Suppose a sales ledger contains four order lines: North has order 10 for 100, order 11 for 50 and order 12 for 50; South has order 20 for 80. These are fictional amounts. You need every original line, its regional running revenue and the preceding line amount. GROUP BY would collapse the rows; a window calculation lets you retain the ledger grain. Decide whether the intended grain is an order, order line or daily summary before writing the query. A join that duplicates order lines will also inflate a window total.
Make the order deterministic
Use a unique order key as the final ordering column. If two rows share the same timestamp, ordering only by timestamp leaves their sequence unspecified. That matters for LAG and for a row-by-row running total. The partition defines which rows are considered together; it does not remove rows from the result. An outer ORDER BY is still needed to specify the displayed order.
SELECT region, order_id, amount,
SUM(amount) OVER (
PARTITION BY region ORDER BY order_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_revenue,
LAG(amount) OVER (PARTITION BY region ORDER BY order_id) AS prior_amount
FROM dbo.Sales
ORDER BY region, order_id;Check the answer by hand
For North, the expected running totals are 100, 150 and 200; prior amounts are NULL, 100 and 50. South starts a separate partition, so its running total is 80 and its prior amount is NULL. Do not replace the initial NULL with zero unless zero expresses your business definition. A first purchase has no preceding purchase; it is not necessarily a change from zero. Add a fifth North line with the same date to verify the tie-breaking behavior explicitly.
Distinguish ranking from accumulation
ROW_NUMBER assigns different sequence numbers to tied amounts; RANK leaves a gap after ties; DENSE_RANK does not. For North amounts sorted descending, RANK yields 1, 2, 2 and DENSE_RANK also yields 1, 2, 2; add a fourth amount of 20 to expose the difference: its ranks become 4 and 3. Use an explicit ROWS frame when your question is about a sequence of individual rows. Peer-based frames answer a different question.
Investigate cost without promising a speedup
Inspect the actual execution plan and logical reads on representative data. Ordering can require a sort; useful indexes depend on filters, partition columns and the rest of the workload. Record the input row count and result checksum before comparing timings. A faster query that changes the grain or drops ties has not solved the same problem. The SQL shown is a SQL Server example to adapt and validate in your own environment, not a benchmark result.
