DEV Community: Judy

Your AI SQL Tool Doesn’t Have an “Undo Logic” Button – That’s the Real Problem

Judy — Tue, 29 Sep 2026 07:19:07 +0000

Have you ever had this experience: revising the prompt for an AI SQL tool through five or six versions, clicking Undo and rewriting countless times, and the result that finally comes out is still wrong.

You can undo what you typed, or delete code you don’t like. But you can’t revert the logic that AI quietly drifted into – and that’ s the most insidious trap in AI-written SQL.

Take a user behavior analysis task as an example: resetting the session number whenever the time gap exceeds 1 hour. The requirement can be stated in a single sentence, while the AI-generated SQL is always syntactically perfect:

WITH lagged AS (
  SELECT *, LAG(dt) OVER (PARTITION BY account_number ORDER BY dt) AS prev_dt
  FROM numEvents
), grouped AS (
  SELECT *,
    SUM(CASE WHEN TIMESTAMPDIFF(SECOND, prev_dt, dt) > 3600 THEN 1 ELSE 0 END)
    OVER (PARTITION BY account_number ORDER BY dt) AS grp
  FROM lagged
)
SELECT *, ROW_NUMBER() OVER (PARTITION BY account_number, grp ORDER BY dt) AS seq
FROM grouped

Three levels of nesting, syntactically flawless. But what if the result is wrong? You don’t know what the bug is – is it an edge case handling error in LAG, a reversed condition in the cumulative sum, or wrong row-number partitioning? Want to fix it? All you can do is revise the prompt and regenerate the entire query – which amounts to rewriting the whole thing from scratch. Your Undo button can delete the entire block of code, but it can’t revert the drift in the logic.

In SQLazy, the same logic is divided into three clear, neat steps:

Each step corresponds to a specific business action. If the time interval is wrong, fix the interval; if the partition is wrong, fix the partition. The error is pinpointed precisely and fixed step by step. You don’t need to rewrite the whole thing from scratch, because each step of the logic comes with a built-in “revert node”.

In this process, AI is only responsible for translating logic at each step – turning natural-language input into SQLazy syntax. You can tell at a glance whether the translation is correct. Once confirmed, the compiler turns it into deterministic SQL.

AI writes the logic. A compiler writes the SQL.

You see every step of the logic, so you can fix every drift precisely.

Our open-source example repository collects complete solutions for these time-window and session-analysis scenarios, covering a dozen common business requirements.

GitHub repository: https://github.com/SPLWare/SQLazy/tree/master/examples/time-window-analytics

If you’re also fed up with the “revise prompt → hope it works → scrap it and start over” cycle, try SQLazy’s online Playground. No sign-up, no download – everything runs locally in your browser, and the data never leaves you device.

Try SQLazsy: sqlazy.com

A truly efficient tool doesn’t mean repeated undos and rewrites – it lets you see the logic from the start, so you take fewer wrong turns.

SQLazy is live on Product Hunt today~~

Judy — Tue, 22 Sep 2026 05:51:28 +0000

If you've ever wrestled with SQL, this one's for you.

AI can write runnable SQL statements, but it is often unreliable. SQLazyturns SQL development into a step-by-step, verifiable, and auditable workflow, with a compiler to ensure that the final output is correct. How SQLazy works? SQLazy does not generate a huge blob of SQL in one go. Instead, it turns the SQL development into a step-by-step, traceable workflow:
Use semi-natural language syntax to describe what each step does.

Verify the logic at each step – Intermediate results are inspectable.

Let the compiler generate the final SQL.

The final SQL is generated by the compiler, not the LLM. This means:

Zero SQL errors from AI hallucination, with 100% correct results.

The logic is fully auditable.

The output is production-ready.

Would love your support — every upvote and comment helps:
https://www.producthunt.com/products/sqlazy?utm_source=other&utm_medium=social

Thank you very much.

I Stopped Trusting AI-Generated SQL – Here’s the Approach I Trust Instead

Judy — Fri, 18 Sep 2026 07:28:33 +0000

Approach I Trust Instead

Why I no longer trust AI-generated SQL: not because it fails to run, but because it runs too smoothly. It passes syntax checks. It runs. But it can be riddled with errors in places nobody’s watching.

Have you ever had AI-generated SQL that passed syntax checks, ran without errors – but silently got a row wrong?

Runs ≠ Trustworthy

Here’s a seemingly simple requirement: In a status history table, the NewStatus field records the status of each ID. Each ID has a ConfirmationStarted row and multiple Closed rows. We need to retrieve the most recent Closed row before the ConfirmationStarted row.

The expected result:

Take ID=147 as an example. It has three Closed rows before ConfirmationStarted; and the last one – dated 06-25 – is “the most recent”. The Closed row dated 08-25 doesn’t count because it comes after ConfirmationsStarted.

The expected output contains only two rows: "147→2022-06-25"、"1645→2023-04-29". The other Closed rows are either too early or come after ConfirmationStarted – none of them count.

You throw the requirement at an AI, and seconds later, get a beautiful block of SQL – CTEs stacked with window functions. Even the code review turns up nothing wrong. It’s not until two weeks after going live that you discover ID 147 was matched to 05-28. One row is wrong, yet the SQL runs fine.

The bug is hard to spot at first glance: the segmentation logic is correct, but the final aggregation uses MIN instead of MAX. As a result, the query returns the earliest Closed row before ConfirmationStarted instead of the one closest to it, quietly shifting the result by one row for boundary cases.

Here’s the buggy AI-generated SQL:

WITH t2 AS (
        SELECT CreatedAt, ID, NewStatus
            , 1 + SUM(CASE WHEN NewStatus='ConfirmationStarted' THEN 1 ELSE 0 END)
                OVER (PARTITION BY ID ORDER BY ID ASC, CreatedAt ASC ROWS UNBOUNDED PRECEDING) AS seg
        FROM mytable
    )
SELECT ID, MIN(CreatedAt) AS CreatedAt -- This is one of the several errors – the correct function should be MAX, to get the most recent row
FROM (
    SELECT CreatedAt, ID, NewStatus, seg
    FROM t2
    WHERE NewStatus='Closed' AND seg=1
) t_3
GROUP BY ID
ORDER BY ID

This is the most dangerous part of having AI generate the final SQL: AI can rewrite a block of code, but catching a subtle one-row logical error is a different story.

We asked AI to do exactly what it shouldn’t

AI is good at breaking down the plain-language requirements into steps, but it isn’t good at giving a 100% guarantee for every boundary condition in the final SQL. It’s a probabilistic model, not a compiler.

Public benchmarks show that even leading large language models often achieve only around 60% execution accuracy on complex queries when translating natural language directly into executable SQL. That’s nowhere near reliable enough for direct sign-off – on average, one attempt in every three or four can be wrong.

The old paradigm is “prompt → AI → non-deterministic final SQL”. It’s a black-box delivery model: where the only option is to have AI re-guess the whole block from scratch.

What we need is a new paradigm: prompt → AI → standardized steps → compiler → deterministic SQL, with every step independently verifiable.

“It runs” doesn’t count. “You’d sign off on it” does.

SQLazy is that confidence to sign off. It is the compiler for standardized steps – and the tool that actually turns the new paradigm into reality.

In SQLazy: This takes just 4 steps

The same “most recent Closed row” task takes just 4 steps in SQLazy. The key is that you can click any step and inspect the intermediate result.

Step 1: sort ID, CreatedAt asc – puts the rows in the correct chronological order first.

Step 2: segment condition (NewStatus ="ConfirmationStarted") partition ID as seg – segments rows by ConfirmationStarted; partition ID ensures that each ID is segmented independently. The segmentation column is seg, where seg=1 is the target segment. There’s no need to hand-write SUM(CASE WHEN ...) OVER.

Step 3: filter (NewStatus ="Closed"and seg = 1) – keeps only the Closed rows in the target segment.

Step 4: summarize CreatedAt max as CreatedAt; group ID – takes the row with the largest timestamp for each ID: that’s the most recent one.

To check whether the segmentation is correct, click t2 and check the seg column. Fix whichever step is wrong – if the error is in step 2, you only fix that step. There’s no need to start over from scratch. Whether you compile for MySQL or Snowflake, the same input always produces the same output. It doesn’t invent fields.

WITH t2 AS (
        SELECT CreatedAt, ID, NewStatus
            , 1 + SUM(CASE
                WHEN (NewStatus = 'ConfirmationStarted') THEN 1
                ELSE 0
            END) OVER (PARTITION BY ID ORDER BY ID ASC, CreatedAt ASC ROWS UNBOUNDED PRECEDING) AS seg
        FROM mytable
    )
SELECT ID, MAX(CreatedAt) AS CreatedAt
FROM (
    SELECT CreatedAt, ID, NewStatus, seg
    FROM t2
    WHERE (NewStatus = 'Closed'
        AND seg = 1)
) t_3
GROUP BY ID
ORDER BY ID

This SQL isn’t guessed by AI. It’s what the compiler produces by translating the four steps according to fixed rules. It’s guaranteed correct. No need to review it.

Of course, there’s no need to use SQLazy for simple CRUD queries. It’s intended for complex logic that takes 30+ lines of SQL, such as logic involving time sequence, segmentation, and relative positions.

Paste your most suspicious AI-generated SQL here

What’s the most typical kind of hallucination? Fabricating fields or tables that don’t exist, applying a filter condition to the wrong column, or getting the join key or aggregation logic wrong. The SQL still runs, but the result is already off. It’s the kind of thing we’ve all seen before.

Stop cycling through “rewrite prompt → rerun → try your luck”. Just hand the part of your business logic you trust least to SQLazy, and let it break the logic down into a step-by-step workflow that can be validated stepwise. Then share your AI hallucination cases in the comments.

AI provides the logic; the compiler makes it real – with zero hallucinations.

Is SQLazy Something Missing from Your dbt Workflow?

Judy — Tue, 15 Sep 2026 07:55:11 +0000

dbt is good – but it has blind spots

If you’re working in analytics engineering, dbt is probably already part of your workflow. Hand it the T in ELT, and SQL becomes testable, documentable, collaborative code. For simple transformations such as JOIN, CASE and GROUP BY, it handles them with ease.

But once the analytical logic gets more complex – for example, when it involves multiple levels of window operations, conditional segmentation, sessionization, cumulative-value/running-total resets, and the like – the SQL in a dbt model balloons. Pile up enough nesting, and the code turns into a wall: no one can read it, no one dares to touch it, and no one knows whether a change will actually fix things or make them worse. Three months later, the requirement changes. You open that model and stare blankly at five CTEs and three window functions –change it, and you risk breaking something; leave it alone, and no one may notice when the data goes wrong. In the end, you either work around it, or simply drop the requirement.

dbt solves the “pipeline” problem in SQL engineering, but it doesn’t address the “design process” for complex analytical logic. What’s needed is a way to work out and validate that complex logic outside dbt first, and then embed the clean SQL into the model.

A real-world sessionization example

Business logic: There is a user behavior event table, where session IDs needs to rest based on a time interval – start a new session whenever there has been no activity for more than one hour. This is the foundation of user behavior analysis – session duration, conversion rate, and retention funnels all depend on it. In the dbt community, almost every analytics engineer has encountered this kind of sessionization or event-based query.

Implementing this logic in SQL usually takes three layers of nested CTEs: use LAG to retrieve the previous timestamp, CASE WHEN to run a cumulative check for session breaks, and finally ROW_NUMBER to generate sequence numbers.

WITH lagged AS (
  SELECT *, LAG(dt) OVER (PARTITION BY account_number ORDER BY dt) AS prev_time
  FROM event_tb
), grouped AS (
  SELECT *,
    SUM(CASE WHEN TIMESTAMPDIFF(SECOND, prev_time, dt) > 3600 THEN 1 ELSE 0 END)
    OVER (PARTITION BY account_number ORDER BY dt) AS grp
  FROM lagged
)
SELECT *, ROW_NUMBER() OVER (PARTITION BY account_number, grp ORDER BY dt) AS seq
FROM grouped

With so many levels of nesting, any change requires validation layer by layer, from innermost CTE outward. The SQL runs – but no one wants to maintain it. Then, three months later, new logic comes along, for example, sessions that cross midnight now count as new. You have to start by understanding the code from the innermost LAG, verify the logic layer by layer to make sure everything still holds. The whole query amounts to a rewrite.

SQLazy’s approach: Design in steps, compile and embed

SQLazy decomposes complex analytical logic into clear, step-by-step operations, validates them one by one, then compiles them into SQL and embeds it in dbt.

Back to the sessionization example. Here’s how SQLazy implements it, step by step:

Run the code online: https://www.sqlazy.com/?4M4

Three steps, in exactly the same logical order as humans would think through them.

Step 1: Sort by user and timestamp, ensuring that events are handled in chronological order.

sort account_number asc dt asc

Step 2: Segment by time interval and assign session ID.

segment condition ((dt[-1] elapse 3600 second)<= dt) partition account_number as grp

This is the critical step. segment handles grouping – it traverses through each user’s data, and when the interval between the current event and the previous one exceeds one hour, start a new group. dt[-1] elapse 3600 second means “add 3,600 seconds to the previous row’s timestamp”. If this value is less than or equal to the current row’s timestamp, the interval is within one hour, and the row stays in the current group; otherwise, a new group starts. account_number ensures that each user’s data is segmented independently.

Once this step is executed, the intermediate table gets a new column, grp – where the same number represents the same session. You can immediately see whether the segmentation is correct.

Step 3: Generate incrementing sequence nubers within each session.

compute # as seq partition account_number grp

compute is used to create a computed column; # is the row number, generating incrementing sequence numbers starting from 1 within each account_number + grp combination. partition specifies the partition or grouping dimension, ensuring the sequence numbers are numbered independently within each session.

You can run each step separately and preview the intermediate result. To change the interval threshold, you only need to modify the one-line step T2 while keeping the other steps unchanged. Once T2 is executed, you can immediately see how grp changes and whether the segmentation is correct. There’s no need to mentally unravel the nested logic or reason about whether “changing one layer breaks the one above it”.

Once the workflow (SQLazy steps) is designed and its logic is validated, click “Compile”. SQLazy’s compiler deterministically converts the workflow into native SQL for the target database – MySQL, PostgreSQL, Snowflake, or BigQuery – just switch the database option. Then paste the compiled SQL into a dbt model’s .sql file, and let dbt handle materialization, testing, documentation, and lineage.

SQLazy doesn’t replace dbt – it fills the gap that dbt doesn’t cover: designing and validating complex analytical logic.

The SQLazy workflow is a living document. When requirements change three months later, you open the workflow, understand the logic from each step, and modify the relevant steps. The compiler guarantees correct SQL – not generated through AI guesswork, but produced through deterministic compilation: the same workflow always generates the same SQL.

Why SQLazy over writing SQL by hand

Some users might think: I’ll just hand-write it. It’s only a little more complicated, so what.

The problem is that in SQL, the cost of becoming “a little more complicated” doesn’t scale linearly. Typically, going from two levels of nested window functions to four increases the maintenance burden by an order of magnitude. And analytical requirements tend to get more complex over time.

Once you embed SQLazy into your dbt workflow, it brings the following changes:

Step-level debugging. Inspect every intermediate table just as you would when debugging code – no need to add a debug field in dbt and repeatedly run dbt run. Say step 2 segments data wrong – fix it on the spot, and every subsequent step recalculates automatically.

Logic as documentation. The workflow itself is a readable description of the logic. A new team member can open it and understand what the analysis does within minutes – no need to dig through dbt’s docs or ask the person who wrote the code.

Cross-dialect portability. The same workflow can be compiled into MySQL, PostgreSQL, Snowflake, and BigQuery dialects at will. When a dbt project is migrated from PostgreSQL to Snowflake, there’s no need to rewrite the analytical logic – the compiler adapts to the target database automatically.

LLM-assisted design. Converting plain-language step descriptions into a standardized workflow lowers the barrier to designing complex logic. AI only translates; it does not make decisions. The final SQL is generated deterministically by the compiler, with zero hallucinations.

Of course, SQLazy has its limits. You don’t need it for simple CRUD queries – using it for SQL queries that can be written in just a few lines is overkill. Where it really shines is exactly where dbt users feel the most pain: analytical logic complex enough to need step-by-step decomposition.

Final thoughts

dbt controls the transformation pipeline, while SQLazy designs the analytical logic. The two aren’t a replacement for each other – they are complementary. dbt handles materializing, testing, documenting, and tracking lineage for the analytical output, while SQLazy helps you design and validate complex analytical logic before turning it into SQL.

If there’s a chunk of analytical SQL running over 30 lines in your dbt model, try decomposing it into steps in SQLazy first.

Try SQLazy online: sqlazy.com (Free to use, signup not required)

Installer download link: https://www.raqsoft.com.cn/download-NaturalSPL

Project repository: github.com/SPLWare/SQLazy

Case collection: github.com/SPLWare/sqlazy/tree/master/examples

SQLazy: Convert Swipe Records into Single-Row Sessions by Pairing Order

Judy — Tue, 08 Sep 2026 09:13:37 +0000

Problem Description

Table userBuilding stores swipe logs for personnel entering and exiting buildings, with one record per timestamp and fields username, building, action (IN/OUT), and timestamp. Normally, records for the same person in the same building appear in pairs, IN followed by OUT. In practice, the data is messy: unpaired records and consecutive actions in the same direction occur. The task is to turn each pair of records for each person and each building into one row by pivoting rows to columns; unpaired records become separate rows, with NULL for the missing side, i.e., convert the vertical log into horizontal sessions in pairing order.

Source Data

Expected Result

Take user-3/building-1 as an example. The raw sequence is OUT, IN, IN, IN, OUT, OUT, OUT, which is split into 6 segments by the pairing rules: the first OUT stands alone, the next two INs each stand alone, the fourth IN pairs with the first OUT into one row, and the last two OUTs each stand alone. Consecutive actions in the same direction are never forced into a pair, ensuring correct session boundaries. In the 13-row result, this user accounts for 6 rows, which illustrates the logic.

SQLazy Step-by-Step Implementation

Core idea: First sort by username, building, and timestamp to arrange the log of the same person in the same building in time order; then use a conditional segment to detect session boundaries - start a new group when the previous record is OUT or the current record is IN, so each group contains at most one IN and at most one OUT; finally group by username, building, and seg, and use conditional max aggregation to collapse the IN time and OUT time within each group into one row, with NULL for unpaired sides.

[Click to run this example online]

The steps are explained below.

Step 1: Sort by person, building, and time

sort username, building, timestamp asc

Sort records of the same person in the same building by timestamp in ascending order to ensure subsequent pairing decisions follow time order. Using username and building as leading sort keys keeps the order within each partition consistent with the partition keys. Sorting is a prerequisite for the subsequent segment and summarize steps.

Step 2: Segment by pairing semantics to generate seg (core)

segment condition ((action[-1] = "OUT")or (action[-1] = "IN" and action = "IN")) partition username, building as seg

The most critical step is to express session boundaries with a conditional segment: start a new group when the previous record is OUT, or when the previous record is IN and the current is also IN. A previous OUT means the previous session is closed and a new one should start; consecutive INs mean multiple swipes for entry, and each additional IN starts a new group to avoid squeezing multiple INs into one session. partition username, building keeps different persons and buildings independent, each numbered with its own seg. The condition action[-1] is SQLazy's relative position syntax, equivalent to LAG(action,1), without manually writing window functions.

Step 3: Group by person, building, and segment number, then conditionally aggregate rows to columns

summarize condition (action = "IN") max timestamp as 'IN', condition (action = "OUT") max timestamp as 'OUT'; group username, building, seg

Group by username, building, and seg; each group contains at most one entry and one exit. Use conditional aggregation: when action = "IN", take max(timestamp) as the IN column, and when action = "OUT", take max(timestamp) as the OUT column. max and first are equivalent here because there is at most one record of each type per group; using conditional max naturally yields NULL on the other side for unpaired groups. Note that in the latest syntax the aggregation function (max) comes before the aggregated expression (timestamp), and grouping keys are specified via "group username, building, seg".

Step 4: Clean up the helper column

derive delete seg

Remove the auxiliary column seg produced by segment, keeping only the four columns username, building, IN, and OUT for the final result. The table is cleaner.

Generated SQL
After confirming the four steps above, the SQLazy compiler automatically generates native SQL (Oracle syntax here):

SELECT MAX(CASE
        WHEN (action = 'OUT') THEN timestamp
        ELSE NULL
    END) AS "OUT"
    , MAX(CASE
        WHEN (action = 'IN') THEN timestamp
        ELSE NULL
    END) AS "IN"
    , building, username
FROM (
    SELECT username, building, action, timestamp
        , 1 + SUM(CASE
            WHEN (col__2 = 'OUT'
                OR action = 'IN')
            THEN 1
            ELSE 0
        END) OVER (PARTITION BY username, building ORDER BY username ASC, building ASC, timestamp ASC ROWS UNBOUNDED PRECEDING) AS seg
    FROM (
        SELECT t1.*, LAG(action) OVER (PARTITION BY username, building ORDER BY username ASC, building ASC, timestamp ASC) AS col__2
        FROM t1
    ) sub__3
) t_4
GROUP BY username, building, seg
ORDER BY username, building, seg;

SQLazy lets you describe logic in business language instead of writing nested queries in SQL syntax. In this example, a single conditional segment expresses the business rule clearly: segment condition ((action[-1] = "OUT")or (action[-1] = "IN" and action = "IN"))partition username, building, i.e., "start a new session when the previous segment is closed or a consecutive swipe-in occurs". Writing this in SQL manually requires LAG to fetch the previous row, SUM OVER to accumulate segment numbers, two layers of subqueries to wrap window columns, and finally conditional aggregation MAX(CASE...) to pivot rows to columns, plus handling partition and ordering consistency. SQLazy compresses all of this into four steps - sort, segment, conditional summarize, and cleanup - each verifiable independently; relative positions and partitions are compiled into window functions, and conditional summarize handles NULL automatically.

Official Links
SQLazy Online Experience: sqlazy.com (free, no registration required)

SQLazy Repository: github.com/SPLWare/SQLazy

SQLazy: Get the Latest Closed Before ConfirmationStarted

Judy — Fri, 04 Sep 2026 08:04:22 +0000

Problem Description

Database table mytable stores the status NewStatus of multiple IDs at different timestamps CreatedAt. Each ID has exactly one ConfirmationStarted and one or more Closed statuses. The task is: within each ID, among all the Closed records before ConfirmationStarted, find the one closest to ConfirmationStarted, and take the record's ID and time fields.

Source Data

Expected Result

Take ID=147 as an example:

ConfirmationStarted occurs on 2022-07-13; before it, the three Closed records happen on 05-28, 06-18 and 06-25, and the one closest to it is 2022-06-25 05:59:01, which is exactly the time in the expected result.

For ID=1645, ConfirmationStarted occurs on 2023-05-08 14:53:34, with only one Closed (2023-04-29 05:59:02) before it, so the result takes that one.

SQLazy Step-by-Step Implementation

Core idea: After sorting each ID's records by time, use segment to cut segments wherever ConfirmationStarted appears; records before the first ConfirmationStarted naturally fall into seg=1. Then filter out the records with seg=1 and status Closed, and finally summarize by ID taking the maximum CreatedAt, which is the Closed closest to ConfirmationStarted.

[Click to run this example online]

The steps are explained below.

Step 1: Sort by ID and time in ascending order

sort ID, CreatedAt asc

Ensures the records within each ID are arranged in time order, providing the basis for the subsequent segmentation and for taking the "latest".

Step 2: Start a new segment when ConfirmationStarted is encountered

segment condition (NewStatus = “ConfirmationStarted”) partition ID as seg
This is the core step. segment with partition ID segments independently within each ID; the segment condition specifies that whenever a record whose NewStatus is ConfirmationStarted is encountered, a new segment is opened and numbered as seg. In this way, all records before the first ConfirmationStarted fall into seg=1, and the seg of ConfirmationStarted itself and the records after it increases in turn. A single statement cuts out the range “before the target status”.

*Step 3: Filter out the target records
*
filter (NewStatus = “Closed” and seg = 1)
Keep only the records with seg=1 (before the first ConfirmationStarted) and status Closed; these are all the Closed records of each ID before ConfirmationStarted.

*Step 4: Summarize by ID to take the latest Closed time
*
summarize CreatedAt max as CreatedAt; group ID
Take the maximum CreatedAt within each ID. Since the records were sorted by time in ascending order earlier, the maximum is exactly the Closed closest to ConfirmationStarted. summarize directly describes the aggregation with the business semantics of “group by ID and take the maximum CreatedAt”, without manually writing window functions.

Generated SQL
After confirming the above 4-step logic, the SQLazy compiler automatically generates native SQL (Oracle syntax here):

WITH t2 AS (
        SELECT CreatedAt, ID, NewStatus
            , 1 + SUM(CASE
                WHEN (NewStatus = 'ConfirmationStarted') THEN 1
                ELSE 0
            END) OVER (PARTITION BY ID ORDER BY ID ASC, CreatedAt ASC ROWS UNBOUNDED PRECEDING) AS seg
        FROM mytable
    )
SELECT ID, MAX(CreatedAt) AS CreatedAt
FROM (
    SELECT CreatedAt, ID, NewStatus, seg
    FROM t2
    WHERE (NewStatus = 'Closed'
        AND seg = 1)
) t_3
GROUP BY ID
ORDER BY ID

SQLazy lets you describe logic in business language instead of writing nested SQL queries. For this kind of problem of cutting segments by events and then taking records from a specified segment, the key is to mark the event stream with segment labels: segment's conditional segmentation directly describes the business semantics with"cut a segment when ConfirmationStarted is encountered", and partition makes the segmentation run independently within each ID. The step-by-step computation of segment first, then filter, then summarize lets every step's intermediate result be verified independently; summarize completes the aggregation with a plain statement like "group by ID and take the maximum time", and the compiler automatically generates runnable SQL.

Official Links
SQLazy Online Experience: sqlazy.com (free, no registration required)

SQLazy Repository: github.com/SPLWare/SQLazy

No More Rewriting the Same Logic for Every Database

Judy — Wed, 02 Sep 2026 06:16:31 +0000

First, let me ask you: how many databases does your team use?

MySQL for OLTP, PostgreSQL for OLAP, Snowflake for the data warehouse, and occasionally, running a report on some legacy Oracle system – that’s practically standard for every data team today.

Now comes the question: How many times do you need to implement the same business logic?

The “dialect tax” every database imposes

SQL has a subtle concept called “dialect.” Standard SQL is one thing, but every database has its own “accent”:

MySQL has LIMIT. Oracle has ROWNUM. SQL Server has TOP.

Date functions: MySQL has DATE_ADD. PostgreSQL has INTERVAL. Oracle has ADD_MONTHS.

String concatenation: MySQL has CONCAT. SQL Server has +. PostgreSQL has ||.

Window function support, recursive CTE syntax, and GROUP BY strictness — every database has its own way of doing things.

You spend a whole afternoon getting a complex analytical SQL query working on MySQL. It runs beautifully. Then comes the requirement: run the same logic on Snowflake. You open the editor, copy the SQL over, hit run — and it throws an error.

Not because the logic is wrong, but because you are using the wrong dialect.

So, you start fixing it: replace LIMIT with QUALIFY, rewrite every date function, change the string concatenation syntax… Eventually, it works. But what happens when the requirements change again? Rewrite everything all over again? And what if the next project uses a different database?

You spend more time translating between dialects than thinking through the logic.

This isn’t an isolated case. According to a Stack Overflow survey, developers spend a significant amount of their coding time dealing with environment setup and compatibility issues. And SQL dialect differences are one of the most subtle yet costly forms of “compatibility tax”.

Worse still, the cost compounds. Every new database you support means one more SQL code version to maintain. Three databases = three SQL code versions. The change of one business rule means three places to update. Miss one update = production data don’t match = a midnight page to fix bugs.

Why can’t we “write once, run everywhere”?

You might think: “Then why not just use standard SQL?”

In theory, yes. But in practice, standard SQL cannot cover real-world business requirements. Dialect differences in window function support, the many different approaches to date and time handling, and different implementations of string operations – these are either undefined or loosely defined in the standard SQL, leaving each database with its own implementation.

Think about it differently: you don’t need “one SQL running everywhere”; you need “one logic generating SQL everywhere.”

These are two very different concepts.

“One SQL running everywhere” means sending the same SQL text to every database – and this approach simply doesn’t work.

“One logic generating SQL for every database” means writing the logic once and letting a tool compile it into each database’s dialect – and this is a path that works.

SQLazy’s approach: Write logic once, adapt to every dialect automatically

SQLazy takes exactly the second approach.

You express your logic as a workflow – a readable, step-by-step sequence of operations. The compiler then translates it into native SQL for the target database.

For example, the logic of “calculating the longest streak of consecutive up days of a stock” can be expressed as the following workflow:

This workflow is database-independent. It describes the logic, not SQL syntax.

Then you tell the compiler the target database – MySQL, PostgreSQL, Oracle, Snowflake, or BigQuery, and it automatically generates native SQL for that database.

With the same workflow, simply switch the database option to generate SQL in a different dialect. You don’t need to rewrite anything.

The workflow stays the same because the logic stays the same. What changes is only the “accent” output by the compiler.

What does this mean?

First, you only need to maintain one version of the logic. When business requirements change, only update the workflow. The compiler regenerates native SQL for every database – no more “updating MySQL but forgetting Oracle”.

Second, it’s easier for new developers to get started. They don’t need to learn database dialect differences – they only need to understand the workflow, while the compiler handles the dialect translation. The workflow is for humans to read; SQL is for databases to execute.

Third, migration costs are nearly zero. Moving from MySQL to PostgreSQL? Just switch the database option, recompile, and the compiler automatically adapts to every SQL dialect. No need to modify code line by line, and no more dialect pitfalls.

Fourth, auditing becomes simpler. You audit the workflow – a readable representation of the logic, instead of hundreds of lines of SQL code versions scattered across different databases.

SQL dialect differences are not a minor inconvenience. They are the hidden tax data teams pay every day. Every additional database means more maintenance costs and a higher risk of errors.

SQLazy’s solution is simple: let compilers, instead of humans, translate dialects.

Your job is to write clear logic, and leave the rest to the compiler. You can also use an LLM to help write the logic. Natural language descriptions can then be standardized into SQLazy statements.

AI writes the logic. A compiler writes the SQL.

SQLazy: Search for Adjacent Records at a Specified Offset Within Groups

Judy — Wed, 26 Aug 2026 08:14:33 +0000

Problem Description

In a table, ProductionLine_Number is the grouping field, and within each group records are sorted by date_Time. The task is to search, within each group, all records whose Cardboard_Number equals a specified string, then take the records within a specified offset before and after each matched record, merge and remove duplicates before outputting.

Source Data

Expected Result

In the ProductionLine_Number=1 group, sorted by time, the ids are 2,4,5,6,7,8, where the row matching spL1ml82N4o is id=4. Taking 2 rows before and after id=4 gives ids 2,4,5,6.

In the ProductionLine_Number=2 group, sorted by time, the ids are 9,10,11,12, where the row matching spL1ml82N4o is id=11. Taking 2 rows before and after id=11 gives ids 9,10,11,12.

Merging and deduplicating both groups gives 2,4,5,6,9,10,11,12. Note that ids 7 and 8 are more than 2 rows away from the matched row id=4, so they are excluded.

SQLazy Step-by-Step Implementation

Core idea: After sorting by grouping field and time within groups, use compute to flag the rows matching the target value within each group, then use the relative-position range syntax flag[-2:2] to take a window of 2 rows before and after the current row, and check whether the window contains a matched row. If so, the current row falls within the result range. Finally filter and deduplicate.

[Click to run this example online]

The steps are explained below.

Step 1: Sort by grouping field and time within groups

sort ProductionLine_Number, date_Time asc

Sort the data by ProductionLine_Number to group records, and by date_Time ascending within each group, ensuring subsequent relative-position calculations follow the correct time order.

Step 2: Flag the rows matching the target value
compute (if (Cardboard_Number = “spL1ml82N4o” then 1)) , as flag; partition ProductionLine_Number
Within each ProductionLine_Number group, mark rows where Cardboard_Number equals the target string spL1ml82N4o as 1, leaving others empty. partition confines the flagging to each group independently.

Step 3: Use range syntax to check whether the window contains a match (core)
compute flag[-2:2] , max , as in_range; partition ProductionLine_Number
This is the core step, using SQLazy’s relative-position range syntax. flag[-2:2] takes the flag values in the window from 2 rows before to 2 rows after the current row, then aggregates with max: as long as the window contains a matched row (flag=1), the current row’s in_range is 1. Thus every record within the offset range of each matched row is covered.

Step 4: Filter records within the range
filter in_range = 1
Keep only rows where in_range is 1, i.e., records falling within the offset range before or after a matched row.

Step 5: Remove duplicate records
distinct id
When the offset ranges of multiple matched rows overlap, the same row may be selected more than once. Use distinct id to deduplicate and output the final result.

Generated SQL

After confirming the above steps, the SQLazy compiler automatically generates native SQL (MySQL syntax):

WITH t2 AS (
    SELECT id, Cardboard_Number, date_Time, ProductionLine_Number
        , CASE
            WHEN Cardboard_Number = 'spL1ml82N4o' THEN 1
            ELSE NULL
        END AS flag
    FROM table1
),
t3 AS (
    SELECT id, Cardboard_Number, date_Time, ProductionLine_Number, flag
        , MAX(flag) OVER (PARTITION BY ProductionLine_Number ORDER BY CASE
            WHEN ProductionLine_Number IS NULL THEN 1
            ELSE 0
        END, ProductionLine_Number ASC, CASE
            WHEN date_Time IS NULL THEN 1
            ELSE 0
        END, date_Time ASC ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS in_range
    FROM t2
),
t4 AS (
    SELECT id, Cardboard_Number, date_Time, ProductionLine_Number, flag
        , in_range
    FROM t3
    WHERE in_range = 1
)
SELECT id, Cardboard_Number, date_Time, ProductionLine_Number, flag
    , in_range
FROM t4
GROUP BY id

SQLazy lets you describe logic in business language instead of writing nested SQL queries. In this example of searching adjacent records at a specified offset within groups, the highlight is the relative-position range syntax: flag[-2:2] expresses a 2-row window before and after with a simple index range, corresponding to the verbose ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING in SQL. Combined with compute's max aggregation, one line determines whether the window contains a match. partition keeps all calculations independent within each group, and step-by-step computation lets every intermediate result be verified.

Official Links
SQLazy Online Experience: sqlazy.com (free, no registration required)
SQLazy Repository: github.com/SPLWare/SQLazy

Trae + SQLazy: A Practical Guide to Writing Complex SQL

Judy — Mon, 24 Aug 2026 08:05:27 +0000

Introduction

AI-assisted data development is reshaping the way SQL is traditionally written. Today’s mainstream approaches can be broadly divided into two categories: one is the “end-to-end” approach, which directly generates native SQL; the other is the “layered” approach, which generates structured intermediate representations and then compiles them into SQL. The former is easy to get started with but difficult to handle complex business scenarios; the latter adds an abstraction layer, which enables engineering-grade guarantees of auditability, debuggability, and reproducibility.

The approach adopted in this article is a combination of Trae (ByteDance’s AI programming tool) as the “brain”, responsible for understanding requirements, clarifying ambiguities, and generating SQLazy step-by-step scripts (.nspl); and SQLazy’s dedicated IDE as the “execution layer”, responsible for syntax validation, step-by-step debugging, and cross-database compilation. Together, they form a closed loop of “AI planning + human review + deterministic engine execution”.

We selected four real-world cases, ranging from simple to complex, covering typical scenarios such as statistical aggregation, multi-table merging, cross-subgroup data filling, and amount allocation. The complete process – from requirement input to validation completion– is demonstrated, with emphasis on documenting the errors encountered and the reasoning behind corrections.

Tool Collaboration Model

2.1 Trae’s Roles and Capabilities

Trae plays three key roles in this workflow:

Automatically Load the Project Knowledge Base

Deploy the global knowledge base specification file (.md) described in Section 3.1 – Environment Setupto the project and register it as a centralized specification:

SQLazy script output format specifications (three tab-separated columns, one function per step)

Hard constraints (reserved word handling, cross-step reference rules, etc.)

Loading paths for function and feature documentation

When the user enters /sqlazy-plan followed by business requirements in the chat box, Trae automatically inherits all the rules above without needing to restate them.

Structured Four-Step Output

Trae is constrained to generate solutions following the four steps below instead of directly providing a final answer:

Capability Overview: List the functions and features involved in the task.
Requirement Decomposition: Break down business requirements into a sequence of data processing steps.
Feature Matching: Match each step with the appropriate SQLazy feature.
Code Generation: Generate the final step-by-step script (.nspl).

This enforced output process ensures the auditability of the solution – the rationale behind each step is clearly visible.

Proactive Requirement Clarification

Facing complex requirements, Trae proactively raises key questions, such as: How should the date range be defined? How should null values be handled? Is the grouping key unique? This prevents the AI from making unsupported assumptions about unstated conditions.

2.2 SQLazy's Core Value

SQLazy provides a dedicated IDE for writing and executing .nspl scripts. Its core value lies in three areas:

Clear Step Semantics, Low Audit Complexity

Taking "longest streak of consecutive up days for a stock" as an example, the .nspl script requires only 5 steps: filter → sort → segment →count → find the maximum. Reading the entire script feels like reading a business operation checklist, rather than parsing complex nested SQL. Each line represents one step; the output of each step becomes the input for the next, with the entire logic laid out explicitly.

Step-by-Step Execution, Quick Problem Identification

In the IDE, you can execute each step individually and inspect intermediate results in real time. Once a step's output doesn't meet expectations, you can immediately pinpoint the specific logic error without having to dig through dozens of lines of nested SQL.

One Script, Compiled Everywhere

After validation passes, one click compiles to native SQL for MySQL, PostgreSQL, Oracle and other mainstream databases. No rewriting per database.

Workflow

3.1 Environment Setup

The project adopts the following standard directory structure:

project_root/
├── plan.md # Global specification: format conventions, loading paths
├── sqlazy-plan.md # Command entry: /sqlazy-plan trigger, inherits all rules from plan.md
├── nspl/ # Delivery directory: .nspl scripts stored here
├── function/ # Function reference documentation (auto-loaded)
└── action/ # Action reference documentation (auto-loaded)

After creating a new project in Trae, copy sqlazy-plan.md, plan.md files, function/ and action/ directories from the SQLazy installation directory's LLM folder to the project root.

3.2 How to Trigger

Use the /sqlazy-plan command in the Trae chat box to trigger the task, followed by a complete business requirement description. It is best to specify the tables, fields, join relationships, grouping dimensions, time range, and output requirements – all in one go.

3.3 Validation and Correction

This is the most critical step in the entire workflow:

Construct a small set of representative test data and manually calculate the expected results.
Run the script step by step in the SQLazy IDE, comparing intermediate results against expected values.
When issues are found, modify the script directly or report them to Trae for regeneration.
After validation passes, compile to native SQL for the target database.
Hands-on Case Studies

The following four cases, from easy to hard, fully document the problem-solving process.

Case 1: Longest Streak of Consecutive Up Days for a Stock

Requirement:

/sqlazy-plan Stock price table stock contains three columns – CODE,DT (date), and CL (closing price). Calculate the maximum number of consecutive days the stock with code 100046 has been rising (i.e., each day’s closing price is higher than the previous day’s).

Analysis and Implementation:

This is the simplest type of statistical requirement. Trae outputs the following script following the four-step process:

Simple statistical requirement, generated correctly by AI in one attempt. Run and validate directly in the SQLazy IDE, then compile to native SQL for the target database.

Case 2: Merging Multiple Tables by ID into Single Rows

Requirement:

/sqlazy-plan There are four data tables, T1, T2, T3, and T4, with similar structures. Each table has two fields: the first field is an ID (named id, id2, id3, and id4 respectively), and the second field is named colA, colB, colC, and colD respectively. The goal is to merge these four tables by their ID values into a single result table with 9 columns: the first column, ID_main, stores the ID value, and the remaining 8 columns contain the fields from the four tables (i.e., all fields from T1, T2, T3, and T4). Each distinct ID appears as exactly one row in the merged table. If an ID is missing from any of the original tables, the corresponding columns from that table are set to NULL.

Analysis and Implementation:

The core challenge of this requirement is that the four tables have different ID field names (id, id2, id3, id4), and a method is needed to join them while ensuring no IDs are lost.

Trae designed a dual-track strategy of "full join + ID coalescing," outputting an 8-step script:

Same as Case 1, correct on first attempt.

Case 3: Cross-Group Sequential Field Value Filling

Requirement:

/sqlazy-plan Given a data table lines, where the first two columns, Group1 and Group2, are grouping columns, the third column, LineID, is a unique row identifier, and the fourth field, TargetField, is a numeric target column. After sorting by Group1, Group2, and LineID, within the same Group1, every Group2 has the same number of records; only the last Group2 (in sort order) has non-NULL values in TargetField, while all other Group2 groups are NULLs. The goal is to copy the TargetField values from the last Group2 group to the other groups within the same Group1, in the same sequential row order (i.e., matching by row positions in sorted order). The final output should be a table containing only the columns Group1, Group2, LineID, and TargetField, sorted in ascending order by the first three columns.

Analysis and Implementation (corrected after one iteration):

The core difficulty of this requirement is that LineID is unique across all rows and cannot be directly used as a cross-group join key. Trae's first version incorrectly used Group1 + LineID as the join key, causing mapping failures. Below is the complete iterative correction process.

First Version (Incorrect)

Trae's first attempt used Group1 + LineID as the join key to fill the TargetField from the last subgroup back to the whole table:

Problem: LineID is unique across all rows (e.g., 101, 105, 201, 205, 301, 305).

Different Group2 groups share no LineID values. Therefore, when using Group1 + LineID as the join key, only the last subgroup’s own rows can match; all other subgroups’ rows fail to match, TargetField remains NULL, and the fill logic completely fails.

Corrected Version: Using Row Number as Mapping Bridge

Instead of using LineID for joining, the “row number” (positional sequence 1, 2, 3, ... obtained by ranking within each Group2 by LineID) serves as the mapping bridge. Since every Group2 within the same Group1 has the same row count, the “Nth row” naturally corresponds across different Group2 groups.

Validation Example:

Assume the data is as follows (all LineID values are unique):

After t2 ranking, a row sequence number row_idx is generated: G2-1(101→1, 105→2), G2-2(201→1, 205→2), G2-3(301→1, 305→2). t3 takes the two records of G2-3. t4 creates a mapping table (A,1→100), (A,2→200). After t5’s backfill, the first row of every subgroup gets 100, and the second row gets 200.

When LineID is unique across the entire table, it cannot be directly used as a cross-group join key. You must first rank within each subgroup to obtain a row sequence number, and use the row number as the mapping bridge. The maximum-rank filter can directly select the last subgroup without requiring the additional aggregation and backfill steps.

Case 4: Invoice Amount Split by Account, with Total Preserved

Requirement:

/sqlazy-plan For the invoice table i (containing fields invoiceid, amount, projectid) and the project table p (containing fields id, projectid, accountcode), join them on projectid. For the joined result, add a new allocation field, splitamount, to implement a splitting logic that distributes the amount by the number of accounts under each project, while ensuring the total sum remains preserved. Within each group, sort by accountcode in ascending order. For the 2nd through the Nth account, calculate splitamount using amount/ total_number_of_accounts and round the result to 2 decimal places. The first account absorbs the rounding remainder; its splitamount equals the invoice’s original amount minus the sum of all other accounts’ splitamount values, so that the allocated amounts exactly match the original invoice amount.

Analysis and Implementation (finalized after two iterations):

This is the most complex of the four cases. Although Trae’s first version correctly identified the partitioned aggregation approach, there were issues with syntax details and step organization. It took two iterations to finalize.

First Iteration: Partitioned Aggregation (partially corrected)

Trae used a mathematically equivalent approach – partitioned aggregation to calculate the remainder – convert “first row’s remainder = total amount – sum of all the other rows’ allocated values” within the partition to amount - sum_temp_split + temp_split, where sum_temp_split is the sum of base allocated values across all rows in the partition.

Two issues were found after running:

Issue 1: Line 4 used * as the count formula, causing the SQLazy parser to report the error “logic error near [*]”. The current version does not support * as a count formula.

Issue 2: User feedback: “Complete the calculation directly in one step based on whether rank=1, without intermediate temporary columns” – they want to merge the base allocation calculation and the final conditional calculation into a single step, reducing the number of intermediate steps.

Second Iteration: Final Version

Two separate corrections were applied:

Changed * to the specific field projectid (counting projectid within a partition is equivalent to counting rows)

Combine the original t5 and t6 steps into one – within a single computed-column statement, create three derived columns separated by semicolons.

Below is the final script:

Validation Example:

projectid=1, amount=100.00, 3 accounts (accountcode=1, 2, 3)

The splitting logic is correct: the first account absorbs the rounding difference of 0.01, and the total is preserved. Three key lessons from this case: ① * is not supported in the current SQLazy environment; specific field names must be used; ② In computed-column statements, aggregate parameters and cross-row parameters are mutually exclusive and cannot be used simultaneously; ③ Reserved words (such as sum) used as names must be enclosed in single quotes.

Conclusion

The core value of the Trae + SQLazy combination does not lie in “letting AI write SQL automatically”, but rather in constraining AI’s uncertainty within an auditable, debuggable intermediate layer, then letting a deterministic engine handle the final execution.

In this workflow, the three parties have clear division of labor:

Trae handles understanding requirements, clarifying ambiguities, and generating the structured initial nspl draft – this is what AI does best: “structuring fuzzy problems”.

SQLazy IDE handles syntax validation, step-by-step debugging, and cross-database compilation — this is the reliable execution by a deterministic engine.

Humans are responsible for verifying business rules, validating test results, and correcting logic deviations – this is irreplaceable business judgment.

Reviewing the four cases: from the simple statistics that passed on first attempt, to the multi-table merge generated correctly in one pass, to the cross-group filling that required correcting the join key, to the invoice split finalized after two iterations – each step confirms the feasibility of the “AI assistance + human review + small-sample validation” approach. In practice, this workflow can be standardized to make AI a true productivity amplifier rather than a source of risk.

SQLazy: Fill Field Values by Sequence Across Sub-Groups

Judy — Wed, 19 Aug 2026 09:00:53 +0000

Problem Description

A table contains Group1 and Group2 as grouping fields, LineID as the sequence number within each group, and TargetField as the target field to fill. After sorting by Group1, Group2, and LineID, within the same Group1, each Group2 has the same number of records; only the last Group2 has TargetField values, while the others are empty. The goal is to copy the values from the last sub-group within each big group to fill the same-row positions of other sub-groups.

Source Data

Expected Result

Group1=2 has 2 sub-groups (Group2=4,5), each with 3 records. The last sub-group Group2=5 has TargetField [5,3,4]; these values are copied to the other sub-group Group2=4.

SQLazy Step-by-Step Implementation

Core idea: First use rank to generate row numbers rn within each (Group1, Group2), then leverage the fact that only the last sub-group has values. Use compute to summarize TargetField by (Group1, rn). Each (Group1, rn) group has only one non-null row, so sum returns that value, naturally copying the last sub-group's values to the same-row positions of other sub-groups.

[Click to run this example online]

The steps are explained below.

Step 1: Sort by grouping fields and sequence

sort Group1, Group2, LineID

Sort the data by Group1, Group2, LineID in ascending order to ensure records within each big group are processed in sub-group and row number order.

Step 2: Generate row numbers within each sub-group

rank as rn; partition Group1, Group2

Use rank within each (Group1, Group2) partition to generate row numbers rn. Since each sub-group has the same number of records, within the same big group, rn=1 rows come from each sub-group's first record, rn=2 from the second, and so on.

Step 3: Aggregate by row number to fill values (core)

compute TargetField sum as tempTarget; partition Group1, rn

This is the core step. Group by (Group1, rn) and use sum to aggregate TargetField. Since only the last sub-group has non-null TargetField values (others are NULL), sum ignores NULL. Each (Group1, rn) group has only one valid value, so sum returns that value, naturally copying the last sub-group's values to the same-row positions of other sub-groups.

Step 4: Select the final result columns

derive Group1, Group2, LineID, tempTarget as TargetField

Generated SQL

After confirming the above steps, the SQLazy compiler automatically generates native SQL (PostgreSQL syntax):

WITH t1 AS (
  SELECT Group1, Group2, LineID, TargetField
  FROM lines
),
sub__6 AS (
  SELECT
    sub__5.*,
    RANK() OVER (PARTITION BY Group1, Group2 ORDER BY Group1, Group2, LineID) AS rn
  FROM t1 sub__5
),
t3 AS (
  SELECT
    Group1, Group2, LineID, TargetField, rn,
    SUM(TargetField) OVER (PARTITION BY Group1, rn) AS tempTarget
  FROM sub__6
)
SELECT Group1, Group2, LineID, tempTarget AS TargetField
FROM t3

SQLazy lets you describe logic in business language instead of writing nested SQL queries.

The solution process is divided into 4 steps, each of which can independently verify intermediate results, reducing the probability of errors in complex logic. SQLazy's rank function generates row numbers directly within partitions, more concise than SQL's RANK() OVER. The compute function combined with sum naturally aggregates unique non-null values from the same row position across sub-groups, replacing explicit cross-row references.

Official Links
SQLazy Online Experience:sqlazy.com(free, no registration required)
SQLazy Repository:github.com/SPLWare/SQLazy

SQLazy: Identify Whether Differences Within Groups Come from Brand or Type Problem Description

Judy — Fri, 07 Aug 2026 06:53:47 +0000

The ID field of table tbl represents car categories, with each category further divided into Brand and Type. The task is to group by ID and determine the source of differences within each group: if a group has multiple brands, Difference is "Brand"; if a group has multiple types, Difference is "Type". The same ID may produce multiple records.

Source Data

Expected Result

ID=1 has both Honda and Jeep as brands, as well as Coupe and SUV as types, so Difference produces both Brand and Type.

ID=2 only has Ford as a brand, but has Sedan and Crossover as types, so only Type is produced.

SQLazy Step-by-Step Implementation

Core idea: First use summarize to group by ID and count distinct brands (cntBrand) and types (cntType), then use expand to cross-join the results with the dimensions ("Brand" and "Type"), and finally use filter to keep only the rows that satisfy the conditions.

[Click to run this example online]

The steps are explained below.

Step 1: Group by ID and count distinct brands and types

summarize Brand icount as cntBrand, Type icount as cntType; group ID

Use summarize to group by ID. The icount function counts distinct Brand and Type values within each group, recorded as cntBrand and cntType respectively. This step compresses each row of detail data into one row per ID, containing distinct counts.

Step 2: Expand the dimension list into rows

expand ["Brand","Type"] as Difference

The expand function unfolds the constant list ["Brand","Type"] into rows, cross-joining with each upstream row to generate the new column Difference. Each ID gets two rows: Difference="Brand" and Difference="Type", while retaining cntBrand and cntType fields.

Step 3: Filter dimensions that meet the conditions

filter (if ((Difference = "Brand") then cntBrand>1; (Difference = "Type") then cntType>1))

Use filter with conditional branching syntax: for rows where Difference="Brand", check cntBrand>1; for rows where Difference="Type", check cntType>1. Only rows with counts greater than 1 are kept. A single filter statement expresses different conditions for different branches, much more intuitive than SQL's nested CASE WHEN.

Step 4: Select the required columns from the result

derive ID, Difference

Generated SQL
After confirming the above steps, the SQLazy compiler automatically generates native SQL (PostgreSQL syntax):

WITH Value1 AS (
  SELECT
    ID,
    COUNT(DISTINCT Brand) AS cntBrand,
    COUNT(DISTINCT Type) AS cntType
  FROM (
    SELECT ID, Brand, Type FROM tbl
  ) tbl
  GROUP BY ID
),
Value2 AS (
  SELECT
    Value1.ID, Value1.cntBrand, Value1.cntType, Difference
  FROM Value1
  CROSS JOIN (
    SELECT 'Brand' AS Difference
    UNION ALL
    SELECT 'Type'
  ) T_1
)
SELECT ID, Difference FROM Value2
WHERE CASE
  WHEN (Difference = 'Brand') THEN cntBrand > 1
  WHEN (Difference = 'Type') THEN cntType > 1
  ELSE NULL
END

SQLazy lets you describe logic in business language instead of writing nested SQL queries. The solution process is divided into 4 steps, each can independently verify intermediate results, reducing the probability of errors in complex logic. The icount function of summarize can directly count distinct values, replacing SQL's COUNT(DISTINCT ...). The expand function expands a constant list into rows, completing in one line what requires CROSS JOIN with UNION ALL in SQL. The filter function's conditional branching syntax (if ... then ...; ... then ...) makes multi-condition filtering logic clear at a glance, clearer than SQL's nested CASE WHEN.

Official Links
SQLazy Online Experience: sqlazy.com (free, no registration required)
SQLazy Repository: github.com/SPLWare/SQLazy

SQLazy：Merge Multiple Tables into Single Rows by Common ID

Judy — Tue, 04 Aug 2026 06:57:22 +0000

Problem Description

Merge multiple structurally similar tables with different column names into a wide table using full outer joins by common ID. Four tables have similar structures, each with two fields. The fields have the same meaning but different names (id, id2, id3, id4 all represent ID). The goal is to merge the four tables into single rows by ID, with each ID appearing in exactly one row. When an ID is absent in a table, the corresponding columns take NULL.

Source Data
T1 table:

T2 table:

T3 table:

T4 table:

*Expected Result
*

For example, ID=555 appears in both T1 and T2 but not in T3 or T4, so id, colA, id2, colB have values, while id3/colC/id4/colD are NULL.

ID=222 only appears in T3, so only id3 and colC have values; all other columns are NULL.

ID=10 appears in T2 and T4 but not in T1 or T3, so id2, colB, id4, colD have values; all other columns are NULL.

SQLazy Step-by-Step Implementation

Core Idea: First use derive to unify the ID column names of each table to ID_main, making subsequent merging easier. Then start from the first table and perform full outer joins one by one: use join to full outer join the current result with the next table on ID_main, then use derive and nvl to merge the new ID into the ID_main column, appending tables one by one to get the final result.

[Click to run this example online]

The steps are explained below.

Steps 1-4: Unify ID Column Names Across Tables

derive id as ID_main, id, colA

Use derive on T1-T4 to rename their respective ID column names (id/id2/id3/id4) uniformly to ID_main.

Step 5: Full Outer Join T1 and T2

join ID_main; with t2; ID_main; take id2, colB; full

Use the join function to full outer join t1 and t2 on ID_main.

Step 6: Merge NULLs in ID Column

derive nvl(ID_main, id2) as ID_main, id, colA, id2, colB

If a record comes from t2 but is not present in t1, its ID_main is NULL. This step assigns t2.id2 to ID_main in such records, ensuring the ID_main column always has a value. Different SQL implementations use different syntax for this step - for example, Oracle uses nvl and SQL Server uses COALESCE - while NSPL uniformly uses the nvl function, which is automatically translated into the corresponding dialect during compilation based on the database type.

Steps 7-8: Append T3

join ID_main; with t3; ID_main; take id3, colC; full

derive nvl(ID_main, id3) as ID_main, id, colA, id2, colB, id3, colC

Repeat the join + derive pattern to full outer join T3 into the current result. After appending each table, use ifn to merge the new ID into the unified ID_main column.

Steps 9-10: Append T4 to Get Final Result

join ID_main; with t4; ID_main; take id4, colD; full

derive nvl(ID_main, id4) as ID_main, id, colA, id2, colB, id3, colC, id4, colD

Generated SQL
After confirming the above steps, the SQLazy compiler automatically generates native SQL (SQL Server syntax):

WITH t1 AS (
        SELECT id AS ID_main, id, colA
        FROM T1
    ),
    t2 AS (
        SELECT id2 AS ID_main, id2, colB
        FROM T2
    ),
    r1 AS (
        SELECT t1.ID_main, t1.id, t1.colA, t2.id2, t2.colB
        FROM t1
            FULL JOIN t2 ON ID_main = t2.ID_main
    ),
    r1b AS (
        SELECT COALESCE(NULLIF(CAST(ID_main AS VARCHAR(10)), ''), NULLIF(CAST(id2 AS VARCHAR(10)), '')) AS ID_main
            , id, colA, id2, colB
        FROM r1
    ),
    t3 AS (
        SELECT id3 AS ID_main, id3, colC
        FROM T3
    ),
    r2 AS (
        SELECT r1b.ID_main, r1b.id, r1b.colA, r1b.id2, r1b.colB
            , t3.id3, t3.colC
        FROM r1b
            FULL JOIN t3 ON ID_main = t3.ID_main
    ),
    r2b AS (
        SELECT COALESCE(NULLIF(CAST(ID_main AS VARCHAR(10)), ''), NULLIF(CAST(id3 AS VARCHAR(10)), '')) AS ID_main
            , id, colA, id2, colB, id3
            , colC
        FROM r2
    ),
    t4 AS (
        SELECT id4 AS ID_main, id4, colD
        FROM T4
    ),
    r3 AS (
        SELECT r2b.ID_main, r2b.id, r2b.colA, r2b.id2, r2b.colB
            , r2b.id3, r2b.colC, t4.id4, t4.colD
        FROM r2b
            FULL JOIN t4 ON ID_main = t4.ID_main
    )
SELECT COALESCE(NULLIF(CAST(ID_main AS VARCHAR(10)), ''), NULLIF(CAST(id4 AS VARCHAR(10)), '')) AS ID_main
    , id, colA, id2, colB, id3
    , colC, id4, colD
FROM r3

SQLazy lets you describe logic in business language instead of writing nested SQL queries. SQLazy's step-by-step computation breaks multi-table merging into independent steps such as unifying column names, performing full outer joins one table at a time, and merging IDs - each step can be independently verified for intermediate results. The join function, combined with derive and nvl, flexibly handles post-merge operations such as renaming columns and merging NULL values. This strategy of appending tables one by one is much clearer and more maintainable than writing deeply nested FULL JOIN + COALESCE queries in SQL.

Official Links
SQLazy Online Experience: sqlazy.com (free, no registration required)
SQLazy Repository: github.com/SPLWare/SQLazy