DEV Community: Judy

LLM Plans, Compiler Generates – A Deterministic SQL Generator with the Workflow as Its Contract

Judy — Tue, 27 Oct 2026 08:43:28 +0000

AI can already produce SQL that runs – but runnable isn’t the same as auditable.

On complex queries, public evaluations show that even the most advanced models often hover around 60% execution accuracy on benchmarks such as BIRD-bench, far short of what it takes to pass review as is. The typical errors are not in syntax but in more complex logic: window boundaries, partition keys, and aggregation definitions, especially in sessionization, time-series gaps, and dynamic pivots. This kind of SQL needs multiple layers of nested CTEs and cumulative window calculations, with hidden join conditions, so a change in one place has to be checked layer by layer, starting from the innermost, to see whether it affects the outer ones.

The consequence is a triple cost: it’s hard to review, hard to debug step by step, and hard to migrate across databases. The code does run, but to know whether the logic is right, you have to replay the whole nesting in your head.

Why having an LLM write SQL directly isn’t deterministic

The root of the problem isn’t AI capability, but the paradigm. Making a probabilistic model responsible for generating the final SQL directly binds planning and generation together in a single sampling pass, so determinism can’t be guaranteed.

Here’s an example. A status log table records, in each row, the status (NewStatus) of an ID at a certain time point. The requirement is to retrieve, for each ID, the most recent Closed row before ConfirmationStarted.

If you ask AI to generate this SQL directly, the typical result is a multi-layer nested query:

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 (
        SELECT CreatedAt, ID, NewStatus, seg
        FROM (
            SELECT CreatedAt, ID, NewStatus, seg
            FROM t2
            WHERE NewStatus = 'Closed'
        ) t_1
        WHERE seg = 1
    ) t_2
    WHERE ID IN (
        SELECT ID
        FROM (
            SELECT ID
            FROM t2
            WHERE NewStatus = 'Closed'
                AND seg = 1
        ) t_3
        GROUP BY ID
    )
) t_4
GROUP BY ID
ORDER BY ID

This SQL does run correctly, but you have to unfold it layer by layer to check whether the logic is right. That’s the cost of having AI produce the final SQL directly: every step is buried inside the nesting, and none of them can be audited on its own.

To restore determinism, planning needs to be separated from generation.

Architecture overview: separating planning from generation

Break down the one-shot Prompt→SQL black box into a Prompt→Workflow→SQL white box, with the Workflow as the contract.

The three components:

Planner: A person or an LLM produces a set of natural-language steps, breaking a plain-language requirement down into a sequence.

Workflow: It uses the DSL contract, has a clear structure, decompose the logic into steps, is human-readable, lets you run and preview each step, and is itself executable documentation. The same workflow always produces the same SQL.

Compiler: A deterministic compiler that translates a workflow into native SQL for the target dialect according to predefined rules. A single workflow can be compiled into native SQL for MySQL, PostgreSQL, Snowflake, or BigQuery.

The contract constrains both ends. The LLM’s output is bound by DSL, so it never produces SQL directly. The compiler takes the workflow as input and does not guess. A single workflow produces exactly the same SQL, whether it is compiled today or tomorrow. The workflow itself becomes auditable logic documentation. Reviewers no longer have to guess what the AI intended; they simply review the steps.

SQLazy is the concrete implementation of this architecture: The LLM plans steps, and the compiler generates SQL deterministic.

Key design elements

Three key design elements underpin SQLazy’s determinism and auditability.

Design 1: DSL-based workflow and step-by-step debugging

The workflow follows the natural order of human reasoning. If an error occurs in step 2, you only need to modify that step. Each step can be executed independently, with its intermediate table available for preview. Boundary errors show up directly in the intermediate results, so there’s no need to work backward from the final output. Take partitioning as an example: run the step, and you immediately see the values in the partition column, so you can tell on the spot whether the data was split as expected.

Design 2: Deterministic compilation

The compiler translates by predefined rules, not by probabilistic generation. Each workflow operation – such as sorting, filtering, partitioning and aggregation – maps one-to-one to corresponding SQL constructs – ORDER BY, WHERE, SUM(CASE WHEN) OVER(PARTITION BY) for partitioning, and GROUP BY. There are no hallucinations, and the results are reproducible. A single workflow produces the same result across runs and across machines, meeting the traceability requirement of compliance-regulated scenarios.

Design 3: Change audit and version control

A workflow is a plain-text contract, so it fits naturally into Git for version control. When the logic changes, you edit only the corresponding step. And the compiler automatically rebuilds the SQL, keeping the logic and SQL in sync. Every change can be diffed, reviewed, and rolled back. The history remains clear and traceable, and regulatory audits only need to go through the Git commit history.

Example validation and scope of applicability

Let’s validate the SQLazy approach using the example above.

Source data mytable:

CreatedAt ID NewStatus

2022-05-25 23:17:44 147 Active

2022-05-28 05:59:02 147 Closed

2022-06-18 05:59:01 147 Closed

2022-06-25 05:59:01 147 Closed

2022-07-13 00:02:47 147 ConfirmationStarted

2022-08-25 05:59:01 147 Closed

2023-04-29 05:59:02 1645 Closed

2023-05-08 14:53:34 1645 ConfirmationStarted

The expected result contains only two rows (147→2022-06-25, 1645→2023-04-29). For all other rows, the Closed date is either too early or falls after ConfirmationStarted.

In SQLazy, the same logic is expressed as an ordered workflow:

Name Anchor Statement

t1 mytable sort ID, CreatedAt asc

segment condition (NewStatus = "ConfirmationStarted") partition ID as seg

filter (NewStatus = "Closed" and seg = 1)

summarize max CreatedAt as CreatedAt; group ID

Run this example onlin: https://www.sqlazy.com/?36K

Step 1: sort ID, CreatedAt asc sorts data by ID and CreatedAt to preserve chronological order. The sort operation maps to SQL’s ORDER BY clause.

Step 2: segment condition (NewStatus = "ConfirmationStarted") partition ID as seg splits the data into segments at each occurrence of ConfirmationStarted. partition ID ensures that each ID is segmented independently. After the operation runs, a new column, seg, appears. Rows where seg=1 identify the target interval, eliminating the need to hand-write SUM(CASE WHEN ...) OVER. To verify the segmentation, simply click t2 to inspect the seg column.

Step 3: filter (NewStatus = "Closed" and seg = 1) only retains the Closed row within the target interval. filter maps to SQL’s WHERE clause.

Step 4: summarize max CreatedAt as CreatedAt; group ID groups the data by ID and gets the latest CreatedAt timestamp for each group. The summarize operation maps to SQL’s GROUP BY and aggregate operation.

Once the workflow is finished, click “Compile” to produce SQL according to predefined rules. Compared with the original SQL, this SQL has a clearer step-by-step structure:

Step 3: filter (NewStatus = "Closed" and seg = 1) only retains the Closed row within the target interval. filter maps to SQL’s WHERE clause.

Once the workflow is finished, click “Compile” to produce SQL according to predefined rules. Compared with the original SQL, this SQL has a clearer step-by-step structure:

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

The same workflow produces exactly the same SQL every time it is compiled. Switching SQL dialects requires no changes to the workflow.

The scope of applicability should also be stated honestly. For simple CRUD operations, writing SQL directly is more appropriate; introducing a workflow for just a few lines of logic is hardly worth the overhead. Features such as macros and loops are still on the roadmap. SQLazy is built for helping developers design and validate complex analytical logic, which can then be embedded into workflows such as dbt. It is intended to complement these tools, not replace them.

Open-source vs Commercial components: SQLazy’s syntax and examples are open source, while its compiler and IDE are proprietary, with a free version available. The IDE supports local and private deployment, meeting enterprise requirements for data export and working offline.

AI writes the logic. A compiler writes the SQL.

Online trial: sqlazy.com (Free to use, no sign-up required. Playground runs locally in your browser).

Open-source examples: github.com/SPLWare/SQLazy/examples

Convert Time Columns into Rows and Pivot into a New Cross Table

Judy — Thu, 22 Oct 2026 07:22:57 +0000

Problem Description

The table tb can be seen as a cross table: the combination of the ID and PartNum fields forms the row header, the three field names EnteredOn, PickedTime and DeliveredTime form the column header, and the cells hold time points. Neither the content nor the number of the row headers is fixed. Now the table has to be turned around: the original cells (the time points) become the row header of the new table, the original row header (the combination of PartNum and ID) becomes the column header of the new table, and the original three column headers are replaced by the strings ENTERED, PICKED and DELIVERED, which are filled into the cells.

Source Data

Expected Result
The column header is the combination of PartNum and ID, the row header is the time point, and the cells hold the action names:

The first row 2024-03-28 08:59:13.727 falls in the column 50A_100 with the value ENTERED, which means it is the entered time of the record with ID=100 and PartNum=50A. Further down the same column the rows PICKED and DELIVERED appear, which are the picked time and the delivered time of that record. In other words, the three time points that used to lie side by side in one row are now stacked vertically in one column.

The records with ID=171 and ID=211 have only an entered time, while the picked and delivered times are empty, so the columns 50A_171 and 70B_211 each contain a single ENTERED and no PICKED or DELIVERED. Empty values have to be filtered out first, otherwise the new table would contain two meaningless time point rows.

The number of columns of the new table equals the number of "row header combinations" in the source data (4 in this example), and the number of rows equals the total number of non-empty values in the three time columns (6 plus 2, that is 8, in this example). Neither direction is fixed, which is why this problem cannot be solved by hard-coding the column names.

SQLazy Step-by-Step Implementation
The essence of this problem is pivoting twice. The first pivot turns the three time columns into rows, so that every time point occupies a row of its own and carries the action it belongs to; the second pivot turns the rows back into columns, using the combination of PartNum and ID as the new column names. Both pivots use the pivot action: the first one uses its inverse mode (column-to-row) and the second one uses the default mode (row-to-column). Two small things happen in between: filtering out the empty time points, and replacing the action field names with the short names the problem asks for.

[Click to run this example online]

The steps are explained below.

Step 1: Turn the three time columns into rows

pivot group ID, PartNum; name action_type; value t_stamp; inverse

This is the first key action of the problem. The pivot action turns rows into columns by default, and adding the inverse parameter switches it to column-to-row: the two grouping columns ID and PartNum stay unchanged, the other three time columns are expanded into rows, the original column names EnteredOn, PickedTime and DeliveredTime become the values of the new column action_type, and the original column values (the time points) become the values of the new column t_stamp.

After this step one wide record becomes three long records: the record with ID=100 turns into three rows carrying the three action names EnteredOn, PickedTime and DeliveredTime together with their time points. How many row header combinations and how many column headers there are does not affect this action, so an unfixed number of row headers is not a problem.

Step 2: Filter out the empty time points
filter (t_stamp notnull)
In the source data the records with ID=171 and ID=211 have only an entered time, while the picked and delivered times are empty. Column-to-row also expands the empty values into rows, and those rows are meaningless, so they are filtered out first. The test for an empty value uses the not-null function, and the whole condition is written inside the parentheses of the unfixed number.

Step 3: Replace the action names with the short names the problem asks for
compute if(action_type = “EnteredOn” then “ENTERED”, action_type = “PickedTime” then “PICKED”, action_type = “DeliveredTime” then “DELIVERED”) as status
The problem requires the cells to hold ENTERED, PICKED and DELIVERED, while column-to-row produces the original column names, so a computed column with the condition function performs the mapping: when the action name equals EnteredOn it becomes ENTERED, when it equals PickedTime it becomes PICKED, and when it equals DeliveredTime it becomes DELIVERED. The result goes into the new column status. The condition function can test several conditions in turn and return the matching value, which suits this kind of mapping with several equality branches.

Step 4: Build the column names of the new table
compute concat(PartNum, “_”, ID) as col_name
The column header of the new table is the combination of PartNum and ID joined by an underscore, such as 50A_100. The concat function joins the two fields and the underscore in between into one string, which is written into the new column col_name. The column names are not known in advance but computed from the data, which is the other reason this problem cannot hard-code them.

Step 5: Keep only the three columns that are needed
derive t_stamp, col_name, status
The previous steps leave intermediate columns such as ID, PartNum and action_type. The second pivot needs only the time point, the column name and the action name, so the derive action picks those three out.

Step 6: Turn the rows into columns
pivot group t_stamp; name col_name; value status
The second pivot does not write a calculation method and uses the default row-to-column mode: group by t_stamp, let every value of col_name become the name of a new column, and fill the values of status into the matching cells. One row and one column occur only once in the data (a time point belongs to the action of a single record), so no aggregation algorithm has to be specified and the default of taking the first record in the group is enough.
No inverse parameter is written here, which is the opposite of step 1: step 1 turns columns into rows and this step turns rows into columns, and the two pivots together turn the whole table around.

Generated SQL
After confirming the logic of the above 6 steps, the SQLazy compiler automatically generates native SQL (MySQL syntax used here):

WITH t1 AS (
    SELECT ID, PartNum, 'EnteredOn' AS action_type, EnteredOn AS t_stamp
    FROM tb
    UNION ALL
    SELECT ID, PartNum, 'PickedTime' AS action_type, PickedTime AS t_stamp
    FROM tb
    UNION ALL
    SELECT ID, PartNum, 'DeliveredTime' AS action_type, DeliveredTime AS t_stamp
    FROM tb
  ),
  t2 AS (
    SELECT ID, PartNum, EnteredOn, PickedTime, DeliveredTime
    FROM t1
    WHERE t_stamp IS NOT NULL
  ),
  t4 AS (
    SELECT ID, PartNum, action_type, t_stamp
      , CASE
        WHEN action_type = 'EnteredOn' THEN 'ENTERED'
        WHEN action_type = 'PickedTime' THEN 'PICKED'
        WHEN action_type = 'DeliveredTime' THEN 'DELIVERED'
        ELSE NULL
      END AS status
      , concat_ws('', PartNum, '_', ID) AS col_name
    FROM t2
  ),
  t5 AS (
    SELECT t_stamp, col_name, status
      , FIRST_VALUE(status) OVER (PARTITION BY t_stamp, col_name ORDER BY NULL) AS FIRST_2
    FROM t4
  )
SELECT t_stamp
  , MIN(CASE
    WHEN col_name = '50A_100' THEN FIRST_2
  END) AS `50A_100`
  , MIN(CASE
    WHEN col_name = '50A_171' THEN FIRST_2
  END) AS `50A_171`
  , MIN(CASE
    WHEN col_name = '60B_125' THEN FIRST_2
  END) AS `60B_125`
  , MIN(CASE
    WHEN col_name = '70B_211' THEN FIRST_2
  END) AS `70B_211`
FROM t5
GROUP BY t_stamp

SQLazy lets you describe logic in business language instead of writing nested SQL queries. Each of the two pivots in this problem is expressed by a single pivot statement: the first uses the inverse mode to expand the three time columns into rows and the second uses the default mode to turn the rows back into columns, so the two steps "columns to rows, then rows to columns" are obvious at a glance and there is no need to hand-write UNION ALL and hard-coded CASE branches in SQL.

The two modes of the pivot action are the key to this problem. One and the same action switches direction depending on whether the inverse parameter is present, and both directions share the same wording of grouping column, name column and value column, so the business statement "which field becomes the new column name and which field becomes the new value" can be copied straight into the parameters without first working out whether SQL needs CASE or UNION.

When the new column names come from the data itself (PartNum joined with ID), SQL can only hard-code them in the statement, and the statement has to be edited whenever the data changes. SQLazy treats building the column name as an ordinary computed column, so the names are generated from the data and neither a change in the number of row headers nor in the number of column headers requires a code change.

Step-by-step calculation lets every step be inspected on its own: after step 1 you can see the expanded long table, after step 2 you can see that the empty values are gone, and after step 4 you can see the finished column names, so when something goes wrong there is no need to run the whole statement and then guess which part is at fault.

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

SQLazy Repository: github.com/SPLWare/SQLazy

SQLazy: Find Strings with 5 or More Consecutive Alphabetical Letters

Judy — Tue, 20 Oct 2026 04:03:36 +0000

Problem Description

Table table_name has a single string field VALUE (max length 50). Find strings that contain at least 5 consecutive letters in ascending alphabetical order. That is: for each string, extract all letters, check them in original order, find the longest consecutive ascending subsequence (each letter >= previous one), and keep rows where the length >= 5. Non-letter characters must be excluded.

Source Data

Expected Result

Two qualifying strings as examples:

ABCDTest: the letters are A, B, C, D, T, e, s, t. The first four A-B-C-D are consecutively ascending. Then T (ASCII 84) is still greater than D, so the ascending segment extends to A-B-C-D-T (5 letters). But the next letter e (ASCII 101) is greater than T (84), so the ascent continues. The entire letter sequence A-B-C-D-T-e-s-t is ascending, with a max run length of 8. In fact >= 5 is sufficient to qualify.

Testuvwxyz: the letters are T, e, s, t, u, v, w, x, y, z. Among them, u-v-w-x-y-z has 6 consecutive ascending letters, length >= 5, qualifying.

SQLazy Step-by-step Implementation

Core idea: first expand by string length into one row per character, filter out non-letter characters, then use segment's not_up condition to detect where the ascending order breaks (start a new group when the current character is less than the previous one), then aggregate by group size, and finally filter strings whose max run >= 5. This pattern of character expansion, segmentation, and aggregation filtering is a typical SQLazy paradigm for string sequence problems.

【Online operation case】

The steps are explained below.

Step 1: Expand rows by max string length

expand 50 as Position

Expand each string into 50 rows, with Position ranging 1~50. This is a preprocessing step for strings, enabling subsequent per-character computation. Since the maximum string length is 50, expand 50 covers all character positions.

Step 2: Extract character at each position

compute mid(VALUE, Position, 1) as Ch

Use the mid function to extract 1 character at Position from VALUE, named Ch. For positions beyond the actual string length, mid returns empty.

Step 3: Filter out non-letter characters

filter (Chisalpha)

Use the isalpha function to keep only letters (A-Z, a-z), removing empty characters, digits, and symbols.

Step 4: Detect ascending breaks (core)

segment Ch; not_up; partition VALUE; as GrpNo

This is the most critical step. The segment statement partitions by VALUE and checks the Ch sequence within each partition: not_up specifies the non-ascending condition - when the current character (ASCII value) is less than or equal to the previous one, a segment break is triggered. Each time the ascending order breaks, a new group starts, generating group number GrpNo. Consecutively ascending letters are assigned to the same group.

Step 5: Count characters per group

summarize Ch count as GrpSize; group VALUE, GrpNo

Group by VALUE and GrpNo, count the number of letters in each group as GrpSize.

Step 6: Get max group length per string

summarize GrpSize max as MaxRun; group VALUE

Group by VALUE, take the maximum GrpSize to get the longest consecutive ascending segment length MaxRun for each string.

Step 7: Filter strings with length >= 5

filter (MaxRun >= 5)

Keep rows where MaxRun >= 5.

Step 8: Keep final result columns

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

WITH TEMP_TABLE__2 AS (
    SELECT 1 AS col_1 FROM DUAL
    UNION ALL SELECT col_1 + 1 FROM TEMP_TABLE__2 WHERE col_1 < 50
), t1 AS (
    SELECT table_name.VALUE, col_1
    FROM table_name CROSS JOIN TEMP_TABLE__2
), t2 AS (
    SELECT VALUE, Position, SUBSTR(VALUE, Position, 1) AS Ch FROM t1
), t3 AS (
    SELECT VALUE, Position, Ch FROM t2 WHERE REGEXP_LIKE(Ch, '^[A-Za-z]+$')
), t5 AS (
    SELECT VALUE, GrpNo, COUNT(Ch) AS GrpSize
    FROM (
        SELECT VALUE, Position, Ch,
            SUM(CASE WHEN Ch <= col__5 THEN 1 ELSE 0 END)
                OVER (PARTITION BY VALUE ORDER BY VALUE
                ROWS UNBOUNDED PRECEDING) + 1 AS GrpNo
        FROM (SELECT t3.*, LAG(Ch,1)
            OVER (PARTITION BY VALUE ORDER BY VALUE) AS col__5 FROM t3) sub
    ) t4 GROUP BY VALUE, GrpNo
), t6 AS (
    SELECT VALUE, MAX(GrpSize) AS MaxRun
    FROM t5 GROUP BY VALUE
)
SELECT VALUE FROM t6 WHERE MaxRun >= 5

SQLazy lets you describe logic in business language instead of writing nested queries in SQL syntax. In this example of finding consecutive alphabetical letters, SQLazy uses 8 steps to clearly describe the entire flow from character expansion to segment aggregation. The segment not_up condition directly expresses the semantics of starting a new group when alphabetical order breaks, simplifying what would otherwise require recursive CTE + LAG + SUM window functions into a single readable declaration. The expand row expansion combined with per-character computation makes string processing as intuitive as table row operations; the step-by-step computation model ensures every step from character extraction to grouping and filtering is traceable and debuggable.

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

Flag Office Visits Within 6 Months After Surgery

Judy — Thu, 15 Oct 2026 08:11:17 +0000

Problem Description

The table tb_healthy records the medical history of patients; each patient has several records, where Visit_code = Surgery means an operation and Visit_code = Office means a consultation. Some patients have only operations, some have only consultations. Two questions have to be answered: whether the patient ever had a consultation, and whether the patient had a consultation within 6 months after an operation. The first goes into Office_Visit and the second into SX_past_6mo, with 1 for yes and 0 for no.

Source Data
tb_healthy table:

Expected Result
Office_Visit means the patient has consultation records, SX_past_6mo means the patient had a consultation within 6 months after an operation:

A30 had an operation on 2004-05-15 and a consultation on 2005-02-05, more than 8 months apart, beyond the 6-month window, so Office_Visit is 1 while SX_past_6mo is 0; this shows that "having a consultation" and "having a consultation within 6 months after an operation" are two different things.

C01 has two operation records and two consultation records, and only the operation of 2001-05-15 pairs with the consultation of 2001-11-01 inside the 6-month window, so SX_past_6mo is 1; C02 has only operations and C03 has only consultations, so both flags are 0 for them.

SQLazy Step-by-Step Implementation

This problem has to answer two questions at once, so it suits calculating the two things separately and merging them at the end. First convert the string dates into real dates, then filter out one copy of the operation records and one copy of the consultation records. "Has the patient ever had a consultation" comes from a summarize that groups by patient and takes the maximum. "Has the patient had a consultation within 6 months after an operation" needs the join action to attach consultation records to operation records, and the attaching condition is "the consultation date is not earlier than the operation date and not later than the operation date plus 6 months", which is a cross-table interval condition and exactly what the non-equi condition parameter of the join action is for. Summarizing the paired records gives the flag, and patients that never paired up are filled with 0 when the results are merged at the end.

[Click to run this example online]

The steps are explained below.

Step 1: Convert the string dates into date values

compute date(Visit_Date; format "M/d/yyyy") as dt

Visit_Date is a string such as "5/15/2004" and cannot be compared directly. Use the date function with the format "M/d/yyyy" to parse it into a date value and write it into the new column dt. This step is the prerequisite for every date comparison that follows.

Step 2: Filter one copy of the operation records and one of the consultation records
filter Visit_code=“Surgery”
filter Visit_code=“Office”
The two copies are used by different calculations later: the operation records serve as the base table of the join and the consultation records as the table being attached. The comparison uses the symbol “=”, not “==” as in SQL.

Step 3: Answer “has the patient ever had a consultation” first
summarize max if(Visit_code=“Office” then 1 else 0) as Office_Visit; group Id
The summarize action groups by Id and takes the maximum of the condition if(Visit_code=“Office” then 1 else 0) inside each group: a group with at least one consultation record yields 1, a group without any yields 0. The aggregation max is written before the aggregated item, and the result is named Office_Visit.

Step 4: Attach the consultations within 6 months to the operations
join Id; with t_off; Id; take dt as off_dt; inner; condition ((dt>=dt@) and (dt<=(dt@ elapse 6 month)))
This is the core step of the problem. The join action takes the operation table t_sx as the base table, joins the consultation table t_off on Id, and attaches the consultation date dt under the new name off_dt.
The condition is a cross-table interval test written in the condition parameter: dt@ with the @ suffix is the date of the base table (the operation), and dt without @ is the date of the joined table (the consultation), so the whole condition reads “the consultation date is not earlier than the operation date and not later than the operation date plus 6 months”. Moving 6 months forward uses the elapse function, written as (dt@ elapse 6 month).
Note: to reference a joined-table field in the condition, use its original name dt in that table, not the new name off_dt; the inner join drops the operation records that do not fit the window, so what remains are the paired records.

Step 5: Summarize the paired records into a flag
summarize max 1 as SX_past_6mo; group Id
Group by Id and take the maximum of the constant 1 inside each group. A patient that paired up at least once has at least one row and yields 1; a patient that never paired up does not appear in this table at all, which is exactly why 0 has to be filled in later. The aggregated item here is the constant 1 rather than a column, because the only information needed is whether a pair exists, not any particular value.

Step 6: Merge the two results
join Id; with t_sx_yes; Id; take SX_past_6mo
Using the result of step 3 as the base table, attach the flag from step 5 with a left join. The left join keeps the patients that never paired up in the result, with SX_past_6mo left empty. No join type is written here because the default is a left join.

Step 7: Replace the empty values with 0
compute nvl(SX_past_6mo,0) as SX_past
Use nvl to replace the empty values with 0 and name the column SX_past, so every flag in the output is either 0 or 1. The last step uses the derive action to pick the three columns that are needed: the patient Id, whether there was a consultation Office_Visit, and whether there was a consultation within 6 months after an operation SX_past.

The last step picks the columns to output.

Generated SQL
After confirming the logic of the above 7 steps, the SQLazy compiler automatically generates native SQL (Oracle syntax used here):

WITH t1 AS (
    SELECT Id, Visit_Date, Visit_code
      , TO_DATE(Visit_Date, 'M/d/yyyy') AS dt
    FROM tb_healthy
  ),
  t_results AS (
    SELECT Id
      , MAX(CASE
        WHEN Visit_code = 'Office' THEN 1
        ELSE 0
      END) AS Office_Visit
    FROM t1
    GROUP BY Id
  ),
  t_off AS (
    SELECT Id, Visit_Date, Visit_code, dt
    FROM t1
    WHERE Visit_code = 'Office'
  ),
  t_sx_yes AS (
    SELECT Id, MAX(1) AS SX_past_6mo
    FROM (
      SELECT t_sx.Id, t_sx.Visit_Date, t_sx.Visit_code, t_sx.dt, t_off.dt AS off_dt
      FROM t1
        INNER JOIN t_off
        ON t_sx.Id = t_off.Id
          AND ((t_off.dt >= t_sx.dt)
            AND (t_off.dt <= ADDMONTHS(t_sx.dt, 6)))
      WHERE t_sx.Visit_code = 'Surgery'
    ) t_2
    GROUP BY Id
  ),
  t_merged AS (
    SELECT t_results.Id, t_results.Office_Visit, t_sx_yes.SX_past_6mo
    FROM t_results
      LEFT JOIN t_sx_yes ON t_results.Id = t_sx_yes.Id
  ),
  t_final AS (
    SELECT Id, Office_Visit, SX_past_6mo
      , COALESCE(SX_past_6mo, 0) AS SX_past
    FROM t_merged
  )
SELECT Id, Office_Visit, SX_past
FROM t_final
ORDER BY Id

SQLazy lets you describe logic in business language instead of writing nested SQL queries. This problem has to answer both "has the patient ever had a consultation" and "was there a consultation within 6 months after an operation", and SQLazy splits them into two independent groups of steps that are worked out separately and merged at the end. The non-equi condition parameter of the join action is the key: "the consultation date falls within 6 months after the operation date" is a cross-table interval test, and SQLazy tells the two tables apart with the @ suffix so the interval condition can be written straight into the join statement instead of building a Cartesian product and filtering afterwards. The summarize action keeps "is there any" questions short, since a group maximum yields 0 or 1 without filtering records first and then testing for existence. Every step is an intermediate result table you can inspect on its own, so when something goes wrong it is immediately clear which step is at fault.

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

SQLazy Repository: github.com/SPLWare/SQLazy

Start and End Dates of the Longest Rising Period

Judy — Thu, 08 Oct 2026 07:21:04 +0000

Problem Description

The stock table records the daily closing prices of many stocks, with the fields CODE (stock code), DT (trading date) and CL (closing price). For a given target stock, find the period during which it rose for the largest number of consecutive days, and give the start date and end date of that period.

Source Data

stock table (only the records of the target stock CODE = 100046 are listed, in ascending DT order):

Expected Result
NoRisingDays is the interval number:
On 01-02 the closing price rose from 3.89 to 3.93, a continuous rise; on 01-05 it fell from 3.93 to 3.78, which breaks the rise. The interval number starts at 1 and increments at each break, so a new interval begins there.

Interval 2 covers 2009-01-07 to 2009-01-12, 4 trading days in total. Its starting point 01-07 closed at 3.77, below the 4.04 of 01-06, and then 4.02, 4.42 and 4.86 climbed steadily, without a single down day in between.

Interval 3 covers 2009-01-13 to 2009-01-16, also 4 trading days. Its starting point 01-13 closed at 4.44, below the 4.86 of 01-12, and then 4.88, 4.98 and 4.99 rose consecutively, tying with interval 2 for the longest run.

SQLazy Step-by-Step Implementation
Core idea: A continuous rise is simply a run of days that does not fall. Sort the data by date first, then use the segment action to watch the closing price: as soon as the closing price drops, the previous rise has ended, the group number increments and a new interval starts. Every record then carries the number of the rising interval it belongs to. Next, group by interval number and count the rows; that count is the number of consecutive rising days in the interval. Finally pick the interval with the largest count and take its first date and last date, which is the answer.

[Click to run this example online]

The steps are explained below.

Step 1: Take the target stock

filter CODE = 100046

Filter the stock table for the records whose CODE equals 100046; the rest of the calculation works on this stock only. The comparison uses the symbol "=", not "==" as in SQL.

Step 2: Sort by date in ascending order

sort DT asc

Sort the filtered records by trading date DT in ascending order. Segmentation walks through the records in order; if the order is wrong, the intervals it produces are meaningless.

Step 3: Start a new interval when the closing price drops
segment CL down as NoRisingDays
This is the core step of the problem. The segment action uses the fixed condition “down” on the closing price CL: when a day closes lower than the previous day, the group number increments; when it does not (a rise or a flat day), the group number stays the same. The generated group numbers are written into the new column NoRisingDays. Records in the same interval share the same group number, and a batch of records with the same group number is exactly one continuous rising run.
There is no need to write a cross-row comparison such as CL < CL[-1], and no need to worry about the first record having no previous day. The “down” condition itself expresses the business meaning of “a drop starts a new interval”.

Step 4: Count how many days each interval has
compute count DT as ContinuousDays; partition NoRisingDays
The compute action counts the records within each NoRisingDays partition: the aggregation count comes before the aggregated item DT, and the result goes into the new column ContinuousDays. Every row in a partition is filled with the total number of days of that interval, so after this step each record carries “how many days my interval rose”. Because partition has already grouped the rows by interval number, taking the maximum of ContinuousDays afterwards is equivalent to “find the longest rising interval”.

Step 5: Pick out the longest interval
rank ContinuousDays max
The rank action takes the records whose ContinuousDays is the maximum. There may be more than one such record, in which case all tied records are returned, so every interval that ties for the longest is kept and none is lost to a tie. This step only filters records and does not change their order.

Step 6: Take the start and end dates of each interval
summarize min DT as start_date, max DT as end_date; group NoRisingDays
The summarize action groups by NoRisingDays, names the minimum DT of each group start_date and the maximum DT end_date. Since the previous step has already limited the records to the longest interval (or the intervals tied for longest), what is computed here is exactly the start date and end date of the longest rising period. The aggregation algorithm of summarize comes before the aggregated item, written as min DT and max DT.

Generated SQL

After confirming the logic of the above 6 steps, SQLazy compiler automatically generates native SQL (MySQL syntax used here):

WITH t2 AS (
  SELECT CODE, DT, CL
  FROM stock
  WHERE CODE = 100046
),
t3 AS (
  SELECT CODE, DT, CL
    , SUM(CASE
      WHEN CL < col__1 THEN 1
      ELSE 0
    END) OVER (ORDER BY CASE
      WHEN DT IS NULL THEN 1
      ELSE 0
    END, DT ASC ROWS UNBOUNDED PRECEDING) + 1 AS NoRisingDays
  FROM (
    SELECT t2.*, LAG(CL, 1) OVER (ORDER BY CASE
        WHEN DT IS NULL THEN 1
        ELSE 0
      END, DT ASC) AS col__1
    FROM t2
  ) sub__2
),
t4 AS (
  SELECT CODE, DT, CL, NoRisingDays
    , COUNT(DT) OVER (PARTITION BY NoRisingDays ) AS ContinuousDays
  FROM t3
),
sub__6 AS (
  SELECT sub__5.*, RANK() OVER (ORDER BY ContinuousDays DESC) AS col_4
  FROM t4 sub__5
)
SELECT CODE, DT, CL, NoRisingDays, ContinuousDays
FROM sub__6
WHERE col_4 = 1

SQLazy lets you describe logic in business language instead of writing nested SQL queries. For this "longest rising period" problem the core is a single sentence: start a new interval when the closing price drops. SQLazy expresses "a drop starts a new interval" directly with segment CL down, whereas hand-written SQL has to build a LAG comparison for the up/down flag first, then accumulate the interval number with SUM, nesting two window functions and handling the boundaries and NULL values by itself.

SQLazy is valuable because it works step by step. The whole calculation is split into six steps: fetch, sort, segment, count, take the longest and summarize. Each step is an intermediate result table you can inspect directly: after step 3 you can see the interval numbers, after step 4 you can see how many days each interval has, and which interval is the longest is obvious. There is no need to run the whole SQL query and then guess whether something went wrong in the middle.

SQLazy also writes the natural business order of "count the days of each interval first, then pick the largest, then take the dates" directly as the code order: compute counts within a partition, rank takes the records with the maximum value, and summarize takes the minimum and maximum dates within a group. In the aggregation actions the aggregation algorithm is written before the aggregated item (count DT, min DT, max DT), so reading the code matches the way the business describes it.

Partitioning and segmentation make problems of the "measure each interval, then pick one" kind solvable without a self-join: partition puts the records of one interval into one group, and counting within a group and taking its first and last dates are ready-made actions. SQLazy compiles this logic into SQL that the target database can run, so the same code needs no rewrite when you switch databases.

Official Links

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

SQLazy Repository: github.com/SPLWare/SQLazy

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.