Data Development, Databases, DP-800, Microsoft Certification, SQL

Write queries that include window functions (DP-800 Exam Prep)

 
This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.
This topic falls under these sections:
Design and develop database solutions (35–40%)
   --> Write advanced T-SQL code
      --> Write queries that include window functions

Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.

Introduction

Window functions are among the most powerful features in Transact-SQL (T-SQL). They enable calculations across a set of rows related to the current row without collapsing the results into a single row, as traditional aggregate functions do. Window functions are widely used in reporting, analytics, business intelligence, financial analysis, and AI-enabled database solutions.

Unlike GROUP BY, which returns one row per group, window functions preserve the individual rows while providing additional calculated values based on a defined “window” of rows.

For the DP-800: Developing AI-Enabled Database Solutions certification exam, you should understand:

  • What window functions are
  • The OVER clause
  • Partitioning data
  • Ordering data within windows
  • Aggregate window functions
  • Ranking functions
  • Offset functions
  • Window frames
  • Practical business scenarios
  • Performance considerations
  • Best practices

Window functions are heavily tested because they allow developers to perform sophisticated calculations efficiently while maintaining readable and maintainable SQL code.

What Is a Window Function?

A window function performs a calculation across a set of rows that are related to the current row.

Unlike aggregate functions used with GROUP BY, a window function does not reduce the number of rows returned.

General syntax:

Function(...) OVER
(
    [PARTITION BY column]
    [ORDER BY column]
)

The OVER clause defines the window over which the calculation occurs.

The OVER Clause

The OVER clause is required for window functions.

It can contain:

  • PARTITION BY
  • ORDER BY
  • Window frame definitions (ROWS or RANGE)

Example:

SELECT
    EmployeeID,
    Salary,
    AVG(Salary) OVER() AS AverageSalary
FROM HumanResources.Employee;

The average salary is calculated across all employees while each employee row remains visible.

PARTITION BY

PARTITION BY divides the result set into logical groups.

Example:

SELECT
    DepartmentID,
    EmployeeID,
    Salary,
    AVG(Salary)
        OVER(PARTITION BY DepartmentID)
        AS DepartmentAverage
FROM HumanResources.Employee;

Each department receives its own average salary.

ORDER BY Within OVER

The ORDER BY clause defines the order of rows within each partition.

Example:

SELECT
    EmployeeID,
    Salary,
    ROW_NUMBER()
        OVER(ORDER BY Salary DESC)
        AS SalaryRank
FROM HumanResources.Employee;

The highest salary receives row number 1.

Aggregate Window Functions

Many aggregate functions can operate as window functions.

Common examples include:

  • SUM()
  • AVG()
  • MIN()
  • MAX()
  • COUNT()

Example:

SELECT
    CustomerID,
    OrderDate,
    TotalAmount,
    SUM(TotalAmount)
        OVER(PARTITION BY CustomerID)
        AS CustomerTotal
FROM Sales.Orders;

Each order row displays the customer’s total sales without grouping the results.

Running Totals

A common use of window functions is calculating running totals.

Example:

SELECT
    OrderDate,
    TotalAmount,
    SUM(TotalAmount)
        OVER
        (
            ORDER BY OrderDate
            ROWS BETWEEN UNBOUNDED PRECEDING
            AND CURRENT ROW
        )
        AS RunningTotal
FROM Sales.Orders;

Each row contains the cumulative total through the current row.

Moving Averages

Window functions simplify moving averages.

Example:

SELECT
    OrderDate,
    SalesAmount,
    AVG(SalesAmount)
        OVER
        (
            ORDER BY OrderDate
            ROWS BETWEEN 2 PRECEDING
            AND CURRENT ROW
        )
        AS ThreeDayAverage
FROM Sales.DailySales;

This example calculates a rolling average over three rows.

Ranking Functions

SQL Server includes several ranking window functions.

These include:

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

ROW_NUMBER()

Assigns a unique sequential number.

Example:

SELECT
    EmployeeID,
    Salary,
    ROW_NUMBER()
        OVER(ORDER BY Salary DESC)
        AS RowNum
FROM HumanResources.Employee;

Even rows with equal salaries receive different numbers.

RANK()

Assigns rankings while allowing gaps after ties.

Example:

SELECT
    EmployeeID,
    Salary,
    RANK()
        OVER(ORDER BY Salary DESC)
        AS SalaryRank
FROM HumanResources.Employee;

If two employees tie for first place, the next rank is 3.

DENSE_RANK()

Assigns rankings without gaps.

Example:

SELECT
    EmployeeID,
    Salary,
    DENSE_RANK()
        OVER(ORDER BY Salary DESC)
        AS SalaryRank
FROM HumanResources.Employee;

If two employees tie for first place, the next rank is 2.

ROW_NUMBER vs. RANK vs. DENSE_RANK

FunctionDuplicate ValuesGaps in Ranking
ROW_NUMBERNoNo
RANKYesYes
DENSE_RANKYesNo

Understanding these differences is a common DP-800 exam objective.

NTILE()

NTILE() divides rows into approximately equal groups.

Example:

SELECT
    EmployeeID,
    Salary,
    NTILE(4)
        OVER(ORDER BY Salary DESC)
        AS Quartile
FROM HumanResources.Employee;

Employees are divided into four salary quartiles.

Offset Functions

Offset functions compare one row to another.

Common functions include:

  • LAG()
  • LEAD()

LAG()

Returns a value from a previous row.

Example:

SELECT
    OrderDate,
    SalesAmount,
    LAG(SalesAmount)
        OVER(ORDER BY OrderDate)
        AS PreviousDaySales
FROM Sales.DailySales;

LEAD()

Returns a value from a following row.

Example:

SELECT
    OrderDate,
    SalesAmount,
    LEAD(SalesAmount)
        OVER(ORDER BY OrderDate)
        AS NextDaySales
FROM Sales.DailySales;

FIRST_VALUE()

Returns the first value in the window.

Example:

SELECT
    EmployeeID,
    Salary,
    FIRST_VALUE(Salary)
        OVER(ORDER BY Salary DESC)
        AS HighestSalary
FROM HumanResources.Employee;

LAST_VALUE()

Returns the last value within the current window frame.

Example:

SELECT
    EmployeeID,
    Salary,
    LAST_VALUE(Salary)
        OVER
        (
            ORDER BY Salary
            ROWS BETWEEN UNBOUNDED PRECEDING
            AND UNBOUNDED FOLLOWING
        )
        AS HighestSalary
FROM HumanResources.Employee;

Because LAST_VALUE() respects the current window frame, explicitly specifying the frame is often necessary to obtain the expected result.

Window Frames

Window frames define which rows participate in a calculation.

Common options include:

  • CURRENT ROW
  • UNBOUNDED PRECEDING
  • UNBOUNDED FOLLOWING
  • n PRECEDING
  • n FOLLOWING

Example:

ROWS BETWEEN 5 PRECEDING
AND CURRENT ROW

This frame includes the current row plus the previous five rows.

ROWS vs. RANGE

ROWSRANGE
Uses physical row positionsUses logical value ranges
Predictable row countsMay include multiple tied rows
Often preferred for running totalsUseful for value-based calculations

For most reporting scenarios, ROWS provides more predictable behavior.

Combining PARTITION BY and ORDER BY

Example:

SELECT
    DepartmentID,
    EmployeeID,
    Salary,
    ROW_NUMBER()
        OVER
        (
            PARTITION BY DepartmentID
            ORDER BY Salary DESC
        )
        AS DepartmentRank
FROM HumanResources.Employee;

Ranking restarts for each department.

Practical Business Uses

Window functions are commonly used for:

  • Sales rankings
  • Running totals
  • Financial reporting
  • Trend analysis
  • Customer segmentation
  • Inventory analysis
  • Employee rankings
  • Time-series analysis
  • Rolling averages
  • Year-over-year comparisons

Performance Considerations

Window functions can require sorting operations.

Performance depends on:

  • Index design
  • Partition size
  • Number of rows
  • ORDER BY columns
  • Available memory

To improve performance:

  • Index columns used in PARTITION BY and ORDER BY.
  • Avoid unnecessary sorting.
  • Limit returned rows when appropriate.
  • Review execution plans.
  • Consider filtered datasets before applying window functions.

AI-Enabled Database Scenarios

Window functions are valuable for preparing data used by AI applications.

Examples include:

  • Ranking search results before intelligent retrieval
  • Identifying the latest customer interactions for Retrieval-Augmented Generation (RAG)
  • Calculating rolling metrics for machine learning features
  • Detecting trends in IoT sensor data
  • Selecting the top-N records for embedding generation
  • Comparing current values with previous observations using LAG() and LEAD()
  • Preparing time-series datasets for AI forecasting models

These capabilities help organize and enrich data before it is consumed by AI pipelines.

Best Practices

  • Always specify an appropriate ORDER BY clause when required.
  • Use PARTITION BY only when logical grouping is needed.
  • Understand the differences among ranking functions.
  • Specify window frames explicitly for running totals and functions such as LAST_VALUE().
  • Create indexes on frequently partitioned or sorted columns.
  • Test performance with production-sized datasets.
  • Avoid unnecessary nested window calculations.
  • Review execution plans for expensive sorts.

Common Exam Tips

For the DP-800 exam, remember these key points:

  • Window functions require the OVER clause.
  • PARTITION BY divides rows into logical groups.
  • ORDER BY defines the order within each window.
  • Window functions preserve individual rows.
  • ROW_NUMBER() always returns unique sequential numbers.
  • RANK() allows gaps after ties.
  • DENSE_RANK() does not leave gaps after ties.
  • LAG() retrieves values from previous rows.
  • LEAD() retrieves values from subsequent rows.
  • Running totals commonly use SUM() with ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Practice Exam Questions

Question 1

Which clause is required for every SQL Server window function?

A. GROUP BY

B. HAVING

C. OVER

D. PARTITION

Answer: C

Explanation: Every window function must include the OVER clause, which defines the window over which the calculation is performed.

Question 2

What is the primary advantage of a window function over a traditional aggregate function?

A. It always executes faster.

B. It preserves individual rows while performing calculations across related rows.

C. It automatically creates indexes.

D. It eliminates the need for sorting.

Answer: B

Explanation: Unlike aggregate functions with GROUP BY, window functions return calculations while preserving each row in the result set.

Question 3

Which window function assigns a unique sequential number to every row, even when duplicate values exist?

A. RANK()

B. DENSE_RANK()

C. NTILE()

D. ROW_NUMBER()

Answer: D

Explanation: ROW_NUMBER() always assigns unique sequential numbers, regardless of duplicate values.

Question 4

Which ranking function assigns the same rank to tied rows without leaving gaps in subsequent rankings?

A. ROW_NUMBER()

B. NTILE()

C. DENSE_RANK()

D. RANK()

Answer: C

Explanation: DENSE_RANK() assigns the same rank to tied rows and continues with the next consecutive rank without gaps.

Question 5

What is the purpose of the PARTITION BY clause?

A. To permanently divide a table into partitions.

B. To group rows into logical partitions for window function calculations.

C. To sort the final result set.

D. To filter rows before processing.

Answer: B

Explanation: PARTITION BY creates logical groups within the result set so calculations are performed independently for each partition.

Question 6

Which function returns the value from the previous row within the defined window?

A. LEAD()

B. FIRST_VALUE()

C. LAST_VALUE()

D. LAG()

Answer: D

Explanation: LAG() retrieves a value from a preceding row within the same window.

Question 7

A developer needs to calculate a running total ordered by transaction date. Which feature is most appropriate?

A. SUM() with OVER and a window frame

B. GROUP BY

C. DISTINCT

D. UNION

Answer: A

Explanation: Running totals are typically calculated using SUM() with the OVER clause and a window frame such as ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Question 8

What is the default behavior of RANK() when multiple rows have the same value?

A. Each tied row receives a different rank.

B. Tied rows receive the same rank, and the next rank contains a gap.

C. Tied rows are ignored.

D. Tied rows receive sequential ranks without gaps.

Answer: B

Explanation: RANK() assigns identical ranks to tied rows and skips the next ranking number accordingly.

Question 9

Which statement about window frames is correct?

A. They are used only with ranking functions.

B. They define which rows participate in a window calculation.

C. They permanently partition a table.

D. They replace the ORDER BY clause.

Answer: B

Explanation: Window frames specify the subset of rows within the window that contribute to the calculation, making them especially useful for running totals and moving averages.

Question 10

How are window functions commonly used in AI-enabled database solutions?

A. They directly generate embeddings from text.

B. They replace vector indexes.

C. They prepare and enrich data by calculating rankings, rolling metrics, and historical comparisons before it is consumed by AI models, intelligent search, or Retrieval-Augmented Generation (RAG) pipelines.

D. They eliminate the need for data preprocessing.

Answer: C

Explanation: Window functions help organize and enrich datasets by calculating analytical metrics, rankings, and trends that serve as valuable inputs to AI workflows and machine learning processes.

Go to the DP-800 Exam Prep Hub main page

Leave a comment