SQL Window Functions Tutorial: ROW_NUMBER, RANK, LAG and Running Totals

Window functions are the point where SQL goes from “I can pull data” to “I can answer the real question”. Rank sales reps within each region, compare today with yesterday, build a running total: all of it needs window functions, and all of it comes up in analyst interviews. This SQL window functions tutorial uses two tiny tables so you can see every result row and check it by hand.

Window functions are taught in month 3 of Omebly Academy’s SQL and Databases course, and they power one of its capstone projects, HR analytics.

Quick answer: A window function calculates across related rows without collapsing them. You write function() OVER (PARTITION BY ... ORDER BY ...). Use ROW_NUMBER, RANK and DENSE_RANK for rankings, LAG and LEAD to compare with previous or next rows, and SUM() OVER (ORDER BY ...) for running totals. To filter on the result, wrap it in a CTE.

Window functions vs GROUP BY

GROUP BY squashes rows: ten sales rows become one row per region. A window function keeps all ten rows and adds a column calculated over a “window” of related rows.

-- One row per region
SELECT region, SUM(amount) FROM sales GROUP BY region;

-- Every row, plus its region's total
SELECT rep, region, amount,
       SUM(amount) OVER (PARTITION BY region) AS region_total
FROM sales;

The parts of OVER()

• PARTITION BY splits rows into groups (like GROUP BY, but nothing collapses). Leave it out and the whole table is one window.
• ORDER BY sets the order inside each partition. Rankings, LAG and running totals need it.
• Frame (ROWS BETWEEN ...) limits which rows count, for moving averages and precise running totals.

The sample tables

sales (one row per rep, this month)

rep	region	amount
Asha	East	500
Rahul	East	300
Meera	East	300
Tanvi	East	200
Imran	West	700
Sara	West	400

daily_sales

day	amount
2026-10-01	1000
2026-10-02	1500
2026-10-03	800
2026-10-04	1200
2026-10-05	2000

Notice that Rahul and Meera tie on 300. That tie is what makes the ranking functions differ.

ROW_NUMBER, RANK and DENSE_RANK

SELECT rep, region, amount,
  ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC, rep) AS row_num,
  RANK()       OVER (PARTITION BY region ORDER BY amount DESC)      AS rnk,
  DENSE_RANK() OVER (PARTITION BY region ORDER BY amount DESC)      AS dense_rnk
FROM sales;

rep	region	amount	row_num	rnk	dense_rnk
Asha	East	500	1	1	1
Meera	East	300	2	2	2
Rahul	East	300	3	2	2
Tanvi	East	200	4	4	3
Imran	West	700	1	1	1
Sara	West	400	2	2	2

What each one does

• ROW_NUMBER gives every row a unique number. With a tie, the order is arbitrary unless you add a tie-breaker; here rep puts Meera before Rahul alphabetically.
• RANK gives ties the same rank, then skips: 1, 2, 2, 4. Like a race where two runners share second place and nobody is third.
• DENSE_RANK gives ties the same rank without skipping: 1, 2, 2, 3.

Which to use

Question	Function
“Pick exactly one row per group” (latest order, top seller)	ROW_NUMBER
“Show positions like a leaderboard, with shared places”	RANK
“Find the second-highest salary, counting ties as one”	DENSE_RANK

The PARTITION BY restarts numbering for each region, which is why Imran is 1 again.

Top N per group

“Top 2 reps in each region” is one of the most common interview questions. You can’t put a window function in WHERE, because WHERE runs before window functions are calculated. Use a CTE:

WITH ranked AS (
  SELECT rep, region, amount,
         ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC, rep) AS rn
  FROM sales
)
SELECT rep, region, amount
FROM ranked
WHERE rn <= 2;

Result: Asha and Meera for East, Imran and Sara for West. Swap ROW_NUMBER for DENSE_RANK if tied reps should both make the cut; Rahul would then join the East list. BigQuery and DuckDB also offer QUALIFY rn <= 2 as a shortcut, but the CTE works everywhere.

Share of total with PARTITION BY

SELECT rep, region, amount,
       ROUND(100.0 * amount / SUM(amount) OVER (PARTITION BY region), 1) AS pct_of_region
FROM sales;

East totals 1,300, so Asha’s 500 is 38.5%. West totals 1,100, so Imran’s 700 is 63.6%. Multiplying by 100.0 rather than 100 avoids integer division in PostgreSQL and SQL Server, which would round everything down to whole numbers.

LAG and LEAD: compare with the previous row

SELECT day, amount,
       LAG(amount) OVER (ORDER BY day)          AS prev_day,
       amount - LAG(amount) OVER (ORDER BY day) AS change
FROM daily_sales;

day	amount	prev_day	change
2026-10-01	1000	NULL	NULL
2026-10-02	1500	1000	500
2026-10-03	800	1500	-700
2026-10-04	1200	800	400
2026-10-05	2000	1200	800

The first day has no previous row, so it’s NULL. LAG(amount, 1, 0) would return 0 instead, and LAG(amount, 7) looks back seven rows, which is handy for week-on-week comparisons on daily data. LEAD does the same thing looking forward.

Percentage change

ROUND(100.0 * (amount - LAG(amount) OVER (ORDER BY day))
      / LAG(amount) OVER (ORDER BY day), 1) AS pct_change

If the previous value can be 0, wrap the divisor in NULLIF(..., 0) to avoid a divide-by-zero error.

Running totals

SELECT day, amount,
       SUM(amount) OVER (ORDER BY day
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM daily_sales;

day	amount	running_total
2026-10-01	1000	1000
2026-10-02	1500	2500
2026-10-03	800	3300
2026-10-04	1200	4500
2026-10-05	2000	6500

Why the frame clause matters

If you write only SUM(amount) OVER (ORDER BY day), the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. RANGE treats rows with the same day as one group, so if a day appears twice, both rows get the same, combined running total. With one row per day you won’t notice; with raw order data you will. Writing ROWS explicitly, or adding a tie-breaker such as order_id to the ORDER BY, removes the surprise. The PostgreSQL documentation on window functions explains frames in detail.

Add PARTITION BY to restart the total, for example per month or per store.

Moving averages

SELECT day, amount,
       ROUND(AVG(amount) OVER (ORDER BY day
                               ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS avg_3day
FROM daily_sales;

day	amount	avg_3day
2026-10-01	1000	1000.00
2026-10-02	1500	1250.00
2026-10-03	800	1100.00
2026-10-04	1200	1166.67
2026-10-05	2000	1333.33

The first two days average fewer than three rows, because there’s nothing earlier. If that would mislead a reader, show the average only from day three onward.

Other window functions worth knowing

• NTILE(4) splits ordered rows into four roughly equal buckets, useful for quartiles of customers by spend.
• FIRST_VALUE / LAST_VALUE return the first or last value in the window. LAST_VALUE needs a full frame (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) or it just returns the current row.
• COUNT(*) OVER (PARTITION BY …) shows group sizes next to each row, which is great for spotting duplicates.

Mistakes that catch beginners

1. Filtering in WHERE. Wrap the window function in a CTE and filter outside.
2. Ties with ROW_NUMBER. Without a tie-breaker, results can change between runs.
3. Forgetting PARTITION BY. You get one ranking across the whole table instead of per group.
4. Default RANGE frames. Use ROWS for running totals on data with repeated order values.
5. Integer division. Multiply by 100.0 for percentages.
6. Gaps in dates. LAG compares with the previous row, not the previous day. If a day is missing, fill it from a calendar table first, usually with a LEFT JOIN; our guide to SQL joins explained shows how.

Practice questions

Using the two tables above:

1. Rank all reps across the company, ignoring region, with DENSE_RANK.
2. Show each rep’s gap to the top seller in their region.
3. Find the best and worst day in daily_sales with ROW_NUMBER.
4. Add a running total that restarts each month (you’ll need more data).
5. Flag days where sales fell compared with the previous day.

When these feel natural, use them in a portfolio piece: a monthly sales trend with running totals and rankings is a solid start. Our list of data analyst portfolio projects has datasets to try them on, and for where window functions sit in the bigger picture, read our guide on how to learn SQL for data analysis.

Window functions checklist

• I know which rows form each partition
• My ORDER BY has a tie-breaker when I need unique numbers
• Running totals use an explicit ROWS frame
• I filter on window results in an outer query, not WHERE
• Percentages use decimal division

Learn it with Omebly

Omebly Academy’s SQL and Databases course uses PostgreSQL as the main database, with MySQL differences explained, and tools such as DBeaver and pgAdmin. Month 3 covers window functions, schema design and normalisation, indexes and views. Classes are live and mentor-led, with recordings and weekly one-to-one reviews.

• Core (3 months, $699): querying, joins, CTEs, window functions and schema design, with 2 capstone projects.
• Professional (6 months, $1,199): adds query optimisation, stored procedures, transactions and PostgreSQL and MySQL admin basics, with 4 capstones, mock interviews and portfolio review.
• Career (12 months, $1,999): adds data warehousing with a BigQuery introduction, dbt basics and a live client-style project with the Omebly team, plus placement assistance (CV help, mock interviews, portfolio review and referrals where possible, not a job guarantee).

Not sure which track fits? Message us on WhatsApp or contact us for a free first call.