SQL Window Functions for Analysts: Ranking, LAG, and Running Totals

SQL window functions for analysts solve a problem that GROUP BY cannot: calculate across related rows while keeping every row in the result. You use them to rank, compare a row to the previous one, build running totals, and keep the latest record per key. This article gives you the patterns, the traps, and the tests.

I have used these functions in hospital reporting, revenue cycle, and finance queries for years. Most defects I found came from three habits: a missing tie-breaker, a default frame, and a filter in the wrong place. Examples use SQL Server syntax. Oracle and Snowflake accept the same core syntax.

What SQL Window Functions Do That GROUP BY Cannot

GROUP BY collapses rows. Ten encounters for one department become one row with a count. A window function computes the same count but attaches it to each of the ten rows. You keep the detail and gain the context.

Think of the window as a lens over the rows. PARTITION BY splits rows into groups. ORDER BY sorts inside each group. The frame decides which neighboring rows the function sees for the current row.

The anatomy of an OVER clause

SELECT  PAT_ENC_CSN_ID,
        PAT_ID,
        HOSP_ADMSN_TIME,
        ROW_NUMBER() OVER (
            PARTITION BY PAT_ID              -- restart the count for each patient
            ORDER BY HOSP_ADMSN_TIME DESC,   -- newest first
                     PAT_ENC_CSN_ID DESC     -- tie-breaker, always add one
        ) AS recency_rank
FROM    PAT_ENC_HSP;

Every window function has the same skeleton: a function, then OVER, then an optional partition, an order, and for aggregates an optional frame. Learn the skeleton once and every function reads the same way.

Partition A (patient 1001)

Row 1 | admit Jan 03 | charge 4,200
Row 2 | admit Feb 11 | charge 900 (current row)
Row 3 | admit Mar 29 | charge 1,750
Row 4 | admit Apr 30 | charge 600
Partition B (patient 1002)

Row 1 | admit Jan 15 | charge 3,100
Row 2 | admit Jun 02 | charge 2,300
PARTITION BY pat_id: each patient is its own group.
ORDER BY admit time: rows sort inside the group.
LAG(admit) on Row 2 reads Row 1 (Jan 03).
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW: Rows 1 and 2.
Partitions never see each other. The frame slides with the current row.

Function families

Standard SQL defines these functions, so skills transfer across platforms. The Microsoft Learn OVER clause reference and the Snowflake window function reference list exact syntax and options for each engine.

The Sample Tables Used in These SQL Window Function Examples

Every query uses a small encounter table and a small ledger table. Names follow Epic Clarity where possible, which keeps the examples close to what you query at work. The PAT_ENC table guide describes the real table and its keys.

Patient identifiers in these examples stay generic. In real work, apply minimum necessary, report keys instead of names, and keep results inside the approved environment.

SQL Window Functions for Analysts: Twelve Patterns With Code

The patterns below cover most window work I see in analyst queries. Each one states when to use it and the trap that goes with it. All code uses SQL Server syntax and runs with small changes on Oracle and Snowflake.

Order of evaluation: why you cannot filter on a window result

A database applies clauses in a fixed logical order. Window functions run after WHERE, GROUP BY, and HAVING, and before ORDER BY. That order explains the most common error message analysts hit.

You cannot write WHERE rn = 1 in the same query level that defines rn. Wrap the window in a CTE or subquery, then filter outside. Snowflake offers QUALIFY to filter in place.

Pattern 1: Keep the latest row per key with ROW_NUMBER

WITH ranked AS (
    SELECT  h.PAT_ENC_CSN_ID, h.PAT_ID, h.HOSP_ADMSN_TIME,
            ROW_NUMBER() OVER (PARTITION BY h.PAT_ID
                               ORDER BY h.HOSP_ADMSN_TIME DESC, h.PAT_ENC_CSN_ID DESC) AS rn
    FROM    PAT_ENC_HSP h)
SELECT PAT_ENC_CSN_ID, PAT_ID, HOSP_ADMSN_TIME
FROM   ranked
WHERE  rn = 1;

Use it for the latest record, the first record, or de-duplication. The tie-breaker after the time column matters. Without it, two encounters with the same admit time return in an arbitrary order, and the “latest” row can change between runs.

Pattern 2: Choose between ROW_NUMBER, RANK, and DENSE_RANK

SELECT  encounter_id, total_charges,
        ROW_NUMBER() OVER (ORDER BY total_charges DESC) AS row_num,
        RANK()       OVER (ORDER BY total_charges DESC) AS rnk,
        DENSE_RANK() OVER (ORDER BY total_charges DESC) AS dense_rnk
FROM    encounter_charges;

ROW_NUMBER never ties, so it picks one row. RANK ties and then skips numbers. DENSE_RANK ties and does not skip. For “top 3 values” use DENSE_RANK. For “exactly one row per key” use ROW_NUMBER.

Pattern 3: Top N per group

WITH dx_counts AS (
    SELECT  h.DEPARTMENT_ID, dx.DX_ID, COUNT(*) AS encounters,
            DENSE_RANK() OVER (PARTITION BY h.DEPARTMENT_ID ORDER BY COUNT(*) DESC) AS r
    FROM    PAT_ENC_HSP h
    JOIN    PAT_ENC_DX dx ON dx.PAT_ENC_CSN_ID = h.PAT_ENC_CSN_ID AND dx.LINE = 1
    WHERE   h.HOSP_DISCH_TIME >= '2026-07-01' AND h.HOSP_DISCH_TIME < '2026-10-01'
    GROUP BY h.DEPARTMENT_ID, dx.DX_ID)
SELECT DEPARTMENT_ID, DX_ID, encounters, r
FROM   dx_counts
WHERE  r <= 3
ORDER BY DEPARTMENT_ID, r;

The window ranks the aggregate in the same query, because windows run after GROUP BY. Ties can return more than three rows per department. Decide whether the business wants ties kept, then pick DENSE_RANK or ROW_NUMBER to match.

Pattern 4: Compare to the previous row with LAG

WITH e AS (
    SELECT  PAT_ID, PAT_ENC_CSN_ID, HOSP_ADMSN_TIME, HOSP_DISCH_TIME,
            LAG(HOSP_DISCH_TIME) OVER (PARTITION BY PAT_ID
                                       ORDER BY HOSP_ADMSN_TIME, PAT_ENC_CSN_ID) AS prior_disch
    FROM    PAT_ENC_HSP)
SELECT  PAT_ID, PAT_ENC_CSN_ID, prior_disch, HOSP_ADMSN_TIME,
        DATEDIFF(day, prior_disch, HOSP_ADMSN_TIME) AS days_since_discharge
FROM    e
WHERE   prior_disch IS NOT NULL
  AND   DATEDIFF(day, prior_disch, HOSP_ADMSN_TIME) BETWEEN 0 AND 30;

This is an operational flag, not the federal measure. The CMS readmission measures apply exclusions, such as planned readmissions and transfers, that this query ignores. Label the output “return within 30 days” unless the business has signed a full definition. For day-count rules, see the length of stay calculation article.

LAG and LEAD take an offset and a default: LAG(col, 1, 0). The first row in each partition has no previous row, so it returns null unless you supply a default. Test that row.

Pattern 5: Running totals with an explicit frame

SELECT  account_id, posting_date, posting_id, amount,
        SUM(amount) OVER (PARTITION BY account_id
                          ORDER BY posting_date, posting_id
                          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_balance
FROM    gl_posting;

Write the frame every time. With ORDER BY and no frame, the default is RANGE from the start to the current row. RANGE treats rows with equal order values as peers and gives them the same total. Two postings on one date then show an identical balance, and the sequence looks wrong. Scenario two below shows the effect. Ledger patterns appear in SQL for financial reporting.

Pattern 6: Moving averages

SELECT  department_id, charge_date, total_charges,
        AVG(total_charges) OVER (PARTITION BY department_id
                                 ORDER BY charge_date
                                 ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7_rows
FROM    daily_charge;

ROWS counts rows, not days. If a department has no row for a Sunday, the “seven day” average spans eight calendar days. Join to a calendar table first so every date exists, with zero for quiet days. The first six rows average fewer than seven values, so mark them as incomplete in the report.

Pattern 7: Share of total

SELECT  department_id, charge_date, total_charges,
        100.0 * total_charges / NULLIF(SUM(total_charges) OVER (PARTITION BY charge_date), 0) AS pct_of_day,
        100.0 * total_charges / NULLIF(SUM(total_charges) OVER (), 0) AS pct_of_all
FROM    daily_charge;

An empty OVER () means “all rows in the result”. It sees only the rows that survive WHERE. If a filter removes departments, the percentages sum to 100 over the filtered set, not over the organization.

Pattern 8: Period-over-period change

WITH m AS (
    SELECT DATEFROMPARTS(YEAR(charge_date), MONTH(charge_date), 1) AS month_start,
           SUM(total_charges) AS total_charges
    FROM   daily_charge GROUP BY DATEFROMPARTS(YEAR(charge_date), MONTH(charge_date), 1))
SELECT  month_start, total_charges,
        LAG(total_charges) OVER (ORDER BY month_start) AS prior_month,
        100.0 * (total_charges - LAG(total_charges) OVER (ORDER BY month_start))
              / NULLIF(LAG(total_charges) OVER (ORDER BY month_start), 0) AS pct_change
FROM    m;

Aggregate first, then apply LAG. Window functions on raw daily rows give a day-over-day change instead. A year-over-year view uses LAG(x, 12) on a complete monthly series, and breaks when a month is missing.

Pattern 9: Gaps and islands

SELECT  department_id, MIN(charge_date) AS island_start, MAX(charge_date) AS island_end, COUNT(*) AS days_in_run
FROM   (SELECT department_id, charge_date,
               DATEADD(day, -ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY charge_date), charge_date) AS grp
        FROM   daily_charge) x
GROUP BY department_id, grp
ORDER BY department_id, island_start;

Subtracting the row number from the date gives the same value for each unbroken run, so grouping on it finds the runs. Use it to find stretches of daily activity or the days a feed went quiet. The date must be unique within each partition. Duplicates break the arithmetic.

Pattern 10: Buckets and percentiles

SELECT  encounter_id, los_days,
        NTILE(4) OVER (ORDER BY los_days) AS quartile,
        PERCENT_RANK() OVER (ORDER BY los_days) AS pct_rank,
        PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY los_days) OVER (PARTITION BY department_id) AS p90_los
FROM    encounter_los;

NTILE forces equal-sized buckets, so equal values can land in different buckets. If the business needs “everyone with the same value gets the same bucket”, use PERCENT_RANK and cut on it. Report the 90th percentile next to the average for skewed measures such as length of stay.

Pattern 11: FIRST_VALUE and LAST_VALUE

SELECT  PAT_ID, HOSP_ADMSN_TIME,
        FIRST_VALUE(DEPARTMENT_ID) OVER (PARTITION BY PAT_ID ORDER BY HOSP_ADMSN_TIME, PAT_ENC_CSN_ID) AS first_dept,
        LAST_VALUE(DEPARTMENT_ID)  OVER (PARTITION BY PAT_ID ORDER BY HOSP_ADMSN_TIME, PAT_ENC_CSN_ID
                                         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_dept
FROM    PAT_ENC_HSP;

LAST_VALUE without the full frame returns the current row, because the default frame ends there. Analysts hit this often and conclude the function is broken. Set the frame to unbounded following.

Pattern 12: De-duplicate staging data

WITH d AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY encounter_csn ORDER BY loaded_at DESC, load_id DESC) AS rn
    FROM   stg.encounter)
DELETE FROM d WHERE rn > 1;

Run a SELECT with WHERE rn > 1 first and review the rows. Take a backup or use a transaction. Oracle and Snowflake do not allow deleting through a CTE, so they need a keyed delete or a rebuilt table.

Scenario: The Follow-Up Call List That Changed Every Morning

A health system built a daily list of patients due a post-discharge follow-up call. The query kept each patient’s latest encounter with ROW_NUMBER. Nurses noticed that some patients appeared one day, vanished the next, and returned later.

I was the QA analyst on the project. The load was stable and the source data had not changed. I ran the list query twice on the same snapshot and compared the keys with EXCEPT. The two runs disagreed.

Before

WITH ranked AS (
    SELECT PAT_ID, PAT_ENC_CSN_ID, HOSP_ADMSN_TIME,
           ROW_NUMBER() OVER (PARTITION BY PAT_ID ORDER BY HOSP_ADMSN_TIME DESC) AS rn
    FROM   PAT_ENC_HSP)
SELECT PAT_ID, PAT_ENC_CSN_ID FROM ranked WHERE rn = 1;

After, with the checks that prove it

-- Fix: deterministic order
WITH ranked AS (
    SELECT PAT_ID, PAT_ENC_CSN_ID, HOSP_ADMSN_TIME,
           ROW_NUMBER() OVER (PARTITION BY PAT_ID
                              ORDER BY HOSP_ADMSN_TIME DESC, PAT_ENC_CSN_ID DESC) AS rn
    FROM   PAT_ENC_HSP)
SELECT PAT_ID, PAT_ENC_CSN_ID FROM ranked WHERE rn = 1;

-- Check 1: how many patients have tied admit times?
SELECT PAT_ID, HOSP_ADMSN_TIME, COUNT(*) AS encounters
FROM   PAT_ENC_HSP
GROUP BY PAT_ID, HOSP_ADMSN_TIME
HAVING COUNT(*) > 1;

-- Check 2: run the fixed query into two temp tables, then compare
SELECT * FROM #run1 EXCEPT SELECT * FROM #run2;   -- expect zero rows
SELECT * FROM #run2 EXCEPT SELECT * FROM #run1;   -- expect zero rows

The data was never wrong. The query left a decision to the optimizer. Any window function whose order key is not unique can behave this way, including LAG, FIRST_VALUE, and running totals. Treat a unique sort key as a requirement.

Scenario: A Running Balance That Skipped a Step

A finance team reviewed an account roll-forward built with a running total. A reviewer flagged that two ledger rows showed the same balance and asked whether a posting was missing. No posting was missing. The query used the default window frame.

-- Before: default frame, ties share a total
SUM(amount) OVER (PARTITION BY account_id ORDER BY posting_date)

-- After: unique order, explicit frame
SUM(amount) OVER (PARTITION BY account_id
                  ORDER BY posting_date, posting_id
                  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

The end balance was the same in both versions, so a tie-out to the trial balance passed. Only the intermediate rows were wrong. When people read the detail, intermediate values matter, so test them as well as the final row.

Choosing SQL Window Functions for Analysts: A Decision Tree

Start from the question the report must answer. The tree below picks the function family. It ends in one rule that applies to every branch: make the sort order unique.

What must the report show?
Compare to aneighbor row
Rank or pick rowsper group
Cumulative ormoving value
Share of a wholeor % of total
LAG / LEADTest the first row
Ties kept: RANK orDENSE_RANKOne row only: ROW_NUMBER
SUM / AVG OVERwith a ROWS frame
SUM(x) OVER(PARTITION BY …)
Every branch: add a tie-breaker so the ORDER BY is unique

Specification Template for Window Function Requirements

A requirement that says “show the latest encounter” invites the defect in scenario one. Write the six decisions below into the story or the data mapping before development starts.

QA test checklist for window function queries

Use the repeatability test from scenario one in your regression pack. The SQL Server vs Oracle vs Snowflake article lists the platform differences that make a window query pass on one engine and fail on another.

A Worked Walkthrough: Reading Window Output Row by Row

Build a seed set small enough to check by hand before you trust a query on millions of rows. One patient with four encounters is enough. Two of them share an admit date, which tests the tie-breaker. The table shows each function over the same rows, ordered by admit time and then encounter key.

Read the table from the top. The first row has no prior discharge, so LAG returns null and the day count is blank. Encounter 9004 gets row number 1, so the latest-encounter pattern keeps it.

Row 9003 shows minus three days. It was admitted on Feb 11, before the prior encounter ended on Feb 14. Overlapping encounters happen when an emergency visit converts to an inpatient stay or when registration creates a second record. A BETWEEN 0 AND 30 filter drops this row from a readmission list, which may or may not match the business rule. Raise the case with the data steward and write the answer into the specification.

Keep this seed set in version control beside the query. After every change, run the query against it and compare the output with the table above. A mismatch in any column points to a specific clause: the partition, the order, the tie-breaker, or the frame.

Platform Differences for Window Functions

The core functions behave the same on SQL Server, Oracle, and Snowflake. The edges differ, and the edges cause ports to fail. Check these rows before you move a query.

The null-ordering row catches the most people. A “latest” query that sorts a nullable column descending puts nulls first on SQL Server and last on Oracle. Add an explicit rule or filter nulls out. The SQL Server vs Oracle vs Snowflake article covers more differences in dates, text, and cost.

-- Snowflake: QUALIFY filters on the window directly
SELECT PAT_ID, PAT_ENC_CSN_ID
FROM   PAT_ENC_HSP
QUALIFY ROW_NUMBER() OVER (PARTITION BY PAT_ID ORDER BY HOSP_ADMSN_TIME DESC, PAT_ENC_CSN_ID DESC) = 1;

-- SQL Server: control null position with a CASE sort key
ORDER BY CASE WHEN HOSP_DISCH_TIME IS NULL THEN 1 ELSE 0 END, HOSP_DISCH_TIME DESC

Performance of SQL Window Functions on Large Tables

A window function needs rows grouped and sorted by its partition and order columns. That sort is usually the cost. On a table of fifty million claim lines, an unfiltered window can run for minutes or spill to disk.

Compare a window against the alternative before you assume it is faster. A window usually beats a self-join or a correlated subquery, because it scans the data once. A window over an unfiltered table can still lose to a narrow aggregate plus a join. Measure on production-sized data.

Role Checklists for Window Function Work

Common Mistakes With SQL Window Functions

When I Would Use Window Functions, GROUP BY, or Something Else

I reach for a window function when the answer needs both the row and its context. When the answer is a single number per group, I use GROUP BY and move on. For more query patterns in this series, see the SQL for IT Analysts hub.

Edge Cases That Break Clean Window Queries

Window results can reveal individual patient histories. Keep outputs inside the approved environment, share keys and aggregates instead of names, and apply minimum necessary to any extract.

Questions Analysts Ask About SQL Window Functions

Is a window function faster than a self-join?

Usually, because it reads the data once and sorts once. A self-join reads the table twice. Test both on production-sized data, because indexes and filters change the result.

Can I use a window function in WHERE or GROUP BY?

No. The database computes windows after those clauses. Put the window in a CTE or subquery, then filter in the outer query. Snowflake also accepts QUALIFY.

What is the difference between PARTITION BY and GROUP BY?

GROUP BY reduces the result to one row per group. PARTITION BY only defines the group for the calculation and leaves every row in place.

Why does my running total repeat values?

You used the default RANGE frame and the order key has duplicates. Add a unique tie-breaker and write ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Run This Before Your Next Release

Pick one report that uses ROW_NUMBER, LAG, or a running total. Check whether its ORDER BY ends in a unique column. Run the query twice on the same snapshot and compare the results with EXCEPT. If any row differs, you have found a defect before your users did.

Sources and Further Reading

• Microsoft Learn OVER clause reference. Official syntax for partitions, ordering, and frames in T-SQL.
• Snowflake window function reference. Official list of Snowflake window functions, including QUALIFY usage.
• Oracle Database SQL Language Reference, analytic functions chapter (docs.oracle.com). Official syntax for Oracle.
• CMS Hospital Readmissions Reduction Program documentation (cms.gov). Official measure definitions and exclusions for readmission reporting.

Table and column names are generic or follow standard Clarity and vary by version. Figures in scenarios are illustrative. Run every query in a non-production environment first.