The 4-level nested SQL

“Longest streak of consecutive up days” is a question that keeps coming back in data role interviews. The pass rate is said to be below 20% - not because it is difficult, but because it lays bare an old SQL problem: you know exactly what the logic is, but writing it in SQL turns it into a tangled mess of nested queries.

First, let’s look at a requirement. Stock – a stock market data table that records each day’s closing prices. Our goal: calculate the longest streak of consecutive up days in a stock’s trading history.

The logic is simple: sort data by date, then going day by day, check whether each day’s price is higher than the previous day’s; flag the consecutive up intervals, count how many days are in each interval, and take the maximum.

You can run through the logic in your head – sort, check, segment, count, take the maximum. Five steps. Crystal clear.

But once it lands in SQL, it looks like this:

select max(ContinuousDays)
from (
    select count(*) ContinuousDays
    from (
        select sum(UpDownTag) over (order by DT) NoRisingDays
        from (
            select DT,
                case when CL > lag(CL) over (order by DT) then 0 
                else 1 end UpDownTag
            from Stock
            where CODE = 100046
        )
    )
    group by NoRisingDays
)

Four levels of nesting. Let’s go through them from the innermost to the outermost:

• The 4^th level: case when CL > lag(CL) ... then 0 else 1 end – Mark zero for a higher price, 1 for a same or lower price.

• The 3^rd level: sum(UpDownTag) over (order by DT) – Cumulative sum: add 0 for a higher price – the value stays the same; add 1 for a same or lower price – the value increases by 1. Within one consecutive-up interval, the cumulative sum stays constant.

• The 2^nd level: Group data by this cumulative sum; count(*) returns how many days are in each interval.

• The 1^st level: Take the maximum value.

Took some real effort, but I finally get what each level does. I think I even understand the logic behind them. But what if the requirement changes next time?

For example, change “up” to “only counts as up if the increase exceeds 1%”. You have to start from the innermost case when. Then check whether the cumulative logic at the 3^rd level still holds; then verify whether the grouping logic at the 2^nd level is still valid; and finally check whether the max logic at the outermost level is affected. Four levels of nesting – change one, and you have to mentally rerun all four.

Another example: in addition to calculating the longest streak of consecutive up days, you also need to find out when it started. There’s only one max operation at the outermost level, so you can’t get the start and end dates there. You have to pass the date field outward, level by level, starting from the innermost. The entire query has to be rewritten.

Classic case of looks easy, falls apart the moment you try.

This is the cost of nested SQL: at every level of nesting, you face a logic puzzle – will the change I just made here affect the level above? The code runs, but the cost of modifying it is even higher than rewriting it from scratch. This isn’t a problem of SQL itself; it’s a problem baked into cramming sequential logic into a nested structure.

SQLazy: The natural way to write SQL

SQLazy’s approach is simple: don’t cram logic into a nested structure – write it out in sequence.

Here’s how to write the longest streak of consecutive up days with the SQLazy workflow:

Name	Anchor	Statement
t1	stock	filter CODE = 100046
t2		sort DT asc
t3		segment CL down as NoRisingDays
t4		summarize DT count as ContinuousDays group NoRisingDays
t5		summarize ContinuousDays max as max_ContinuousDays

Run this sample code online: https://www.sqlazy.com/?4GL

Five steps – in exactly the same order you ran through in your head.

• filter: Filter stocks to get the target one.

• sort: Sort stocks by date.

• segment: Segment rows by a condition – CL down means if the closing price falls, start a new group. Rows within one consecutive-up interval automatically fall into the same group.

• summarize: Count days in every group.

• summarize: Get the maximum value.

You can preview the intermediate results at every step. The moment step 3 finishes, you see the NoRisingDays column, where the same number corresponds to the same consecutive-up interval. Right or wrong, you know it on the spot.

That covers a single stock. Now here’s the next requirement: among all stocks, find those that have had a streak of more than three consecutive up days.

In SQL, this means wrapping an additional layer – PARTITION BY CODE – around the nested query above, then filtering with GROUP BY CODE at the outermost level. One more layer of code, one more notch down in readability. You have to start by understanding the innermost window function and work your way out to the outermost level, before you figure out what this HAVING clause is actually filtering.

In SQL, this means wrapping an additional layer — PARTITION BY CODE — around the nested query above, then filtering with GROUP BY CODE at the outermost level. One more layer of code, one more notch down in readability. You have to start by understanding the innermost window function, then work your way out to the outermost level, before you can figure out what this HAVING clause is actually filtering.

With SQLazy, the logic doesn’t change at all. Just add partition CODE onto the stock-based grouping operation:

Name	Anchor	Statement
t1	stock	sort CODE asc, DT asc
t2		segment CL down as NoRisingDays partition CODE
t3		summarize DT count as ContinuousDays group CODE, NoRisingDays
t4		filter ContinuousDays > 3
t5		derive CODE distinct

Run this example online: https://www.sqlazy.com/?49V

Still the five steps in sequence:

• sort: Sort rows by stock code and date.

• segment partition CODE: Partition each stock individually into segments based on price declines.

• summarize group CODE, NoRisingDays: For each stock, count the days in each consecutive-up interval.

• filter ContinuousDays > 3: Only retain intervals with more than 3 consecutive up days.

• derive CODE distinct: Extract distinct stock codes that had at least one such interval.

Every step is clear. No need to mentally unfold the nesting, nor do you need to reason about whether a change at this level affects the level above.

The compiler’s guarantee: 100% accurate, auto-generated SQL

Now the workflow is finished, and the logic is verified. You just need to do one more thing: click the “Compile” button. The SQLazy compiler will deterministically translate the workflow into native SQL for the target database.

Note: It is the “compiler”, rather than an “AI generator”. Here are their essential differences:

	AI generator	SQLazy compiler
Output consistency	Same input, but output may differ each time	Same input, always the same SQL output
Accuracy	Probabilistic; possible hallucinations	Deterministic; 100% accuracy
Auditability	Cannot trace why this code was generated	Every step of the conversion is traceable and verifiable

The compiler does not “guess”. It does just one thing: translate each step of the workflow into the corresponding SQL dialect according to fixed rules. “sort” is ORDER BY, “filter” is WHERE, “segment” is window-function-based cumulative segmentation, and “summarize” is GROUP BY. One-to-one correspondence, deterministically certain.

This means that:

• No hallucinations – the compiler does not “invent” non-existent tables or fields, nor does it “guess” the business logic.

• No randomness – the same workflow gives the same result, whether compiled today or tomorrow.

• Auditable – every step of the workflow can be traced back to the SQL fragment it generates. Auditors can verify why this SQL is written the way it is.

If the target database changes from MySQL to PostgreSQL, simply switch the database option and the compiler automatically adapts the target SQL dialect. No need to modify the workflow, because the logic stays the same - what changes is only the “translation target”.

The workflow is for humans to read, and SQL is for the database to run. The compiler guarantees they always stay in sync.

LLM-assisted polish

LLMs formalize natural language input into SQLazy statements

The former is “decision-making” – letting AI decide how the SQL should be written, which carries extremely high risk; the latter is “translation” – asking AI to translate natural language into a formalized format. Even if the AI’s translation is off, you can spot it at a glance at the workflow level, and the cost of correction is almost zero.

AI writes the logic. A compiler writes the SQL.