SQLazy: Get the Latest Closed Before ConfirmationStarted

Problem Description

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

Source Data

Expected Result

Take ID=147 as an example:

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

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

SQLazy Step-by-Step Implementation

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

[Click to run this example online]

The steps are explained below.

Step 1: Sort by ID and time in ascending order

sort ID, CreatedAt asc

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

Step 2: Start a new segment when ConfirmationStarted is encountered

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

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

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

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

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

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

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

SQLazy Repository: github.com/SPLWare/SQLazy