<?xml version="1.0" encoding="UTF-8"?>
<rss version="2.0" xmlns:atom="http://www.w3.org/2005/Atom" xmlns:dc="http://purl.org/dc/elements/1.1/">
  <channel>
    <title>DEV Community: Judy</title>
    <description>The latest articles on DEV Community by Judy (@esproc_spl).</description>
    <link>https://dev.to/esproc_spl</link>
    <image>
      <url>https://media2.dev.to/dynamic/image/width=90,height=90,fit=cover,gravity=auto,format=auto/https:%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Fuser%2Fprofile_image%2F1191782%2Fdac3272f-c56a-41d9-914e-8f8fba86506b.jpg</url>
      <title>DEV Community: Judy</title>
      <link>https://dev.to/esproc_spl</link>
    </image>
    <atom:link rel="self" type="application/rss+xml" href="https://dev.to/feed/esproc_spl"/>
    <language>en</language>
    <item>
      <title>Convert Time Columns into Rows and Pivot into a New Cross Table</title>
      <dc:creator>Judy</dc:creator>
      <pubDate>Thu, 19 Nov 2026 06:25:41 +0000</pubDate>
      <link>https://dev.to/esproc_spl/convert-time-columns-into-rows-and-pivot-into-a-new-cross-table-5ba9</link>
      <guid>https://dev.to/esproc_spl/convert-time-columns-into-rows-and-pivot-into-a-new-cross-table-5ba9</guid>
      <description>&lt;h2&gt;
  
  
  Problem Description
&lt;/h2&gt;

&lt;p&gt;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.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Source Data&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F4mx7pnh5rqvbprmpxx2m.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F4mx7pnh5rqvbprmpxx2m.png" alt="Source Data" width="800" height="222"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;h2&gt;
  
  
  Expected Result
&lt;/h2&gt;

&lt;p&gt;The column header is the combination of PartNum and ID, the row header is the time point, and the cells hold the action names:&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fgxq4knz899jnbhotwqya.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fgxq4knz899jnbhotwqya.png" alt="Expected Result" width="800" height="481"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;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.&lt;/p&gt;

&lt;p&gt;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.&lt;/p&gt;

&lt;p&gt;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.&lt;/p&gt;

&lt;h2&gt;
  
  
  SQLazy Step-by-Step Implementation
&lt;/h2&gt;

&lt;p&gt;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.&lt;/p&gt;

&lt;p&gt;[&lt;a href="https://www.sqlazy.com/?3cy" rel="noopener noreferrer"&gt;Click to run this example online&lt;/a&gt;]&lt;/p&gt;

&lt;p&gt;The steps are explained below.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F98x6vhysv9rto0oc1r20.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F98x6vhysv9rto0oc1r20.png" alt="explained below" width="800" height="346"&gt;&lt;/a&gt;&lt;br&gt;
Step 1: Turn the three time columns into rows&lt;/p&gt;

&lt;p&gt;pivot group ID, PartNum; name action_type; value t_stamp; inverse&lt;/p&gt;

&lt;p&gt;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.&lt;/p&gt;

&lt;p&gt;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.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fjenaf79udonrg11odd5y.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fjenaf79udonrg11odd5y.png" alt="an unfixed " width="800" height="406"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;Step 2: Filter out the empty time points&lt;br&gt;
filter (t_stamp notnull)&lt;br&gt;
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 filter action.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F359v1mkd8ylpegel17l6.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F359v1mkd8ylpegel17l6.png" alt="parentheses " width="799" height="356"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;Step 3: Replace the action names with the short names the problem asks for&lt;br&gt;
compute if(action_type = “EnteredOn” then “ENTERED”, action_type = “PickedTime” then “PICKED”, action_type = “DeliveredTime” then “DELIVERED”) as status&lt;br&gt;
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.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fkx4o699rmwwkd5km1i32.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fkx4o699rmwwkd5km1i32.png" alt="equality branches" width="799" height="350"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;Step 4: Build the column names of the new table&lt;br&gt;
compute concat(PartNum, “_”, ID) as col_name&lt;br&gt;
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.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fofv8y943elk9um43t3oi.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fofv8y943elk9um43t3oi.png" alt="hard-code them" width="800" height="334"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;Step 5: Keep only the three columns that are needed&lt;br&gt;
derive t_stamp, col_name, status&lt;br&gt;
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.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Ffv6zvetfar643k2lkvxg.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Ffv6zvetfar643k2lkvxg.png" alt="action name" width="800" height="348"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;Step 6: Turn the rows into columns&lt;br&gt;
pivot group t_stamp; name col_name; value status; header ‘50A_100’,‘60B_125’,‘50A_171’,‘70B_211’&lt;br&gt;
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.&lt;br&gt;
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.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fo7kls8r7z28yawt626zc.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fo7kls8r7z28yawt626zc.png" alt="pivots togethe" width="800" height="345"&gt;&lt;/a&gt;&lt;/p&gt;
&lt;h2&gt;
  
  
  Generated SQL
&lt;/h2&gt;

&lt;p&gt;After confirming the logic of the above 6 steps, the SQLazy compiler automatically generates native SQL (MySQL syntax used here):&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;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
    ),
    t5 AS (
        SELECT t_stamp
            , concat_ws('', PartNum, '_', ID) AS col_name
            , CASE
                WHEN action_type = 'EnteredOn' THEN 'ENTERED'
                WHEN action_type = 'PickedTime' THEN 'PICKED'
                WHEN action_type = 'DeliveredTime' THEN 'DELIVERED'
                ELSE NULL
            END AS status
        FROM t1
        WHERE t_stamp IS NOT NULL
    ),
    t5_2 AS (
        SELECT t_stamp, col_name, status
            , FIRST_VALUE(status) OVER (PARTITION BY t_stamp, col_name ORDER BY NULL) AS FIRST_1
        FROM t5
    )
SELECT t_stamp
    , MIN(CASE
        WHEN col_name = '50A_100' THEN FIRST_1
    END) AS `50A_100`
    , MIN(CASE
        WHEN col_name = '50A_171' THEN FIRST_1
    END) AS `50A_171`
    , MIN(CASE
        WHEN col_name = '60B_125' THEN FIRST_1
    END) AS `60B_125`
    , MIN(CASE
        WHEN col_name = '70B_211' THEN FIRST_1
    END) AS `70B_211`
FROM t5_2
GROUP BY t_stamp
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;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.&lt;/p&gt;

&lt;p&gt;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.&lt;/p&gt;

&lt;p&gt;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.&lt;/p&gt;

&lt;p&gt;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.&lt;/p&gt;

&lt;h2&gt;
  
  
  Official Links
&lt;/h2&gt;

&lt;p&gt;SQLazy Online Experience: &lt;a href="https://sqlazy.com/" rel="noopener noreferrer"&gt;sqlazy.com&lt;/a&gt; (free, no registration required)&lt;/p&gt;

&lt;p&gt;SQLazy Repository: &lt;a href="https://github.com/SPLWare/SQLazy" rel="noopener noreferrer"&gt;github.com/SPLWare/SQLazy&lt;/a&gt;&lt;/p&gt;

</description>
      <category>data</category>
      <category>database</category>
      <category>sql</category>
    </item>
    <item>
      <title>Debugging SQL Should Be Just Like Debugging Code</title>
      <dc:creator>Judy</dc:creator>
      <pubDate>Tue, 17 Nov 2026 06:51:54 +0000</pubDate>
      <link>https://dev.to/esproc_spl/debugging-sql-should-be-just-like-debugging-code-49d1</link>
      <guid>https://dev.to/esproc_spl/debugging-sql-should-be-just-like-debugging-code-49d1</guid>
      <description>&lt;h2&gt;
  
  
  Debugging SQL is still mostly guesswork
&lt;/h2&gt;

&lt;p&gt;Suppose you run an 80-line SQL query and discover that one of the result columns contains incorrect numbers. What’s your first instinct? Run it again.&lt;/p&gt;

&lt;p&gt;At this point, a programmer would open the debugger, set breakpoints, and step through the code. They could see exactly how each variable changes and what value it holds at every step. What about SQL developers? They can only stare at the final result and guess: Is the join condition wrong? Does a window function miss a partition? You want to see the result of an intermediate step? You can’t. You have to split the CTEs and even the nested queries, one by one, turn them into standalone SELECT statements, run them individually, and then piece everything back together.&lt;/p&gt;

&lt;h2&gt;
  
  
  This isn’t debugging – this is archeology.
&lt;/h2&gt;

&lt;p&gt;SQL debugging is still stuck at “run it, then stare at the output and guess.”&lt;/p&gt;

&lt;p&gt;You’d never accept this way of debugging when write code – so why do you put up with it when you write SQL?&lt;/p&gt;

&lt;h2&gt;
  
  
  A real-world scenario: finding a neighboring record by offset
&lt;/h2&gt;

&lt;p&gt;That’s all abstract. Let’s look a concrete example.&lt;/p&gt;

&lt;p&gt;A workshop scan log table records, in each row, the cardboard sheet number scanned at a certain moment on a certain production line. The requirement: group by production line and sort by time within each group. Then find the records whose number equals a given string (for example, spL1ml82N4o), together with the 2 records before and the 2 records after each one, and finally remove duplicates by id.&lt;/p&gt;

&lt;p&gt;Written in traditional SQL (MySQL), it looks like this:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
  &lt;span class="nb"&gt;MAX&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="nc"&gt;Cardboard_Number&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="nc"&gt;Cardboard_Number&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
  &lt;span class="nb"&gt;MAX&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;date_Time&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;date_Time&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
  &lt;span class="nb"&gt;MAX&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="nc"&gt;ProductionLine_Number&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="nc"&gt;ProductionLine_Number&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
  &lt;span class="nb"&gt;MAX&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;flag&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;flag&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
  &lt;span class="nb"&gt;MAX&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;in_range&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;in_range&lt;/span&gt;
&lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
  &lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="nc"&gt;Cardboard_Number&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;date_Time&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="nc"&gt;ProductionLine_Number&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;flag&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;in_range&lt;/span&gt;
  &lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
    &lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="nc"&gt;Cardboard_Number&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;date_Time&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="nc"&gt;ProductionLine_Number&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
      &lt;span class="nb"&gt;MAX&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;flag&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;OVER&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
        &lt;span class="k"&gt;PARTITION&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="nc"&gt;ProductionLine_Number&lt;/span&gt;
        &lt;span class="k"&gt;ORDER&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="nc"&gt;ProductionLine_Number&lt;/span&gt; &lt;span class="k"&gt;ASC&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;date_Time&lt;/span&gt; &lt;span class="k"&gt;ASC&lt;/span&gt;
        &lt;span class="k"&gt;ROWS&lt;/span&gt; &lt;span class="k"&gt;BETWEEN&lt;/span&gt; &lt;span class="mi"&gt;2&lt;/span&gt; &lt;span class="k"&gt;PRECEDING&lt;/span&gt; &lt;span class="k"&gt;AND&lt;/span&gt; &lt;span class="mi"&gt;2&lt;/span&gt; &lt;span class="k"&gt;FOLLOWING&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;in_range&lt;/span&gt;
    &lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
      &lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="nc"&gt;Cardboard_Number&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;date_Time&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="nc"&gt;ProductionLine_Number&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
        &lt;span class="k"&gt;CASE&lt;/span&gt; &lt;span class="k"&gt;WHEN&lt;/span&gt; &lt;span class="nc"&gt;Cardboard_Number&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="s1"&gt;'spL1ml82N4o'&lt;/span&gt; &lt;span class="k"&gt;THEN&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt; &lt;span class="k"&gt;END&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;flag&lt;/span&gt;
      &lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;table1&lt;/span&gt;
    &lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;flagged&lt;/span&gt;
  &lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;marked&lt;/span&gt;
  &lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="n"&gt;in_range&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt;
&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;filtered&lt;/span&gt;
&lt;span class="k"&gt;GROUP&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="n"&gt;id&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;We won’t explain this code line by line, only what it looks like: three layers of nested derived tables, with flagging, window frame, filtering, and distinct all crammed into one query. Every layer has to list the fields all over again, and to see an intermediate result, all you can do is keep breaking it apart from the inside.&lt;/p&gt;

&lt;p&gt;The real problem isn’t that the SQL is hard to read. It’s that it’s hard to debug. The most fragile part of this SQL is the window frame: ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING. If you get the offsets wrong, it won’t throw an error. If you leave out a segmentation key, it won’t throw an error either. It will simply produce slightly incorrect results, silently. The output looks almost right, but not quite – the kind of result that makes your palms sweat just before deployment. And the intermediate flag columns and window-match indicator columns aren’t included in the final output at all. If you can’t see them, you can’t validate whether they’re correct.&lt;/p&gt;

&lt;h2&gt;
  
  
  SQLazy: Write step by step, debug one step at a time
&lt;/h2&gt;

&lt;p&gt;What if we took a different approach? Instead of delivering a single SQL query as the final product, we break the logic into steps, each of which can be executed and its result inspected.&lt;/p&gt;

&lt;p&gt;That’s exactly what SQLazy does. SQLazy is an intuitive, easy-to-understand language for structured data processing. It breaks logic into clear steps, produces deterministic output, and makes the entire process easy to audit. The same task takes just five steps in SQLazy:&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fy3aqitba62zkyih2yg5y.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fy3aqitba62zkyih2yg5y.png" alt=" " width="799" height="267"&gt;&lt;/a&gt;&lt;br&gt;
It also has a dedicated debugging feature: single-step execution. Click once and it runs one step. and the result tables of the current step and all the steps before it are there for you to browse at any time. This is the same thing as the single-stepping you use when debugging Python or Java. The only difference is that SQLazy’s code structure is simpler, with no branches, loops or subroutines, so you don’t need breakpoints, step in and step out routine. Every statement is already one step.&lt;/p&gt;

&lt;p&gt;Back to the task above, here’s what the debugging experience looks like:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;&lt;p&gt;Step 1: Sorting: click a group and check whether the order is right;&lt;/p&gt;&lt;/li&gt;
&lt;li&gt;&lt;p&gt;Step 2: Flag the target rows and check whether any have been missed.&lt;/p&gt;&lt;/li&gt;
&lt;li&gt;&lt;p&gt;Step 3: Retrieve the two preceding and two following rows. This is the most error-prone part of the entire query, where both the window frame and segmentation keys come into play. Step through the query to this point and inspect the in_range column. You can see at a glance which rows matched and which didn’t.&lt;/p&gt;&lt;/li&gt;
&lt;li&gt;&lt;p&gt;Step 4: Filter the rows.&lt;/p&gt;&lt;/li&gt;
&lt;li&gt;&lt;p&gt;Step 5: Remove duplicates. Just click each step to inspect its result.&lt;/p&gt;&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;&lt;strong&gt;In SQL, a mistake in the window frame leaves you guessing. In SQLazy, just step through to Step 3 and inspect the results.&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F656x30bsz5c3vzi57u5f.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F656x30bsz5c3vzi57u5f.png" alt=" " width="800" height="335"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;If something goes wrong, just fix the step where the error occurs – there’s no need to rewrite the entire query from scratch. Once you’ve verified that the logic is correct, you can also execute all the steps with a single click, letting them run in sequence to produce the final result.&lt;/p&gt;

&lt;h2&gt;
  
  
  It’s a change in form, not just syntax
&lt;/h2&gt;

&lt;p&gt;Someone might argue, “Can’t I just run a SELECT on each CTE in SQL to inspect the intermediate results?” Yes, you can. But that’s manual, makeshift step-by-step debugging. Every time the logic changes, you have to split the query again. Those steps aren’t saved for you. AI can’t fix this either. What AI writes for you is still one final SQL statement. If something goes wrong, you’re back to guessing from scratch.&lt;/p&gt;

&lt;h2&gt;
  
  
  AI writes the logic. A compiler writes the SQL.
&lt;/h2&gt;

&lt;p&gt;In SQLazy, AI plans the business logic as a sequence of steps, and the compiler compiles these steps into native SQL. The steps are for human to read and debug, and the SQL is for machines to run. Each has its own job.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F8vcycet8uu3m4r73hucg.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F8vcycet8uu3m4r73hucg.png" alt=" " width="800" height="678"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;he compiled SQL is equivalent to what you’d write by hand. There’s no extra abstraction layer – j you can copy it and use it as is, and it isn’t tied to any particular database.&lt;/p&gt;

&lt;p&gt;To be honest, for simple CRUD, just write the SQL directly. You don’t need SQLazy for that. What really deserves step-by-step debugging is logic where one wrong step spoils everything – things like grouping records to find the neighbor, condition-based resets, and segment-based alignment.&lt;/p&gt;

&lt;h2&gt;
  
  
  A different approach to debugging
&lt;/h2&gt;

&lt;p&gt;Want to try the step-by-step execution? Visit &lt;a href="https://www.sqlazy.com/?2Qk" rel="noopener noreferrer"&gt;https://www.sqlazy.com/?2Qk&lt;/a&gt;to run this example of searching for adjacent records by offset.&lt;/p&gt;

&lt;p&gt;You can also visit our example library (&lt;a href="https://github.com/SPLWare/SQLazy/tree/master/examples" rel="noopener noreferrer"&gt;https://github.com/SPLWare/SQLazy/tree/master/examples&lt;/a&gt;) to find more examples about sessionization, continuous interval, and dynamic grouped reports.&lt;/p&gt;

&lt;p&gt;How do you usually find errors when you debug SQL? Break the CTEs apart and run them one by one, or stare at the output and think hard? Either way, it’s more tiring than clicking single-step execution.&lt;/p&gt;

</description>
      <category>database</category>
      <category>debugging</category>
      <category>programming</category>
      <category>sql</category>
    </item>
    <item>
      <title>First Record with a Specific Value Within Each Group</title>
      <dc:creator>Judy</dc:creator>
      <pubDate>Thu, 12 Nov 2026 08:02:42 +0000</pubDate>
      <link>https://dev.to/esproc_spl/first-record-with-a-specific-value-within-each-group-1ba4</link>
      <guid>https://dev.to/esproc_spl/first-record-with-a-specific-value-within-each-group-1ba4</guid>
      <description>&lt;h2&gt;
  
  
  Problem Description
&lt;/h2&gt;

&lt;p&gt;The table records stores the daily orders of every customer. Its fields are row_id (row number), customer_id (customer number), date and order_count (number of orders placed that day). The records of each customer are sorted by date in ascending order, and row_id is in the same order as date. For every customer, find the row_id of the record where order_count first became 0; customers that never had a zero-order day must also appear in the result, with an empty row_id.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Source Data&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fb4h60mkeilbafe834w5j.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fb4h60mkeilbafe834w5j.png" alt="Source Data" width="799" height="593"&gt;&lt;/a&gt;&lt;br&gt;
&lt;strong&gt;Expected Result&lt;/strong&gt;&lt;br&gt;
One row per customer, row_id being the record number of that customer first zero-order day, empty if there is none:&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F7rqydsrl4mdzzpvxjmlj.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F7rqydsrl4mdzzpvxjmlj.png" alt="zero-order" width="800" height="157"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;Customer 1 has only one zero-order day (2024-01-04, row_id=4), so the result is 4.&lt;/p&gt;

&lt;p&gt;Customer 3 has two zero-order days (row_id=11 on 2024-01-01 and row_id=14 on 2024-01-04). The earlier one has to be taken, which is row_id=11, so the later 14 must not appear in the result.&lt;/p&gt;

&lt;p&gt;Customer 2 has no zero-order day in these five days, yet still gets a row in the result, with an empty row_id.&lt;/p&gt;
&lt;h2&gt;
  
  
  SQLazy Step-by-Step Implementation
&lt;/h2&gt;

&lt;p&gt;The problem has to satisfy two things at once: take the earliest zero-order record, and keep the customers that have no zero-order record at all. The two things are handled separately: first take a complete customer list from the source table, then group the source table by customer and take the smallest row_id among the zero-order records only, which gives a lookup table from customer to zero-order record, and finally left join that table to the customer list. The left join keeps the customers that found no match in the result, and the unmatched position is empty, which is exactly the null the problem asks for.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://www.sqlazy.com/?2CO" rel="noopener noreferrer"&gt;[Click to run this example online]&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;The steps are explained below.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Ftp0lhh8w8a1twlou2clu.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Ftp0lhh8w8a1twlou2clu.png" alt=" " width="799" height="229"&gt;&lt;/a&gt;&lt;br&gt;
&lt;strong&gt;Step 1: Take the customer number column&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;derive customer_id&lt;/p&gt;

&lt;p&gt;Keep only the customer_id column, preparing the way for deduplication in the next step.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fslnpbtw5zyllzhs8chm1.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fslnpbtw5zyllzhs8chm1.png" alt=" next step" width="800" height="489"&gt;&lt;/a&gt;&lt;br&gt;
&lt;strong&gt;Step 2: Get the complete customer list&lt;/strong&gt;&lt;br&gt;
distinct customer_id&lt;br&gt;
Deduplicate by customer_id to get a complete list of one row per customer, 3 rows in total. This list is the safety net that keeps customers with no zero-order day in the result.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F004qewk0qzz5e9wisydm.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F004qewk0qzz5e9wisydm.png" alt="zero-order day" width="799" height="244"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Step 3: Take the earliest zero-order record of each customer&lt;/strong&gt;&lt;br&gt;
summarize min row_id condition (order_count=0) as row_id; group customer_id&lt;br&gt;
Group by customer_id, write the aggregation min before the aggregated item row_id, and name the result row_id. The condition parameter filters the records of the group first, letting only the records with order_count=0 take part in the aggregation, so what is computed is the smallest row_id among the zero-order records; when a group has no zero-order record at all (such as customer 2) the result is empty.&lt;br&gt;
Taking the smallest row_id is the same as taking the earliest record, which relies on row_id being in the same order as date as stated in the problem description; when the data is not in that order, the smallest date has to be taken instead.&lt;br&gt;
The parameter order is aggregation, aggregated item, condition and naming; writing the condition before the aggregation is wrong.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F3ey23fh4de00oir8w6vr.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F3ey23fh4de00oir8w6vr.png" alt="riting the condition" width="799" height="244"&gt;&lt;/a&gt;&lt;br&gt;
&lt;strong&gt;Step 4: Left join the customer list with the zero-order records&lt;/strong&gt;&lt;br&gt;
join customer_id; with t_zeros; customer_id; take row_id; left&lt;br&gt;
Take the customer list t_all as the base table, join t_zeros by customer_id and attach its row_id. The left join keeps every customer in the list, and the unmatched row_id is empty; an inner join would drop customer 2.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Flt12gec160vld1dhynla.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Flt12gec160vld1dhynla.png" alt="customer" width="800" height="247"&gt;&lt;/a&gt;&lt;/p&gt;
&lt;h2&gt;
  
  
  Generated SQL
&lt;/h2&gt;

&lt;p&gt;After confirming the logic of the above 4 steps, the SQLazy compiler automatically generates native SQL (MySQL syntax used here):&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;WITH t_ids AS (
    SELECT customer_id
    FROM records
  ),
  t_all AS (
    SELECT customer_id
    FROM t_ids
    GROUP BY customer_id
  ),
  t_zeros AS (
    SELECT customer_id
      , MIN(CASE
        WHEN order_count = 0 THEN row_id
        ELSE NULL
      END) AS row_id
    FROM records
    GROUP BY customer_id
  ),
  t_result AS (
    SELECT t_all.customer_id, t_zeros.row_id
    FROM t_all
      LEFT JOIN t_zeros ON t_all.customer_id = t_zeros.customer_id
  )
SELECT customer_id, row_id
FROM t_result
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;SQLazy lets you describe logic in business language instead of writing nested SQL queries. Taking the earliest record and keeping every customer are split into two steps, each in charge of one thing: summarize takes the smallest row_id among the zero-order records, and the left join keeps all customers.&lt;/p&gt;

&lt;p&gt;The summarize action filters before it aggregates: the condition parameter screens the records first, so only the zero-order records are aggregated, and a group without any zero-order record comes out empty. The aggregation algorithm goes before the aggregated item and the condition right after it, so the parameter order is the order in which the business states it.&lt;/p&gt;

&lt;p&gt;The join type is a literal translation of the business wording: write a left join to keep all customers, or an inner join to keep only the customers that match.&lt;/p&gt;

&lt;p&gt;Official Links&lt;br&gt;
SQLazy Online Experience: &lt;a href="https://sqlazy.com/" rel="noopener noreferrer"&gt;sqlazy.com &lt;/a&gt;(free, no registration required)&lt;/p&gt;

&lt;p&gt;SQLazy Repository: &lt;a href="https://github.com/SPLWare/SQLazy" rel="noopener noreferrer"&gt;github.com/SPLWare/SQLazy&lt;/a&gt;&lt;/p&gt;

</description>
      <category>algorithms</category>
      <category>database</category>
      <category>programming</category>
      <category>sql</category>
    </item>
    <item>
      <title>What We Learned from N Developers Testing the Zero-Hallucination SQL Compiler</title>
      <dc:creator>Judy</dc:creator>
      <pubDate>Tue, 10 Nov 2026 06:10:44 +0000</pubDate>
      <link>https://dev.to/esproc_spl/what-we-learned-from-n-developers-testing-the-zero-hallucination-sql-compiler-1dpb</link>
      <guid>https://dev.to/esproc_spl/what-we-learned-from-n-developers-testing-the-zero-hallucination-sql-compiler-1dpb</guid>
      <description>&lt;p&gt;Shortly after SQLazy was launched, we offer it to a group of developers to test using their own queries.&lt;/p&gt;

&lt;p&gt;Overall, the feedback was positive. Most developers were able to get their workflows running on the first try and put the compiled SQL straight to use. The most frequently praised features were that the generated SQL didn’t invent column names, every step could be inspected, and even old queries that they had once dared not touch could now be reworked. But we also received plenty of negative feedbacks, some of it quite pointed. These pain points weren’t all the same, so we grouped them into two categories.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Some capabilities aren’t clearly explained in the documentation.&lt;/strong&gt; For example, if you want to retrieve only duplicate rows and exclude unique ones, you can handle this directly using the three options of the deduplication feature. To check whether a value belongs to a set, simply use the contain function. These cases don’t require any code changes; a clearer explanation is all that’s needed.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;The capability is there, but the syntax is cumbersome.&lt;/strong&gt; You can express what you want, but it takes a roundabout approach, or the reader has to scrutinize the code to understand it. What we learned from this category of feedback is that users often get stuck not because the functionality is missing, but because the code is cumbersome to write and read. So, we made some changes in this area. The following are the most worthwhile improvements from this round. Each change may look small, but it addresses a need that comes up repeatedly in everyday use.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Min/Max Aggregation: Getting related attributes, not the min/max value itself&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;Many developers have run into the same problem: they want a different field from the record containing the maximum or minimum value – for example, the amount of the most recent order.&lt;/p&gt;

&lt;p&gt;SQL has no built-in, one-step way to do this, so developers usually resort to a subquery: first find the latest date for each customer, then filter the main table to retrieve the corresponding records, often combining this with aggregation along the way:&lt;/p&gt;

&lt;p&gt;SELECT ClientID, MAX(Amount) AS MaxAmt FROM orders o&lt;/p&gt;

&lt;p&gt;WHERE OrderDate = (SELECT MAX(OrderDate) FROM orders WHERE ClientID = o.ClientID)&lt;/p&gt;

&lt;p&gt;GROUP BY ClientID;&lt;/p&gt;

&lt;p&gt;One thing to keep in mind is that multiple records may correspond to the same minimum or maximum value, so a second round of aggregation may be needed. Of course, applying aggregation again won’t hurt even if there’s only one matching record.&lt;/p&gt;

&lt;p&gt;A few databases, such as Oracle, let you do this in a single step using a proprietary KEEP syntax (MAX(Amount) KEEP (DENSE_RANK LAST ORDER BY OrderDate)). Other databases don’t support it, so the logic has to be broken into multiple steps.&lt;/p&gt;

&lt;p&gt;SQLazy previously required two steps: first, filter for records of the latest date, then find the maximum value among those records.&lt;/p&gt;

&lt;p&gt;t2=orders: rank OrderDate; max; partition ClientID&lt;/p&gt;

&lt;p&gt;t3=t2: summarize max Amount; group ClientID&lt;/p&gt;

&lt;p&gt;Now, we’ve simplified this entire category of queries into a single line of SQLazy code: find the record or records with the minimum or maximum value, then retrieve another field from those records.&lt;/p&gt;

&lt;p&gt;result= summarize argmax OrderDate max Amount; group ClientID&lt;/p&gt;

&lt;p&gt;argmax takes only a single value aggregation after it. It first pins down the one or more records for the latest OrderDate, then takes the largest amount among them. It’s written in one statement, and it works on any database.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Computed Columns: Cross-row metrics in one Pass, not two&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;Cross-row metrics such as percentage of total and cumulative sums used to get no dedicated handling. They could be done, but only by using the computed column function twice, which made the code cumbersome.&lt;/p&gt;

&lt;p&gt;For example, say the orders table is sorted by salesperson (SellerId), and you want to calculate each order’s share of its salesperson’s total sales. The usual SQL approach is to use a window function (a window per group): calculate the total in one pass, then divide:&lt;/p&gt;

&lt;p&gt;SELECT OrderID, Client, SellerId, Amount, OrderDate,&lt;/p&gt;

&lt;p&gt;SUM(Amount) OVER (PARTITION BY SellerId) AS SellerTotal,&lt;/p&gt;

&lt;p&gt;Amount / SUM(Amount) OVER (PARTITION BY SellerId) AS AmountShare&lt;/p&gt;

&lt;p&gt;FROM orders;&lt;/p&gt;

&lt;p&gt;Previously, SQLazy code had to calculate twice: first compute the total and store it in a column, then divide:&lt;/p&gt;

&lt;p&gt;t1 orders compute summarize, Amount, Name SellerTotal; segment SellerId&lt;/p&gt;

&lt;p&gt;t2 t1 compute Amount/ SellerTotal, name AmountShare&lt;/p&gt;

&lt;p&gt;Now we’ve added a convenient cross-row parameter, so the SQLazy code takes just one statement. Note that this is something we added, not something that was there originally:&lt;/p&gt;

&lt;p&gt;t1 orders compute Amount, Share, Name AmountShare; segment SellerId&lt;/p&gt;

&lt;p&gt;In the new code, “share” is simply a cross-row parameter. It calculates each row’s share of its group’s total in one go, with no need for the separate total column. In the same way, cumulative sum, cumulative share, growth rate, same-period comparison, and YoY growth each have a corresponding cross-row parameter, and each takes just one statement.&lt;/p&gt;

&lt;p&gt;What we learned is this: cross-row metrics are something everyone has to compute. What gets convoluted is not the machine but the people reading the code. It can be written in two steps, but you first need to understand what that intermediate column is for.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Segmentation, alignment, and ranking&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;There are endlessly many possible bases for segmentation, and in the past you could only hard-write them with conditional expressions. For example, “start a new segment when the value changes” had to be written as a comparison with the previous row plus special handling for a null first row, and the reader had to walk through it in their head to be sure it was right. Here’s what we took from this: the bases look endless, but only a handful are in common use. So, we distilled the common ones into eight enumerated options: changed, unchanged, increased, not increased, decreased, not decreased, plus a total number of groups and grouped every N rows. You just state the basis.&lt;/p&gt;

&lt;p&gt;Reports require continuous dates, so a day with no data still has to keep a row. The feedback made us realize that what makes empty rows fill-in awkward is not the writing itself, but having to specify, across three steps, which column is the alignment basis. The usual SQL approach is to generate a date sequence and left-join it back to the original table. SQLazy used to require the same two steps, generating the sequence and then joining. So, we folded the three steps into one align. Now a single statement aligns rows to a basis and fills in the empty ones. Its parameters are the alignment basis, sort-only, and keep other columns and segments.&lt;/p&gt;

&lt;p&gt;Getting the top N is a request you encounter every day, yet it used to take two steps: calculate a rank column (ordering rows by some column), then filter on it. So, we made rank function select target rows directly by rank, with top function and bottom function taking a count, and max and min functions retrieving records.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Why these changes were worth it&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;None of these changes is large, but they all land on the same kind of thing: “lock a batch of records by a definition, then aggregate,” “calculate the share and cumulative sum within a segment,” “segment by a business basis,” “fill in the missing dates,” “take the top N.” Every one of these is a need that comes up every day. After the changes, code that used to be written in a roundabout way is now a single statement.&lt;/p&gt;

&lt;p&gt;The application scope hasn’t changed: for simple select, insert, update and delete, writing SQL directly is easier. Macros are still on the roadmap, and recursion is about to be released. The capabilities above aren’t everything, either. We only changed the places receiving the most feedback.&lt;/p&gt;

&lt;p&gt;SQLazy: Complex SQL, made lazy. Trusted by design.&lt;/p&gt;

&lt;p&gt;Post the pitfalls you encountered to our forum, whether they were generated by AI or left behind by someone else. Each item above has a matching real-world problem in the example library (github.com/SPLWare/SQLazy/tree/master/examples). If you submit your own problem, we’ll break it down, and add it to the library.&lt;/p&gt;

</description>
      <category>ai</category>
      <category>programming</category>
      <category>devops</category>
      <category>discuss</category>
    </item>
    <item>
      <title>We Build a Compiler So You Don’t Have to Gamble on AI Getting It Right</title>
      <dc:creator>Judy</dc:creator>
      <pubDate>Thu, 05 Nov 2026 08:45:00 +0000</pubDate>
      <link>https://dev.to/esproc_spl/we-build-a-compiler-so-you-dont-have-to-gamble-on-ai-getting-it-right-580i</link>
      <guid>https://dev.to/esproc_spl/we-build-a-compiler-so-you-dont-have-to-gamble-on-ai-getting-it-right-580i</guid>
      <description>&lt;p&gt;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.&lt;/p&gt;

&lt;p&gt;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.&lt;/p&gt;

&lt;h2&gt;
  
  
  Why we built a compiler
&lt;/h2&gt;

&lt;p&gt;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.&lt;/p&gt;

&lt;p&gt;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.&lt;/p&gt;

&lt;p&gt;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.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Ffyuyecjdnpqb89qvuqa7.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Ffyuyecjdnpqb89qvuqa7.png" alt="AI getting" width="800" height="450"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fzxu90c3a54t9x6t8fjzq.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fzxu90c3a54t9x6t8fjzq.png" alt="amazonaws" width="800" height="450"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;h2&gt;
  
  
  An example of SQL that runs but gets the right result only by luck
&lt;/h2&gt;

&lt;p&gt;The request was simple, yet it was enough to make us hold our noses and push it live.&lt;/p&gt;

&lt;p&gt;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.&lt;/p&gt;

&lt;p&gt;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:&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fg7k03bb4vbzctdrgmyvk.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fg7k03bb4vbzctdrgmyvk.png" alt="grouped together" width="800" height="373"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;The expected result only has five rows:&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fv82yq83y0p22s2pmdkgr.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fv82yq83y0p22s2pmdkgr.png" alt="five rows" width="799" height="225"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;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?&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;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 &amp;lt;&amp;gt; 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;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;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.&lt;/p&gt;

&lt;p&gt;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.&lt;/p&gt;

&lt;p&gt;It is the last row where the error occurs:&lt;/p&gt;

&lt;p&gt;The expected last row: 9 | 1 | 2023-11-18 13:00:00 | 9999-12-31 00:00:00&lt;/p&gt;

&lt;p&gt;The actual last row: 9 | 1 | 2023-11-18 13:00:00 | NULL&lt;/p&gt;

&lt;p&gt;We asked AI to do the one thing it should never do&lt;/p&gt;

&lt;p&gt;AI is good at planning steps, but not at guaranteeing that the final SQL is correct.&lt;/p&gt;

&lt;p&gt;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.&lt;/p&gt;

&lt;p&gt;The new paradigm should be “prompt → AI → structured steps → compiler → deterministic SQL,” with every step open to validation.&lt;/p&gt;

&lt;h2&gt;
  
  
  AI writes the logic. A compiler writes the SQL.
&lt;/h2&gt;

&lt;p&gt;&lt;strong&gt;With SQLazy, it takes just 5 steps&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;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.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fs9mkz47o4t3o35psvchp.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fs9mkz47o4t3o35psvchp.png" alt="SQLazy" width="800" height="263"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F9jts0387220nfib13hqp.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F9jts0387220nfib13hqp.png" alt="inspect" width="800" height="454"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;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.&lt;br&gt;
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.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fegr4ngvpe7r0i6uejdru.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fegr4ngvpe7r0i6uejdru.png" alt="compiler generates" width="800" height="253"&gt;&lt;/a&gt;&lt;br&gt;
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.&lt;br&gt;
The compiler generates the SQL according to predefined rules, making the output traceable and auditable:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;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 &amp;lt;&amp;gt; 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;

&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;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.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fedff3d48pv1c2ewoyxfg.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fedff3d48pv1c2ewoyxfg.png" alt="same SQL" width="800" height="698"&gt;&lt;/a&gt;&lt;br&gt;
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.&lt;/p&gt;

&lt;p&gt;Post your suspicious-looking piece of AI-generated SQL here&lt;/p&gt;

&lt;p&gt;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?&lt;/p&gt;

&lt;p&gt;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.&lt;/p&gt;

&lt;p&gt;Try it online and run this example:&lt;/p&gt;

&lt;p&gt;Open-source example ：&lt;br&gt;
[github.com/SPLWare/SQLazy/examples]             library(&lt;a href="https://github.com/SPLWare/SQLazy/tree/master/examples" rel="noopener noreferrer"&gt;https://github.com/SPLWare/SQLazy/tree/master/examples&lt;/a&gt;)&lt;/p&gt;

&lt;p&gt;Playground – click the link to run the example illustrated in this article&lt;br&gt;
&lt;a href="https://www.sqlazy.com/?3IT" rel="noopener noreferrer"&gt;https://www.sqlazy.com/?3IT&lt;/a&gt;&lt;/p&gt;

</description>
      <category>ai</category>
      <category>programming</category>
      <category>devops</category>
      <category>sql</category>
    </item>
    <item>
      <title>To Write SQL, the Most Rigorous Thing You’ll Do This Quarter: Be SQLazy</title>
      <dc:creator>Judy</dc:creator>
      <pubDate>Tue, 03 Nov 2026 05:41:42 +0000</pubDate>
      <link>https://dev.to/esproc_spl/to-write-sql-the-most-rigorous-thing-youll-do-this-quarter-be-sqlazy-35je</link>
      <guid>https://dev.to/esproc_spl/to-write-sql-the-most-rigorous-thing-youll-do-this-quarter-be-sqlazy-35je</guid>
      <description>&lt;p&gt;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.&lt;/p&gt;

&lt;p&gt;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.&lt;/p&gt;

&lt;p&gt;Write lazier, get more rigor&lt;/p&gt;

&lt;p&gt;What we mean by “lazy” is this: writing gets lazier, and the results get more rigorous.&lt;/p&gt;

&lt;p&gt;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.&lt;/p&gt;

&lt;p&gt;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.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fkumnzh361g2guett6wtb.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fkumnzh361g2guett6wtb.png" alt="rigorous in results" width="528" height="297"&gt;&lt;/a&gt;&lt;br&gt;
Lazy doesn’t mean slacking off. It means quality control.&lt;/p&gt;

&lt;p&gt;SQLazy is like a sloth. Its role here isn’t to be a specimen, it’s to be a quality inspector.&lt;/p&gt;

&lt;p&gt;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.&lt;/p&gt;

&lt;p&gt;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.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F8fitez3ivmf2o17e1o4o.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F8fitez3ivmf2o17e1o4o.png" alt="validation" width="396" height="214"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;See you in the comments&lt;/p&gt;

&lt;p&gt;AI writes the logic. A compiler writes the SQL.&lt;/p&gt;

&lt;p&gt;SQLazy: Complex SQL, made lazy. Trusted by design.&lt;/p&gt;

&lt;p&gt;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:&lt;/p&gt;

&lt;p&gt;&lt;a href="https://github.com/SPLWare/SQLazy/tree/master/examples" rel="noopener noreferrer"&gt;https://github.com/SPLWare/SQLazy/tree/master/examples&lt;/a&gt;&lt;/p&gt;

</description>
      <category>ai</category>
      <category>programming</category>
      <category>devops</category>
      <category>discuss</category>
    </item>
    <item>
      <title>LLM Plans, Compiler Generates – A Deterministic SQL Generator with the Workflow as Its Contract</title>
      <dc:creator>Judy</dc:creator>
      <pubDate>Tue, 27 Oct 2026 08:43:28 +0000</pubDate>
      <link>https://dev.to/esproc_spl/llm-plans-compiler-generates-a-deterministic-sql-generator-with-the-workflow-as-its-contract-1fbe</link>
      <guid>https://dev.to/esproc_spl/llm-plans-compiler-generates-a-deterministic-sql-generator-with-the-workflow-as-its-contract-1fbe</guid>
      <description>&lt;p&gt;AI can already produce SQL that runs – but runnable isn’t the same as auditable.&lt;/p&gt;

&lt;p&gt;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.&lt;/p&gt;

&lt;p&gt;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.&lt;/p&gt;

&lt;p&gt;Why having an LLM write SQL directly isn’t deterministic&lt;/p&gt;

&lt;p&gt;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.&lt;/p&gt;

&lt;p&gt;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.&lt;/p&gt;

&lt;p&gt;If you ask AI to generate this SQL directly, the typical result is a multi-layer nested query:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;WITH&lt;/span&gt; &lt;span class="n"&gt;t2&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
        &lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;CreatedAt&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;ID&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;NewStatus&lt;/span&gt;
            &lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt; &lt;span class="o"&gt;+&lt;/span&gt; &lt;span class="k"&gt;SUM&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;CASE&lt;/span&gt;
                &lt;span class="k"&gt;WHEN&lt;/span&gt; &lt;span class="n"&gt;NewStatus&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="s1"&gt;'ConfirmationStarted'&lt;/span&gt; &lt;span class="k"&gt;THEN&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt;
                &lt;span class="k"&gt;ELSE&lt;/span&gt; &lt;span class="mi"&gt;0&lt;/span&gt;
            &lt;span class="k"&gt;END&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="n"&gt;OVER&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;PARTITION&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="n"&gt;ID&lt;/span&gt; &lt;span class="k"&gt;ORDER&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="n"&gt;ID&lt;/span&gt; &lt;span class="k"&gt;ASC&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;CreatedAt&lt;/span&gt; &lt;span class="k"&gt;ASC&lt;/span&gt; &lt;span class="k"&gt;ROWS&lt;/span&gt; &lt;span class="n"&gt;UNBOUNDED&lt;/span&gt; &lt;span class="k"&gt;PRECEDING&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;seg&lt;/span&gt;
        &lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;mytable&lt;/span&gt;
    &lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;ID&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="k"&gt;MAX&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;CreatedAt&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;CreatedAt&lt;/span&gt;
&lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
    &lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;CreatedAt&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;ID&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;NewStatus&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;seg&lt;/span&gt;
    &lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
        &lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;CreatedAt&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;ID&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;NewStatus&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;seg&lt;/span&gt;
        &lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
            &lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;CreatedAt&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;ID&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;NewStatus&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;seg&lt;/span&gt;
            &lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;t2&lt;/span&gt;
            &lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="n"&gt;NewStatus&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="s1"&gt;'Closed'&lt;/span&gt;
        &lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="n"&gt;t_1&lt;/span&gt;
        &lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="n"&gt;seg&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt;
    &lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="n"&gt;t_2&lt;/span&gt;
    &lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="n"&gt;ID&lt;/span&gt; &lt;span class="k"&gt;IN&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
        &lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;ID&lt;/span&gt;
        &lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
            &lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;ID&lt;/span&gt;
            &lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;t2&lt;/span&gt;
            &lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="n"&gt;NewStatus&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="s1"&gt;'Closed'&lt;/span&gt;
                &lt;span class="k"&gt;AND&lt;/span&gt; &lt;span class="n"&gt;seg&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt;
        &lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="n"&gt;t_3&lt;/span&gt;
        &lt;span class="k"&gt;GROUP&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="n"&gt;ID&lt;/span&gt;
    &lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="n"&gt;t_4&lt;/span&gt;
&lt;span class="k"&gt;GROUP&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="n"&gt;ID&lt;/span&gt;
&lt;span class="k"&gt;ORDER&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="n"&gt;ID&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;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.&lt;/p&gt;

&lt;p&gt;To restore determinism, planning needs to be separated from generation.&lt;/p&gt;

&lt;p&gt;Architecture overview: separating planning from generation&lt;/p&gt;

&lt;p&gt;Break down the one-shot Prompt→SQL black box into a Prompt→Workflow→SQL white box, with the Workflow as the contract.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fp5oyoml4kzw65do2hi8m.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fp5oyoml4kzw65do2hi8m.png" alt="Architecture overview" width="799" height="450"&gt;&lt;/a&gt;&lt;br&gt;
The three components:&lt;/p&gt;

&lt;p&gt;Planner: A person or an LLM produces a set of natural-language steps, breaking a plain-language requirement down into a sequence.&lt;/p&gt;

&lt;p&gt;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.&lt;/p&gt;

&lt;p&gt;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.&lt;/p&gt;

&lt;p&gt;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.&lt;/p&gt;

&lt;p&gt;SQLazy is the concrete implementation of this architecture: The LLM plans steps, and the compiler generates SQL deterministic.&lt;/p&gt;

&lt;p&gt;Key design elements&lt;/p&gt;

&lt;p&gt;Three key design elements underpin SQLazy’s determinism and auditability.&lt;/p&gt;

&lt;p&gt;Design 1: DSL-based workflow and step-by-step debugging&lt;/p&gt;

&lt;p&gt;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.&lt;/p&gt;

&lt;p&gt;Design 2: Deterministic compilation&lt;/p&gt;

&lt;p&gt;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.&lt;/p&gt;

&lt;p&gt;Design 3: Change audit and version control&lt;/p&gt;

&lt;p&gt;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.&lt;/p&gt;

&lt;p&gt;Example validation and scope of applicability&lt;/p&gt;

&lt;p&gt;Let’s validate the SQLazy approach using the example above.&lt;/p&gt;

&lt;p&gt;Source data mytable:&lt;/p&gt;

&lt;p&gt;CreatedAt ID NewStatus&lt;/p&gt;

&lt;p&gt;2022-05-25 23:17:44 147 Active&lt;/p&gt;

&lt;p&gt;2022-05-28 05:59:02 147 Closed&lt;/p&gt;

&lt;p&gt;2022-06-18 05:59:01 147 Closed&lt;/p&gt;

&lt;p&gt;2022-06-25 05:59:01 147 Closed&lt;/p&gt;

&lt;p&gt;2022-07-13 00:02:47 147 ConfirmationStarted&lt;/p&gt;

&lt;p&gt;2022-08-25 05:59:01 147 Closed&lt;/p&gt;

&lt;p&gt;2023-04-29 05:59:02 1645 Closed&lt;/p&gt;

&lt;p&gt;2023-05-08 14:53:34 1645 ConfirmationStarted&lt;/p&gt;

&lt;p&gt;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.&lt;/p&gt;

&lt;p&gt;In SQLazy, the same logic is expressed as an ordered workflow:&lt;/p&gt;

&lt;p&gt;Name Anchor Statement&lt;/p&gt;

&lt;p&gt;t1 mytable sort ID, CreatedAt asc&lt;/p&gt;

&lt;p&gt;t2&lt;/p&gt;

&lt;p&gt;segment condition (NewStatus = "ConfirmationStarted") partition ID as seg&lt;/p&gt;

&lt;p&gt;t3&lt;/p&gt;

&lt;p&gt;filter (NewStatus = "Closed" and seg = 1)&lt;/p&gt;

&lt;p&gt;t4&lt;/p&gt;

&lt;p&gt;summarize max CreatedAt as CreatedAt; group ID&lt;/p&gt;

&lt;p&gt;Run this example onlin: &lt;a href="https://www.sqlazy.com/?36K" rel="noopener noreferrer"&gt;https://www.sqlazy.com/?36K&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;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.&lt;/p&gt;

&lt;p&gt;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.&lt;/p&gt;

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

&lt;p&gt;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.&lt;/p&gt;

&lt;p&gt;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:&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fy5oysxkwrlsac59vkfjf.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fy5oysxkwrlsac59vkfjf.png" alt="Image descriptionArchitecture overview" width="800" height="427"&gt;&lt;/a&gt;&lt;/p&gt;

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

&lt;p&gt;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.&lt;/p&gt;

&lt;p&gt;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:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;WITH&lt;/span&gt; &lt;span class="n"&gt;t2&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
        &lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;CreatedAt&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;ID&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;NewStatus&lt;/span&gt;
            &lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt; &lt;span class="o"&gt;+&lt;/span&gt; &lt;span class="k"&gt;SUM&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;CASE&lt;/span&gt;
                &lt;span class="k"&gt;WHEN&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;NewStatus&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="s1"&gt;'ConfirmationStarted'&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;THEN&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt;
                &lt;span class="k"&gt;ELSE&lt;/span&gt; &lt;span class="mi"&gt;0&lt;/span&gt;
            &lt;span class="k"&gt;END&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="n"&gt;OVER&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;PARTITION&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="n"&gt;ID&lt;/span&gt; &lt;span class="k"&gt;ORDER&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="n"&gt;ID&lt;/span&gt; &lt;span class="k"&gt;ASC&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;CreatedAt&lt;/span&gt; &lt;span class="k"&gt;ASC&lt;/span&gt; &lt;span class="k"&gt;ROWS&lt;/span&gt; &lt;span class="n"&gt;UNBOUNDED&lt;/span&gt; &lt;span class="k"&gt;PRECEDING&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;seg&lt;/span&gt;
        &lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;mytable&lt;/span&gt;
    &lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;ID&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="k"&gt;MAX&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;CreatedAt&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;CreatedAt&lt;/span&gt;
&lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
    &lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;CreatedAt&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;ID&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;NewStatus&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;seg&lt;/span&gt;
    &lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;t2&lt;/span&gt;
    &lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;NewStatus&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="s1"&gt;'Closed'&lt;/span&gt;
        &lt;span class="k"&gt;AND&lt;/span&gt; &lt;span class="n"&gt;seg&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="n"&gt;t_3&lt;/span&gt;
&lt;span class="k"&gt;GROUP&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="n"&gt;ID&lt;/span&gt;
&lt;span class="k"&gt;ORDER&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="n"&gt;ID&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;The same workflow produces exactly the same SQL every time it is compiled. Switching SQL dialects requires no changes to the workflow.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F1ril6dis64sipvplaagz.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F1ril6dis64sipvplaagz.png" alt=" " width="800" height="440"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;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.&lt;/p&gt;

&lt;p&gt;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.&lt;/p&gt;

&lt;p&gt;AI writes the logic. A compiler writes the SQL.&lt;/p&gt;

&lt;p&gt;Online trial: &lt;a href="https://sqlazy.com/" rel="noopener noreferrer"&gt;sqlazy.com&lt;/a&gt; (Free to use, no sign-up required. Playground runs locally in your browser).&lt;/p&gt;

&lt;p&gt;Open-source examples: &lt;a href="https://github.com/SPLWare/SQLazy/tree/master/examples" rel="noopener noreferrer"&gt;github.com/SPLWare/SQLazy/examples&lt;/a&gt;&lt;/p&gt;

</description>
      <category>ai</category>
      <category>database</category>
      <category>llm</category>
      <category>sql</category>
    </item>
    <item>
      <title>Convert Time Columns into Rows and Pivot into a New Cross Table</title>
      <dc:creator>Judy</dc:creator>
      <pubDate>Thu, 22 Oct 2026 07:22:57 +0000</pubDate>
      <link>https://dev.to/esproc_spl/convert-time-columns-into-rows-and-pivot-into-a-new-cross-table-3im2</link>
      <guid>https://dev.to/esproc_spl/convert-time-columns-into-rows-and-pivot-into-a-new-cross-table-3im2</guid>
      <description>&lt;h2&gt;
  
  
  Problem Description
&lt;/h2&gt;

&lt;p&gt;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.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Source Data&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fpmhl64om8wztm4wmdnfl.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fpmhl64om8wztm4wmdnfl.png" alt="Source Data" width="799" height="224"&gt;&lt;/a&gt;&lt;br&gt;
&lt;strong&gt;Expected Result&lt;/strong&gt;&lt;br&gt;
The column header is the combination of PartNum and ID, the row header is the time point, and the cells hold the action names:&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fosonmo3y8tbm7glklk4x.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fosonmo3y8tbm7glklk4x.png" alt="action names" width="800" height="487"&gt;&lt;/a&gt;&lt;br&gt;
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.&lt;/p&gt;

&lt;p&gt;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.&lt;/p&gt;

&lt;p&gt;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.&lt;/p&gt;
&lt;h2&gt;
  
  
  SQLazy Step-by-Step Implementation
&lt;/h2&gt;

&lt;p&gt;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.&lt;/p&gt;

&lt;p&gt;[&lt;a href="https://www.sqlazy.com/?2W8" rel="noopener noreferrer"&gt;Click to run this example online&lt;/a&gt;]&lt;/p&gt;

&lt;p&gt;The steps are explained below.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fbjx87v9ud40e0836j4nt.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fbjx87v9ud40e0836j4nt.png" alt="steps are explained below.&lt;br&gt;
" width="800" height="329"&gt;&lt;/a&gt;&lt;br&gt;
Step 1: Turn the three time columns into rows&lt;/p&gt;

&lt;p&gt;pivot group ID, PartNum; name action_type; value t_stamp; inverse&lt;/p&gt;

&lt;p&gt;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.&lt;/p&gt;

&lt;p&gt;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.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F6ak8k7uvb4fluznm0r61.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F6ak8k7uvb4fluznm0r61.png" alt="this action" width="800" height="406"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;Step 2: Filter out the empty time points&lt;br&gt;
filter (t_stamp notnull)&lt;br&gt;
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 filter action.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F6jj2ryr45q878mssjnlr.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F6jj2ryr45q878mssjnlr.png" alt="parentheses" width="799" height="356"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;Step 3: Replace the action names with the short names the problem asks for&lt;br&gt;
compute if(action_type = “EnteredOn” then “ENTERED”, action_type = “PickedTime” then “PICKED”, action_type = “DeliveredTime” then “DELIVERED”) as status&lt;br&gt;
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.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fnmi2iaav3id4m3y9ya13.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fnmi2iaav3id4m3y9ya13.png" alt="kind of mapping" width="799" height="350"&gt;&lt;/a&gt;&lt;br&gt;
Step 4: Build the column names of the new table&lt;br&gt;
compute concat(PartNum, “_”, ID) as col_name&lt;br&gt;
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.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Ft1dj16ho2gremeklunx4.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Ft1dj16ho2gremeklunx4.png" alt="cannot hard-code them" width="800" height="334"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;Step 5: Keep only the three columns that are needed&lt;br&gt;
derive t_stamp, col_name, status&lt;br&gt;
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.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F6yw288ys8epvbp2910b3.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F6yw288ys8epvbp2910b3.png" alt="ction name" width="800" height="348"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;Step 6: Turn the rows into columns&lt;br&gt;
pivot group t_stamp; name col_name; value status&lt;br&gt;
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.&lt;br&gt;
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.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fjwbze3uggw0a8to7ui8c.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fjwbze3uggw0a8to7ui8c.png" alt="group is enough" width="800" height="345"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Generated SQL&lt;/strong&gt;&lt;br&gt;
After confirming the logic of the above 6 steps, the SQLazy compiler automatically generates native SQL (MySQL syntax used here):&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;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
    ),
    t5 AS (
        SELECT t_stamp
            , concat_ws('', PartNum, '_', ID) AS col_name
            , CASE
                WHEN action_type = 'EnteredOn' THEN 'ENTERED'
                WHEN action_type = 'PickedTime' THEN 'PICKED'
                WHEN action_type = 'DeliveredTime' THEN 'DELIVERED'
                ELSE NULL
            END AS status
        FROM t1
        WHERE t_stamp IS NOT NULL
    ),
    t5_2 AS (
        SELECT t_stamp, col_name, status
            , FIRST_VALUE(status) OVER (PARTITION BY t_stamp, col_name ORDER BY NULL) AS FIRST_1
        FROM t5
    )
SELECT t_stamp
    , MIN(CASE
        WHEN col_name = '50A_100' THEN FIRST_1
    END) AS `50A_100`
    , MIN(CASE
        WHEN col_name = '50A_171' THEN FIRST_1
    END) AS `50A_171`
    , MIN(CASE
        WHEN col_name = '60B_125' THEN FIRST_1
    END) AS `60B_125`
    , MIN(CASE
        WHEN col_name = '70B_211' THEN FIRST_1
    END) AS `70B_211`
FROM t5_2
GROUP BY t_stamp
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;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.&lt;/p&gt;

&lt;p&gt;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.&lt;/p&gt;

&lt;p&gt;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.&lt;/p&gt;

&lt;p&gt;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.&lt;br&gt;
Official Links&lt;br&gt;
SQLazy Online Experience: &lt;a href="https://sqlazy.com/" rel="noopener noreferrer"&gt;sqlazy.com&lt;/a&gt; (free, no registration required)&lt;/p&gt;

&lt;p&gt;SQLazy Repository: &lt;a href="https://github.com/SPLWare/SQLazy" rel="noopener noreferrer"&gt;github.com/SPLWare/SQLazy&lt;/a&gt;&lt;/p&gt;

</description>
      <category>ai</category>
      <category>programming</category>
      <category>devops</category>
      <category>sql</category>
    </item>
    <item>
      <title>SQLazy: Find Strings with 5 or More Consecutive Alphabetical Letters</title>
      <dc:creator>Judy</dc:creator>
      <pubDate>Tue, 20 Oct 2026 04:03:36 +0000</pubDate>
      <link>https://dev.to/esproc_spl/sqlazy-find-strings-with-5-or-more-consecutive-alphabetical-letters-1aic</link>
      <guid>https://dev.to/esproc_spl/sqlazy-find-strings-with-5-or-more-consecutive-alphabetical-letters-1aic</guid>
      <description>&lt;h2&gt;
  
  
  Problem Description
&lt;/h2&gt;

&lt;p&gt;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 &amp;gt;= previous one), and keep rows where the length &amp;gt;= 5. Non-letter characters must be excluded.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Source Data&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F9989rm76bzsrr9eerbcn.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F9989rm76bzsrr9eerbcn.png" alt="Source Data" width="800" height="270"&gt;&lt;/a&gt;&lt;br&gt;
&lt;strong&gt;Expected Result&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F068shoxs29wagewhfgyg.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F068shoxs29wagewhfgyg.png" alt="Expected Result" width="799" height="118"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;Two qualifying strings as examples:&lt;/p&gt;

&lt;p&gt;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 &amp;gt;= 5 is sufficient to qualify.&lt;/p&gt;

&lt;p&gt;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 &amp;gt;= 5, qualifying.&lt;/p&gt;
&lt;h2&gt;
  
  
  SQLazy Step-by-step Implementation
&lt;/h2&gt;

&lt;p&gt;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 &amp;gt;= 5. This pattern of character expansion, segmentation, and aggregation filtering is a typical SQLazy paradigm for string sequence problems.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fzxh6ordvohz63lbx0dr2.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fzxh6ordvohz63lbx0dr2.png" alt="SQLazy Step-by-step Implementation" width="800" height="343"&gt;&lt;/a&gt;&lt;br&gt;
【&lt;a href="https://www.sqlazy.com/?5bc" rel="noopener noreferrer"&gt;Online operation case&lt;/a&gt;】&lt;/p&gt;

&lt;p&gt;The steps are explained below.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Step 1: Expand rows by max string length&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;expand 50 as Position&lt;/p&gt;

&lt;p&gt;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.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fw9qma8dotc7rx6sa2s6d.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fw9qma8dotc7rx6sa2s6d.png" alt="expand 50 as Position" width="800" height="614"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Step 2: Extract character at each position&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;compute mid(VALUE, Position, 1) as Ch&lt;/p&gt;

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

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fszmsx5zowjz2sp8lda42.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fszmsx5zowjz2sp8lda42.png" alt="compute mid" width="800" height="602"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Step 3: Filter out non-letter characters&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;filter (Chisalpha)&lt;/p&gt;

&lt;p&gt;Use the isalpha function to keep only letters (A-Z, a-z), removing empty characters, digits, and symbols.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fkl1zsdzara2jdshwpxii.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fkl1zsdzara2jdshwpxii.png" alt="filter" width="800" height="621"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Step 4: Detect ascending breaks (core)&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;segment Ch; not_up; partition VALUE; as GrpNo&lt;/p&gt;

&lt;p&gt;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.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Step 5: Count characters per group&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;summarize Ch count as GrpSize; group VALUE, GrpNo&lt;/p&gt;

&lt;p&gt;Group by VALUE and GrpNo, count the number of letters in each group as GrpSize.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F69cczx5leg28lk74tcdx.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F69cczx5leg28lk74tcdx.png" alt="Count characters" width="800" height="606"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F0iioblajn49mp9td1a1o.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F0iioblajn49mp9td1a1o.png" alt="summarize" width="800" height="596"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Step 6: Get max group length per string&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;summarize GrpSize max as MaxRun; group VALUE&lt;/p&gt;

&lt;p&gt;Group by VALUE, take the maximum GrpSize to get the longest consecutive ascending segment length MaxRun for each string.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fzhcuxw79wjrz0pz5lvhv.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fzhcuxw79wjrz0pz5lvhv.png" alt="summarize GrpSize" width="800" height="438"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Step 7: Filter strings with length &amp;gt;= 5&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;filter (MaxRun &amp;gt;= 5)&lt;/p&gt;

&lt;p&gt;Keep rows where MaxRun &amp;gt;= 5.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fsrs22dg6bfhrlzr9hscp.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fsrs22dg6bfhrlzr9hscp.png" alt="filter" width="800" height="261"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Step 8: Keep final result columns&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;Generated SQL&lt;br&gt;
After confirming the above steps, the SQLazy compiler automatically generates native SQL (Oracle syntax):&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;WITH&lt;/span&gt; &lt;span class="n"&gt;TEMP_TABLE__2&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
    &lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;col_1&lt;/span&gt; &lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;DUAL&lt;/span&gt;
    &lt;span class="k"&gt;UNION&lt;/span&gt; &lt;span class="k"&gt;ALL&lt;/span&gt; &lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;col_1&lt;/span&gt; &lt;span class="o"&gt;+&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt; &lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;TEMP_TABLE__2&lt;/span&gt; &lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="n"&gt;col_1&lt;/span&gt; &lt;span class="o"&gt;&amp;lt;&lt;/span&gt; &lt;span class="mi"&gt;50&lt;/span&gt;
&lt;span class="p"&gt;),&lt;/span&gt; &lt;span class="n"&gt;t1&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
    &lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="k"&gt;table_name&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;VALUE&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;col_1&lt;/span&gt;
    &lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="k"&gt;table_name&lt;/span&gt; &lt;span class="k"&gt;CROSS&lt;/span&gt; &lt;span class="k"&gt;JOIN&lt;/span&gt; &lt;span class="n"&gt;TEMP_TABLE__2&lt;/span&gt;
&lt;span class="p"&gt;),&lt;/span&gt; &lt;span class="n"&gt;t2&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
    &lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;VALUE&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="k"&gt;Position&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;SUBSTR&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;VALUE&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="k"&gt;Position&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;Ch&lt;/span&gt; &lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;t1&lt;/span&gt;
&lt;span class="p"&gt;),&lt;/span&gt; &lt;span class="n"&gt;t3&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
    &lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;VALUE&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="k"&gt;Position&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;Ch&lt;/span&gt; &lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;t2&lt;/span&gt; &lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="n"&gt;REGEXP_LIKE&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;Ch&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s1"&gt;'^[A-Za-z]+$'&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="p"&gt;),&lt;/span&gt; &lt;span class="n"&gt;t5&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
    &lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;VALUE&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;GrpNo&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="k"&gt;COUNT&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;Ch&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;GrpSize&lt;/span&gt;
    &lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
        &lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;VALUE&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="k"&gt;Position&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;Ch&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
            &lt;span class="k"&gt;SUM&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;CASE&lt;/span&gt; &lt;span class="k"&gt;WHEN&lt;/span&gt; &lt;span class="n"&gt;Ch&lt;/span&gt; &lt;span class="o"&gt;&amp;lt;=&lt;/span&gt; &lt;span class="n"&gt;col__5&lt;/span&gt; &lt;span class="k"&gt;THEN&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt; &lt;span class="k"&gt;ELSE&lt;/span&gt; &lt;span class="mi"&gt;0&lt;/span&gt; &lt;span class="k"&gt;END&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
                &lt;span class="n"&gt;OVER&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;PARTITION&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="n"&gt;VALUE&lt;/span&gt; &lt;span class="k"&gt;ORDER&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="n"&gt;VALUE&lt;/span&gt;
                &lt;span class="k"&gt;ROWS&lt;/span&gt; &lt;span class="n"&gt;UNBOUNDED&lt;/span&gt; &lt;span class="k"&gt;PRECEDING&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="o"&gt;+&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;GrpNo&lt;/span&gt;
        &lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;t3&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="o"&gt;*&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;LAG&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;Ch&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;&lt;span class="mi"&gt;1&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
            &lt;span class="n"&gt;OVER&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;PARTITION&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="n"&gt;VALUE&lt;/span&gt; &lt;span class="k"&gt;ORDER&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="n"&gt;VALUE&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;col__5&lt;/span&gt; &lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;t3&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="n"&gt;sub&lt;/span&gt;
    &lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="n"&gt;t4&lt;/span&gt; &lt;span class="k"&gt;GROUP&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="n"&gt;VALUE&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;GrpNo&lt;/span&gt;
&lt;span class="p"&gt;),&lt;/span&gt; &lt;span class="n"&gt;t6&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
    &lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;VALUE&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="k"&gt;MAX&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;GrpSize&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;MaxRun&lt;/span&gt;
    &lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;t5&lt;/span&gt; &lt;span class="k"&gt;GROUP&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="n"&gt;VALUE&lt;/span&gt;
&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;VALUE&lt;/span&gt; &lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;t6&lt;/span&gt; &lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="n"&gt;MaxRun&lt;/span&gt; &lt;span class="o"&gt;&amp;gt;=&lt;/span&gt; &lt;span class="mi"&gt;5&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;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.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Official Links&lt;/strong&gt;&lt;br&gt;
SQLazy Online Experience: &lt;a href="https://sqlazy.com/" rel="noopener noreferrer"&gt;sqlazy.com&lt;/a&gt; (free, no registration required)&lt;br&gt;
SQLazy Repository: &lt;a href="https://github.com/SPLWare/SQLazy" rel="noopener noreferrer"&gt;github.com/SPLWare/SQLazy&lt;/a&gt;&lt;/p&gt;

</description>
      <category>algorithms</category>
      <category>database</category>
      <category>programming</category>
      <category>sql</category>
    </item>
    <item>
      <title>Flag Office Visits Within 6 Months After Surgery</title>
      <dc:creator>Judy</dc:creator>
      <pubDate>Thu, 15 Oct 2026 08:11:17 +0000</pubDate>
      <link>https://dev.to/esproc_spl/flag-office-visits-within-6-months-after-surgery-hgg</link>
      <guid>https://dev.to/esproc_spl/flag-office-visits-within-6-months-after-surgery-hgg</guid>
      <description>&lt;h2&gt;
  
  
  Problem Description
&lt;/h2&gt;

&lt;p&gt;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.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Source Data&lt;/strong&gt;&lt;br&gt;
tb_healthy table:&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fgq4d1c3kyjltciz25d75.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fgq4d1c3kyjltciz25d75.png" alt=" Source Data" width="800" height="412"&gt;&lt;/a&gt;&lt;br&gt;
&lt;strong&gt;Expected Result&lt;/strong&gt;&lt;br&gt;
Office_Visit means the patient has consultation records, SX_past_6mo means the patient had a consultation within 6 months after an operation:&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fzisbkza9wej9ht0dveo7.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fzisbkza9wej9ht0dveo7.png" alt="operation" width="800" height="231"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;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.&lt;/p&gt;

&lt;p&gt;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.&lt;/p&gt;
&lt;h2&gt;
  
  
  SQLazy Step-by-Step Implementation
&lt;/h2&gt;

&lt;p&gt;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.&lt;/p&gt;

&lt;p&gt;[&lt;a href="https://www.sqlazy.com/?4Pt" rel="noopener noreferrer"&gt;Click to run this example online&lt;/a&gt;]&lt;/p&gt;

&lt;p&gt;The steps are explained below.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fiapzhqnommm5kdshfo9o.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fiapzhqnommm5kdshfo9o.png" alt="steps" width="800" height="417"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;Step 1: Convert the string dates into date values&lt;/p&gt;

&lt;p&gt;compute date(Visit_Date; format "M/d/yyyy") as dt&lt;/p&gt;

&lt;p&gt;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.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fe3gtn9lxbhjsopm837ri.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fe3gtn9lxbhjsopm837ri.png" alt="Visit_Date " width="799" height="405"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;Step 2: Filter one copy of the operation records and one of the consultation records&lt;br&gt;
filter Visit_code=“Surgery”&lt;br&gt;
filter Visit_code=“Office”&lt;br&gt;
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.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F9cvqwqa0pngk9yck0g6y.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F9cvqwqa0pngk9yck0g6y.png" alt="in SQL" width="800" height="519"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;Step 3: Answer “has the patient ever had a consultation” first&lt;br&gt;
summarize max if(Visit_code=“Office” then 1 else 0) as Office_Visit; group Id&lt;br&gt;
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.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fds6dfep02gtg7as0d643.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fds6dfep02gtg7as0d643.png" alt="Office_Visit" width="800" height="262"&gt;&lt;/a&gt;&lt;br&gt;
Step 4: Attach the consultations within 6 months to the operations&lt;br&gt;
join Id; with t_off; Id; take dt as off_dt; inner; condition ((dt&amp;gt;=dt@) and (dt&amp;lt;=(dt@ elapse 6 month)))&lt;br&gt;
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.&lt;br&gt;
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).&lt;br&gt;
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.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fmhhhpfu76uop4ediidqo.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fmhhhpfu76uop4ediidqo.png" alt="paired records" width="800" height="177"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;Step 5: Summarize the paired records into a flag&lt;br&gt;
summarize max 1 as SX_past_6mo; group Id&lt;br&gt;
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.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fduw4tgg1qjzh7pqlj329.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fduw4tgg1qjzh7pqlj329.png" alt="particular value" width="800" height="172"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;Step 6: Merge the two results&lt;br&gt;
join Id; with t_sx_yes; Id; take SX_past_6mo&lt;br&gt;
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.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fa81hri8b4l642l1ncpy4.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fa81hri8b4l642l1ncpy4.png" alt="left join" width="800" height="260"&gt;&lt;/a&gt;&lt;br&gt;
Step 7: Replace the empty values with 0&lt;br&gt;
compute nvl(SX_past_6mo,0) as SX_past&lt;br&gt;
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.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fyzbh5mk407lka79fhaki.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fyzbh5mk407lka79fhaki.png" alt="operation SX_past" width="799" height="262"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;The last step picks the columns to output.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Generated SQL&lt;/strong&gt;&lt;br&gt;
After confirming the logic of the above 7 steps, the SQLazy compiler automatically generates native SQL (Oracle syntax used here):&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;WITH&lt;/span&gt; &lt;span class="n"&gt;t1&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
    &lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;Id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;Visit_Date&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;Visit_code&lt;/span&gt;
      &lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;TO_DATE&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;Visit_Date&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s1"&gt;'M/d/yyyy'&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;dt&lt;/span&gt;
    &lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;tb_healthy&lt;/span&gt;
  &lt;span class="p"&gt;),&lt;/span&gt;
  &lt;span class="n"&gt;t_results&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
    &lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;Id&lt;/span&gt;
      &lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="k"&gt;MAX&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;CASE&lt;/span&gt;
        &lt;span class="k"&gt;WHEN&lt;/span&gt; &lt;span class="n"&gt;Visit_code&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="s1"&gt;'Office'&lt;/span&gt; &lt;span class="k"&gt;THEN&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt;
        &lt;span class="k"&gt;ELSE&lt;/span&gt; &lt;span class="mi"&gt;0&lt;/span&gt;
      &lt;span class="k"&gt;END&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;Office_Visit&lt;/span&gt;
    &lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;t1&lt;/span&gt;
    &lt;span class="k"&gt;GROUP&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="n"&gt;Id&lt;/span&gt;
  &lt;span class="p"&gt;),&lt;/span&gt;
  &lt;span class="n"&gt;t_off&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
    &lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;Id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;Visit_Date&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;Visit_code&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;dt&lt;/span&gt;
    &lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;t1&lt;/span&gt;
    &lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="n"&gt;Visit_code&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="s1"&gt;'Office'&lt;/span&gt;
  &lt;span class="p"&gt;),&lt;/span&gt;
  &lt;span class="n"&gt;t_sx_yes&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
    &lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;Id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="k"&gt;MAX&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="mi"&gt;1&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;SX_past_6mo&lt;/span&gt;
    &lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
      &lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;t_sx&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;Id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;t_sx&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;Visit_Date&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;t_sx&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;Visit_code&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;t_sx&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;dt&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;t_off&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;dt&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;off_dt&lt;/span&gt;
      &lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;t1&lt;/span&gt;
        &lt;span class="k"&gt;INNER&lt;/span&gt; &lt;span class="k"&gt;JOIN&lt;/span&gt; &lt;span class="n"&gt;t_off&lt;/span&gt;
        &lt;span class="k"&gt;ON&lt;/span&gt; &lt;span class="n"&gt;t_sx&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;Id&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;t_off&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;Id&lt;/span&gt;
          &lt;span class="k"&gt;AND&lt;/span&gt; &lt;span class="p"&gt;((&lt;/span&gt;&lt;span class="n"&gt;t_off&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;dt&lt;/span&gt; &lt;span class="o"&gt;&amp;gt;=&lt;/span&gt; &lt;span class="n"&gt;t_sx&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;dt&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
            &lt;span class="k"&gt;AND&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;t_off&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;dt&lt;/span&gt; &lt;span class="o"&gt;&amp;lt;=&lt;/span&gt; &lt;span class="n"&gt;ADDMONTHS&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;t_sx&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;dt&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="mi"&gt;6&lt;/span&gt;&lt;span class="p"&gt;)))&lt;/span&gt;
      &lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="n"&gt;t_sx&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;Visit_code&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="s1"&gt;'Surgery'&lt;/span&gt;
    &lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="n"&gt;t_2&lt;/span&gt;
    &lt;span class="k"&gt;GROUP&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="n"&gt;Id&lt;/span&gt;
  &lt;span class="p"&gt;),&lt;/span&gt;
  &lt;span class="n"&gt;t_merged&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
    &lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;t_results&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;Id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;t_results&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;Office_Visit&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;t_sx_yes&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;SX_past_6mo&lt;/span&gt;
    &lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;t_results&lt;/span&gt;
      &lt;span class="k"&gt;LEFT&lt;/span&gt; &lt;span class="k"&gt;JOIN&lt;/span&gt; &lt;span class="n"&gt;t_sx_yes&lt;/span&gt; &lt;span class="k"&gt;ON&lt;/span&gt; &lt;span class="n"&gt;t_results&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;Id&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;t_sx_yes&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;Id&lt;/span&gt;
  &lt;span class="p"&gt;),&lt;/span&gt;
  &lt;span class="n"&gt;t_final&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
    &lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;Id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;Office_Visit&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;SX_past_6mo&lt;/span&gt;
      &lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;COALESCE&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;SX_past_6mo&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="mi"&gt;0&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;SX_past&lt;/span&gt;
    &lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;t_merged&lt;/span&gt;
  &lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;Id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;Office_Visit&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;SX_past&lt;/span&gt;
&lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;t_final&lt;/span&gt;
&lt;span class="k"&gt;ORDER&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="n"&gt;Id&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;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.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Official Links&lt;/strong&gt;&lt;br&gt;
SQLazy Online Experience: &lt;a href="https://sqlazy.com/" rel="noopener noreferrer"&gt;sqlazy.com&lt;/a&gt; (free, no registration required)&lt;/p&gt;

&lt;p&gt;SQLazy Repository:  &lt;a href="https://github.com/SPLWare/SQLazy" rel="noopener noreferrer"&gt;github.com/SPLWare/SQLazy&lt;/a&gt;&lt;/p&gt;

</description>
      <category>database</category>
      <category>programming</category>
      <category>sql</category>
    </item>
    <item>
      <title>Start and End Dates of the Longest Rising Period</title>
      <dc:creator>Judy</dc:creator>
      <pubDate>Thu, 08 Oct 2026 07:21:04 +0000</pubDate>
      <link>https://dev.to/esproc_spl/start-and-end-dates-of-the-longest-rising-period-3m6j</link>
      <guid>https://dev.to/esproc_spl/start-and-end-dates-of-the-longest-rising-period-3m6j</guid>
      <description>&lt;h2&gt;
  
  
  Problem Description
&lt;/h2&gt;

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

&lt;h2&gt;
  
  
  Source Data
&lt;/h2&gt;

&lt;p&gt;stock table (only the records of the target stock CODE = 100046 are listed, in ascending DT order):&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F0r9a42wns0il425wp5rh.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F0r9a42wns0il425wp5rh.png" alt="Source Data" width="800" height="492"&gt;&lt;/a&gt;&lt;/p&gt;

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

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

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

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

&lt;p&gt;&lt;a href="https://www.sqlazy.com/?4OY" rel="noopener noreferrer"&gt;[Click to run this example online]&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;The steps are explained below.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F9l2dhmfl8mi81kn805pk.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F9l2dhmfl8mi81kn805pk.png" alt="Step-by-Step" width="799" height="119"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;Step 1: Take the target stock&lt;/p&gt;

&lt;p&gt;filter CODE = 100046&lt;/p&gt;

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

&lt;p&gt;Step 2: Sort by date in ascending order&lt;/p&gt;

&lt;p&gt;sort DT asc&lt;/p&gt;

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

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fqnjyohz2uss72aoj0apu.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fqnjyohz2uss72aoj0apu.png" alt="sort DT asc" width="800" height="566"&gt;&lt;/a&gt;&lt;/p&gt;

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

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F20d98zo1qhqpxqqojeiv.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F20d98zo1qhqpxqqojeiv.png" alt="new interval" width="800" height="570"&gt;&lt;/a&gt;&lt;/p&gt;

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

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fuobzhshm8cr2p6zmn8wi.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fuobzhshm8cr2p6zmn8wi.png" alt="interval" width="800" height="513"&gt;&lt;/a&gt;&lt;/p&gt;

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

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fzx96quv529x68kl706nv.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fzx96quv529x68kl706nv.png" alt=" order." width="800" height="442"&gt;&lt;/a&gt;&lt;/p&gt;

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

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fspi1jlqf01s6djs9oho1.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fspi1jlqf01s6djs9oho1.png" alt=" before the aggregated" width="799" height="242"&gt;&lt;/a&gt;&lt;/p&gt;
&lt;h2&gt;
  
  
  Generated SQL
&lt;/h2&gt;

&lt;p&gt;After confirming the logic of the above 6 steps, SQLazy compiler automatically generates native SQL (MySQL syntax used here):&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;WITH&lt;/span&gt; &lt;span class="n"&gt;t2&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
  &lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;CODE&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;DT&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;CL&lt;/span&gt;
  &lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;stock&lt;/span&gt;
  &lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="n"&gt;CODE&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="mi"&gt;100046&lt;/span&gt;
&lt;span class="p"&gt;),&lt;/span&gt;
&lt;span class="n"&gt;t3&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
  &lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;CODE&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;DT&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;CL&lt;/span&gt;
    &lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="k"&gt;SUM&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;CASE&lt;/span&gt;
      &lt;span class="k"&gt;WHEN&lt;/span&gt; &lt;span class="n"&gt;CL&lt;/span&gt; &lt;span class="o"&gt;&amp;lt;&lt;/span&gt; &lt;span class="n"&gt;col__1&lt;/span&gt; &lt;span class="k"&gt;THEN&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt;
      &lt;span class="k"&gt;ELSE&lt;/span&gt; &lt;span class="mi"&gt;0&lt;/span&gt;
    &lt;span class="k"&gt;END&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="n"&gt;OVER&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;ORDER&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="k"&gt;CASE&lt;/span&gt;
      &lt;span class="k"&gt;WHEN&lt;/span&gt; &lt;span class="n"&gt;DT&lt;/span&gt; &lt;span class="k"&gt;IS&lt;/span&gt; &lt;span class="k"&gt;NULL&lt;/span&gt; &lt;span class="k"&gt;THEN&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt;
      &lt;span class="k"&gt;ELSE&lt;/span&gt; &lt;span class="mi"&gt;0&lt;/span&gt;
    &lt;span class="k"&gt;END&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;DT&lt;/span&gt; &lt;span class="k"&gt;ASC&lt;/span&gt; &lt;span class="k"&gt;ROWS&lt;/span&gt; &lt;span class="n"&gt;UNBOUNDED&lt;/span&gt; &lt;span class="k"&gt;PRECEDING&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="o"&gt;+&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;NoRisingDays&lt;/span&gt;
  &lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
    &lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;t2&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="o"&gt;*&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;LAG&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;CL&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="n"&gt;OVER&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;ORDER&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="k"&gt;CASE&lt;/span&gt;
        &lt;span class="k"&gt;WHEN&lt;/span&gt; &lt;span class="n"&gt;DT&lt;/span&gt; &lt;span class="k"&gt;IS&lt;/span&gt; &lt;span class="k"&gt;NULL&lt;/span&gt; &lt;span class="k"&gt;THEN&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt;
        &lt;span class="k"&gt;ELSE&lt;/span&gt; &lt;span class="mi"&gt;0&lt;/span&gt;
      &lt;span class="k"&gt;END&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;DT&lt;/span&gt; &lt;span class="k"&gt;ASC&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;col__1&lt;/span&gt;
    &lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;t2&lt;/span&gt;
  &lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="n"&gt;sub__2&lt;/span&gt;
&lt;span class="p"&gt;),&lt;/span&gt;
&lt;span class="n"&gt;t4&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
  &lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;CODE&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;DT&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;CL&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;NoRisingDays&lt;/span&gt;
    &lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="k"&gt;COUNT&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;DT&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="n"&gt;OVER&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;PARTITION&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="n"&gt;NoRisingDays&lt;/span&gt; &lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;ContinuousDays&lt;/span&gt;
  &lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;t3&lt;/span&gt;
&lt;span class="p"&gt;),&lt;/span&gt;
&lt;span class="n"&gt;sub__6&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
  &lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;sub__5&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="o"&gt;*&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;RANK&lt;/span&gt;&lt;span class="p"&gt;()&lt;/span&gt; &lt;span class="n"&gt;OVER&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;ORDER&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="n"&gt;ContinuousDays&lt;/span&gt; &lt;span class="k"&gt;DESC&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;col_4&lt;/span&gt;
  &lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;t4&lt;/span&gt; &lt;span class="n"&gt;sub__5&lt;/span&gt;
&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;CODE&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;DT&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;CL&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;NoRisingDays&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;ContinuousDays&lt;/span&gt;
&lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;sub__6&lt;/span&gt;
&lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="n"&gt;col_4&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



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

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

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

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

&lt;h2&gt;
  
  
  Official Links
&lt;/h2&gt;

&lt;p&gt;SQLazy Online Experience: &lt;a href="https://sqlazy.com/" rel="noopener noreferrer"&gt;sqlazy.com &lt;/a&gt;(free, no registration required)&lt;/p&gt;

&lt;p&gt;SQLazy Repository: &lt;a href="https://github.com/SPLWare/SQLazy" rel="noopener noreferrer"&gt;github.com/SPLWare/SQLazy&lt;/a&gt;&lt;/p&gt;

</description>
      <category>algorithms</category>
      <category>computerscience</category>
      <category>database</category>
      <category>sql</category>
    </item>
    <item>
      <title>Your AI SQL Tool Doesn’t Have an “Undo Logic” Button – That’s the Real Problem</title>
      <dc:creator>Judy</dc:creator>
      <pubDate>Tue, 29 Sep 2026 07:19:07 +0000</pubDate>
      <link>https://dev.to/esproc_spl/your-ai-sql-tool-doesnt-have-an-undo-logic-button-thats-the-real-problem-4i6f</link>
      <guid>https://dev.to/esproc_spl/your-ai-sql-tool-doesnt-have-an-undo-logic-button-thats-the-real-problem-4i6f</guid>
      <description>&lt;p&gt;Have you ever had this experience: revising the prompt for an AI SQL tool through five or six versions, clicking Undo and rewriting countless times, and the result that finally comes out is still wrong.&lt;/p&gt;

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

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

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;WITH lagged AS (
  SELECT *, LAG(dt) OVER (PARTITION BY account_number ORDER BY dt) AS prev_dt
  FROM numEvents
), grouped AS (
  SELECT *,
    SUM(CASE WHEN TIMESTAMPDIFF(SECOND, prev_dt, dt) &amp;gt; 3600 THEN 1 ELSE 0 END)
    OVER (PARTITION BY account_number ORDER BY dt) AS grp
  FROM lagged
)
SELECT *, ROW_NUMBER() OVER (PARTITION BY account_number, grp ORDER BY dt) AS seq
FROM grouped
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



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

&lt;p&gt;In SQLazy, the same logic is divided into three clear, neat steps:&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fejgp69ddfah33o1qf9io.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fejgp69ddfah33o1qf9io.png" alt="neat steps" width="800" height="219"&gt;&lt;/a&gt;&lt;br&gt;
Each step corresponds to a specific business action. If the time interval is wrong, fix the interval; if the partition is wrong, fix the partition. The error is pinpointed precisely and fixed step by step. You don’t need to rewrite the whole thing from scratch, because each step of the logic comes with a built-in “revert node”.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fmoedlm2k19cvaytu7x7d.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fmoedlm2k19cvaytu7x7d.png" alt="revert node" width="800" height="428"&gt;&lt;/a&gt;&lt;br&gt;
In this process, AI is only responsible for translating logic at each step – turning natural-language input into SQLazy syntax. You can tell at a glance whether the translation is correct. Once confirmed, the compiler turns it into deterministic SQL.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fm0pigtpwamkiqrsgm9it.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fm0pigtpwamkiqrsgm9it.png" alt="deterministic SQL" width="800" height="428"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;AI writes the logic. A compiler writes the SQL.&lt;/p&gt;

&lt;p&gt;You see every step of the logic, so you can fix every drift precisely.&lt;/p&gt;

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

&lt;p&gt;GitHub repository: &lt;a href="https://github.com/SPLWare/SQLazy/tree/master/examples/time-window-analytics" rel="noopener noreferrer"&gt;https://github.com/SPLWare/SQLazy/tree/master/examples/time-window-analytics&lt;/a&gt;&lt;/p&gt;

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

&lt;p&gt;Try SQLazsy: sqlazy.com&lt;/p&gt;

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

</description>
      <category>ai</category>
      <category>programming</category>
      <category>devops</category>
      <category>sql</category>
    </item>
  </channel>
</rss>
