๐ช SQL Window Functions โ Advanced Analytics Without Losing Rows
Window Functions are one of the most powerful features in SQL.
They allow you to perform calculations across related rows without collapsing the rows into a single result.
This is the key difference between:
GROUP BY
โ combines rows
Window Function
โ keeps the rows and calculates across them
Window Functions are heavily used for:
โข Rankings
โข Running totals
โข Moving averages
โข Previous/next row comparisons
โข Customer analysis
โข Sales analysis
โข Time-series analysis
โข Percentage calculations
โข Top-N analysis
๐ง 1. The Problem Window Functions Solve
Suppose we have:
order_id| customer_id| amount
1| 101| 500
2| 101| 800
3| 102| 300
4| 102| 700
If we use:
SELECT
customer_id,
SUM(amount) AS total_spending
FROM orders
GROUP BY customer_id;
we get:
customer_id| total_spending
101| 1300
102| 1000
The individual orders disappear.
But what if we want:
order_id| customer_id| amount| customer_total
1| 101| 500| 1300
2| 101| 800| 1300
3| 102| 300| 1000
4| 102| 700| 1000
This is where a Window Function is useful.
๐ช 2. Basic Window Function Syntax
The general structure is:
function_name(...) OVER (
PARTITION BY ...
ORDER BY ...
)
Example:
SELECT
order_id,
customer_id,
amount,
SUM(amount) OVER (
PARTITION BY customer_id
) AS customer_total
FROM orders;
The result keeps every order while calculating the customer's total.
๐น 3. What Does OVER() Mean?
"OVER()" tells SQL:
ยซPerform this calculation across a window of rows.ยป
For example:
SUM(amount) OVER ()
means:
ยซCalculate the sum across all rows while keeping every row.ยป
Example:
SELECT
order_id,
amount,
SUM(amount) OVER () AS total_revenue
FROM orders;
Every row will show the overall revenue.
๐ 4. PARTITION BY
"PARTITION BY" divides the data into logical groups.
Example:
SUM(amount) OVER (
PARTITION BY customer_id
)
means:
ยซCalculate the sum separately for each customer.ยป
All Orders
โ
Partition by customer
โ
Customer 101 โ calculate separately
Customer 102 โ calculate separately
Customer 103 โ calculate separately
Unlike "GROUP BY", the original rows remain visible.
๐ 5. GROUP BY vs Window Function
GROUP BY
SELECT
customer_id,
SUM(amount) AS total_spending
FROM orders
GROUP BY customer_id;
Result:
1 row per customer
Window Function
SELECT
order_id,
customer_id,
amount,
SUM(amount) OVER (
PARTITION BY customer_id
) AS total_spending
FROM orders;
Result:
1 row per order
Remember:
GROUP BY reduces rows.
Window Functions preserve rows.
๐ 6. RANK()
"RANK()" assigns a ranking to rows.
Example:
SELECT
employee_name,
salary,
RANK() OVER (
ORDER BY salary DESC
) AS salary_rank
FROM employees;
If salaries are:
90000
80000
70000
the ranks are:
1
2
3
๐ฅ 7. Ranking with Ties
Suppose salaries are:
90000
80000
80000
70000
Using "RANK()":
90000 โ 1
80000 โ 2
80000 โ 2
70000 โ 4
Notice that rank 3 is skipped.
That's how "RANK()" handles ties.
๐ข 8. DENSE_RANK()
"DENSE_RANK()" also handles ties but does not skip the next rank.
For:
90000
80000
80000
70000
the result is:
90000 โ 1
80000 โ 2
80000 โ 2
70000 โ 3
Difference:
RANK()
1, 2, 2, 4
DENSE_RANK()
1, 2, 2, 3
๐ข 9. ROW_NUMBER()
"ROW_NUMBER()" assigns a unique sequential number to every row.