We Build a Compiler So You Don’t Have to Gamble on AI Getting It Right
We built a compiler, so you no longer have to rely on AI getting it right. It’s not that AI models aren’t accurate enough; it’s that the industry has been asking them to do things they shouldn’t be doing.
Have you ever run into this? AI-generated SQL executes successfully and passes validation, only for you to discover after going live that the boundary is off by one row – with no error reported.
Why we built a compiler
We’ve been used to throw a requirement at AI and get back a beautiful CTE in seconds – window functions nested inside window functions, and nothing for review to flag. It wasn’t until reconciliation that we found the grouping was off by one, and the fallback row had been dropped.
That’s when we realized that while the entire industry was racing to make models more accurate, we needed to take a different path: stop asking models to generate complex, production-ready SQL.
AI is good at breaking plain-language requests down into steps. A compiler is good at translating those steps into SQL. Let each do what it does best, and there’s no need to gamble on AI getting it right.
!AI getting
!amazonaws
An example of SQL that runs but gets the right result only by luck
The request was simple, yet it was enough to make us hold our noses and push it live.
Sort the events table by time. Consecutive rows with the same value column value belong to the same group. For each group, output its start time as effective_from and the start time of the next group as effective_to. By convention, set effective_to for the last group to 9999-12-31.
The value column in the source table contains the sequence 1, 2, 2, 1, 1, 1, 2, 2, 1. Consecutive identical values are grouped together:
!grouped together
The expected result only has five rows:
!five rows
We gave the AI a one-sentence requirement and got SQL that looked like this: six CTEs, nested subqueries, the full lineup of LAG, SUM, and LEAD, plus an extra layer of filtering that served no obvious purpose. It looks perfectly correct on the surface. But would you dare deploy it straight to production?
WITH Value AS (
SELECT id, value, timestamp FROM events
),
Value1 AS (
SELECT Value.*, LAG(value) OVER (ORDER BY timestamp ASC) AS prev_value
FROM Value
),
Value2 AS (
SELECT id, value, timestamp,
SUM(CASE WHEN value <> prev_value THEN 1 ELSE 0 END) OVER (ORDER BY timestamp ASC) AS gid
FROM Value1
),
Grouped AS (
SELECT gid, MIN(id) AS id, MIN(value) AS value, MIN(timestamp) AS effective_from
FROM Value2
GROUP BY gid
),
t_1 AS (
SELECT gid, id, value, effective_from,
LEAD(effective_from, 1) OVER (ORDER BY gid) AS effective_to
FROM (
SELECT * FROM Grouped WHERE id IN (SELECT id FROM Grouped WHERE effective_from IS NOT NULL)
) inner_q
),
cte_final_v2 AS (
SELECT gid, id, value, effective_from, effective_to FROM t_1 WHERE 1 = 1
)
SELECT * FROM cte_final_v2 ORDER BY gid;
Could you spot the errors without running through the groups one by one? The most dangerous thing about this kind of SQL isn’t its syntax, but three subtle logic errors hidden inside it. The ORDER BY clause omits the branch for handling NULL values. The SUM calculation is missing a + 1, causing gid to start from 0. The LEAD function is missing a default value. The LEAD issue directly causes the fallback value for the last group to become NULL: one row is wrong, no error is reported, and all you can do is hope for the best. The other two issues can also cause failures when boundary cases arise.
It’s not that the model isn’t accurate enough – it’s that the delivery format is wrong: it outputs the final SQL in one shot, and if something goes wrong, the only option is to rewrite the prompt and regenerate the whole thing, so logical deviations naturally can’t be undone.
It is the last row where the error occurs:
The expected last row: 9 | 1 | 2023-11-18 13:00:00 | 9999-12-31 00:00:00
The actual last row: 9 | 1 | 2023-11-18 13:00:00 | NULL
We asked AI to do the one thing it should never do
AI is good at planning steps, but not at guaranteeing that the final SQL is correct.
The old paradigm was “prompt → AI → nondeterministic final SQL”: a black-box deliverable that leaves you with no choice but to ask the AI to regenerate the entire query whenever something goes wrong.
The new paradigm should be “prompt → AI → structured steps → compiler → deterministic SQL,” with every step open to validation.
AI writes the logic. A compiler writes the SQL.
With SQLazy, it takes just 5 steps
The same validity-period logic – the logic of getting each group’s start and end times, as described above – takes just five steps in SQLazy. You can inspect the intermediate table at every step.
!SQLazy
!inspect
Here’s what the five steps do at a glance: sort the data chronologically, automatically create a new segment whenever value changes, retrieve the first record in each segment along with the start time of the next segment, and finally remove the helper columns. You don’t need to know how to write a LAG function.
Remember the three subtle logic errors we saw earlier? In SQLazy, they have nowhere to hide. Just click on any step to inspect its intermediate result. To check whether the segmentation is correct, all you need to do is look at the gid column.
!compiler generates
If the error is in step 2, just fix step 2. There’s no need to start over from the beginning. You can make changes with confidence because every step is visible and inspectable.
The compiler generates the SQL according to predefined rules, making the output traceable and auditable:
WITH Value AS (
SELECT id, value, timestamp FROM events
),
Value2 AS (
SELECT gid, id AS id, value AS value, timestamp AS effective_from
FROM (
SELECT id, value, timestamp,
SUM(CASE WHEN value <> col__5 THEN 1 ELSE 0 END)
OVER (ORDER BY CASE WHEN timestamp IS NULL THEN 1 ELSE 0 END, timestamp ASC) + 1 AS gid
FROM (
SELECT Value.*, LAG(value) OVER (ORDER BY CASE WHEN timestamp IS NULL THEN 1 ELSE 0 END, timestamp ASC) AS col__5
FROM Value
) sub__6
) Value1
GROUP BY gid
)
SELECT gid, id, value, effective_from,
LEAD(effective_from, 1, TO_DATE('9999-12-31 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) OVER (ORDER BY gid) AS effective_to
FROM Value2
ORDER BY gid;
There’s no guesswork in this SQL. The compiler translates the five steps according to predefined rules. The same workflow always produces the same SQL, and no column names are ever made up.
!same SQL
For simple CRUD operations, just let AI write the SQL. Use SQLazy for more complex logic like segmentation and retrieving the first and last records in each group.
Post your suspicious-looking piece of AI-generated SQL here
What’s the most suspicious piece of AI-generated SQL you’ve ever put into production? Did it invent a nonexistent column, omit a segmentation key, or quietly get the aggregation logic wrong?
You no longer have to get stuck in the “rewrite the prompt → rerun the code → hope for the best” loop. Paste the AI-generated SQL you trust the least into the Playground, follow the same step-by-step, verifiable workflow shown above, and share your experience.
Try it online and run this example:
Open-source example ：
[github.com/SPLWare/SQLazy/examples] library(github.com/SPLWare/SQLazy/tree/master/examples)
Playground – click the link to run the example illustrated in this article
sqlazy.com
5 reactions
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
Mentions
We Build a Compiler So You Don’t Have to Gamble on AI Getting It Right We built a compiler, so you no longer have to rely on AI getting it right. It’s not that AI models aren’t accurate enough; it’s that the industry has been asking them to do things they shouldn’t be doing. Have you ever run into this? AI-generated SQL executes successfully and passes validation, only for you to discover after going live that the boundary is off by one row – with no error reported. Why we built a compiler We’ve been used to throw a requirement at AI and get back a beautiful CTE in seconds – window functions nested inside window functions, and nothing for review to flag. It wasn’t until reconciliation that we found the grouping was off by one, and the fallback row had been dropped. That’s when we realized that while the entire industry was racing to make models more accurate, we needed to take a different path: stop asking models to generate complex, production-ready SQL. AI is good at breaking plain-language requests down into steps. A compiler is good at translating those steps into SQL. Let each do what it does best, and there’s no need to gamble on AI getting it right. !AI getting !amazonaws An example of SQL that runs but gets the right result only by luck The request was simple, yet it was enough to make us hold our noses and push it live. Sort the events table by time. Consecutive rows with the same value column value belong to the same group. For each group, output its start time as effective_from and the start time of the next group as effective_to. By convention, set effective_to for the last group to 9999-12-31. The value column in the source table contains the sequence 1, 2, 2, 1, 1, 1, 2, 2, 1. Consecutive identical values are grouped together: !grouped together The expected result only has five rows: !five rows We gave the AI a one-sentence requirement and got SQL that looked like this: six CTEs, nested subqueries, the full lineup of LAG, SUM, and LEAD, plus an extra layer of filtering that served no obvious purpose. It looks perfectly correct on the surface. But would you dare deploy it straight to production? WITH Value AS ( SELECT id, value, timestamp FROM events ), Value1 AS ( SELECT Value.*, LAG(value) OVER (ORDER BY timestamp ASC) AS prev_value FROM Value ), Value2 AS ( SELECT id, value, timestamp, SUM(CASE WHEN value <> prev_value THEN 1 ELSE 0 END) OVER (ORDER BY timestamp ASC) AS gid FROM Value1 ), Grouped AS ( SELECT gid, MIN(id) AS id, MIN(value) AS value, MIN(timestamp) AS effective_from FROM Value2 GROUP BY gid ), t_1 AS ( SELECT gid, id, value, effective_from, LEAD(effective_from, 1) OVER (ORDER BY gid) AS effective_to FROM ( SELECT * FROM Grouped WHERE id IN (SELECT id FROM Grouped WHERE effective_from IS NOT NULL) ) inner_q ), cte_final_v2 AS ( SELECT gid, id, value, effective_from, effective_to FROM t_1 WHERE 1 = 1 ) SELECT * FROM cte_final_v2 ORDER BY gid; Could you spot the errors without running through the groups one by one? The most dangerous thing about this kind of SQL isn’t its syntax, but three subtle logic errors hidden inside it. The ORDER BY clause omits the branch for handling NULL values. The SUM calculation is missing a + 1, causing gid to start from 0. The LEAD function is missing a default value. The LEAD issue directly causes the fallback value for the last group to become NULL: one row is wrong, no error is reported, and all you can do is hope for the best. The other two issues can also cause failures when boundary cases arise. It’s not that the model isn’t accurate enough – it’s that the delivery format is wrong: it outputs the final SQL in one shot, and if something goes wrong, the only option is to rewrite the prompt and regenerate the whole thing, so logical deviations naturally can’t be undone. It is the last row where the error occurs: The expected last row: 9 | 1 | 2023-11-18 13:00:00 | 9999-12-31 00:00:00 The actual last row: 9 | 1 | 2023-11-18 13:00:00 | NULL We asked AI to do the one thing it should never do AI is good at planning steps, but not at guaranteeing that the final SQL is correct. The old paradigm was “prompt → AI → nondeterministic final SQL”: a black-box deliverable that leaves you with no choice but to ask the AI to regenerate the entire query whenever something goes wrong. The new paradigm should be “prompt → AI → structured steps → compiler → deterministic SQL,” with every step open to validation. AI writes the logic. A compiler writes the SQL. With SQLazy, it takes just 5 steps The same validity-period logic – the logic of getting each group’s start and end times, as described above – takes just five steps in SQLazy. You can inspect the intermediate table at every step. !SQLazy !inspect Here’s what the five steps do at a glance: sort the data chronologically, automatically create a new segment whenever value changes, retrieve the first record in each segment along with the start time of the next segment, and finally remove the helper columns. You don’t need to know how to write a LAG function. Remember the three subtle logic errors we saw earlier? In SQLazy, they have nowhere to hide. Just click on any step to inspect its intermediate result. To check whether the segmentation is correct, all you need to do is look at the gid column. !compiler generates If the error is in step 2, just fix step 2. There’s no need to start over from the beginning. You can make changes with confidence because every step is visible and inspectable. The compiler generates the SQL according to predefined rules, making the output traceable and auditable: WITH Value AS ( SELECT id, value, timestamp FROM events ), Value2 AS ( SELECT gid, id AS id, value AS value, timestamp AS effective_from FROM ( SELECT id, value, timestamp, SUM(CASE WHEN value <> col__5 THEN 1 ELSE 0 END) OVER (ORDER BY CASE WHEN timestamp IS NULL THEN 1 ELSE 0 END, timestamp ASC) + 1 AS gid FROM ( SELECT Value.*, LAG(value) OVER (ORDER BY CASE WHEN timestamp IS NULL THEN 1 ELSE 0 END, timestamp ASC) AS col__5 FROM Value ) sub__6 ) Value1 GROUP BY gid ) SELECT gid, id, value, effective_from, LEAD(effective_from, 1, TO_DATE('9999-12-31 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) OVER (ORDER BY gid) AS effective_to FROM Value2 ORDER BY gid; There’s no guesswork in this SQL. The compiler translates the five steps according to predefined rules. The same workflow always produces the same SQL, and no column names are ever made up. !same SQL For simple CRUD operations, just let AI write the SQL. Use SQLazy for more complex logic like segmentation and retrieving the first and last records in each group. Post your suspicious-looking piece of AI-generated SQL here What’s the most suspicious piece of AI-generated SQL you’ve ever put into production? Did it invent a nonexistent column, omit a segmentation key, or quietly get the aggregation logic wrong? You no longer have to get stuck in the “rewrite the prompt → rerun the code → hope for the best” loop. Paste the AI-generated SQL you trust the least into the Playground, follow the same step-by-step, verifiable workflow shown above, and share your experience. Try it online and run this example: Open-source example ： [github.com/SPLWare/SQLazy/examples] library(github.com/SPLWare/SQLazy/tree/master/examples) Playground – click the link to run the example illustrated in this article sqlazy.com
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