SQL Window Functions Practice Questions with Solutions

SQL Window Functions perform calculations across a set of rows related to the current row without grouping the data into a single result. SQL Window Functions practice questions with solutions help to build concepts

Unlike aggregate functions (SUM(), AVG(), COUNT()), window functions allow you to:

• Rank records
• Calculate running totals
• Compare current and previous rows
• Compare current and next rows
• Find top-performing employees
• Generate leaderboards
• Analyze trends

Window functions are heavily used in:

• Data Analytics
• Business Intelligence
• Financial Reporting
• HR Dashboards
• Sales Analysis
• Banking Systems
• E-commerce Analytics

They are also one of the most frequently asked SQL interview topics.

Why Use Window Functions?

Window functions help you:

• Rank employees by salary
• Find top-selling products
• Calculate cumulative sales
• Compare month-over-month performance
• Analyze customer purchase history
• Build business dashboards
• Generate advanced reports

Basic Syntax

window_function() OVER
(
    PARTITION BY column_name
    ORDER BY column_name
)

Sample Table Used in This Chapter

employees

1. SQL ROW_NUMBER() to Rank Employees by Salary

Problem Statement

The HR department wants to assign a unique rank to every employee based on salary.

The employee with the highest salary should receive Rank 1.

SQL Solution

SELECT
    employee_name,
    salary,

    ROW_NUMBER() OVER
    (
        ORDER BY salary DESC
    ) AS salary_rank

FROM employees;

Sample Output

Explanation

ROW_NUMBER() assigns a unique sequential number to every row.

Even if two employees have the same salary, they receive different row numbers.

Notice:

• Riya → Rank 3
• Neha → Rank 4

Although both earn ₹70,000, their row numbers remain unique.

Concepts Covered

• ROW_NUMBER()
• ORDER BY
• Ranking

2. SQL ROW_NUMBER() with PARTITION BY

Problem Statement

Generate employee rankings within each department.

Each department should start ranking from 1.

SQL Solution

SELECT
    employee_name,
    department,
    salary,

    ROW_NUMBER() OVER
    (
        PARTITION BY department

        ORDER BY salary DESC
    ) AS department_rank

FROM employees;

Sample Output

Explanation

PARTITION BY department divides employees into separate groups.

Ranking starts from 1 inside every department.

Without PARTITION BY, the ranking would be calculated for the entire company.

Concepts Covered

• ROW_NUMBER()
• PARTITION BY
• Department-wise Ranking

3. SQL ROW_NUMBER() to Display Top 3 Highest Paid Employees

Problem Statement

Display only the Top 3 highest-paid employees.

SQL Solution

WITH employee_rank AS
(
    SELECT
        employee_name,
        salary,

        ROW_NUMBER() OVER
        (
            ORDER BY salary DESC
        ) AS salary_rank

    FROM employees
)

SELECT
    employee_name,
    salary,
    salary_rank

FROM employee_rank

WHERE salary_rank <= 3;

Sample Output

Explanation

The CTE first assigns row numbers.

The outer query filters only the first three rows.

This technique is widely used for:

• Top Sales Reports
• Highest Revenue Products
• Best Employees
• Business Leaderboards

Concepts Covered

• ROW_NUMBER()
• CTE
• Top-N Queries

4. SQL ROW_NUMBER() to Find the First Order Placed by Each Customer

Problem Statement

An online shopping company wants to identify the first order placed by every customer.

Write an SQL query using ROW_NUMBER().

Sample Table

orders

SQL Solution

WITH customer_orders AS
(
    SELECT
        order_id,
        customer_name,
        order_date,

        ROW_NUMBER() OVER
        (
            PARTITION BY customer_name
            ORDER BY order_date
        ) AS order_rank

    FROM orders
)

SELECT
    order_id,
    customer_name,
    order_date

FROM customer_orders

WHERE order_rank = 1;

Sample Output

Explanation

The PARTITION BY customer_name creates a separate group for each customer.

Inside each group, ROW_NUMBER() sorts orders by date.

The earliest order receives Row Number = 1.

The outer query selects only those first orders.

This technique is commonly used in:

• Customer Analytics
• First Purchase Reports
• CRM Systems
• Loyalty Programs

Concepts Covered

• ROW_NUMBER()
• PARTITION BY
• ORDER BY
• Customer Analytics

5. SQL ROW_NUMBER() to Display the Latest Salary Record for Each Employee

Problem Statement

A payroll system stores salary history for employees.

Display only the latest salary record of every employee.

Sample Table

salary_history

SQL Solution

WITH latest_salary AS
(
    SELECT
        employee_name,
        salary,
        effective_date,

        ROW_NUMBER() OVER
        (
            PARTITION BY employee_name
            ORDER BY effective_date DESC
        ) AS salary_rank

    FROM salary_history
)

SELECT
    employee_name,
    salary,
    effective_date

FROM latest_salary

WHERE salary_rank = 1;

Sample Output

Explanation

Each employee may have multiple salary records.

ROW_NUMBER() ranks salary records by effective date in descending order.

The newest salary receives Rank 1.

The final query returns only the latest salary for each employee.

This technique is widely used in:

• Payroll Systems
• HR Dashboards
• Employee Management
• Salary Reporting

Concepts Covered

• ROW_NUMBER()
• Latest Record
• Payroll Database

Interview Tip

A very common SQL interview question is:

“Find the latest record for each customer or employee.”

The standard solution is:

ROW_NUMBER() OVER
(
    PARTITION BY column_name
    ORDER BY date_column DESC
)

Then filter:

WHERE row_number = 1

This pattern appears frequently in real-world SQL projects and interviews.

6. SQL RANK() to Rank Employees by Salary

Problem Statement

A company wants to rank employees according to their salary.

If two employees have the same salary, they should receive the same rank, and the next rank should be skipped.

Write an SQL query using RANK().

Sample Table

employees

SQL Solution

SELECT
    employee_name,
    salary,

    RANK() OVER
    (
        ORDER BY salary DESC
    ) AS salary_rank

FROM employees;

Sample Output

Explanation

RANK() assigns the same rank to duplicate values.

Since Riya and Neha have the same salary:

• Both receive Rank 3
• Rank 4 is skipped
• The next employee receives Rank 5

Concepts Covered

• RANK()
• Window Function
• Salary Ranking

7. SQL DENSE_RANK() to Rank Employees Department-wise

Problem Statement

Generate salary rankings within each department.

Employees with the same salary should receive the same rank, but no rank should be skipped.

Use DENSE_RANK().

Sample Table

employees

SQL Solution

SELECT
    employee_name,
    department,
    salary,

    DENSE_RANK() OVER
    (
        PARTITION BY department
        ORDER BY salary DESC
    ) AS department_rank

FROM employees;

Sample Output

Explanation

DENSE_RANK() also assigns the same rank to duplicate values.

However, unlike RANK(), it does not skip rank numbers.

Example:

• Rank 1
• Rank 1
• Rank 2

There is no missing Rank 2.

Concepts Covered

• DENSE_RANK()
• PARTITION BY
• Department Ranking

8. Difference Between ROW_NUMBER(), RANK(), and DENSE_RANK()

Problem Statement

A company wants to understand the difference between the three SQL ranking functions.

Sample Table

SQL Solution

SELECT
    employee_name,
    salary,

    ROW_NUMBER() OVER
    (
        ORDER BY salary DESC
    ) AS row_number,

    RANK() OVER
    (
        ORDER BY salary DESC
    ) AS rank_number,

    DENSE_RANK() OVER
    (
        ORDER BY salary DESC
    ) AS dense_rank

FROM employees;

Sample Output

Explanation

ROW_NUMBER()

• Every row gets a unique number.

RANK()

• Duplicate values share the same rank.
• The next rank is skipped.

DENSE_RANK()

• Duplicate values share the same rank.
• No rank numbers are skipped.

Concepts Covered

• ROW_NUMBER()
• RANK()
• DENSE_RANK()
• Ranking Comparison

9. SQL RANK() to Find Top Selling Products

Problem Statement

Rank products according to the number of units sold.

Products with equal sales should receive the same rank.

Sample Table

product_sales

SQL Solution

SELECT
    product_name,
    units_sold,

    RANK() OVER
    (
        ORDER BY units_sold DESC
    ) AS sales_rank

FROM product_sales;

Sample Output

Explanation

Monitor and Keyboard sold the same number of units.

Both receive Rank 2.

The next available rank becomes Rank 4.

Concepts Covered

• RANK()
• Sales Reports
• Product Analytics

10. SQL DENSE_RANK() to Rank Students by Marks

Problem Statement

Assign rankings to students according to their marks.

Students with equal marks should receive the same rank, and no ranks should be skipped.

Sample Table

students

SQL Solution

SELECT
    student_name,
    marks,

    DENSE_RANK() OVER
    (
        ORDER BY marks DESC
    ) AS student_rank

FROM students;

Sample Output

Explanation

Neha and Amit scored the same marks.

Both receive Rank 2.

The next student receives Rank 3 because DENSE_RANK() never skips rank numbers.

Concepts Covered

• DENSE_RANK()
• Student Ranking
• Academic Reports

11. SQL LAG() to Compare Previous Month Sales

Problem Statement

A company wants to compare each month’s sales with the previous month’s sales.

Use the LAG() window function.

Sample Table

monthly_sales

SQL Solution

SELECT
    month,
    sales,

    LAG(sales) OVER
    (
        ORDER BY month
    ) AS previous_month_sales

FROM monthly_sales;

Sample Output

Explanation

LAG() retrieves the value from the previous row based on the specified ordering.

The first row has no previous record, so it returns NULL.

This function is widely used for:

• Month-over-month sales analysis
• Financial reporting
• Trend analysis
• Performance comparison

Concepts Covered

• LAG()
• Previous Row Comparison
• Sales Analysis

12. SQL LEAD() to Compare Next Month Sales

Problem Statement

Display each month’s sales along with the next month’s sales.

Use the LEAD() window function.

SQL Solution

SELECT
    month,
    sales,

    LEAD(sales) OVER
    (
        ORDER BY month
    ) AS next_month_sales

FROM monthly_sales;

Sample Output

Explanation

LEAD() returns the value from the next row.

The last row has no following record, so the result is NULL.

This is commonly used in:

• Forecasting
• Business Reporting
• Sales Comparison
• Revenue Planning

Concepts Covered

• LEAD()
• Next Row Comparison
• Trend Analysis

13. SQL Running Total Using SUM() OVER()

Problem Statement

A company wants to calculate the running total of monthly sales.

SQL Solution

SELECT
    month,
    sales,

    SUM(sales) OVER
    (
        ORDER BY month
    ) AS running_total

FROM monthly_sales;

Sample Output

Explanation

SUM() OVER() calculates a cumulative total without using GROUP BY.

Each row contains the sum of all previous rows including the current row.

Running totals are commonly used in:

• Revenue Dashboards
• Financial Reports
• Inventory Tracking
• Business Intelligence

Concepts Covered

• SUM() OVER()
• Running Total
• Window Aggregation

14. SQL Moving Average Using Window Functions

Problem Statement

Calculate the moving average of sales over the current and previous month.

SQL Solution

SELECT
    month,
    sales,

    AVG(sales) OVER
    (
        ORDER BY month
        ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
    ) AS moving_average

FROM monthly_sales;

Sample Output

Explanation

The window frame:

ROWS BETWEEN 1 PRECEDING AND CURRENT ROW

means:

• Include the previous row
• Include the current row

The average is calculated over these rows.

Moving averages are frequently used in:

• Stock Market Analysis
• Business Forecasting
• KPI Dashboards
• Sales Trends

Concepts Covered

• AVG() OVER()
• Window Frames
• Moving Average

15. SQL Department-wise Running Salary Total

Problem Statement

Calculate the cumulative salary paid within each department.

Sample Table

employees

SQL Solution

SELECT
    employee_name,
    department,
    salary,

    SUM(salary) OVER
    (
        PARTITION BY department
        ORDER BY salary
    ) AS running_department_salary

FROM employees;

Sample Output

Explanation

PARTITION BY department creates a separate running total for each department.

The cumulative salary restarts whenever the department changes.

This technique is useful for:

• Payroll Reports
• Department Budget Analysis
• HR Dashboards
• Financial Reporting

Concepts Covered

• PARTITION BY
• SUM() OVER()
• Running Total
• Department-wise Analysis

Chapter Summary

In this chapter, you learned how SQL Window Functions perform calculations across a set of related rows without collapsing the result into a single row.

Unlike aggregate functions that return one result per group, window functions preserve every row while adding valuable analytical information such as rankings, running totals, previous values, and moving averages.

Throughout this chapter, you practiced:

• Using ROW_NUMBER() to assign unique row numbers
• Using RANK() to handle ties with skipped rankings
• Using DENSE_RANK() to handle ties without skipping rankings
• Using LAG() to compare the current row with the previous row
• Using LEAD() to compare the current row with the next row
• Calculating running totals with SUM() OVER()
• Calculating moving averages with AVG() OVER()
• Using PARTITION BY to analyze data within groups
• Solving real-world reporting and analytics problems

Window Functions are among the most frequently used SQL features in reporting, dashboards, and interview questions for Data Analysts, Business Analysts, and SQL Developers.

Key Takeaways

• Window Functions analyze related rows while keeping every row in the output.
• ROW_NUMBER() assigns a unique sequential number to each row.
• RANK() assigns the same rank to duplicate values and skips the next rank.
• DENSE_RANK() assigns the same rank to duplicate values without skipping ranks.
• LAG() retrieves values from previous rows.
• LEAD() retrieves values from upcoming rows.
• SUM() OVER() calculates running totals.
• AVG() OVER() calculates moving averages.
• PARTITION BY divides data into independent groups before calculations.
• Window Functions are essential for dashboards, financial reports, sales analysis, and interview preparation.

Frequently Asked Questions (FAQs)

1. What is a Window Function in SQL?

A Window Function performs calculations across a group of related rows while returning every row in the result set.

Example:

SELECT
    employee_name,
    salary,

    ROW_NUMBER() OVER
    (
        ORDER BY salary DESC
    ) AS salary_rank

FROM employees;

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

3. What does PARTITION BY do?

PARTITION BY divides data into separate groups before applying a Window Function.

Example:

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

Each department receives its own independent ranking.

4. What is LAG() used for?

LAG() retrieves data from the previous row.

It is commonly used for:

• Month-over-month comparisons
• Sales trend analysis
• Financial reporting
• Performance tracking

5. What is LEAD() used for?

LEAD() retrieves data from the next row.

It is useful for:

• Forecasting
• Future value comparison
• Sequential analysis
• Business reporting

6. How do Window Functions differ from GROUP BY?

7. Where are Window Functions used in real-world projects?

They are widely used in:

• Power BI Dashboards
• Tableau Reports
• Financial Dashboards
• Payroll Systems
• Banking Applications
• Sales Reporting
• Customer Analytics
• Inventory Management
• Business Intelligence
• Data Warehousing

8. Are Window Functions important for SQL interviews?

Yes.

They are one of the most frequently asked advanced SQL topics in interviews for:

• Data Analyst
• Business Analyst
• SQL Developer
• BI Developer
• Data Engineer

Interviewers commonly ask candidates to solve ranking, running total, and comparison problems using Window Functions.

Written by Shubhranshu Shekhar, who has trained 20000+ students in coding.

Rishabh Kumar