SQL Window Functions – Basics
Table of Contents
1. SQL Window Basics
1.1 Calculate the Total Sales Across All Orders
Task:
Write a query to calculate the total sales across all orders in Sales.Orders.
💡 Suggested Answers
SELECT
SUM(Sales) AS Total_Sales
FROM Sales.Orders;
1.2 Calculate the Total Sales for Each Product
Task:
Write a query to calculate the total sales for each product in Sales.Orders.
💡 Suggested Answers
SELECT
ProductID,
SUM(Sales) AS Total_Sales
FROM Sales.Orders
GROUP BY ProductID;
2. SQL Window OVER Clause
2.1 Total Sales Across All Orders with Details
Task:
Find the total sales across all orders, but still show each order row with OrderID, OrderDate, ProductID, and Sales.
💡 Suggested Answers
SELECT
OrderID,
OrderDate,
ProductID,
Sales,
SUM(Sales) OVER () AS Total_Sales
FROM Sales.Orders;
3. SQL Window PARTITION Clause
3.1 Total Sales Overall and Per Product with Details
Task:
Find the total sales across all orders and the total sales for each product, while still showing details like OrderID, OrderDate, ProductID, and Sales.
💡 Suggested Answers
SELECT
OrderID,
OrderDate,
ProductID,
Sales,
SUM(Sales) OVER () AS Total_Sales,
SUM(Sales) OVER (PARTITION BY ProductID) AS Sales_By_Product
FROM Sales.Orders;
3.2 Total Sales Overall, Per Product, and Per Product+Status
Task: Find:
- total sales across all orders
- total sales for each product
- total sales for each combination of product and order status
while still showing OrderID, OrderDate, ProductID, OrderStatus, and Sales.
💡 Suggested Answers
SELECT
OrderID,
OrderDate,
ProductID,
OrderStatus,
Sales,
SUM(Sales) OVER () AS Total_Sales,
SUM(Sales) OVER (PARTITION BY ProductID) AS Sales_By_Product,
SUM(Sales) OVER (PARTITION BY ProductID, OrderStatus) AS Sales_By_Product_Status
FROM Sales.Orders;
4. SQL Window ORDER Clause
4.1 Rank Each Order by Sales
Task:
Rank each order by Sales from highest to lowest, using a window ranking function.
💡 Suggested Answers
SELECT
OrderID,
OrderDate,
Sales,
RANK() OVER (ORDER BY Sales DESC) AS Rank_Sales
FROM Sales.Orders;
5. SQL Window FRAME Clause
5.1 Total Sales for Current and Next Two Orders
Task:
For each order, calculate total sales by Order Status for the current order and the next two orders (based on OrderDate).
💡 Suggested Answers
SELECT
OrderID,
OrderDate,
ProductID,
OrderStatus,
Sales,
SUM(Sales) OVER (
PARTITION BY OrderStatus
ORDER BY OrderDate
ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING
) AS Total_Sales
FROM Sales.Orders;
5.2 Total Sales for Current and Previous Two Orders
Task:
For each order, calculate total sales by Order Status for the current order and the previous two orders (based on OrderDate).
💡 Suggested Answers
SELECT
OrderID,
OrderDate,
ProductID,
OrderStatus,
Sales,
SUM(Sales) OVER (
PARTITION BY OrderStatus
ORDER BY OrderDate
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS Total_Sales
FROM Sales.Orders;
5.3 Total Sales from Previous Two Orders Only
Task: For each order, calculate total sales by Order Status from the previous two orders only (excluding the current row).
💡 Suggested Answers
SELECT
OrderID,
OrderDate,
ProductID,
OrderStatus,
Sales,
SUM(Sales) OVER (
PARTITION BY OrderStatus
ORDER BY OrderDate
ROWS 2 PRECEDING
) AS Total_Sales
FROM Sales.Orders;
5.4 Cumulative Total Sales up to Current Order
Task: For each order, calculate the cumulative total sales by Order Status from the first order in that status up to the current order.
💡 Suggested Answers
SELECT
OrderID,
OrderDate,
ProductID,
OrderStatus,
Sales,
SUM(Sales) OVER (
PARTITION BY OrderStatus
ORDER BY OrderDate
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS Total_Sales
FROM Sales.Orders;
5.5 Cumulative Total Sales from Start to Current Row
Task: For each order, calculate the cumulative total sales by Order Status from the start of that status partition to the current row, using the shorter frame syntax.
💡 Suggested Answers
SELECT
OrderID,
OrderDate,
ProductID,
OrderStatus,
Sales,
SUM(Sales) OVER (
PARTITION BY OrderStatus
ORDER BY OrderDate
ROWS UNBOUNDED PRECEDING
) AS Total_Sales
FROM Sales.Orders;
6. SQL Window Rules
6.1 Rule 1 – Window Functions Only in SELECT or ORDER BY
Task:
The following query attempts to use a window function in the WHERE clause.
Write the query exactly as in the script to show this invalid usage.
💡 Suggested Answers
/* RULE 1:
Window functions can only be used in SELECT or ORDER BY clauses
*/
SELECT
OrderID,
OrderDate,
ProductID,
OrderStatus,
Sales,
SUM(Sales) OVER (PARTITION BY OrderStatus) AS Total_Sales
FROM Sales.Orders
WHERE SUM(Sales) OVER (PARTITION BY OrderStatus) > 100; -- Invalid: window function in WHERE clause
6.2 Rule 2 – No Nesting of Window Functions
Task: The following query nests window functions, which is not allowed. Write the same query to illustrate this rule.
💡 Suggested Answers
/* RULE 2:
Window functions cannot be nested
*/
SELECT
OrderID,
OrderDate,
ProductID,
OrderStatus,
Sales,
SUM(SUM(Sales) OVER (PARTITION BY OrderStatus)) OVER (PARTITION BY OrderStatus) AS Total_Sales -- Invalid nesting
FROM Sales.Orders;
7. SQL Window with GROUP BY
7.1 Rank Customers by Their Total Sales
Task: For each customer, calculate the total sales and then rank customers by their total sales (from highest to lowest).
💡 Suggested Answers
/* TASK 12:
Rank customers by their total sales
*/
SELECT
CustomerID,
SUM(Sales) AS Total_Sales,
RANK() OVER (ORDER BY SUM(Sales) DESC) AS Rank_Customers
FROM Sales.Orders
GROUP BY CustomerID;