15-SQL-Window_Function

Window Functions in SQL

Window Functions are advanced SQL functions used to perform calculations across a set of rows related to the current row without grouping the result.

Unlike GROUP BY, they do not collapse rows. Every row remains visible.

OVER() clause:

It is used with window functions to define that window
1. partition the rows to form a set of rows
2. order rows within the partition into a particular order

Basic Syntax:

SELECT column_name,
       window_function() OVER (PARTITION BY column ORDER BY column)
FROM table_name;
Parts of Window Function
Part	Meaning
window_function()	Function like ROW_NUMBER(), RANK()
OVER()	Defines the window
PARTITION BY	Divides data into groups
ORDER BY	Defines order inside the group

1️⃣ ROW_NUMBER()

Assigns a unique number to each row.

Example:

SELECT transactions_id,
ROW_NUMBER() OVER(ORDER BY transactions_id) AS row_num
FROM retail_sales;

Example result:

transactions_id	row_num
1	               1
2	               2
3	               3
5	               4

2️⃣ RANK()

Gives ranking but skips numbers when there is a tie.

SELECT total_sale,
RANK() OVER(ORDER BY total_sale DESC) AS rank_num
FROM retail_sales;

Example:

total_sale	rank
1000	     1
900	         2
900	         2
800	         4

3️⃣ DENSE_RANK()

Similar to RANK() but does not skip numbers.

SELECT total_sale,
DENSE_RANK() OVER(ORDER BY total_sale DESC) AS rank_num
FROM retail_sales;

Example:

total_sale	rank
1000	     1
900	         2
900	         2
800          3

4️⃣ LEAD()

Gets the next row value.

SELECT transactions_id,
LEAD(transactions_id) OVER(ORDER BY transactions_id) AS next_id
FROM retail_sales;

Example:

transactions_id	 next_id
1	               2
2	               3
3	               5

Here we can see 4 is missing.

5️⃣ LAG()

Gets the previous row value.

SELECT transactions_id,
LAG(transactions_id) OVER(ORDER BY transactions_id) AS previous_id
FROM retail_sales;

Example:

transactions_id	 previous_id
2	               1
3	               2
5	               3

⭐ Example: Finding Missing IDs Using LEAD()
SELECT transactions_id,
LEAD(transactions_id) OVER(ORDER BY transactions_id) AS next_id
FROM retail_sales;

Then check:

next_id - transactions_id > 1

This shows gaps in transaction IDs.

# Why Window Functions Are Important

🔹They are heavily used in:

🔹Data Analysis

🔹Financial reports

🔹Ranking customers

🔹Running totals

🔹Detecting missing data

🔹Time series analysis

✅ Since we are learning SQL for Data Analyst roles, the 5 most important window functions to master are:

ROW_NUMBER()

RANK()

DENSE_RANK()

LEAD()

LAG()

EXAMPLE:

USE market_star_schema;

-- find the avg sales for each city using window functions
SELECT city, ROUND(sales,2) AS sales,
AVG(sales) OVER(PARTITION BY city ORDER BY sales DESC) AS avg_sales
FROM market_fact_full mff
JOIN cust_dimen cd ON mff.cust_id=cd.cust_id;

-- find the rank(), dense_rank, row_num() for the rows based on order_quantity
SELECT order_quantity,
RANK() OVER(ORDER BY order_quantity DESC) AS order_quantity_rank,
DENSE_RANK() OVER(ORDER BY order_quantity DESC) AS order_quantity_dense_rank,
ROW_NUMBER() OVER(ORDER BY order_quantity DESC) AS order_quantity_row_num
FROM market_fact_full;

-- find the rank(), dense_rank(), row_num() based on sales amount
SELECT *, 
RANK() OVER(ORDER BY sales DESC) AS sales_rank,
DENSE_RANK() OVER(ORDER BY sales DESC) AS sales_dense_rank,
ROW_NUMBER() OVER(ORDER BY sales DESC) AS sales_row_num
FROM market_fact_full;

-- find the rank(), dense_rank(), row_num() for Bangalore and Chennai cities based on avg sales
SELECT city, ROUND(AVG(sales),2) AS avg_sales,
RANK() OVER(ORDER BY AVG(sales) DESC) AS city_rank,
DENSE_RANK() OVER(ORDER BY AVG(sales) DESC) AS city_dense_rank,
ROW_NUMBER() OVER(ORDER BY AVG(sales) DESC) AS city_row_num
FROM cust_dimen cd 
JOIN market_fact_full mff ON cd.cust_id=mff.cust_id
WHERE city IN ('Bangalore','Chennai')
GROUP BY city;

/* find rank(), dense_rank(), row_num() based on avg_profit for the orders placed in October,
March for the years 2011 and 2012 from regular air ship_mode */

SELECT MONTH(order_date), YEAR(order_date), 
AVG(profit) AS avg_profit,
RANK() OVER(ORDER BY AVG(profit) DESC) AS avg_profit_rank,
DENSE_RANK() OVER(ORDER BY AVG(profit) DESC) AS avg_profit_dense_rank,
ROW_NUMBER() OVER(ORDER BY AVG(profit) DESC) AS avg_profit_row_num
FROM market_fact_full mff
JOIN orders_dimen od ON mff.ord_id=od.ord_id
JOIN shipping_dimen sd ON mff.ship_id=sd.ship_id
WHERE MONTH(order_date) IN ('10','03')
AND YEAR(order_date) IN ('2011','2012')
AND ship_mode='Regular Air'
GROUP BY MONTH(order_date), YEAR(order_date);

-- first 5 records with highest sales values without using Limit & offset
WITH sales_summary AS (
SELECT *, 
RANK() OVER(ORDER BY sales DESC) AS sales_rank
FROM market_fact_full)
SELECT * FROM sales_summary
WHERE sales_rank<=5;

-- find the details of the order who made highest sales in each city without using limit & offset
WITH cust_summary AS (
SELECT cd.cust_id, city, ROUND(sales,2) AS sales, 
RANK() OVER(PARTITION BY city ORDER BY sales DESC) AS cust_rank,
DENSE_RANK() OVER(PARTITION BY city ORDER BY sales DESC) AS cust_dense_rank,
ROW_NUMBER() OVER(PARTITION BY city ORDER BY sales DESC) AS cust_row_num
FROM cust_dimen cd
JOIN market_fact_full mff ON cd.cust_id=mff.cust_id)
SELECT * FROM cust_summary
WHERE cust_rank=1;

-- find the profit change compared to previous day and next day for each order date
SELECT order_date, profit, 
LAG(profit) OVER(ORDER BY order_date) AS prev_day_profit,
LEAD(profit) OVER(ORDER BY order_date) AS next_day_profit,
(profit - (LAG(profit,1,0) OVER(ORDER BY order_date))) AS profit_change
FROM market_fact_full mff
JOIN orders_dimen od ON mff.ord_id=od.ord_id;