Window functions perform calculations across a set of rows related to the current row without collapsing the rows into a single result. Unlike aggregate functions, window functions retain the row-level details while adding aggregated or ranked data as additional columns.
Key Components of Window Functions:
- OVER() Clause: Defines the window of rows to perform calculations on.
- PARTITION BY: Divides rows into groups or partitions for calculation.
- ORDER BY: Specifies the order of rows within the window.
Questions and Answers:
------------------------------
* How can I calculate a running total of sales for each customer?
SELECT
CustomerID,
OrderDate,
Amount,
SUM(Amount) OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS RunningTotal
FROM Sales;
- `PARTITION BY CustomerID`: Groups rows by each customer.
- `ORDER BY OrderDate`: Orders rows by date within each group.
- `SUM(Amount)`: Adds up the sales amounts progressively.
* How can I rank employees based on their salary within each department?
SELECT
DepartmentID,
EmployeeID,
Salary,
RANK() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS Rank
FROM Employees;
- `RANK()`: Assigns a rank to each employee in descending salary order.
- `PARTITION BY DepartmentID`: Ensures the ranking is specific to each department.
* How can I calculate the percentile rank of students based on their scores?
SELECT
StudentID,
Score,
PERCENT_RANK() OVER (ORDER BY Score DESC) AS Percentile
FROM Students;
- `PERCENT_RANK()`: Calculates the relative standing of each score in the list.
- No `PARTITION BY` clause means the calculation applies to all rows.
* How can I find the sales amount of the previous order for each customer?
SELECT
CustomerID,
OrderID,
OrderDate,
Amount,
LAG(Amount) OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS PreviousOrderAmount
FROM Sales;
- `LAG(Amount)`: Returns the value of the previous row in the window.
- `PARTITION BY CustomerID`: Ensures the calculation is specific to each customer.
* How can I calculate the 3-month moving average of sales for each product?
SELECT
ProductID,
OrderDate,
Amount,
AVG(Amount) OVER (PARTITION BY ProductID ORDER BY OrderDate ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS MovingAvg
FROM Sales;
- `ROWS BETWEEN 2 PRECEDING AND CURRENT ROW`: Defines a sliding window of 3 rows.
- `AVG(Amount)`: Calculates the average over this window.
These examples demonstrate how window functions can provide powerful tools for advanced analytics while preserving row-level details.
