SQL window functions (also called analytic functions) are powerful tools for performing calculations across rows that are related to the current row, without collapsing the result set like GROUP BY. They are widely used in reporting, ranking, and trend analysis.
Why Use Window Functions?
Window functions let you:
- Calculate running totals or moving averages
- Rank rows within partitions
- Compare each row to overall aggregates (like average or max)
- Assign row numbers based on custom ordering
This enables advanced insights that are difficult or impossible with simple aggregate queries.
Basic Syntax
1
2
3
SELECT column1, column2,
SUM(amount) OVER (PARTITION BY category ORDER BY date) AS running_total
FROM sales;
Key parts:
- OVER() → defines the window
- PARTITION BY → groups data within the window
- ORDER BY → defines row order inside each partition
Example: Ranking Customers by Sales
Imagine you want to rank customers by their total purchase amount.
1
2
3
4
5
6
7
8
9
SELECT
CustomerID,
CompanyName,
SUM(Quantity * UnitPrice) AS TotalSales,
RANK() OVER (ORDER BY SUM(Quantity * UnitPrice) DESC) AS SalesRank
FROM Customers
JOIN Orders ON Orders.CustomerID = Customers.CustomerID
JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID
GROUP BY CustomerID, CompanyName;
This gives you:
- Total sales per customer
- Their rank compared to others
Example: Calculating Running Totals
You can track how sales accumulate over time.
1
2
3
4
5
SELECT
OrderDate,
SUM(Quantity * UnitPrice) OVER (ORDER BY OrderDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal
FROM Orders
JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID;
Common Window Functions
- ROW_NUMBER() → unique row sequence
- RANK() → rank with gaps
- DENSE_RANK() → rank without gaps
- LEAD() / LAG() → look ahead or behind
- NTILE(n) → divide rows into n equal parts
Best Practices
- Use PARTITION BY carefully to group logically
- Watch performance on large datasets; indexes help
- Combine with CTEs (Common Table Expressions) for clearer structure
- Remember window functions do not filter rows; use WHERE or QUALIFY
SQL window functions transform how you analyze and understand data, enabling richer, more detailed insights that go beyond basic aggregates.