DEV Community: Saami abbas Khan

LeetCode Problem Statements Be Testing My English More Than SQL

Saami abbas Khan — Sun, 13 Sep 2026 10:40:19 +0000

Currently, I am solving SQL problems on LeetCode, and believe me, the wording quality of some problems, especially the premium ones, is so poor. After spending the worst 15 minutes trying to understand the question, I finally realise that the problem is saying something completely different 😭. There is not even a proper explanation of the output on the main problem page. I do not know why. Maybe too few people are paying for premium and reporting these issues, hahaha. Anyway, I just wanted to share my frustration. Now, before solving a problem, I ask AI to explain the hidden secrets of the question without giving me the solution 😂.

How an Employee Hierarchy Helped Me Understand Recursive CTEs

Saami abbas Khan — Fri, 28 Aug 2026 16:45:24 +0000

How I Solved the Employee Hierarchy Problem (LC 3482 for our reference), With a Recursive CTE

Hierarchical SQL problems can feel completely different from ordinary aggregation problems.

When I first attempted LeetCode 3482, I tried to solve it using the SQL concepts I already knew: joins, subqueries, grouping, and window functions. However, the organization can have an unknown number of levels. An employee may manage someone who manages another employee, who may manage another employee, and so on.

A fixed number of joins can only handle a fixed number of levels. What I needed was a query that could continue moving through the organization until no additional employees were found.

That is exactly what a recursive Common Table Expression, or recursive CTE, is designed to do.

In this article, I will explain:

How to recognize a hierarchy problem
How a recursive CTE works
Why I included every employee in their own hierarchy
How the query calculates team size and budget
Why the hierarchy level must be taken from the CEO's perspective
Why a recursive JOIN is more suitable than a correlated subquery here
How the complete solution fits together

My submitted solution performed better than 99% of accepted MySQL submissions at the time of submission. Runtime percentiles can change, but more importantly, this problem helped me understand how recursive SQL works.

The problem in simple terms

We are given an Employees table containing each employee's manager and salary.

+-------------+---------------+------------+--------+-------------+
| employee_id | employee_name | manager_id | salary | department  |
+-------------+---------------+------------+--------+-------------+
| 1           | Alice         | null       | 12000  | Executive   |
| 2           | Bob           | 1          | 10000  | Sales       |
| 3           | Charlie       | 1          | 10000  | Engineering |
| 4           | David         | 2          | 7500   | Sales       |
| 5           | Eva           | 2          | 7500   | Sales       |
| 6           | Frank         | 3          | 9000   | Engineering |
| 7           | Grace         | 3          | 8500   | Engineering |
| 8           | Hank          | 4          | 6000   | Sales       |
| 9           | Ivy           | 6          | 7000   | Engineering |
| 10          | Judy          | 6          | 7000   | Engineering |
+-------------+---------------+------------+--------+-------------+

For every employee, we need to determine:

Their level in the company hierarchy
The number of employees in their complete team
The total budget of that team

The complete team includes both direct and indirect reports. The budget includes the employee's own salary as well as the salaries of all direct and indirect reports.

Visualizing the organization

Before writing SQL, it helps to convert the table into a tree:

Alice (1)
├── Bob (2)
│   ├── David (4)
│   │   └── Hank (8)
│   └── Eva (5)
└── Charlie (3)
    ├── Frank (6)
    │   ├── Ivy (9)
    │   └── Judy (10)
    └── Grace (7)

From this tree:

Alice is at level 1.
Bob and Charlie are at level 2.
David, Eva, Frank, and Grace are at level 3.
Hank, Ivy, and Judy are at level 4.

Alice's complete team contains every employee below her. Bob's complete team contains David, Eva, and Hank. Charlie's complete team contains Frank, Grace, Ivy, and Judy.

This is important because an ordinary self-join finds only one generation at a time. For example, joining Employees to itself once can find Bob's direct reports, David and Eva, but it does not automatically continue from David to Hank.

Why ordinary joins are not enough

A self-join can find direct reports:

SELECT
    manager.employee_id,
    report.employee_id AS report_id
FROM Employees AS manager
JOIN Employees AS report
    ON report.manager_id = manager.employee_id;

We could add another join to find reports two levels below:

JOIN Employees AS second_level
    ON second_level.manager_id = report.employee_id

But then we would need another join for the third level, another for the fourth level, and so on.

That approach assumes we already know the maximum depth of the organization. A recursive CTE does not require us to know that depth. It repeatedly applies the same relationship until no more matching employees exist.

How a recursive CTE works

A recursive CTE has two main parts:

WITH RECURSIVE cte_name AS (
    -- Anchor query

    UNION

    -- Recursive query
)

The anchor query

The anchor query creates the initial rows. It answers the question:

Where should the recursion begin?

The recursive query

The recursive query uses rows already produced by the CTE to find the next set of rows. It answers the question:

Given the employees found in the previous step, who should be visited next?

The database keeps executing the recursive part for newly generated rows. The recursion stops naturally when an iteration produces no more rows.

The key idea in my solution

Instead of creating only one hierarchy starting from the CEO, I create a separate hierarchy starting from every employee.

This means:

One hierarchy starts from Alice.
Another starts from Bob.
Another starts from Charlie.
The same process is repeated for every employee.

Why do this?

Because the problem asks for the complete team and budget of every employee. If I built only the CEO's hierarchy, I would know where everyone is globally, but I would still need a way to identify every employee's descendants.

By building a hierarchy for every employee, all descendants of a particular employee share the same starting employee_id. I can then group by that starting ID to calculate the complete team size and budget.

Step 1: Start one hierarchy from every employee

The anchor query is:

SELECT
    employee_id,
    employee_id AS reporter_id,
    1 AS level,
    salary
FROM Employees

The full recursive CTE begins like this:

WITH RECURSIVE t AS (
    SELECT
        employee_id,
        employee_id AS reporter_id,
        1 AS level,
        salary
    FROM Employees

    UNION

    SELECT
        t.employee_id,
        e.employee_id,
        t.level + 1,
        e.salary
    FROM t
    JOIN Employees AS e
        ON e.manager_id = t.reporter_id
)

The column names can be understood as follows:

employee_id: the employee whose complete hierarchy we are currently building
reporter_id: the current person reached inside that hierarchy
level: the current person's distance from the starting employee, with the starting employee at level 1
salary: the salary of the current reporter_id

The distinction between employee_id and reporter_id is the most important part of the solution.

employee_id remains fixed throughout one hierarchy. reporter_id changes as the recursion moves downward.

For Bob's hierarchy, the rows conceptually look like this:

employee_id | reporter_id | level | salary
------------+-------------+-------+-------
2           | 2           | 1     | 10000
2           | 4           | 2     | 7500
2           | 5           | 2     | 7500
2           | 8           | 3     | 6000

The value 2 remains fixed in employee_id because all four rows belong to Bob's hierarchy. The reporter_id changes as Bob, David, Eva, and Hank are visited.

Step 2: Understand the recursive join

The recursive part is:

SELECT
    t.employee_id,
    e.employee_id,
    t.level + 1,
    e.salary
FROM t
JOIN Employees AS e
    ON e.manager_id = t.reporter_id

For each current row in t, the query searches Employees for people whose manager_id equals the current reporter_id.

Suppose the current row in Bob's hierarchy is:

employee_id = 2
reporter_id = 2
level       = 1

The join checks:

e.manager_id = 2

It finds David and Eva. The recursive query then produces:

2 | 4 | 2 | 7500
2 | 5 | 2 | 7500

The key detail is that recursion does not continue for only one returned row. If an iteration returns multiple rows, every newly returned row participates in the next recursive iteration.

Therefore:

David is checked for reports.
Eva is also checked for reports.

David manages Hank, so David's row produces:

2 | 8 | 3 | 6000

Eva manages nobody, so her branch produces no additional row and ends. The recursion continues independently for every active branch until no branch can produce another employee.

This was one of the most important things I learned from the problem: a recursive CTE expands every row returned by the previous iteration, not just one row.

Step 3: Why each employee includes themselves

In the anchor query, I set both IDs to the same value:

employee_id,
employee_id AS reporter_id

This includes every employee in their own hierarchy.

At first, that can look unnecessary because an employee is not their own report. However, it makes the budget calculation much cleaner.

For Bob, the required budget is:

Bob     = 10000
David   =  7500
Eva     =  7500
Hank    =  6000
----------------
Budget  = 31000

Because Bob is already included in his own hierarchy, the budget can be calculated with one expression:

SUM(t1.salary)

We do not need to calculate the reports' salaries separately and then add Bob's salary afterward.

The team size should not include Bob himself. Since his hierarchy contains four rows but only three reports, it can be calculated as:

COUNT(*) - 1

This is a useful SQL design pattern: sometimes deliberately including a row simplifies one calculation, and a small adjustment makes the other calculation correct.

Step 4: Aggregate each employee's hierarchy

After the recursive CTE generates all hierarchies, the following query calculates each employee's team size and budget:

SELECT
    t1.employee_id,
    e.employee_name,
    COUNT(*) - 1 AS team_size,
    SUM(t1.salary) AS budget
FROM t AS t1
JOIN Employees AS e
    ON t1.employee_id = e.employee_id
GROUP BY
    t1.employee_id,
    e.employee_name

For every starting employee_id:

COUNT(*) - 1 counts all direct and indirect reports.
SUM(t1.salary) adds the starting employee's salary and all descendant salaries.
The join retrieves the starting employee's name.

For Bob, this produces:

employee_id | employee_name | team_size | budget
------------+---------------+-----------+-------
2           | Bob           | 3         | 31000

For Charlie, the hierarchy contains Charlie, Frank, Grace, Ivy, and Judy:

Charlie = 10000
Frank   =  9000
Grace   =  8500
Ivy     =  7000
Judy    =  7000
----------------
Budget  = 41500

Charlie has four reports in total, so his aggregated values are:

employee_id | employee_name | team_size | budget
------------+---------------+-----------+-------
3           | Charlie       | 4         | 41500

Step 5: The subtle problem with `level`

The recursive CTE creates a hierarchy starting from every employee. As a result, its level value is relative to the starting employee.

For example, these are valid rows in different hierarchies:

employee_id | reporter_id | level
------------+-------------+------
1           | 4           | 3
2           | 4           | 2
4           | 4           | 1

All three rows refer to David as reporter_id = 4, but they describe David from different starting points:

David is level 3 from Alice's perspective.
David is level 2 from Bob's perspective.
David is level 1 from his own perspective.

The required result needs the global company level, not a relative level. Therefore, we need the hierarchy whose starting point is the CEO.

Step 6: Identify the CEO's hierarchy

The CEO is the employee whose manager_id is NULL:

SELECT employee_id
FROM Employees
WHERE manager_id IS NULL

Using the sample data, this returns Alice's ID, 1.

We then select only the recursive CTE rows whose starting employee_id is the CEO:

SELECT *
FROM t
WHERE employee_id = (
    SELECT employee_id
    FROM Employees
    WHERE manager_id IS NULL
)

This produces Alice's complete hierarchy:

employee_id | reporter_id | level
------------+-------------+------
1           | 1           | 1
1           | 2           | 2
1           | 3           | 2
1           | 4           | 3
1           | 5           | 3
1           | 6           | 3
1           | 7           | 3
1           | 8           | 4
1           | 9           | 4
1           | 10          | 4

Because this hierarchy begins at the top of the organization, its levels are the global levels required by the problem.

In this result:

employee_id is always the CEO's ID.
reporter_id identifies the employee at that level.

That is why the final join matches the aggregated employee ID with level_table.reporter_id.

Step 7: Combine the calculations with the global levels

The aggregated subquery contains:

Employee ID
Employee name
Team size
Budget

The CEO-based hierarchy contains:

Each employee's global level

The two results are joined as follows:

ON x.employee_id = level_table.reporter_id

This attaches the correct global level to every employee's aggregated result.

The final ordering is:

ORDER BY
    level ASC,
    budget DESC,
    x.employee_name ASC

This means:

Employees closer to the top appear first.
Employees at the same level are ordered by larger budget first.
Remaining ties are resolved alphabetically by employee name.

Complete MySQL solution

WITH RECURSIVE t AS (
    SELECT
        employee_id,
        employee_id AS reporter_id,
        1 AS level,
        salary
    FROM Employees

    UNION

    SELECT
        t.employee_id,
        e.employee_id,
        t.level + 1,
        e.salary
    FROM t
    JOIN Employees AS e
        ON e.manager_id = t.reporter_id
)

SELECT
    x.employee_id,
    x.employee_name,
    level_table.level,
    x.team_size,
    x.budget
FROM (
    SELECT
        t1.employee_id,
        e.employee_name,
        COUNT(*) - 1 AS team_size,
        SUM(t1.salary) AS budget
    FROM t AS t1
    JOIN Employees AS e
        ON t1.employee_id = e.employee_id
    GROUP BY
        t1.employee_id,
        e.employee_name
) AS x
JOIN (
    SELECT *
    FROM t
    WHERE employee_id = (
        SELECT employee_id
        FROM Employees
        WHERE manager_id IS NULL
    )
) AS level_table
    ON x.employee_id = level_table.reporter_id
ORDER BY
    level ASC,
    budget DESC,
    x.employee_name ASC;

Walking through the result

Using the sample hierarchy, the calculation for every employee is:

Alice   -> reports: 9 -> budget: 84500 -> level: 1
Bob     -> reports: 3 -> budget: 31000 -> level: 2
Charlie -> reports: 4 -> budget: 41500 -> level: 2
David   -> reports: 1 -> budget: 13500 -> level: 3
Eva     -> reports: 0 -> budget:  7500 -> level: 3
Frank   -> reports: 2 -> budget: 23000 -> level: 3
Grace   -> reports: 0 -> budget:  8500 -> level: 3
Hank    -> reports: 0 -> budget:  6000 -> level: 4
Ivy     -> reports: 0 -> budget:  7000 -> level: 4
Judy    -> reports: 0 -> budget:  7000 -> level: 4

Alice's budget is the sum of all ten salaries:

12000 + 10000 + 10000 + 7500 + 7500
+ 9000 + 8500 + 6000 + 7000 + 7000
= 84500

After applying the requested ordering, Charlie appears before Bob at level 2 because Charlie has the larger budget. At level 3, Frank appears first, followed by David, Grace, and Eva according to descending budget.

Why I used a `JOIN` instead of a correlated subquery

While learning recursion, I wondered whether a correlated subquery could replace the recursive join.

The core operation is:

JOIN Employees AS e
    ON e.manager_id = t.reporter_id

At every recursive step, we need to take the employees found in the previous step and produce a new set of employee rows. A join expresses exactly that relationship: match every current node with all of its children.

A scalar correlated subquery is usually expected to return one value. Even a set-returning correlated subquery does not, by itself, repeatedly feed its results back into the same operation for an unknown number of hierarchy levels.

The recursion provides the repetition, while the join provides the parent-to-child expansion. That is why recursive hierarchy queries commonly use a join in their recursive member.

`UNION` versus `UNION ALL`

My submitted solution uses UNION:

UNION

UNION removes duplicate rows, while UNION ALL retains them and usually avoids the work required for duplicate elimination.

In a valid employee tree where every employee has only one manager and there are no cycles, the recursive paths should not generate duplicate hierarchy rows. In that case, UNION ALL can also be appropriate:

UNION ALL

However, cycle handling deserves special attention in real-world systems. If invalid data allows employees to eventually report back to an employee already present in the same path, recursive traversal may repeat indefinitely or reach the database's recursion limit. Production queries may need explicit cycle detection, data constraints, or database-specific recursion safeguards.

For the LeetCode problem, the input represents a valid hierarchy, so the recursive structure is safe under the problem's assumptions.

Complexity discussion

Let n be the number of employees and let h be the total number of ancestor-descendant relationships generated across all employee hierarchies.

The CTE does not create only n rows. It creates one row for an employee and each employee in that person's subtree. Therefore, the generated row count depends on the shape of the organization.

In a relatively balanced hierarchy, the number of generated relationships can be much smaller than the worst case.
In a chain where every employee manages exactly one other employee, the CTE generates approximately n + (n - 1) + ... + 1 rows, which is quadratic.
The aggregation then processes those generated hierarchy rows.

A useful high-level description is therefore:

Time: proportional to the hierarchy rows generated and processed, with a worst case of O(n^2) for a chain-shaped organization
Space: proportional to the generated recursive result, also up to O(n^2) in the worst case

The actual execution cost also depends on the database engine, indexes, recursive CTE implementation, and query plan. An index on manager_id is especially useful in a real database because the recursive join repeatedly searches for employees reporting to a particular manager.

Common mistakes in this type of problem

1. Counting only direct reports

Grouping employees by manager_id finds only direct reports. It misses employees farther down the hierarchy.

2. Using a fixed number of self-joins

This works only when the maximum hierarchy depth is known and small. It is not a general solution for an unknown-depth tree.

3. Losing the starting employee's identity

If the recursive CTE stores only the current employee being visited, it becomes difficult to know which root employee the row belongs to. Keeping a fixed starting employee_id solves this.

4. Using the relative level as the global level

Because every employee begins at level 1 in their own hierarchy, those levels cannot directly be used as company-wide levels. The global level must come from the CEO's hierarchy.

5. Forgetting whether budget includes the employee

The starting employee must be included in the salary sum. Including that employee in the anchor query makes the calculation straightforward.

6. Counting the employee as their own report

Because the employee is included for budget calculation, team size must subtract one.

7. Assuming recursion processes only one row at a time

If a recursive iteration finds several children, every child is used in the following iteration. Each branch continues independently until it reaches a leaf employee.

What this problem taught me

This problem was difficult for me because it required a different way of thinking about SQL.

Most SQL queries feel set-based and flat: filter rows, join tables, aggregate values, and return a result. A hierarchy is still processed using sets, but recursion allows the result of one iteration to become the input to the next iteration.

The most useful lessons I took from this problem were:

Recognize unknown-depth relationships as a sign that recursion may be required.
Separate the fixed root of a traversal from the current node being visited.
Remember that every row produced by one recursive iteration can expand in the next.
Design the CTE output around the final aggregation you need.
Distinguish between a level relative to a selected employee and a level relative to the global root.
Use joins in the recursive member to expand parent rows into child rows.
Draw the hierarchy before writing the query.

A reusable recursive hierarchy pattern

The central idea can be generalized beyond this problem:

WITH RECURSIVE hierarchy AS (
    SELECT
        root_id,
        current_id,
        1 AS depth
    FROM starting_rows

    UNION ALL

    SELECT
        hierarchy.root_id,
        child.id,
        hierarchy.depth + 1
    FROM hierarchy
    JOIN source_table AS child
        ON child.parent_id = hierarchy.current_id
)
SELECT *
FROM hierarchy;

This pattern appears in many real-world scenarios:

Employee and manager structures
Product categories and subcategories
Folder and directory trees
Bill-of-materials relationships
Comment and reply threads
Geographic region hierarchies
Referral networks

The names of the tables and columns change, but the thought process remains similar:

Choose the starting row or rows.
Preserve the root identity.
Track the current node.
Join the current node to its children.
Increase the depth.
Stop when no children remain.

Final thoughts

Recursive CTEs looked intimidating when I first encountered them. The syntax was not the hardest part. The real challenge was understanding what each row represented and how the result of one iteration was used to generate the next.

For this solution, the breakthrough was to think in terms of two identities:

The employee whose entire team I want to calculate
The current employee being visited inside that team

Once those roles were separated into employee_id and reporter_id, the remaining steps became much clearer:

Recursively build every employee's subtree.
Include the employee themselves to simplify budget calculation.
Group each subtree to calculate team size and budget.
Use the CEO's subtree to obtain global hierarchy levels.
Join the results and apply the required ordering.

This was not just another SQL problem for me. It introduced me to a way of solving hierarchies that ordinary joins and aggregations cannot handle cleanly on their own.

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.

DEV Community: Saami abbas Khan

LeetCode Problem Statements Be Testing My English More Than SQL

How an Employee Hierarchy Helped Me Understand Recursive CTEs

How I Solved the Employee Hierarchy Problem (LC 3482 for our reference), With a Recursive CTE

The problem in simple terms

Visualizing the organization

Why ordinary joins are not enough

How a recursive CTE works

The anchor query

The recursive query

The key idea in my solution

Step 1: Start one hierarchy from every employee

Step 2: Understand the recursive join

Step 3: Why each employee includes themselves

Step 4: Aggregate each employee's hierarchy

Step 5: The subtle problem with level

Step 6: Identify the CEO's hierarchy

Step 7: Combine the calculations with the global levels

Complete MySQL solution

Walking through the result

Why I used a JOIN instead of a correlated subquery

UNION versus UNION ALL

Complexity discussion

Common mistakes in this type of problem

1. Counting only direct reports

2. Using a fixed number of self-joins

3. Losing the starting employee's identity

4. Using the relative level as the global level

5. Forgetting whether budget includes the employee

6. Counting the employee as their own report

7. Assuming recursion processes only one row at a time

What this problem taught me

A reusable recursive hierarchy pattern

Final thoughts

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

Step 5: The subtle problem with `level`

Why I used a `JOIN` instead of a correlated subquery

`UNION` versus `UNION ALL`

`ROWS`

`RANGE`