SQL Window Functions Explained (With Simple Examples)

If subqueries felt advanced, window functions might look even more intimidating.

But here’s the truth:

Window functions are just calculations performed across a set of rows while keeping each row visible.

Unlike GROUP BY, they don’t collapse your data.

That’s the key difference.

Let’s simplify everything.

What Are SQL Window Functions?

A window function performs calculations across related rows defined by an OVER() clause.

Basic structure:

FUNCTION_NAME() OVER (
    PARTITION BY column
    ORDER BY column
)

The OVER() clause defines the “window” of rows the function operates on

Why Not Just Use GROUP BY?

Here’s the difference:

• GROUP BY → combines rows into one result per group
• Window functions → keep all rows but add extra calculated columns

This is why window functions are heavily used in dashboards built in Microsoft Power BI and Tableau especially for ranking, running totals, and comparisons.

ROW_NUMBER()

This assigns a unique number to each row.

Example:

SELECT 
    name,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees;

This ranks employees by salary.

RANK() vs DENSE_RANK()

This is a common interview question.

RANK()

If two people tie, the next rank skips a number.

Example ranking:
1, 2, 2, 4

SELECT 
    name,
    salary,
    RANK() OVER (ORDER BY salary DESC) AS rank_position
FROM employees;

DENSE_RANK()

No gaps in ranking.

Example ranking:
1, 2, 2, 3

SELECT 
    name,
    salary,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank_position
FROM employees;

Interview tip: Always know the difference between ROW_NUMBER, RANK, and DENSE_RANK.

PARTITION BY

PARTITION BY splits data into groups before applying the function.

Example:

SELECT 
    name,
    department_id,
    salary,
    RANK() OVER (
        PARTITION BY department_id 
        ORDER BY salary DESC
    ) AS dept_rank
FROM employees;

Now each department has its own ranking.

Think of PARTITION BY as:
“GROUP BY but without collapsing rows.”

Running Total (SUM OVER)

One of the most practical window function examples.

SELECT 
    order_date,
    amount,
    SUM(amount) OVER (
        ORDER BY order_date
    ) AS running_total
FROM sales;

This creates a cumulative total over time.

Extremely common in:

• Revenue dashboards
• KPI tracking
• Financial analysis

Moving Average

Example:

SELECT 
    order_date,
    amount,
    AVG(amount) OVER (
        ORDER BY order_date 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg
FROM sales;

This calculates a rolling average.

Very useful for trend analysis.

Execution Order

Window functions run after:

• FROM
• WHERE
• GROUP BY
• HAVING

But before:

• ORDER BY (final output order)

Understanding execution order helps avoid errors in interviews.

Common Beginner Mistakes

Forgetting ORDER BY inside OVER()
Confusing PARTITION BY with GROUP BY
Not understanding ranking differences
Using window functions when simple aggregation is enough

When Are Window Functions Used?

You’ll see them often in:

• Data analyst interviews
• SQL technical tests
• Business reporting
• Dashboard data modeling
• Performance comparisons

If you’re preparing for entry-level or mid-level roles, mastering window functions gives you a strong advantage.

Window functions are powerful because they let you:

Rank data
Compare within groups
Calculate running totals
Analyze trends
Keep your dataset intact

Once you understand the OVER() clause, everything starts making sense.

Practice with ranking and running totals first, those are the most asked in interviews.

FAQs