DEV Community: Saami abbas Khan

Confused by UNBOUNDED PRECEDING and CURRENT ROW in SQL window functions? I was too. This guide breaks down exactly how window frames work and uses LeetCode 1204 to show how they create a running total step by step.

Saami abbas Khan — Sun, 23 Aug 2026 12:34:06 +0000

Saami abbas Khan

Aug 23

SQL Window Frames Explained: How UNBOUNDED PRECEDING Creates a Running Total

#sql #database #beginners #mysql

9 min read

SQL Window Frames Explained: How UNBOUNDED PRECEDING Creates a Running Total

Saami abbas Khan — Sun, 23 Aug 2026 12:30:12 +0000

If you have started learning SQL window functions, you have probably seen something like this:

SUM(weight) OVER(
    ORDER BY turn
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

At first glance, the syntax looks intimidating.

What exactly is a frame?

What does UNBOUNDED PRECEDING mean?

Why does CURRENT ROW not mean that the calculation only considers the current row?

And how does this produce:

instead of just giving the total 100 everywhere?

I had the same confusion, so let's break it down from first principles and then use LeetCode 1204 — Last Person to Fit in the Bus as a practical example.

1. First: What Is a Window Function?
2. So What Is a Window?
3. What Is a Window Frame?
4. Understanding UNBOUNDED PRECEDING
5. Understanding CURRENT ROW
6. Putting It Together
7. Why Doesn't CURRENT ROW Limit the Scope?
8. ROWS vs RANGE
9. What Happens If We Don't Write the Frame?
10. Not Every Window Function Uses the Frame
11. A Real Problem: LeetCode 1204
12. Building the Running Total
13. Remove Anyone Who Exceeds the Limit
14. Using FIRST_VALUE()
15. Complete Solution
16. The Mental Model I Use for Window Frames
17. Other Useful Window Frames
18. Final Takeaway
Quick Reference
References

1. First: What Is a Window Function?

A normal aggregate function such as:

SUM(weight)

combines multiple rows into a single result.

For example:

weight
------
10
20
30
40

A normal SUM() gives:

The individual rows are no longer represented in the result of that aggregation.

A window function is different.

When we write:

SUM(weight) OVER(...)

SQL calculates a sum using a set of related rows, but keeps the original rows.

So instead of:

we can get something like:

That is the first important idea:

A window function performs a calculation across related rows without collapsing those rows into one row.

2. So What Is a Window?

Consider:

SUM(weight) OVER(
    ORDER BY turn
)

There are several pieces here.

SUM(weight)

This tells SQL what calculation to perform.

OVER(...)

turns the calculation into a window function.

ORDER BY turn

tells SQL how the rows should be ordered inside that window.

But there is one more concept:

Which rows should actually be included for the current row?

That is where the window frame comes in.

3. What Is a Window Frame?

A frame is a subset of the rows in the current window/partition that the function operates on for the current row.

Think of it as a moving boundary.

For example, suppose we have:

turn	weight
1	10
2	20
3	30
4	40

If we want a running total, we want the frame to behave like this:

For turn 1:

[10]


For turn 2:

[10, 20]


For turn 3:

[10, 20, 30]


For turn 4:

[10, 20, 30, 40]

Therefore the results are:

The frame is what lets us describe this behavior.

4. Understanding UNBOUNDED PRECEDING

Now let's look at:

UNBOUNDED PRECEDING

This means:

Start at the first row of the partition.

It does not mean "some extremely large number of rows before the current row."

It literally means that the frame begins at the beginning of the partition.

So:

UNBOUNDED PRECEDING

is essentially saying:

Start here
↓
[ FIRST ROW ------------------------ CURRENT ROW ]

For our example:

Turn 1:
[10]

Turn 2:
[10, 20]

Turn 3:
[10, 20, 30]

Turn 4:
[10, 20, 30, 40]

5. Understanding CURRENT ROW

Now comes the part that initially seems confusing.

We have:

CURRENT ROW

Does that mean the frame contains only the current row?

No.

It means:

The end boundary of the frame is the current row.

So when we write:

BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

we are saying:

Start at the beginning and stop at the current row.

Therefore:

Current row = 1

[1]


Current row = 2

[1, 2]


Current row = 3

[1, 2, 3]


Current row = 4

[1, 2, 3, 4]

That's why it creates a running total.

6. Putting It Together

Now the entire expression:

SUM(weight) OVER(
    ORDER BY turn
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

can be translated into plain English as:

Order the rows by turn, and for each row, sum everything from the beginning of the window up to the current row.

For:

turn	weight
1	10
2	20
3	30
4	40

the calculation is:

Turn 1:
10
= 10


Turn 2:
10 + 20
= 30


Turn 3:
10 + 20 + 30
= 60


Turn 4:
10 + 20 + 30 + 40
= 100

Result:

turn	weight	running_total
1	10	10
2	20	30
3	30	60
4	40	100

That is a running total.

7. Why Doesn't CURRENT ROW Limit the Scope?

This is the most common source of confusion.

When you see:

BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

don't read it as:

"Use the current row."

Read it as:

"The frame starts at the beginning and its ending boundary is the current row."

The frame is therefore different for every row.

Row 1:
[1]


Row 2:
[1, 2]


Row 3:
[1, 2, 3]


Row 4:
[1, 2, 3, 4]

The current row moves, and the frame moves with it.

That's the key mental model:

A window frame is evaluated relative to each current row.

8. ROWS vs RANGE

There are two frame units that are particularly important in MySQL:

ROWS

and

RANGE

They can look similar, but they are conceptually different.

`ROWS`

ROWS works with physical row positions.

For example:

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

means:

Include every physical row from the first row through the current row.

`RANGE`

RANGE works with values in the window ordering, so rows that are peers according to the ORDER BY can belong to the same frame boundary.

For example, if we have:

turn	weight
1	10
1	20
2	30

then with:

ORDER BY turn
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

the two rows with turn = 1 are peers.

So the frame for the turn = 1 rows includes both of them.

This distinction matters whenever your ORDER BY column contains duplicate values.

For a simple row-by-row running total where the ordering column is unique, ROWS is often the clearest way to express the intention:

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

9. What Happens If We Don't Write the Frame?

This is another important point.

In MySQL, when an ORDER BY is present and no explicit frame is specified, the default frame is equivalent to:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

So:

SUM(weight) OVER(
    ORDER BY turn
)

is effectively using that default frame.

However, writing the frame explicitly can be useful when you're learning or when you want the query to communicate exactly which rows should be included.

It also makes the distinction between RANGE and ROWS explicit.

10. Not Every Window Function Uses the Frame

This is an extremely important distinction.

Functions such as:

SUM()
AVG()
FIRST_VALUE()
LAST_VALUE()
NTH_VALUE()

can operate on the rows in the current frame.

But functions such as:

ROW_NUMBER()
RANK()
DENSE_RANK()
LAG()
LEAD()

do not use the frame in the same way.

For example:

ROW_NUMBER() OVER(
    ORDER BY turn
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

does not suddenly make ROW_NUMBER() calculate a running count based on that frame.

ROW_NUMBER() is concerned with the position of the current row in the ordered partition.

This is why it is useful to distinguish between:

window ordering

and

window framing

They are related, but they are not the same concept.

11. A Real Problem: LeetCode 1204

Now let's use this idea in an actual SQL problem.

The problem gives us people waiting to enter a bus.

Each person has:

person_name
weight
turn

The bus can carry a maximum weight of 1000.

We need to find:

The last person who can get on the bus without making the total weight exceed 1000.

The queue order is determined by turn.

So the first thing we need is:

Person 1
Person 1 + Person 2
Person 1 + Person 2 + Person 3
...

In other words:

We need a running total.

12. Building the Running Total

This is the key part of the solution:

SUM(weight) OVER(
    ORDER BY turn ASC
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS sum

Suppose the queue is:

person_name	weight	turn
Alice	250	1
Bob	300	2
Charlie	400	3
David	200	4

The window frame produces:

Turn 1:
[250]
→ 250


Turn 2:
[250, 300]
→ 550


Turn 3:
[250, 300, 400]
→ 950


Turn 4:
[250, 300, 400, 200]
→ 1150

So our derived table becomes:

person_name	weight	turn	sum
Alice	250	1	250
Bob	300	2	550
Charlie	400	3	950
David	200	4	1150

Now the problem is much easier.

13. Remove Anyone Who Exceeds the Limit

We can simply use:

WHERE sum <= 1000

This leaves:

person_name	sum
Alice	250
Bob	550
Charlie	950

David is excluded because:

1150 > 1000

Now the answer must be the person with the largest remaining cumulative total.

14. Using FIRST VALUE

This is where I use:

FIRST_VALUE(person_name) OVER(ORDER BY sum DESC)

After filtering, the rows are ordered by cumulative weight:

Charlie → 950
Bob     → 550
Alice   → 250

Therefore, the first value is:

Charlie

FIRST_VALUE() returns that first value from the current frame.

Since the first value is the same for all the remaining rows, the intermediate result is:

Charlie
Charlie
Charlie

So I use:

DISTINCT

to get:

Charlie

15. Complete Solution

Here is the complete query:

SELECT 

    DISTINCT FIRST_VALUE(person_name) OVER(ORDER BY sum DESC) AS 'person_name'

FROM (

    SELECT *, 

        SUM(weight) OVER(
            ORDER BY turn ASC 
            RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS 'sum'

    FROM Queue

) x 

WHERE sum <= 1000;

The solution can be viewed as four steps:

Queue
   ↓
Calculate running total
   ↓
Remove totals > 1000
   ↓
Find the person with the largest valid total

16. The Mental Model I Use for Window Frames

When I see:

ROWS/RANGE BETWEEN ... AND ...

I try not to memorize the syntax.

Instead, I ask two questions:

Where does the frame start?

For example:

UNBOUNDED PRECEDING

means:

Start from the beginning.

Where does the frame end?

For example:

CURRENT ROW

means:

Stop at the current row.

So:

BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

becomes:

START
 ↓
[---------------- CURRENT ROW]

As the current row changes:

Row 1:
[1]


Row 2:
[1, 2]


Row 3:
[1, 2, 3]


Row 4:
[1, 2, 3, 4]

That's the mental model.

Once you visualize the frame, the syntax becomes much less intimidating.

17. Other Useful Window Frames

The same idea can be used for many other problems.

Running total

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

1
1 + 2
1 + 2 + 3
1 + 2 + 3 + 4

Moving 3-row average

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

For example:

Row 1 → [1]
Row 2 → [1, 2]
Row 3 → [1, 2, 3]
Row 4 → [2, 3, 4]
Row 5 → [3, 4, 5]

This is useful for rolling/moving calculations.

Entire partition

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

The frame contains the entire partition for every row.

18. Final Takeaway

The biggest thing I learned from window frames is that:

CURRENT ROW is a boundary, not a restriction to a single row.

When we write:

BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

we are saying:

Start from the beginning of the partition and extend the frame through the current row.

That is why:

SUM(weight) OVER(
    ORDER BY turn
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

creates a running total.

The frame changes for every current row:

[1]
[1, 2]
[1, 2, 3]
[1, 2, 3, 4]
...

Once this mental model clicks, many window-function problems become much easier to reason about.

Quick Reference

Syntax	Meaning
`UNBOUNDED PRECEDING`	Start at the first row
`CURRENT ROW`	End at the current row
`UNBOUNDED FOLLOWING`	Extend to the last row
`ROWS`	Frame based on physical row positions
`RANGE`	Frame based on ordering values and their peers
`ROWS/RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`	Running/cumulative frame

If you're learning SQL window functions, don't just memorize:

UNBOUNDED PRECEDING

Visualize the frame moving through the partition.

That is the part that makes the syntax finally make sense.

References

Correlated Subqueries Explained: From Confusing to Intuitive

Saami abbas Khan — Sat, 01 Aug 2026 11:16:22 +0000

Hey everyone! 👋

It's been a while since I posted here. Over the past few days, I've been revisiting SQL from the ground up—not just solving problems, but understanding why different SQL concepts exist and when they should be used.

One topic that confused me (and many beginners) was correlated subqueries.

You may have seen queries like this:

SELECT ...
FROM table1 t1
WHERE EXISTS (
    SELECT *
    FROM table2 t2
    WHERE t2.id = t1.id
);

and wondered:

How is the inner query accessing columns from the outer query?
When does the inner query execute?
How is this different from a normal subquery?

In this article, we'll answer all of these questions by solving a real SQL problem from LeetCode.

By the end, you'll understand:

✅ What correlated subqueries are
✅ How they work internally
✅ SQL's logical execution order
✅ When to use them
✅ How they compare with window functions

Let's dive in!

Prerequisites

This article assumes you know:

Basic SELECT statements

WHERE

GROUP BY

Aggregate functions (MIN, MAX, COUNT, ...)

If you're comfortable with these, you're ready to learn correlated subqueries.

Why Are Correlated Subqueries So Confusing?
Correlated Subqueries from Scratch
Correlated vs Non-Correlated Subqueries
SQL's Logical Execution Order (and Where Correlated Subqueries Fit)
Using LeetCode 3421 as Our Running Example
Before We Write SQL...
My Fully Annotated Solution
Walking Through the Query Step by Step Using Real Data
Performance Discussion: Correlated Subqueries vs Window Functions
Conclusion & Practice Problems

1. Why Are Correlated Subqueries So Confusing?

When I first started learning SQL, correlated subqueries felt like magic.

Questions like these constantly came to mind:

How can the inner query access columns from the outer query?
When does the inner query execute?
Does it run once or multiple times?
Why can't I execute it by itself?

If you've asked yourself any of these questions, don't worry—you'll have answers to all of them by the end of this article.

2. Correlated Subqueries from Scratch

What is a subquery?

A subquery is just a SELECT statement nested inside another SQL statement. Nothing scary yet:

SELECT name, salary
FROM Employees
WHERE salary > (SELECT AVG(salary) FROM Employees);

Here, the inner query (SELECT AVG(salary) FROM Employees) runs once, produces a single number (say, 55000), and the outer query just uses that number. The inner query doesn't care what row the outer query is currently looking at. This is a non-correlated (or "simple") subquery — it's fully independent and could be run on its own.

What makes a subquery "correlated"?

💡 Key Idea

A correlated subquery depends on values from the outer query.

Because of that, it cannot execute independently — it re-executes once per row of the outer query, using that row's values each time.

SELECT e.name, e.salary, e.department_id
FROM Employees e
WHERE e.salary > (
    SELECT AVG(salary)
    FROM Employees e2
    WHERE e2.department_id = e.department_id   -- 👈 references outer row `e`
);

Read that inner query in isolation:

SELECT AVG(salary) FROM Employees e2 WHERE e2.department_id = e.department_id

e.department_id doesn't exist inside this subquery's own FROM Employees e2 — it's borrowed from the outer query. That's the defining trait. Mentally, think of it like a function with a parameter:

find_avg_salary_for(department_id):
    return AVG(salary) WHERE department_id = department_id

And SQL calls that "function" once for every single row the outer query touches, plugging in that row's department_id. That's the whole concept. Everything else is just decoration on top of this idea.

A tiny mental model

Think of the outer query as a for loop, and the correlated subquery as code that runs inside that loop, using the loop variable:

# This is NOT real SQL — just a mental model
for row in Employees:
    dept_avg = AVG(salary WHERE department_id == row.department_id)
    if row.salary > dept_avg:
        emit(row.name, row.salary, row.department_id)

This mental model is exactly why correlated subqueries can get expensive — because that's genuinely close to how the database may end up executing it if it can't optimize it away (more on that in the performance section).

Summary

A correlated subquery executes once for every row (or group) produced by the outer query, using that row's values as its input.

3. Correlated vs Non-Correlated: Side by Side

	Non-Correlated Subquery	Correlated Subquery
References outer query?	❌ No	✅ Yes
Can run standalone?	✅ Yes	❌ No
Execution frequency	Once, total	Once per outer row (conceptually)
Typical use	Compare against a global value (e.g. overall average)	Compare against a value scoped to that row's group (e.g. that department's average, that student's max date)

Non-correlated example — "Employees who earn more than the company-wide average":

SELECT name FROM Employees
WHERE salary > (SELECT AVG(salary) FROM Employees);

Correlated example — "Employees who earn more than their own department's average":

SELECT e.name FROM Employees e
WHERE e.salary > (
    SELECT AVG(salary) FROM Employees e2 WHERE e2.department_id = e.department_id
);

Same shape, one crucial difference: the WHERE e2.department_id = e.department_id line. That one line is the entire difference between "global" and "per-row-scoped."

Summary

If the subquery can run on its own and return a sensible result, it's non-correlated. If it needs a value from the outer row to even make sense, it's correlated.

4. SQL's Logical Execution Order (and Where Subqueries Fit)

This trips up a lot of people, so let's nail it down. A SELECT statement is written top to bottom (SELECT → FROM → WHERE → GROUP BY → ...), but it does not execute in that order. The logical order MySQL (and most SQL engines) actually evaluates is:

① FROM        (and JOINs) — build the working row set

↓

② WHERE       — filter individual rows

↓

③ GROUP BY    — bucket rows into groups

↓

④ HAVING      — filter groups

↓

⑤ SELECT      — compute the output columns
                (this is where subqueries in the SELECT list run)

↓

⑥ ORDER BY    — sort the final result

↓

⑦ LIMIT       — trim the result

Correlated subqueries that live inside the SELECT list (like the ones in my solution below) are evaluated at step ⑤ — for every row/group that survives FROM → WHERE → GROUP BY → HAVING. This is the key insight for our problem:

💡 Key Idea

By the time the correlated subqueries in SELECT run, MySQL has already figured out which (student_id, subject) groups exist (thanks to GROUP BY). The subqueries then just get asked, once per group: "for this specific student and subject, what's the first score, and what's the latest score?"

That's it. The whole query is really just: 1) group by student+subject, 2) for each group, ask two side-questions.

Summary

Correlated subqueries in the SELECT list run at step ⑤ — once per group that has already survived filtering and grouping.

5. LeetCode 3421 as Our Running Example: Find Students Who Improved

Table: Scores

Column Name	Type
student_id	int
subject	varchar
score	int
exam_date	varchar

(student_id, subject, exam_date) is the primary key. Each row is one student's score in one subject on one exam date. score is between 0 and 100.

Task: Find students who have improved. A student counts as improved if, for a given subject, both of these are true:

They took the exam in that subject on at least two different dates
Their latest score in that subject is higher than their first score

Return student_id, subject, first_score, latest_score, ordered by student_id, subject ascending.

Example Input:

student_id	subject	score	exam_date
101	Math	70	2023-01-15
101	Math	85	2023-02-15
101	Physics	65	2023-01-15
101	Physics	60	2023-02-15
102	Math	80	2023-01-15
102	Math	85	2023-02-15
103	Math	90	2023-01-15
104	Physics	75	2023-01-15
104	Physics	85	2023-02-15

Expected Output:

student_id	subject	first_score	latest_score
101	Math	70	85
102	Math	80	85
104	Physics	75	85

Notice what got filtered out:

101 / Physics — took it twice, but score dropped (65 → 60). Not an improvement.
103 / Math — only took the exam once. Doesn't satisfy "at least two different dates."

Simple to state, but notice the core difficulty: for every (student_id, subject) pair, you need to reach back into the same table to find that specific group's minimum date and maximum date, and then the scores tied to those dates. This "reach back into the same table, but scoped to the current row's group" is exactly what a correlated subquery is built for.

6. Before We Write SQL...

Let's forget SQL for a minute.

Imagine you're writing this in C++, Java, or Python.

What would you do? Probably something like this:

for each student

    for each subject

        earliest_exam

        latest_exam

        first_score

        latest_score

        if latest_score > first_score

            print()

SQL is simply another way of expressing this algorithm. The GROUP BY gives you the for each student / for each subject part, and the correlated subqueries give you the earliest_exam, latest_exam, first_score, and latest_score lookups. Keep this loop in your head — it makes the query below feel a lot less abstract.

7. My Annotated Solution

Complete Query

WITH t AS (
    SELECT
        student_id,
        subject,

        -- Correlated subquery #1: get the score tied to this group's EARLIEST exam_date
        (SELECT score
         FROM Scores s
         WHERE s.student_id = t.student_id
           AND s.subject    = t.subject
           AND s.exam_date  = (
                -- nested correlated subquery: find the MIN(exam_date) for THIS student+subject
                SELECT MIN(exam_date)
                FROM Scores ss
                WHERE ss.student_id = t.student_id
                  AND ss.subject    = t.subject
           )
        ) AS first_score,

        -- Correlated subquery #2: get the score tied to this group's LATEST exam_date
        (SELECT score
         FROM Scores s
         WHERE s.student_id = t.student_id
           AND s.subject    = t.subject
           AND s.exam_date  = (
                -- nested correlated subquery: find the MAX(exam_date) for THIS student+subject
                SELECT MAX(exam_date)
                FROM Scores ss
                WHERE ss.student_id = t.student_id
                  AND ss.subject    = t.subject
           )
        ) AS latest_score

    FROM Scores t
    GROUP BY student_id, subject
)
SELECT *
FROM t
WHERE latest_score &gt; first_score
ORDER BY student_id ASC, subject ASC;

What each piece is doing

FROM Scores t GROUP BY student_id, subject — collapses the raw exam rows down to one row per (student_id, subject) combo. This is our set of "candidates" to evaluate.
first_score — a correlated subquery nested inside another correlated subquery. The inner one (MIN(exam_date) scoped WHERE ss.student_id = t.student_id AND ss.subject = t.subject) finds the earliest date scoped to the current group t. The outer one then fetches the score on that exact date, for that exact student and subject.
latest_score — same idea, but with MAX(exam_date) instead of MIN(exam_date).
The condition "at least two different dates" is handled implicitly: if a student/subject only has one exam date, then first_score and latest_score end up being the same value (same date → same score), so latest_score > first_score is false and that row gets filtered out naturally by the outer WHERE. Nice side effect of the logic — no extra HAVING COUNT(DISTINCT exam_date) >= 2 needed.
Final WHERE latest_score > first_score — the actual "did they improve" check.
ORDER BY student_id, subject — matches the problem's required output ordering.

The line that creates the correlation

Everything hinges on this pair of conditions inside the nested subquery:

WHERE
    ss.student_id = t.student_id
AND ss.subject    = t.subject

These two conditions are the entire magic. They're what tie the inner query's MIN/MAX calculation to this specific row of the outer query t, instead of computing a MIN/MAX across the whole table. Remove them, and the subquery stops being correlated — it just becomes "the earliest date across every student and subject," which isn't what we want at all.

Summary

The nested MIN(exam_date) / MAX(exam_date) subqueries are correlated because they're filtered by the outer group's student_id and subject — that's what scopes them to "this student, this subject" instead of the whole table.

8. Walking the Query Step-by-Step on Real Data

Let's trace it for student 101, subject Math using the example data:

exam_date	score
2023-01-15	70
2023-02-15	85

Outer Query

----------------------------------
student_id = 101
subject    = 'Math'
----------------------------------

│
▼

Correlated Subquery

----------------------------------
Find MIN(exam_date)
WHERE
    student_id = 101
AND subject    = 'Math'
----------------------------------

│
▼

2023-01-15

│
▼

Find score where exam_date = 2023-01-15

│
▼

first_score = 70

The latest_score branch follows the exact same path, just swapping MIN(exam_date) for MAX(exam_date), which resolves to 2023-02-15 → score = 85.

Putting it together, step by step:

GROUP BY produces the group (101, Math).
Inner subquery for first_score: MIN(exam_date) for (101, Math) → 2023-01-15.
Outer subquery for first_score: score where exam_date = 2023-01-15 and student=101, subject=Math → 70.
Inner subquery for latest_score: MAX(exam_date) for (101, Math) → 2023-02-15.
Outer subquery for latest_score: score where exam_date = 2023-02-15 → 85.
Row so far: (101, Math, 70, 85).
Final filter: is 85 > 70? Yes → row survives.

Now trace student 103, subject Math (only one exam):

exam_date	score
2023-01-15	90

Group (103, Math).
MIN(exam_date) → 2023-01-15 → first_score = 90.
MAX(exam_date) → 2023-01-15 (same date, only one row) → latest_score = 90.
Final filter: is 90 > 90? No → row is dropped. This is exactly how the "at least two exam dates" rule gets enforced for free.

And student 101, subject Physics (score dropped from 65 to 60):

first_score = 65, latest_score = 60.
Is 60 > 65? No → dropped.

That's the entire algorithm, traced by hand.

Summary

Every group's first_score and latest_score are resolved independently by "asking" the same two-step question: find the boundary date, then find the score on that date.

9. Performance Discussion: Subqueries vs Window Functions

MySQL 8.0+ gives us window functions, which can express "first and last value per group" far more efficiently and readably:

Window Function Version

WITH ranked AS (
    SELECT
        student_id,
        subject,
        score,
        exam_date,
        FIRST_VALUE(score) OVER (
            PARTITION BY student_id, subject ORDER BY exam_date ASC
        ) AS first_score,
        FIRST_VALUE(score) OVER (
            PARTITION BY student_id, subject ORDER BY exam_date DESC
        ) AS latest_score,
        COUNT(*) OVER (PARTITION BY student_id, subject) AS exam_count
    FROM Scores
)
SELECT DISTINCT student_id, subject, first_score, latest_score
FROM ranked
WHERE exam_count &gt;= 2 AND latest_score &gt; first_score
ORDER BY student_id, subject;

💡 Key Idea

Window functions typically require the engine to sort/partition the data once, then compute all the values for every partition in that single pass. My correlated-subquery version, by contrast, re-runs MIN/MAX/lookup subqueries for every group — and in the worst case (or without solid indexing), those inner lookups can degrade toward re-scanning relevant rows per group rather than a single unified pass.

That said — a fair note on my original solution: because of the GROUP BY, the correlated subqueries only run once per distinct (student_id, subject) pair, not once per raw row. With a proper composite index on (student_id, subject, exam_date), MySQL can satisfy each MIN/MAX lookup and the point-lookup for score almost instantly, so in practice this query performs quite well on realistic dataset sizes — it isn't the worst-case O(n²) scenario people sometimes assume correlated subqueries always are. The window-function version is still the more "modern SQL" way to express it, and scales more predictably, but the difference is often smaller than people expect once indexing is in place.

Rule of thumb:

Reach for correlated subqueries when you need to express "for each row/group, ask a targeted question about a related scope" — they're extremely readable for that.
Reach for window functions when you're computing this kind of first/last/rank/running-total logic across an entire partitioned dataset — they usually scale better and avoid re-execution per group.
Always check EXPLAIN on your actual data before assuming either is "the fast one" — data size, indexes, and MySQL version all matter more than the textbook complexity story.

Summary

Correlated subqueries shine for readability and targeted per-group lookups; window functions shine for scale, since they avoid re-running the same calculation over and over.

10. Conclusion & Further Practice

The core idea to take away from this whole post is small enough to fit in one sentence:

A correlated subquery is just a mini-query that gets handed one value from the outer row at a time and answers a question scoped to that value.

Once that clicks, most "hard" correlated-subquery problems stop being hard — they just become "what's the mini-question I need to ask per row/group, and what value from the outer query does it need?"

If you want to build this muscle further, here are a few problems that lean directly on the same skill:

Second Highest Salary (classic correlated subquery warm-up)
Department Top Three Salaries
Rank Scores
Consecutive Numbers
Employees Earning More Than Their Managers

Try solving each one twice, the way I did here: once with a correlated subquery (or window function), and once by hand-tracing it like a loop on paper. The loop trace is what actually builds intuition — the SQL syntax is just the encoding of that intuition afterward.

Final Thoughts

Correlated subqueries are often considered one of the trickiest SQL concepts—not because the syntax is difficult, but because it's easy to lose track of which query is executing and which row is being referenced.

Once you realize that the inner query is simply "borrowing" values from the current row of the outer query, the concept becomes much more intuitive.

I hope this article helped make that mental model a little clearer.

Happy learning! 🚀

Any improvements that are needed? I’d be happy to receive suggestions. The app is posted on itch.io and, since it’s a free unsigned Windows .exe, the page has been temporarily quarantined (still downloadable). As traffic and trust increase it'll be fine.

Saami abbas Khan — Wed, 07 Jan 2026 15:55:14 +0000

Saami abbas Khan

Dec 16 '25

I Made a Habit Tracker Because the Free Ones Were… Not Free 😅

#opensource #gemini #ai #vscode

1 min read

My Indie App Got Quarantined on itch.io — and I’m Opening It Up to the Dev Community

Saami abbas Khan — Wed, 07 Jan 2026 15:41:09 +0000

A few days ago, something unexpected happened.

I published a small indie project on itch.io — a lightweight, offline habit-tracking app I built for people who want fewer distractions and more consistency.

Within hours, the page was automatically placed under quarantine for additional review. No takedown, No warning.

Just… quarantine.

🧠 What the App Is (and Isn’t)

Focus & Grow is a fully offline Windows habit tracker.

No accounts
No ads
No trackers
No background network activity

Tech stack

TypeScript for app logic
Tauri (Rust) for packaging
Distributed as a zipped Windows .exe
Includes a clear README explaining usage and intent

Nothing obfuscated. Nothing hidden.

🤔 Why This Probably Happened

If you’ve shipped a Windows executable before, you know the struggle:

Unsigned .exe
New project with no reputation
Automated security heuristics doing their thing

I completely understand why platforms do this — but it raises a real question:

How do indie developers establish trust when starting from zero?

🧪 Why I’m Sharing This Here

I’ve already contacted itch.io for a manual review.

In the meantime, I’m doing the most transparent thing I can:

Opening the project up to the dev community.

Curious? Take a look.
Cautious? Inspect it.
Been through this before? Share your experience.

Feedback, comments, or even just engagement genuinely help — both for improving the app and for signaling legitimacy.

🔗 The Project

The app is called Focus & Grow — a simple habit tracker designed to stay out of your way.

If you check it out and have thoughts, I’d love to hear them.

Building in public isn’t always comfortable — but it’s worth it.

Let’s talk.

I Made a Habit Tracker Because the Free Ones Were… Not Free 😅

Saami abbas Khan — Tue, 16 Dec 2025 08:45:36 +0000

Recently, I was scrolling through Instagram and came across this amazing habit-tracking template on Notion. But when I tried to download it… surprise, it was paid. Ah, the pain. 😅

Instead of endlessly searching for free alternatives or settling for something that didn’t quite click, I did the non-obvious thing: I made my own.

Enter Focus & Grow – a minimalistic, distraction-free monthly habit tracker that I vibe-coded using TypeScript, React, and Tauri. The goal? A tool that’s:

Clean & simple – no clutter, just your habits and progress
Privacy-first – 100% local storage, no login required
Powerful analytics – streaks, trends, completion rates
Exportable – share your monthly consistency chart as an image
Desktop-ready – works natively via Tauri, or in your browser

You can either download the ready-to-use app from Itch.io for a smooth experience, or check out the code on GitHub if you want to tinker or contribute.

It would mean a lot if you could take a few minutes to test it out, share feedback, or suggest new features. I’m especially curious about:

How intuitive it feels
Any additional features you’d like to see
Suggestions for improvement

Thanks a ton for reading! 💛

If you try it, feel free to comment below—I’d love to hear how you focus and grow.

I Made a Habit Tracker Because the Free Ones Were… Not Free 😅

Saami abbas Khan — Mon, 15 Dec 2025 10:48:36 +0000

Recently, I was scrolling through Instagram and came across this amazing habit-tracking template on Notion. But when I tried to download it… surprise, it was paid. Ah, the pain. 😅

Instead of endlessly searching for free alternatives or settling for something that didn’t quite click, I did the non-obvious thing: I made my own.

Enter Focus & Grow – a minimalistic, distraction-free monthly habit tracker that I vibe-coded using TypeScript, React, and Tauri. The goal? A tool that’s:

Clean & simple – no clutter, just your habits and progress
Privacy-first – 100% local storage, no login required
Powerful analytics – streaks, trends, completion rates
Exportable – share your monthly consistency chart as an image
Desktop-ready – works natively via Tauri, or in your browser

You can either download the ready-to-use app from Itch.io for a smooth experience, or check out the code on GitHub if you want to tinker or contribute.

It would mean a lot if you could take a few minutes to test it out, share feedback, or suggest new features. I’m especially curious about:

How intuitive it feels
Any additional features you’d like to see
Suggestions for improvement

Thanks a ton for reading! 💛

If you try it, feel free to comment below—I’d love to hear how you focus and grow.

CourseTime Analyzer: Python + Selenium + GUI Project for Tracking YouTube Course Time

Saami abbas Khan — Wed, 17 Sep 2025 15:47:07 +0000

Ever wondered how long it will take to finish that YouTube course playlist you just found? Instead of manually checking each video, I built a small Python project called CourseTime Analyzer 🚀.

This tool automatically searches YouTube for a course playlist, fetches all video durations, and calculates the total study time — all packed in a simple Tkinter GUI.

🔥 Why I Built This

Whenever I started a YouTube course, I always wanted to know “How much total time will this take?” Sure, YouTube shows individual durations, but for playlists with 50+ videos, calculating by hand is painful.

So I automated it with Python + Selenium and wrapped it in a clean GUI using Tkinter.

👉 To be honest, this was a time-pass project. I wasn’t in the mood to continue my actual work (learning more about Softmax Regression 😅), so I coded this as a fun escape.

⚙️ Features

🔍 Search YouTube for any course playlist
📺 Fetch playlist title and creator details
⏱️ Calculate total video duration in hours
🎥 Show video count and display durations (first 15 listed, rest summarized)
🔗 Clickable playlist link directly inside GUI
🖼️ Clean interface with background image
🖥️ Supports GUI mode (graphics.py) and CLI mode (main.py)

🧩 Modularized Approach

The project is structured to keep things clean and reusable:

main.py → Handles the core mechanism (YouTube scraping & analysis via Selenium).
graphics.py → A wrapper around main.py that provides a Tkinter-based GUI.

You can run main.py independently in CLI mode — the GUI is just an additional layer.

🚀 GitHub Repository

All the code is open-source and available here:

👉 CourseTime Analyzer on GitHub

💬 Feedback

Comments are open! Feel free to suggest improvements, criticize the approach, or even fork the repo and make it better. This was just a fun side project, so I’d love to see how others take it further. 🚀

Yo!

Saami abbas Khan — Mon, 07 Jul 2025 16:57:06 +0000

Hello! After a 10-month break 😅

Hacktoberfest Experience

Saami abbas Khan — Sun, 27 Oct 2024 14:26:13 +0000

This is a submission for the 2024 Hacktoberfest Writing challenge: Contributor Experience

Hacktoberfest was an amazing experience! I learned so much about open-source collaboration, from understanding new codebases to following contributor guidelines. Although some issues were challenging, each taught me valuable skills and boosted my confidence.

The community’s support was also very incredible.

Should You Even Put Your Simple Projects on GitHub?

Saami abbas Khan — Tue, 01 Oct 2024 19:20:19 +0000

I was hesitant to share my latest project, a Simple CLI File Explorer, because I feared it might be too basic. But after a lot of thought, I finally hit the upload button.

I'm a beginner, still learning at my own pace, even if it’s just a little bit each day. But the key is, I’m staying consistent (thank God!). This project was designed to strengthen my core concepts, and while I know it’s far from perfect, and there are definitely areas for improvement, I honestly just needed to get it out there.

Please check out my Simple CLI File Explorer, and I would love your honest feedback—be as brutal as you want! Did I make the right call posting this? Where do I stand with this project?

Your Opinion Matters!

Unlocking Functional Programming in Java: A Guide to Lambdas, Method References, and Chaining

Saami abbas Khan — Fri, 27 Sep 2024 11:57:04 +0000

In this post we'll discover the power of functional programming in Java with lambdas, method references, and function chaining. Simplify your code and boost efficiency with these modern techniques!

Introduction to Functional Programming
Lambda Expressions
Method References
Functional Interfaces
Lambda Chaining
Predicate Chaining
Custom vs. Default Functional Interface Chaining
Conclusion

Introduction to Functional Programming

Functional programming is a programming paradigm that emphasizes writing concise, efficient, and reusable code by extensively using functions, particularly lambdas. One of its key benefits is brevity—reducing code length without sacrificing clarity or efficiency. In functional programming, functions are treated as first-class citizens, allowing for easier function chaining, leading to less verbose code.

Adopting functional programming can significantly enhance productivity and maintainability, especially when working with complex data transformations or streamlining logic. However, brevity doesn’t mean sacrificing efficiency or readability. A well-written functional program should still be easy to understand, debug, and maintain.

To successfully leverage functional programming, it’s essential to understand key terminologies such as functional interfaces, lambda expressions, method references, and chaining of functions.

In this post, we'll explore these concepts in detail to help you harness the full power of functional programming in Java.

Lambda Expressions

Lambda expressions are simply a concise way to represent methods or functions in programming languages like Java. They are a key component of functional programming, allowing you to write cleaner, more expressive code.

In Java, lambda expressions are tightly coupled with functional interfaces. To use lambdas effectively, it's essential to understand what a functional interface is.

A functional interface in Java is an interface with only one abstract method. This method can be implemented using a lambda expression, which makes the code shorter and more readable.

Here's a simple example:

@FunctionalInterface
interface countInterface<T> {
    int count(T t); // Returns the count, e.g., "Saami" returns 5
}

// Implementing the interface using a lambda
countInterface<String> variable = s -> s.length(); // Lambda to return string length
var result = variable.count("Saami");
System.out.println(result); // Outputs: 5

In this example, the lambda s -> s.length() is used to implement the count() method from the countInterface. It's a compact and elegant way of writing what would otherwise require a more verbose approach using anonymous classes.

While you could create a method to achieve the same result, using lambdas aligns with the functional programming paradigm of brevity—writing concise and expressive code. Lambdas can also be multi-line, but the aim is to maintain simplicity and brevity whenever possible

Method References

Method references in Java are a shorthand way to further simplify lambda expressions. They provide a more readable and concise syntax, making your code easier to understand while maintaining functionality. Method references are particularly useful when your lambda expression simply calls a method.

Let’s take a look at some examples where a lambda expression can be replaced with a method reference for improved readability:

@FunctionalInterface
interface CountInterface<T> {
    int count(T t); // Returns the count, e.g., "Saami" returns 5
}
// Implementing the interface using a method reference

CountInterface<String> variable = String::length; 
// Using the method reference to get the length of the string
var result = variable.count("Saami");
System.out.println(result); // Outputs: 5

Functional Interfaces

In Java, a functional interface is an interface that contains exactly one abstract method. This concept is pivotal in functional programming, as it allows the use of lambda expressions to implement the interface's functionality in a concise manner. Functional interfaces can also contain default or static methods, but they must adhere to the rule of having only one abstract method.

The @FunctionalInterface annotation is used to indicate that an interface is intended to be a functional interface. While this annotation is not mandatory, it provides compile-time checking to ensure that the interface remains functional. If you accidentally add more than one abstract method, the compiler will throw an error.

For more details on functional interfaces, feel free to check out my dedicated post on functional interfaces where I delve deeper into their usage, examples, and best practices.

Lambda Chaining

Before diving into lambda chaining, it’s important to understand the default functional interfaces provided by Java. For a detailed overview, check out my post on Default Functional Interfaces in Java.

In Java, you can chain lambda expressions using the andThen() method, which is available in both the Function and Consumer interfaces. The main difference between the two lies in how they handle inputs and outputs:

Function Interface: The Function interface is designed for transformations. It takes an input, processes it, and returns an output. When chaining functions, the output of the first lambda expression becomes the input for the second. This allows for a seamless flow of data through multiple transformations.

Example:

Function<String, String> uCase = String::toUpperCase;

Function<String, String[]> fun = uCase.andThen(s -> s.concat("KHAN")).andThen(s -> s.split(""));
System.out.println(Arrays.toString(fun.apply("Saami")));

// Output
// S A A M I K H A N

Consumer Interface: In contrast, the Consumer interface does not return any result. Instead, it takes an input and performs an action, typically producing side effects. When using andThen() with consumers, the first consumer will execute, and then the second will follow.

Example:

Consumer<String> printUpperCase = s -> System.out.println(s.toUpperCase());
Consumer<String> printLength = s -> System.out.println("Length: " + s.length());

Consumer<String> combinedConsumer = printUpperCase.andThen(printLength);
combinedConsumer.accept("Saami"); // Outputs: "SAAMI" and "Length: 5"

By using andThen(), you can effectively chain lambda expressions to create more complex behavior in a clean and readable manner. This chaining allows for efficient code organization and minimizes boilerplate, aligning with the principles of functional programming.

Predicate Chaining

Unlike the Function or Consumer interfaces, we don’t have an andThen()method for predicates. However, you can chain predicates using the and(), or(), and negate() methods. These methods allow you to combine multiple predicates into a logical chain, facilitating complex conditional checks in a concise manner.

Example of Predicate Chaining:

Predicate<String> p1 = s -> s.equals("Saami");
Predicate<String> p2 = s -> s.startsWith("S");
Predicate<String> p3 = s -> s.endsWith("b");

// Chaining predicates using or(), negate(), and and()
Predicate<String> combined = p1.or(p2).negate().and(p3); 

// Here, chaining requires no `andThen()`; you can directly chain the logical convenience methods using the dot (.) operator.
// Thus making a LOGICAL CHAIN

System.out.println(combined.test("SaamI")); // Outputs: false

In this example:

p1 checks if the string equals "Saami".
p2 checks if the string starts with "S".
p3 checks if the string ends with "b".

The combined predicate first checks if either p1 or p2 is true and then negates that result. Finally, it checks if p3 is true. This allows you to build a logical chain without needing additional methods like andThen(), making it straightforward and intuitive.

By utilizing these chaining methods, you can create complex conditional logic while keeping your code clean and readable, which aligns perfectly with the goals of functional programming.

Custom Functional Interface Chaining vs. Default Functional Interfaces

While creating custom functional interfaces allows for flexibility in defining specific behaviors, chaining these custom interfaces can become quite complex. Here’s why using default functional interfaces is often the better choice:

Complexity of Custom Functional Interface Chaining:

When you decide to chain custom functional interfaces, you must carefully consider how parameters are passed between lambdas. This involves:

Parameter Matching: Ensuring that the parameters of one lambda match the expected input type of the next. This can add overhead to your design.
Edge Case Handling: You need to think through various edge cases and potential input scenarios to maintain consistent and correct behavior across chains.

This added complexity can lead to more cumbersome and error-prone code.

Default Functional Interfaces Are Optimized for such purposes, Java's built-in functional interfaces, such as Function, Predicate, and Consumer, are designed for common use cases and come with several advantages:

Conclusion

In summary, functional programming in Java offers powerful tools for writing clean, efficient, and maintainable code. By leveraging lambda expressions, method references, and functional interfaces, developers can express complex operations concisely. Chaining functions, whether through the andThen() method for functional transformations or through logical methods for predicates, enhances code readability and organization.

While custom functional interfaces provide flexibility, they often introduce complexity that can be avoided by utilizing Java’s built-in default functional interfaces. This approach not only streamlines the development process but also aligns with the principles of functional programming.

By understanding and applying these concepts, you can unlock the full potential of functional programming in Java, making your code more expressive and easier to maintain.

All information in this post reflects my personal learnings as I document my journey in programming. I casually create posts to share insights with others.
I would love to hear any additional tips or insights from fellow developers! Feel free to share your thoughts in the comments below.

DEV Community: Saami abbas Khan

Confused by UNBOUNDED PRECEDING and CURRENT ROW in SQL window functions? I was too. This guide breaks down exactly how window frames work and uses LeetCode 1204 to show how they create a running total step by step.

SQL Window Frames Explained: How UNBOUNDED PRECEDING Creates a Running Total

SQL Window Frames Explained: How UNBOUNDED PRECEDING Creates a Running Total

If you have started learning SQL window functions, you have probably seen something like this:

Table of Contents

1. First: What Is a Window Function?

2. So What Is a Window?

3. What Is a Window Frame?

4. Understanding UNBOUNDED PRECEDING

5. Understanding CURRENT ROW

6. Putting It Together

7. Why Doesn't CURRENT ROW Limit the Scope?

8. ROWS vs RANGE

ROWS

RANGE

9. What Happens If We Don't Write the Frame?

10. Not Every Window Function Uses the Frame

11. A Real Problem: LeetCode 1204

12. Building the Running Total

13. Remove Anyone Who Exceeds the Limit

14. Using FIRST VALUE

15. Complete Solution

16. The Mental Model I Use for Window Frames

Where does the frame start?

Where does the frame end?

17. Other Useful Window Frames

Running total

Moving 3-row average

Entire partition

18. Final Takeaway

Quick Reference

References

Correlated Subqueries Explained: From Confusing to Intuitive

Prerequisites

Table of Contents

1. Why Are Correlated Subqueries So Confusing?

2. Correlated Subqueries from Scratch

What is a subquery?

What makes a subquery "correlated"?

A tiny mental model

Summary

3. Correlated vs Non-Correlated: Side by Side

Summary

4. SQL's Logical Execution Order (and Where Subqueries Fit)

Summary

5. LeetCode 3421 as Our Running Example: Find Students Who Improved

6. Before We Write SQL...

7. My Annotated Solution

What each piece is doing

The line that creates the correlation

Summary

8. Walking the Query Step-by-Step on Real Data

Summary

9. Performance Discussion: Subqueries vs Window Functions

Summary

10. Conclusion & Further Practice

Final Thoughts

Any improvements that are needed? I’d be happy to receive suggestions. The app is posted on itch.io and, since it’s a free unsigned Windows .exe, the page has been temporarily quarantined (still downloadable). As traffic and trust increase it'll be fine.

I Made a Habit Tracker Because the Free Ones Were… Not Free 😅

My Indie App Got Quarantined on itch.io — and I’m Opening It Up to the Dev Community

🧠 What the App Is (and Isn’t)

Tech stack

🤔 Why This Probably Happened

🧪 Why I’m Sharing This Here

🔗 The Project

I Made a Habit Tracker Because the Free Ones Were… Not Free 😅

I Made a Habit Tracker Because the Free Ones Were… Not Free 😅

CourseTime Analyzer: Python + Selenium + GUI Project for Tracking YouTube Course Time

🔥 Why I Built This

⚙️ Features

🧩 Modularized Approach

🚀 GitHub Repository

💬 Feedback

Yo!

Hacktoberfest Experience

Should You Even Put Your Simple Projects on GitHub?

Unlocking Functional Programming in Java: A Guide to Lambdas, Method References, and Chaining

Table of Contents

Introduction to Functional Programming

`ROWS`

`RANGE`