Tutorials›SQL›Window Functions
🟢 Free Demo
SQL TutorialTopic 19 of 20

Window Functions

ROW_NUMBER, RANK, DENSE_RANK, LEAD, LAG

✅ What You Will Learn

›What window functions are and how they differ from GROUP BY
›How ROW_NUMBER(), RANK(), and DENSE_RANK() work
›How to calculate running totals with SUM() OVER()
›How to access previous and next row values with LAG() and LEAD()
›How PARTITION BY creates separate windows per group

Window functions perform calculations across a set of rows that are related to the current row — without collapsing the rows the way GROUP BY does. They are one of the most powerful features in SQL and a key skill for senior data analyst roles.

The "window" is the set of rows the function operates on, defined by the OVER() clause. Window functions are used for rankings, running totals, moving averages, and comparing a row to previous or next rows.

📋 Window functions add computed columns without collapsing rows

order_idcustomer_nameproductamountcity
1001Rahul SharmaLaptop45000Delhi
1002Priya VermaMobile Phone18000Noida
1003Amit KumarHeadphones3500Gurgaon
1004Sneha KapoorLaptop52000Delhi
1005Vikram SinghTablet28000Noida
1006Neha JoshiLaptop48000Delhi

Syntax

SQL SYNTAX
function_name() OVER (
  PARTITION BY column    -- optional: divide into groups
  ORDER BY column        -- defines row order within the window
)

Examples

Example 1ROW_NUMBER — assign a unique rank to each row
SELECT customer_name,
       product,
       amount,
       ROW_NUMBER() OVER (ORDER BY amount DESC) AS rank_by_amount
FROM orders;
OUTPUT
customer_name | product      | amount | rank_by_amount
--------------|--------------|--------|---------------
Sneha Kapoor  | Laptop       | 52000  | 1
Rahul Sharma  | Laptop       | 45000  | 2
Priya Verma   | Mobile Phone | 18000  | 3
Amit Kumar    | Headphones   | 3500   | 4
💡

ROW_NUMBER always gives unique numbers — even if two rows have the same value. Compare with RANK which handles ties differently.

Example 2RANK vs DENSE_RANK — handling ties
SELECT product,
       amount,
       RANK()       OVER (ORDER BY amount DESC) AS rank_with_gaps,
       DENSE_RANK() OVER (ORDER BY amount DESC) AS rank_no_gaps
FROM orders;
💡

If two rows tie for rank 2, RANK gives both a 2 and skips to 4. DENSE_RANK gives both a 2 and continues with 3.

Example 3PARTITION BY — rank within each group
SELECT customer_name,
       product,
       amount,
       RANK() OVER (PARTITION BY product ORDER BY amount DESC) AS rank_within_product
FROM orders;
💡

PARTITION BY product restarts the ranking for each product separately. This lets you find the top order per product category.

Example 4LAG and LEAD — compare with previous/next row
SELECT order_date,
       SUM(amount) AS daily_revenue,
       LAG(SUM(amount)) OVER (ORDER BY order_date) AS prev_day_revenue,
       SUM(amount) - LAG(SUM(amount)) OVER (ORDER BY order_date) AS day_on_day_change
FROM orders
GROUP BY order_date
ORDER BY order_date;
💡

LAG accesses the previous row's value. LEAD accesses the next row. Both are used for period-over-period comparisons — daily, weekly, monthly growth calculations.

Example 5Running total with SUM OVER
SELECT order_date,
       amount,
       SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders
ORDER BY order_date;
OUTPUT
order_date  | amount | running_total
------------|--------|-------------
2026-01-15  | 45000  | 45000
2026-01-16  | 18000  | 63000
2026-01-16  | 3500   | 66500
2026-01-17  | 52000  | 118500
💡

SUM OVER without PARTITION gives a running total across all rows in the defined order.

📌 Key Points to Remember

  • ✓Window functions do not collapse rows — unlike GROUP BY
  • ✓OVER() defines the window — the set of rows to calculate across
  • ✓PARTITION BY divides the window into groups (like GROUP BY but without collapsing)
  • ✓ORDER BY inside OVER() controls which rows come "before" in the window
  • ✓ROW_NUMBER, RANK, DENSE_RANK are the most asked-about in SQL interviews

🏢 Real-World Application

Window functions are what separate intermediate SQL writers from advanced ones. They are used in almost every complex analytics task. "Rank each salesperson within their region by monthly revenue" — RANK() OVER(PARTITION BY region ORDER BY revenue DESC). "Calculate a 7-day rolling average of daily orders" — AVG(orders) OVER(ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW). "Find month-over-month revenue change" — revenue - LAG(revenue, 1) OVER(ORDER BY month). Data analyst roles at top companies like Google, Meta, Flipkart, and HDFC Bank regularly test window function skills in SQL interviews.

⚠️ Common Mistakes to Avoid

WRONGConfusing RANK() and DENSE_RANK()
FIXRANK() skips numbers after a tie: 1, 2, 2, 4. DENSE_RANK() does not skip: 1, 2, 2, 3. Use DENSE_RANK() when you do not want gaps in the ranking sequence.
WRONGTrying to use window functions in WHERE clause
FIXWindow functions cannot be used directly in WHERE — they are evaluated after WHERE. Wrap the window function in a subquery or CTE: SELECT * FROM (SELECT *, ROW_NUMBER() OVER(...) AS rn FROM t) WHERE rn = 1.
WRONGForgetting ORDER BY inside OVER() for running totals
FIXSUM() OVER() without ORDER BY returns the total for all rows (like a scalar aggregate). Add ORDER BY inside OVER to get a running total: SUM(amount) OVER(ORDER BY order_date).
✏️Test Yourself

Which window function assigns a unique sequential number to each row?

❓ Frequently Asked Questions

What are window functions in SQL?

Window functions perform calculations across a set of rows related to the current row — without collapsing the result like GROUP BY does. They add a computed column to each row while keeping all rows visible. Common ones: ROW_NUMBER(), RANK(), SUM() OVER(), LAG(), LEAD().

What is the difference between GROUP BY and window functions?

GROUP BY collapses rows into a single summary row per group. Window functions keep all rows intact and add computed values alongside them. Use GROUP BY for aggregated reports; use window functions when you need both detail and aggregated context in the same row.

What is PARTITION BY in window functions?

PARTITION BY divides rows into groups (partitions) within which the window function is applied independently. ROW_NUMBER() OVER(PARTITION BY product ORDER BY amount DESC) numbers rows within each product group, restarting at 1 for each product.

What is the difference between ROW_NUMBER, RANK, and DENSE_RANK?

All three assign rankings but handle ties differently. ROW_NUMBER gives a unique number to every row even with ties. RANK gives the same rank to ties but skips the next rank(1,2,2,4). DENSE_RANK gives the same rank to ties without skipping (1,2,2,3).

What are LAG and LEAD functions in SQL?

LAG(col, n) returns the value of col from n rows before the current row. LEAD(col, n) returns the value from n rows after. Both are used for period-over-period comparisons: LAG(revenue, 1) gets last month's revenue for month-over-month change calculations.

✏️ Practice Exercise

Write a query to show each order with a running total of revenue. Then add a column showing each order's rank by amount within its product category.

← PreviousDate FunctionsNext →CTEs — Common Table Expressions
🎓 Level Up Faster

Learn SQL with Live Trainer Guidance

These tutorials give you the theory. Our live SQL course at EVIKA Academy, Noida teaches you to apply SQL on real company datasets — with a trainer who uses it daily at MakeMyTrip.