SQL Window Functions: The Skill You Need Beyond Basic CRUD

Blog Thumbnail

If you are a software engineer, you should learn SQL window functions because dashboards, reports, analytics screens, leaderboards, financial summaries, and admin panels all need more than simple SELECT, INSERT, UPDATE, and DELETE.

Basic SQL is enough to store and fetch data. But real product work usually asks better questions.

Which student ranked first in each branch? What is the running balance of a bank account after every transaction? What is the total score of a subject, but without removing the original rows?

That is where window functions become useful.

The Problem with GROUP BY

Most developers first solve aggregation problems using GROUP BY.

Example: suppose you have an exam_scores table, and you want the total score for each subject.

SELECT 
    e.subject,
    SUM(e.score) AS subject_total
FROM exam_scores AS e
GROUP BY e.subject;

This works. It gives one row per subject.

But it also changes the shape of the result.

If the original table had 30 rows, and there are only 4 subjects, the output becomes 4 rows. The detailed records are gone from the result. Student name, exam attempt, date, individual score — all removed unless explicitly grouped or aggregated.

Sometimes that is fine.

Sometimes it is not.

Window Functions Keep the Rows Alive

A window function lets you calculate across a group of rows without collapsing the table.

Here is the window-function version:

SELECT 
    e.*,
    SUM(e.score) OVER (PARTITION BY e.subject) AS subject_total
FROM exam_scores AS e;

This query keeps every original row. Then it adds one extra column: subject_total.

The important part is this:

OVER (PARTITION BY e.subject)

PARTITION BY creates logical groups, or windows, inside the result. One window for English. One for Math. One for History. One for SQL.

Then SUM(e.score) runs inside each subject window.

So every Math row gets the Math total. Every English row gets the English total. The table remains detailed, but now it also carries summary information.

That is the main difference.

GROUP BY reduces rows. Window functions enrich rows.

A Real Example: Running Bank Balance

Window functions become even more valuable when order matters.

Think about a bank passbook. A user deposits money, withdraws money, deposits again. The balance after each transaction depends on all previous transactions.

That is called a running total, or cumulative sum.

A sample table might look like this:

CREATE TABLE bank_transactions (
    transaction_id INT,
    account_holder VARCHAR(100),
    transaction_date DATE,
    transaction_type VARCHAR(20),
    amount DECIMAL(10, 2)
);

If withdrawals are stored as negative amounts, the balance calculation becomes easier.

Example query:

SELECT 
    *,
    SUM(amount) OVER (
        PARTITION BY account_holder
        ORDER BY transaction_date
    ) AS closing_balance
FROM bank_transactions;

This query does two things.

First, it separates transactions by account holder:

PARTITION BY account_holder

So Rahul’s balance is calculated only from Rahul’s transactions. Shubham’s balance is calculated only from Shubham’s transactions.

Second, it applies a timeline:

ORDER BY transaction_date

That order is critical. A transaction on 7 January cannot affect the closing balance on 3 January. The database must calculate the balance in chronological order.

For the first row, the window contains only the first transaction. For the second row, it contains the first and second. For the third row, it contains the first three.

And so on.

That is how SQL produces a running balance.

Ranking with Window Functions

Window functions are not only for SUM, AVG, or other aggregate calculations. SQL also has ranking functions.

Three common ones are:

ROW_NUMBER()
RANK()
DENSE_RANK()

Suppose you want to rank students inside each branch based on total score.

First, you may calculate total score per student:

SELECT 
    s.student_id,
    s.name,
    s.branch,
    SUM(e.score) AS total_score
FROM students AS s
INNER JOIN exam_scores AS e
    ON s.student_id = e.student_id
GROUP BY 
    s.student_id,
    s.name,
    s.branch;

Now ranking can be added.

SELECT 
    s.student_id,
    s.name,
    s.branch,
    SUM(e.score) AS total_score,
    ROW_NUMBER() OVER (
        PARTITION BY s.branch
        ORDER BY SUM(e.score) DESC
    ) AS row_num
FROM students AS s
INNER JOIN exam_scores AS e
    ON s.student_id = e.student_id
GROUP BY 
    s.student_id,
    s.name,
    s.branch;

Here, ranking restarts for every branch because of:

PARTITION BY s.branch

CS gets its own ranking. IT gets its own ranking.

The ordering is based on highest score first:

ORDER BY SUM(e.score) DESC

ROW_NUMBER() vs RANK() vs DENSE_RANK()

These three functions look similar, but they behave differently when two rows have the same score.

ROW_NUMBER()

ROW_NUMBER()

Always gives unique numbers.

If two students have the same score, one may get rank 2 and the other rank 3. It does not treat ties as equal.

Example:

1, 2, 3, 4

RANK()

RANK()

Gives the same rank to tied records, but skips the next number.

Example:

1, 2, 2, 4

If two students are tied at rank 2, the next student becomes rank 4.

DENSE_RANK()

Gives the same rank to tied records and does not skip the next number.

Example:

1, 2, 2, 3

This is often better for leaderboards where you still want a clean top-three list.

A full ranking query can look like this:

SELECT 
    s.student_id,
    s.name,
    s.branch,
    SUM(e.score) AS total_score,

    ROW_NUMBER() OVER (
        PARTITION BY s.branch
        ORDER BY SUM(e.score) DESC
    ) AS row_num,

    RANK() OVER (
        PARTITION BY s.branch
        ORDER BY SUM(e.score) DESC
    ) AS rank_num,

    DENSE_RANK() OVER (
        PARTITION BY s.branch
        ORDER BY SUM(e.score) DESC
    ) AS dense_rank_num

FROM students AS s
INNER JOIN exam_scores AS e
    ON s.student_id = e.student_id
GROUP BY 
    s.student_id,
    s.name,
    s.branch;

Final Thought

SQL window functions are one of those topics many developers skip because basic SQL feels enough in the beginning. Then dashboard requirements arrive. Finance reports arrive. Ranking logic arrives. Running totals arrive.

At that point, GROUP BY alone is not enough.

Window functions let you calculate totals, running balances, ranks, and comparisons while keeping row-level detail available. That makes them extremely useful for analytics-heavy applications.

Learn them once, properly. They show up more often than people expect.

Thanks a lot for reading this.

I always enjoy hearing what you think — so if something here stood out to you or you just want to share your thoughts, feel free to drop a comment. I’m always around to chat.

And if you enjoyed the blog, don’t forget to leave a clap — it really helps! 👏

If you want to stay in touch or see more of what I’m doing, you can find me here:

• ▶️ YouTube: youtube.com/@yashjainio
• 🎯 Topmate: topmate.io/yashjainio
• 🔗 LinkedIn: linkedin.com/in/yashjainio
• 💻 GitHub: github.com/yashjainio

Let’s keep learning, creating, messing up, fixing things, and growing together.