Problem Description
Table userBuilding stores swipe logs for personnel entering and exiting buildings, with one record per timestamp and fields username, building, action (IN/OUT), and timestamp. Normally, records for the same person in the same building appear in pairs, IN followed by OUT. In practice, the data is messy: unpaired records and consecutive actions in the same direction occur. The task is to turn each pair of records for each person and each building into one row by pivoting rows to columns; unpaired records become separate rows, with NULL for the missing side, i.e., convert the vertical log into horizontal sessions in pairing order.
Source Data
Expected Result
Take user-3/building-1 as an example. The raw sequence is OUT, IN, IN, IN, OUT, OUT, OUT, which is split into 6 segments by the pairing rules: the first OUT stands alone, the next two INs each stand alone, the fourth IN pairs with the first OUT into one row, and the last two OUTs each stand alone. Consecutive actions in the same direction are never forced into a pair, ensuring correct session boundaries. In the 13-row result, this user accounts for 6 rows, which illustrates the logic.
SQLazy Step-by-Step Implementation
Core idea: First sort by username, building, and timestamp to arrange the log of the same person in the same building in time order; then use a conditional segment to detect session boundaries - start a new group when the previous record is OUT or the current record is IN, so each group contains at most one IN and at most one OUT; finally group by username, building, and seg, and use conditional max aggregation to collapse the IN time and OUT time within each group into one row, with NULL for unpaired sides.
[Click to run this example online]
The steps are explained below.
Step 1: Sort by person, building, and time
sort username, building, timestamp asc
Sort records of the same person in the same building by timestamp in ascending order to ensure subsequent pairing decisions follow time order. Using username and building as leading sort keys keeps the order within each partition consistent with the partition keys. Sorting is a prerequisite for the subsequent segment and summarize steps.
Step 2: Segment by pairing semantics to generate seg (core)
segment condition ((action[-1] = "OUT")or (action[-1] = "IN" and action = "IN")) partition username, building as seg
The most critical step is to express session boundaries with a conditional segment: start a new group when the previous record is OUT, or when the previous record is IN and the current is also IN. A previous OUT means the previous session is closed and a new one should start; consecutive INs mean multiple swipes for entry, and each additional IN starts a new group to avoid squeezing multiple INs into one session. partition username, building keeps different persons and buildings independent, each numbered with its own seg. The condition action[-1] is SQLazy's relative position syntax, equivalent to LAG(action,1), without manually writing window functions.
Step 3: Group by person, building, and segment number, then conditionally aggregate rows to columns
summarize condition (action = "IN") max timestamp as 'IN', condition (action = "OUT") max timestamp as 'OUT'; group username, building, seg
Group by username, building, and seg; each group contains at most one entry and one exit. Use conditional aggregation: when action = "IN", take max(timestamp) as the IN column, and when action = "OUT", take max(timestamp) as the OUT column. max and first are equivalent here because there is at most one record of each type per group; using conditional max naturally yields NULL on the other side for unpaired groups. Note that in the latest syntax the aggregation function (max) comes before the aggregated expression (timestamp), and grouping keys are specified via "group username, building, seg".
Step 4: Clean up the helper column
derive delete seg
Remove the auxiliary column seg produced by segment, keeping only the four columns username, building, IN, and OUT for the final result. The table is cleaner.
Generated SQL
After confirming the four steps above, the SQLazy compiler automatically generates native SQL (Oracle syntax here):
SELECT MAX(CASE
WHEN (action = 'OUT') THEN timestamp
ELSE NULL
END) AS "OUT"
, MAX(CASE
WHEN (action = 'IN') THEN timestamp
ELSE NULL
END) AS "IN"
, building, username
FROM (
SELECT username, building, action, timestamp
, 1 + SUM(CASE
WHEN (col__2 = 'OUT'
OR action = 'IN')
THEN 1
ELSE 0
END) OVER (PARTITION BY username, building ORDER BY username ASC, building ASC, timestamp ASC ROWS UNBOUNDED PRECEDING) AS seg
FROM (
SELECT t1.*, LAG(action) OVER (PARTITION BY username, building ORDER BY username ASC, building ASC, timestamp ASC) AS col__2
FROM t1
) sub__3
) t_4
GROUP BY username, building, seg
ORDER BY username, building, seg;
SQLazy lets you describe logic in business language instead of writing nested queries in SQL syntax. In this example, a single conditional segment expresses the business rule clearly: segment condition ((action[-1] = "OUT")or (action[-1] = "IN" and action = "IN"))partition username, building, i.e., "start a new session when the previous segment is closed or a consecutive swipe-in occurs". Writing this in SQL manually requires LAG to fetch the previous row, SUM OVER to accumulate segment numbers, two layers of subqueries to wrap window columns, and finally conditional aggregation MAX(CASE...) to pivot rows to columns, plus handling partition and ordering consistency. SQLazy compresses all of this into four steps - sort, segment, conditional summarize, and cleanup - each verifiable independently; relative positions and partitions are compiled into window functions, and conditional summarize handles NULL automatically.
Official Links
SQLazy Online Experience: sqlazy.com (free, no registration required)
SQLazy Repository: github.com/SPLWare/SQLazy
Top comments (1)
Nice example — this is exactly the kind of problem where the segment abstraction makes the intent much easier to read than the equivalent LAG + running SUM + conditional aggregation SQL. 👍
One thing I’d be careful about in production: the ordering is only deterministic if timestamp is unique within each username, building partition.
If two swipe events have the same timestamp, then:
ORDER BY username, building, timestamp
doesn’t guarantee which event comes first, and that can change the LAG(action) result and therefore the session boundaries.
I’d probably add a stable tie-breaker such as event_id or sequence_no:
sort username, building, timestamp, event_id asc
That would make the pairing semantics fully deterministic.
Also, I liked that the generated SQL uses an explicit ROWS UNBOUNDED PRECEDING frame — nice detail.
Good example overall. It shows the value of expressing the business rule directly instead of burying it inside several layers of window SQL. 😄