Querying into Data (DQL)

Window Functions Decoded

Learning Outcome

4

Relate window functions to real-life Instagram scenarios

3

Apply PARTITION BY and ORDER BY in window functions

2

Use basic window functions like ROW_NUMBER(), RANK(), SUM() OVER()

1

Understand what Window Functions are

 Instagram Influencer Dashboard

Imagine an influencer named Priya.

She has posted 4 photos on Instagram.

Each post has different likes:

On the dashboard, Instagram wants to display:

Each post’s likes

Total likes of Priya

Rank of each post (based on likes)

BUT...

They do not want to merge posts

They do not want to show only total

This is where Window Functions are used.

850

180

300

250

SQL provides Window Functions.

Window functions perform calculations across a set of rows while keeping each row intact.

From the story:

  • We want calculations
  • But we don’t want to group rows
  • We want original rows + extra calculated column​

What are

Window Functions?

Window Functions

  • Perform calculations across related rows
  • Do not reduce number of rows
  • Use the OVER() clause

SELECT column1,
       FUNCTION(column2)
       OVER ([PARTITION BY column3] [ORDER  BY column4])

       AS new_column
FROM table_name;

Syntax :

Columns you want to display

Calculation (SUM, AVG, RANK, ROW_NUMBER, etc.)

Makes it a window function

Table name

Name of result column

Define order (optional)

Divide data into groups (optional)

OVER() Clause

The OVER() clause is what turns a function into a window function.

Without OVER() → it becomes a normal aggregate

With OVER() → it becomes a window function

It tells

How to divide data (Partition)

Which rows to consider

How to order data

Common Window Functions

DENSE_RANK()

PARTITION BY

RANK() 

SUM()

with

OVER()

ROW

NUMBER()

DENSE

RANK()

SELECT post_id,
       likes,
       SUM(likes) OVER() AS total_likes
FROM posts;

OUTPUT:

Example:

SUM() with OVER()

post_id likes total_likes
1 10 60
2 20 60
3 30 60
post_id likes
1 10
2 20
3 30

Does NOT group rows like GROUP BY

Calculates the total sum

But keeps all rows visible

#syntax
SUM(column_name) OVER()

SELECT user_id, post_id, likes,
       SUM(likes) OVER(PARTITION BY user_id) 
       AS user_total_likes
FROM posts;

PARTITION BY

It keeps every row visible

It works like GROUP BY

But it does NOT merge rows

#syntax
function(column_name) 
OVER (PARTITION BY column_name)

SELECT post_id,
       likes,
       ROW_NUMBER() OVER(ORDER BY likes DESC) 
       AS row_num
FROM posts;

ROW_NUMBER()

No duplicates in ranking

It is a ranking window function

It always gives unique numbers

#syntax
ROW_NUMBER() 
OVER (ORDER BY column_name)
SELECT post_id,
       likes,
       RANK() OVER(ORDER BY likes DESC) 
       AS rank
FROM posts;

RANK() Function

But it skips numbers after a tie

Same values get the same rank

#syntax
RANK() 
OVER (ORDER BY column_name DESC)
SELECT post_id,
       likes,
       DENSE_RANK() OVER(ORDER BY likes DESC) 
       AS dense_rank
FROM posts;

DENSE_RANK() Function

BUT it does NOT skip numbers after a tie

Same values get the same rank

#syntax
DENSE_RANK() 
OVER (ORDER BY column_name DESC)

Key Points to Remember

Window functions use OVER()

Do not collapse rows

Can use PARTITION BY

Can use ORDER BY

Used for ranking, running totals, analytics

Summary

5

Important for interviews and real projects

4

Helpful in analytics dashboards

3

Useful for ranking Instagram posts

2

Do not reduce dataset

1

Window functions perform calculations across rows

Quiz

What is the main purpose of window functions?

A. Delete records

B. Modify structure

C. Perform calculations without reducing rows

D. Insert data

Quiz

What is the main purpose of window functions?

A. Delete records

B. Modify structure

C. Perform calculations without reducing rows

D. Insert data

SQL : Window Functions Decoded

By Content ITV

SQL : Window Functions Decoded

  • 2

More from Content ITV