Window Functions

🪟 Window Functions: Your Data’s Magic Telescope

Imagine you’re watching a race. You can see who’s in first place, second place, and third place—all at the same time. That’s what Window Functions do for your data!

🎯 What Are Window Functions?

Think of your database table like a big classroom full of students. Normal SQL can tell you things like:

• “How many students are there?” (COUNT)
• “What’s the average grade?” (AVG)

But what if you want to know: “What is each student’s rank in the class, while still seeing everyone’s names and grades?”

That’s where Window Functions come in! 🚀

The Magic Behind It

A Window Function looks at a “window” (a group of rows) and calculates something—but keeps every single row visible.

SELECT
    name,
    score,
    RANK() OVER (ORDER BY score DESC) AS ranking
FROM students;

Result:

See? Everyone stays in the list, but now we know their rank! ✨

📦 The OVER Clause: Opening Your Window

The OVER clause is like opening a window to peek at your data. Without it, Window Functions can’t work.

Think of It This Way

Imagine you’re in a house with no windows. You can’t see outside! The OVER clause opens a window so you can look at other rows.

-- This counts ALL rows in the table
SELECT
    product_name,
    COUNT(*) OVER () AS total_products
FROM products;

What happens:

• Every row shows the SAME total count
• You still see each product individually

| product_name | total_products |
|--------------|----------------|
| Apple        | 5              |
| Banana       | 5              |
| Cherry       | 5              |
| Date         | 5              |
| Elderberry   | 5              |

The OVER () with empty parentheses means: “Look at ALL rows in the table.”

🧩 PARTITION BY: Dividing Into Groups

What if your classroom has multiple classes—Class A, Class B, and Class C? You want to rank students within their own class, not against everyone!

That’s what PARTITION BY does. It creates mini-windows for each group.

The Birthday Cake Analogy 🎂

Imagine cutting a big birthday cake:

• The whole cake = your entire table
• Each slice = a partition (a separate group)
• Decorations on each slice = calculations for that group only

SELECT
    class_name,
    student_name,
    score,
    RANK() OVER (
        PARTITION BY class_name
        ORDER BY score DESC
    ) AS class_rank
FROM students;

Result:

Emma is #1 in Class A. Sophia is #1 in Class B. Each class is ranked separately! 🎉

🔢 ROW_NUMBER: Giving Everyone a Unique Ticket

ROW_NUMBER() is like handing out numbered tickets at a bakery. Everyone gets a unique number, even if you arrived at the same time as someone else.

No Ties Allowed! 🎫

SELECT
    product,
    sales,
    ROW_NUMBER() OVER (ORDER BY sales DESC)
        AS row_num
FROM store_sales;

Result:

Notice: Laptop and Phone have the same sales (500), but they get different numbers (1 and 2). ROW_NUMBER doesn’t care about ties!

When to Use It

• Creating unique row identifiers
• Pagination (showing page 1, page 2, etc.)
• Picking the “first” item from each group

🏆 RANK: The Fair Olympics Judge

RANK() is like an Olympics medal ceremony. If two people tie for 1st place, they BOTH get gold. But then the next person gets 3rd place (not 2nd)!

The Podium Example 🥇🥇🥉

SELECT
    athlete,
    points,
    RANK() OVER (ORDER BY points DESC) AS ranking
FROM competition;

Result:

Ana and Bob tied at 100 points → both get rank 1 Carol comes next → she gets rank 3 (position 2 is skipped!)

Visual Comparison

graph TD
    A["🥇 Rank 1: Ana"] --> C["🥉 Rank 3: Carol"]
    B["🥇 Rank 1: Bob"] --> C
    C --> D["Rank 4: Dan"]

📊 DENSE_RANK: No Gaps Allowed!

DENSE_RANK() is like RANK’s friendlier cousin. When there’s a tie, it gives the same rank—but doesn’t skip numbers.

The Friendly Leaderboard 🏅

SELECT
    player,
    score,
    DENSE_RANK() OVER (ORDER BY score DESC)
        AS dense_ranking
FROM game_scores;

Result:

Max and Zoe tied → both get rank 1 Leo comes next → he gets rank 2 (not 3!)

RANK vs DENSE_RANK: Quick Comparison

Remember: RANK skips, DENSE_RANK doesn’t! 🎯

🍕 NTILE: Slicing Your Data Pizza

NTILE(n) divides your rows into n equal groups (as equal as possible). It’s like cutting a pizza into slices!

Dividing Students into Groups 🍕

SELECT
    student,
    score,
    NTILE(4) OVER (ORDER BY score DESC) AS quartile
FROM exam_results;

Result (8 students, 4 groups):

8 students ÷ 4 groups = 2 students per group!

Common Uses

• NTILE(4) → Quartiles (top 25%, second 25%, etc.)
• NTILE(10) → Deciles (top 10%, etc.)
• NTILE(100) → Percentiles

🎨 Putting It All Together

Let’s see all our new friends working together!

SELECT
    employee_name,
    department,
    salary,
    ROW_NUMBER() OVER (
        ORDER BY salary DESC
    ) AS row_num,
    RANK() OVER (
        ORDER BY salary DESC
    ) AS ranking,
    DENSE_RANK() OVER (
        ORDER BY salary DESC
    ) AS dense_ranking,
    NTILE(3) OVER (
        ORDER BY salary DESC
    ) AS salary_tier
FROM employees;

The Complete Picture

🧠 Quick Memory Trick

Think of a race finish line:

🚀 You Did It!

You’ve just learned one of SQL’s most powerful features! Window Functions let you:

✅ Keep all your rows visible ✅ Calculate rankings and positions ✅ Compare each row to others ✅ Group and analyze data like a pro

Next time you need to find “the top seller in each region” or “which customers are in the top 25%”—you’ve got the tools! 🎉

“The window is open. Now you can see the whole view!” 🪟✨