SQLazy Tool Converts Messy Swipe Logs into Paired Session Rows

A technique using the SQLazy query tool transforms raw building swipe logs—where each entry or exit is recorded as a separate timestamped row—into single-row sessions pairing each IN and OUT event. The source data is often messy, containing unpaired records and consecutive swipes in the same direction, making straightforward pairing unreliable. The approach works in three steps: sorting records by person, building, and timestamp; applying a conditional segmentation rule to detect session boundaries based on the previous action; and finally grouping and aggregating each segment into one row with separate IN and OUT columns. Unpaired records are preserved as individual rows with NULL filling the missing side, ensuring no data is lost. The method relies on SQLazy's relative position syntax to reference the previous row's value, replacing the need for explicit SQL window functions like LAG.