SQL Server Interview Question #92

What is the OVER clause?

Aggregate Functions, GROUP BY & Window Functions Mid-Level Intermediate

Detailed Explanation

OVER defines the window of rows used by a window function. It can contain PARTITION BY, ORDER BY, and—where supported—a window frame such as ROWS BETWEEN.

PARTITION BY separates rows into independent groups without collapsing them. ORDER BY defines logical ordering inside each partition. The frame can further restrict which ordered rows participate in a calculation.

Code Example

SELECT Id,
       CategoryId,
       Price,
       AVG(Price) OVER
       (
           PARTITION BY CategoryId
       ) AS CategoryAverage
FROM dbo.Products;