SQL Window Functions: Practical Examples Every Analyst Needs

Basic SQL queries can filter, retrieve, aggregate, and summarize data. But analysts often need to answer more complex questions without collapsing individual rows. That is where SQL window functions become especially useful.

Window functions allow you to rank records, calculate running totals, create moving averages, compare current and previous values, and perform department-wise or category-wise analysis using the OVER() clause. Functions such as ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD(), SUM() OVER(), and AVG() OVER() are widely used in data analytics, reporting, and business intelligence workflows.

Unlike GROUP BY, which combines rows into aggregated results, SQL window functions preserve each original row while adding analytical calculations alongside it.

In this guide, you will learn what SQL window functions are, how the OVER(), PARTITION BY, ORDER BY, and frame clauses work, and how analysts use window functions for ranking, running totals, moving averages, comparisons, and real-world reporting tasks.

Quick Answer

SQL window functions perform calculations across related rows without collapsing the result set.

• Ranking: ROW_NUMBER(), RANK(), DENSE_RANK()
• Comparisons: LAG() and LEAD()
• Running Totals: SUM() OVER()
• Moving Averages: AVG() OVER()
• Grouping Logic: PARTITION BY
• Best For: Analytics, reporting, BI dashboards, and SQL interviews

What Are SQL Window Functions?

SQL window functions calculate values across a defined “window” of rows using the OVER() clause. Unlike GROUP BY, they do not combine rows into a single result. Instead, each row remains visible while additional analytical values are calculated.

This allows analysts to answer questions such as:

• Which employee has the highest salary in each department?
• What is the running total of monthly sales?
• How does today’s revenue compare with yesterday’s?
• What is the average sales value over the last seven days?

Key Components of SQL Window Functions

• OVER() Clause – Defines the window for calculations.
• PARTITION BY – Divides rows into groups.
• ORDER BY – Specifies the calculation order.
• Window Function – Performs the analytical operation.
• Frame Clause – Controls which rows participate in calculations.

Why Learn SQL Window Functions?

SQL window functions are among the most valuable skills for data analysts because they simplify complex reporting tasks and improve query efficiency.

Benefits of Learning SQL Window Functions

Curious to build stronger SQL skills? HCL GUVI’s Mastering MySQL Course helps you learn MySQL fundamentals through practical exercises and real-world database examples. 

Top 5 SQL Window Function Examples Every Analyst Should Know

1. Rank Employees Within Each Department

Use DENSE_RANK() when you want to rank employees separately within each department without leaving gaps after ties.

SELECT employee_name,
       department,
       salary,
       DENSE_RANK() OVER(
           PARTITION BY department
           ORDER BY salary DESC
       ) AS salary_rank
FROM employees;

Use case: Finding the highest-paid, second-highest-paid, or top-N employees in each department.

2. Calculate a Running Sales Total

Use SUM() OVER() to calculate cumulative sales while keeping every transaction visible.

SELECT sales_date,
       sales_amount,
       SUM(sales_amount) OVER(
           ORDER BY sales_date
       ) AS running_total
FROM sales;

Use case: Tracking cumulative revenue, account balances, or inventory movement over time.

3. Compare Current Sales With Previous Sales

Use LAG() to access the value from the previous row.

SELECT sales_date,
       sales_amount,
       LAG(sales_amount) OVER(
           ORDER BY sales_date
       ) AS previous_sales,
       sales_amount -
       LAG(sales_amount) OVER(
           ORDER BY sales_date
       ) AS sales_change
FROM sales;

Use case: Day-over-day, month-over-month, or transaction-to-transaction comparisons.

4. Calculate a Moving Average

Use AVG() with a window frame to calculate an average across the current row and preceding rows.

SELECT sales_date,
       sales_amount,
       AVG(sales_amount) OVER(
           ORDER BY sales_date
           ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ) AS moving_average
FROM sales;

Use case: Smoothing short-term fluctuations in sales, traffic, demand, or financial data.

5. Identify Duplicate Records

Use ROW_NUMBER() to number records inside each duplicate group.

SELECT customer_id,
       email,
       ROW_NUMBER() OVER(
           PARTITION BY email
           ORDER BY customer_id
       ) AS row_num
FROM customers;

Rows where row_num > 1 can be treated as duplicates based on the selected criteria.

Use case: Data cleaning, duplicate detection, and keeping only the first or latest record.

Quick Comparison

These five patterns cover a large share of practical SQL window function problems analysts encounter in dashboards, reporting workflows, and technical interviews.

Master SQL window functions and strengthen your data analysis skills with HCL GUVI’s Data Science Course. Learn SQL, Python, data analytics, and real-world data workflows through 6 months of live online classes, expert mentorship, 1:1 doubt sessions, and placement assistance with 1000+ hiring partners. The program offers weekday and weekend classes in English, Hindi, Telugu, and Tamil.

Popular SQL Window Functions

Real-World Applications of SQL Window Functions

SQL window functions are especially useful when analysts need to compare rows, calculate trends, rank records, or generate cumulative metrics without losing row-level detail.

1. Sales Analytics

Use window functions to calculate:

• Running revenue totals
• Monthly sales rankings
• Moving averages
• Month-over-month growth
• Top-performing products by category

Functions such as SUM() OVER(), RANK(), and LAG() are commonly used here.

2. Financial Reporting

Window functions help analysts track:

• Running account balances
• Transaction comparisons
• Period-wise changes
• Customer spending trends
• Ranking of accounts by value

LAG() and cumulative SUM() are especially useful for financial time-series analysis.

3. HR Analytics

HR teams can use SQL window functions to:

• Rank employees by salary or performance
• Compare salaries within departments
• Identify top performers
• Calculate department averages
• Find salary percentiles or relative positions

PARTITION BY department helps perform these calculations separately for each team.

4. Customer Analytics

Window functions can help identify:

• Highest-value customers
• Repeat purchase patterns
• Previous and next transactions
• Customer-level running spend
• Purchase frequency trends

LAG() and LEAD() are useful when customer behavior needs to be analyzed over time.

5. Inventory and Supply Chain Analysis

Analysts can use window functions to monitor:

• Running stock movement
• Product-level demand trends
• Previous inventory levels
• Supplier performance rankings
• Rolling demand averages

This supports more accurate inventory planning and replenishment decisions.

6. Business Intelligence and Dashboards

Window functions are useful for preparing datasets for BI tools.

They can support:

• Rankings
• Cumulative KPIs
• Rolling metrics
• Trend comparisons
• Category-wise performance
• Period-over-period reporting

This allows dashboards to receive analysis-ready data directly from SQL.

7. Marketing Analytics

Marketing teams can use window functions to:

• Rank campaigns by performance
• Compare current and previous campaign results
• Track cumulative conversions
• Analyze customer engagement over time
• Identify top channels within each region or segment

Best Practices for Using SQL Window Functions

1. Define the Partition Carefully

Use PARTITION BY only when calculations should restart for separate groups.

For example:

RANK() OVER(
    PARTITION BY department
    ORDER BY salary DESC
)

This ranks employees independently within each department.

2. Always Check the ORDER BY Logic

The result of functions such as RANK(), LAG(), LEAD(), running totals, and moving averages depends heavily on row order.

Make sure the ordering column represents the sequence you actually want to analyze.

3. Use Window Frames Explicitly for Rolling Calculations

For moving averages or rolling totals, define the frame clearly.

Example:

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW

This makes the intended seven-row window explicit.

4. Choose the Correct Ranking Function

Use:

• ROW_NUMBER() when every row needs a unique number
• RANK() when ties should share a rank and gaps are acceptable
• DENSE_RANK() when ties should share a rank without gaps

Choosing the wrong function can change the business meaning of the result.

5. Avoid Unnecessary PARTITION BY Clauses

Do not partition data unless the calculation genuinely needs separate groups. Extra partitioning can make queries harder to understand and may increase processing work.

6. Keep Queries Readable

Use:

• Clear indentation
• Meaningful aliases
• CTEs when logic becomes complex
• One analytical step at a time

Readable SQL is easier to debug and explain in interviews or production environments.

7. Validate Edge Cases

Test how your query behaves with:

• Duplicate values
• Ties
• NULL values
• Missing dates
• Empty partitions
• Irregular time intervals

This is particularly important for ranking and time-based analysis.

8. Do Not Replace GROUP BY Automatically

Window functions and GROUP BY solve different problems.

Use GROUP BY when you need one aggregated row per group. Use window functions when you need analytical calculations while retaining the original rows.

9. Filter Window Function Results Correctly

When filtering values generated by a window function, use a subquery or CTE where required.

For example, if you want only the top-ranked rows, calculate the rank first and then filter in the outer query.

10. Review Query Performance

Window functions can become expensive on very large datasets, especially when sorting large partitions.

To improve performance:

• Reduce unnecessary rows early
• Select only needed columns
• Partition logically
• Order by indexed or appropriate columns where possible
• Review the execution plan for heavy analytical queries

SQL Window Functions Interview Roadmap

Use this roadmap to move from basic SQL concepts to interview-ready window function problems in the right order.

Step 1: Strengthen SQL Fundamentals

Before learning window functions, make sure you are comfortable with:

• SELECT
• WHERE
• ORDER BY
• GROUP BY
• Aggregate functions
• JOINs
• Subqueries

These concepts make advanced analytical queries much easier to understand.

Step 2: Learn the OVER() Clause

Start with the basic structure of a window function and understand how OVER() defines the rows involved in a calculation.

Practice simple examples with:

• SUM() OVER()
• AVG() OVER()
• COUNT() OVER()

Step 3: Master PARTITION BY and ORDER BY

Learn how:

• PARTITION BY creates separate calculation groups
• ORDER BY controls row sequence inside each window

Practice department-wise, customer-wise, product-wise, and region-wise calculations.

Step 4: Practice Ranking Functions

Focus on:

• ROW_NUMBER()
• RANK()
• DENSE_RANK()

Solve problems such as:

• Top 3 salaries per department
• Second-highest salary
• Best-selling products by category
• Removing duplicate records

Step 5: Learn LAG() and LEAD()

Use these functions for row-to-row comparisons.

Practice:

• Previous month’s sales
• Day-over-day revenue change
• Previous transaction amount
• Next customer activity

Step 6: Build Running Totals and Moving Averages

Practice:

• Cumulative revenue
• Running balances
• 7-day moving averages
• Rolling performance metrics

This is also where you should understand window frames such as ROWS BETWEEN.

Step 7: Solve Real Interview Patterns

Practice mixed problems involving:

• Ranking within groups
• Top-N records
• Running totals
• Moving averages
• Previous vs current values
• Duplicate handling
• Customer or employee segmentation

Step 8: Explain Your Query Logic

For every solution, be ready to explain:

Function → Partition → Order → Window Frame → Output

Interviewers often evaluate how clearly you explain your reasoning, not just whether the query runs.

Final Interview Readiness Checklist

Before an SQL interview, make sure you can:

• Explain GROUP BY vs window functions
• Differentiate ROW_NUMBER(), RANK(), and DENSE_RANK()
• Use PARTITION BY correctly
• Use LAG() and LEAD()
• Calculate running totals
• Build moving averages
• Solve top-N-per-group problems
• Explain window frame clauses
• Write and explain queries without relying heavily on notes

SQL Window Functions Interview Questions and Answers

1. What is a window function in SQL?

A window function performs a calculation across a set of related rows while preserving each individual row in the result. It uses the OVER() clause to define the window.

2. What is the difference between GROUP BY and window functions?

GROUP BY combines multiple rows into one aggregated result per group. Window functions keep all original rows and add calculated values such as rankings, running totals, or averages.

3. What does PARTITION BY do in a window function?

PARTITION BY divides rows into separate groups before the window function performs its calculation.

For example:

RANK() OVER(
    PARTITION BY department
    ORDER BY salary DESC
)

This ranks employees separately within each department.

4. What is the difference between ROW_NUMBER(), RANK(), and DENSE_RANK()?

• ROW_NUMBER() assigns a unique sequential number to every row.
• RANK() gives tied rows the same rank and leaves gaps afterward.
• DENSE_RANK() gives tied rows the same rank without leaving gaps.

For values 100, 100, 90:

• ROW_NUMBER() → 1, 2, 3
• RANK() → 1, 1, 3
• DENSE_RANK() → 1, 1, 2

5. How do you find the second-highest salary in each department?

Use DENSE_RANK() with PARTITION BY:

SELECT employee_name,
       department,
       salary
FROM (
    SELECT employee_name,
           department,
           salary,
           DENSE_RANK() OVER(
               PARTITION BY department
               ORDER BY salary DESC
           ) AS salary_rank
    FROM employees
) ranked
WHERE salary_rank = 2;

DENSE_RANK() is useful because employees with the same salary receive the same rank.

6. How do you calculate a running total in SQL?

Use SUM() as a window function:

SELECT sales_date,
       sales_amount,
       SUM(sales_amount) OVER(
           ORDER BY sales_date
       ) AS running_total
FROM sales;

Each row shows the cumulative sales amount up to that point.

7. What is the difference between LAG() and LEAD()?

LAG() accesses a value from a previous row, while LEAD() accesses a value from a following row.

For example, LAG() can compare today’s sales with yesterday’s sales, while LEAD() can compare the current value with the next period.

8. How do you compare current sales with previous sales?

Use LAG():

SELECT sales_date,
       sales_amount,
       LAG(sales_amount) OVER(
           ORDER BY sales_date
       ) AS previous_sales
FROM sales;

You can also subtract the previous value from the current value to calculate the change.

9. What is a window frame in SQL?

A window frame defines the exact rows included in a calculation relative to the current row.

For example:

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

This includes the current row and the previous two rows.

Window frames are commonly used for moving averages and rolling calculations.

10. How do you calculate a moving average using a window function?

Use AVG() with a frame clause:

SELECT sales_date,
       sales_amount,
       AVG(sales_amount) OVER(
           ORDER BY sales_date
           ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ) AS moving_average
FROM sales;

This calculates an average using the current row and the previous two rows.

11. Can window functions be used to find duplicate records?

Yes. ROW_NUMBER() can assign numbers to duplicate groups.

SELECT *,
       ROW_NUMBER() OVER(
           PARTITION BY email
           ORDER BY customer_id
       ) AS row_num
FROM customers;

Rows where row_num > 1 can be treated as duplicate records based on the selected partitioning criteria.

12. Why is ORDER BY important inside the OVER() clause?

ORDER BY determines the sequence in which a window function processes rows. It is essential for rankings, running totals, LAG(), LEAD(), and moving averages because these calculations depend on row order.

13. Can aggregate functions be used as window functions?

Yes. Aggregate functions such as SUM(), AVG(), COUNT(), MIN(), and MAX() can be used with OVER().

Example:

SELECT department,
       employee_name,
       salary,
       AVG(salary) OVER(
           PARTITION BY department
       ) AS department_avg
FROM employees;

This shows each employee alongside the average salary of their department.

14. When should you use a window function instead of a subquery?

Use a window function when you need analytical calculations while preserving row-level detail. It can often make ranking, cumulative calculations, and row comparisons cleaner than deeply nested subqueries.

15. Which SQL window functions are most important for interviews?

Focus first on:

• ROW_NUMBER()
• RANK()
• DENSE_RANK()
• LAG()
• LEAD()
• SUM() OVER()
• AVG() OVER()
• PARTITION BY
• ORDER BY
• Window frame clauses

These cover most common SQL window-function interview patterns.

Conclusion

SQL window functions give analysts a powerful way to perform advanced calculations without losing row-level detail. By combining functions such as ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD(), SUM(), and AVG() with the OVER() clause, you can solve common analytical problems with cleaner and more readable SQL.

The key is to understand how PARTITION BY, ORDER BY, and window frames control which rows participate in each calculation. Once these concepts become familiar, tasks such as department-wise ranking, cumulative sales, previous-period comparisons, and moving averages become much easier to implement.

After mastering SQL window functions, strengthen related concepts such as joins, subqueries, CTEs, indexing, and query optimization. Together, these skills provide a stronger foundation for data analytics, business intelligence, reporting, and database-focused roles.

FAQs