🔢 Window Functions – Perform Calculations Over a Set of Rows

(because sometimes you need to calculate values across multiple rows without grouping them)

🔧 Quick Syntax

SELECT column, aggregate_function() OVER (PARTITION BY column ORDER BY column)
FROM table;

This tells SQL:
“Apply an aggregate function like SUM, AVG, or ROW_NUMBER over a set of rows (window) without collapsing them into one row.”

👀 Imagine You Have This sales Table Again:

Scenario: You want to find the running total of quantity sold for each product.

🔧 Step 1: Calculate a Running Total with Window Functions

To get a running total, we’ll use the SUM() function along with OVER().

SELECT product, sale_date, quantity,
       SUM(quantity) OVER (PARTITION BY product ORDER BY sale_date) AS running_total
FROM sales;

✅ Result:

🔁 How It Works:

• SUM(quantity) OVER() calculates a running total for the quantity of each product.
• PARTITION BY product restarts the running total for each product.
• ORDER BY sale_date orders the rows by sale date to calculate the running total in order.

🧃 Recap

• Window functions let you perform calculations across multiple rows without collapsing them into one row.
• SUM() OVER() is used for things like running totals, and you can use other window functions like ROW_NUMBER(), RANK(), and AVG() to perform similar calculations.
• Use PARTITION BY to restart the calculation for different groups (like products), and ORDER BY to order the data within those groups.