Problem description
A table stores multiple time intervals for each account, and the intervals may overlap.
Source data:
Take account A as an example:
The first three intervals (6/20-6/29, 6/25-7/25, 7/20-8/26) overlap and can be merged into 6/20 - 8/26;
The nonoverlapping interval 12/25 - 1/25;
The overlapping intervals (4/27-7/27, 6/25-7/14, 7/10-8/14) can be merged into 4/27-8/14;
The nonoverlapping interval 9/10-11/12.
Apply the same merging operations to account B: merge any intervals that overlap.
Step-by-step implementation with SQLazy
Key approach: Check whether the current interval’s start date is later than the maximum end date among all previous intervals. If it is, the current interval does not overlap with any of the previous intervals and a new group should be started; otherwise, merge it into the current group.
Step 1: Sort rows by account and the start date
sort account_id, start_date
Sort rows by account_id and start_date in ascending order, ensuring intervals within each account are processed in chronological order.
Step 3: Segment rows based on the condition and assign group numbers
segment condition start_date > prev_max as gid; partition account_id
Check each row in sequence: if start_date > prev_max, the current interval does not overlap with any of the previous intervals and a new group is started (gid+1); otherwise, the row is assigned to the current group.
Finally, remove the helper column gid.
Compile the steps into SQL
Once the above steps are complete and verified, SQLazy’s compiler can automatically generate the equivalent native SQL (using MySQL as an example):
WITH t2 AS (
SELECT
account_id,
start_date,
end_date,
MAX(end_date) OVER (
PARTITION BY account_id
ORDER BY
CASE WHEN account_id IS NULL THEN 1 ELSE 0 END,
account_id ASC,
CASE WHEN start_date IS NULL THEN 1 ELSE 0 END,
start_date ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
) AS prev_max
FROM
acc
),
t3 AS (
SELECT
account_id,
start_date,
end_date,
prev_max,
1 + SUM(
CASE
WHEN start_date > prev_max THEN 1
ELSE 0
END
) OVER (
PARTITION BY account_id
ORDER BY
CASE WHEN account_id IS NULL THEN 1 ELSE 0 END,
account_id ASC,
CASE WHEN start_date IS NULL THEN 1 ELSE 0 END,
start_date ASC
) AS gid
FROM
t2
)
SELECT
account_id,
MIN(start_date) AS start_date,
MAX(end_date) AS end_date
FROM
t3
GROUP BY
account_id,
gid
ORDER BY
account_id,
start_date;
You only need to verify the logic of each of the four steps – no need to understand or debug the SQL – and the compiler will generate the production-ready code.
Why SQLazy is more efficient
(Free to use, signup not required)
SQLazy project repository: github.com/SPLWare/SQLazy
SOCIAL SHARE CARD GENERATOR