To Write SQL, the Most Rigorous Thing You’ll Do This Quarter: Be SQLazy
When people first hear the name SQLazy, many wonder: “Can you really be lazy about SQL? Is this tool actually reliable?” We knew people would ask these questions when we came up with the name.
The name is all about being lazy, yet what we talk about everyday are auditability, zero hallucination, and compliance, which sounds contradictory. In fact, laziness and rigor are two sides of the same coin.
Write lazier, get more rigor
What we mean by “lazy” is this: writing gets lazier, and the results get more rigorous.
Lazy in writing: Describe complex logic step by step in natural language, with no hand-written nesting and no need to memorize each database’s window function dialects. Rigorous in results: AI only translates plain language into steps, and the compiler generates the final SQL deterministically. The same logic always compiles to the same SQL, with no invented field names and zero hallucinations.
Rigor doesn’t mean spending more effort hand-writing SQL. It means spending that effort on validation. Every step can be executed and previewed. Reviewers inspect the steps, not hundreds of lines of nested SQL. In one sentence: Lazy to write, rigorous in results.
!rigorous in results
Lazy doesn’t mean slacking off. It means quality control.
SQLazy is like a sloth. Its role here isn’t to be a specimen, it’s to be a quality inspector.
It allows you inspect and validate each step carefully before signing off on the final result. That sign-off means: I’ve reviewed every step of this SQL, and I’m confident enough to take responsibility for it.
A piece of legacy query that no one dares touch – over a hundred lines of nesting, and one glance at it gives you a headache. By turning it to a multi-step workflow, you can perform one business action at each step and click on any step to inspect and validate the intermediate result. A true lazy person isn’t diligent about repetitive labor – they are serious about validation.
!validation
See you in the comments
AI writes the logic. A compiler writes the SQL.
SQLazy: Complex SQL, made lazy. Trusted by design.
Which part of SQL do you most wish you could slack off on but don’t dare to? Rewriting dialects for each database? Session splitting? Or the legacy mess of quarterly reports? Chat with us in the comments. Here’s the sample database:
github.com/SPLWare/SQLazy/tree/master/examples
5 reactions
LLM Plans, Compiler Generates – A Deterministic SQL Generator with the Workflow as Its Contract
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.
!Architecture overview
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
t2
segment condition (NewStatus = "ConfirmationStarted") partition ID as seg
t3
filter (NewStatus = "Closed" and seg = 1)
t4
summarize max CreatedAt as CreatedAt; group ID
Run this example onlin: sqlazy.com
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:
!Image descriptionArchitecture overview
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:
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.
!Image description
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/tree/master/examples
5 reactions
Convert Time Columns into Rows and Pivot into a New Cross Table
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
!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:
!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.
!he 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.
!unfixed number
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.
!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.
!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.
!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.
!action name
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.
!two pivotsn
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: SPLWare/SQLazy
5 reactions
SQLazy: Find Strings with 5 or More Consecutive Alphabetical Letters
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
!Source Data
Expected Result
!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.
!SQLazy Step-by-step Implementation
【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.
!expand 50 as Position
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.
!compute mid
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.
!filter
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.
!Count characters
!summarize
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.
!summarize GrpSize
Step 7: Filter strings with length >= 5
filter (MaxRun >= 5)
Keep rows where MaxRun >= 5.
!filter
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: SPLWare/SQLazy
5 reactions
Flag Office Visits Within 6 Months After Surgery
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:
!Source Data
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:
!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.
!steps
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.
!Visit_Date
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.
!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.
!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.
!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.
!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.
!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.
!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]( SPLWare/SQLazy)
5 reactions
Mentions
To Write SQL, the Most Rigorous Thing You’ll Do This Quarter: Be SQLazy When people first hear the name SQLazy, many wonder: “Can you really be lazy about SQL? Is this tool actually reliable?” We knew people would ask these questions when we came up with the name. The name is all about being lazy, yet what we talk about everyday are auditability, zero hallucination, and compliance, which sounds contradictory. In fact, laziness and rigor are two sides of the same coin. Write lazier, get more rigor What we mean by “lazy” is this: writing gets lazier, and the results get more rigorous. Lazy in writing: Describe complex logic step by step in natural language, with no hand-written nesting and no need to memorize each database’s window function dialects. Rigorous in results: AI only translates plain language into steps, and the compiler generates the final SQL deterministically. The same logic always compiles to the same SQL, with no invented field names and zero hallucinations. Rigor doesn’t mean spending more effort hand-writing SQL. It means spending that effort on validation. Every step can be executed and previewed. Reviewers inspect the steps, not hundreds of lines of nested SQL. In one sentence: Lazy to write, rigorous in results. !rigorous in results Lazy doesn’t mean slacking off. It means quality control. SQLazy is like a sloth. Its role here isn’t to be a specimen, it’s to be a quality inspector. It allows you inspect and validate each step carefully before signing off on the final result. That sign-off means: I’ve reviewed every step of this SQL, and I’m confident enough to take responsibility for it. A piece of legacy query that no one dares touch – over a hundred lines of nesting, and one glance at it gives you a headache. By turning it to a multi-step workflow, you can perform one business action at each step and click on any step to inspect and validate the intermediate result. A true lazy person isn’t diligent about repetitive labor – they are serious about validation. !validation See you in the comments AI writes the logic. A compiler writes the SQL. SQLazy: Complex SQL, made lazy. Trusted by design. Which part of SQL do you most wish you could slack off on but don’t dare to? Rewriting dialects for each database? Session splitting? Or the legacy mess of quarterly reports? Chat with us in the comments. Here’s the sample database: github.com/SPLWare/SQLazy/tree/master/examples
LLM Plans, Compiler Generates – A Deterministic SQL Generator with the Workflow as Its Contract 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. !Architecture overview 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 t2 segment condition (NewStatus = "ConfirmationStarted") partition ID as seg t3 filter (NewStatus = "Closed" and seg = 1) t4 summarize max CreatedAt as CreatedAt; group ID Run this example onlin: sqlazy.com 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: !Image descriptionArchitecture overview 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: 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. !Image description 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/tree/master/examples
Convert Time Columns into Rows and Pivot into a New Cross Table 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 !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: !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. !he 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. !unfixed number 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. !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. !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. !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. !action name 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. !two pivotsn 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: SPLWare/SQLazy
SQLazy: Find Strings with 5 or More Consecutive Alphabetical Letters 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 !Source Data Expected Result !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. !SQLazy Step-by-step Implementation 【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. !expand 50 as Position 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. !compute mid 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. !filter 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. !Count characters !summarize 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. !summarize GrpSize Step 7: Filter strings with length >= 5 filter (MaxRun >= 5) Keep rows where MaxRun >= 5. !filter 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: SPLWare/SQLazy
Flag Office Visits Within 6 Months After Surgery 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: !Source Data 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: !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. !steps 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. !Visit_Date 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. !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. !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. !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. !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. !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. !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]( SPLWare/SQLazy)