Window Functions

Window functions are a game-changer for data analysis. They perform calculations across a set of rows while keeping all rows in the result.

Why Window Functions?

GROUP BY collapses rows:

SELECT category, COUNT(*) FROM products GROUP BY category;
-- Returns 3 rows (one per category)

Window functions keep ALL rows:

SELECT name, category, COUNT(*) OVER() as total
FROM products;
-- Returns 10 rows with a total column added

ROW_NUMBER()

Assigns a unique sequential number to each row:

SELECT name, price,
       ROW_NUMBER() OVER (ORDER BY price DESC) as rank
FROM products;

Result:

name	price	rank
Laptop	999	1
Tablet	499	2
Phone	299	3

💡 Real-World Use

• Ranking products, customers, or employees
• Top-N per group (top 3 products in each category)
• Pagination (show rows 11–20)

Your Task

Rank all products by price (highest = 1) and show name, price, and rank.