Merge overlapping subscription periods

SQL data engineering interview problem. Difficulty: advanced. Pattern: Window Functions. About 20 minutes. Part of the Pro drill bank.

user_subscriptions tracks subscription contracts with columns user_id, start_date, and end_date. Merge all overlapping date ranges for each user into continuous coverage intervals defined by coverage_start and coverage_end. Return user_id, coverage_start, and coverage_end, ordered by user_id ascending and coverage_start ascending.

Requirements

• Output columns: user_id, coverage_start, coverage_end.
• Order by user_id ascending, coverage_start ascending.

Constraints

• start_date <= end_date.
• Ranges are inclusive on both ends.

Examples

Input: user_subscriptions user_id,start_date,end_date U1,2024-01-01,2024-01-15 U1,2024-01-10,2024-01-25 U1,2024-02-01,2024-02-10 U2,2024-03-01,2024-03-31 U2,2024-04-01,2024-04-15 U3,2024-05-01,2024-05-10 Output: user_id | coverage_start | coverage_end U1 | 2024-01-01 | 2024-01-25 U1 | 2024-02-01 | 2024-02-10 U2 | 2024-03-01 | 2024-03-31 U2 | 2024-04-01 | 2024-04-15 U3 | 2024-05-01 | 2024-05-10 Why this passes: U1's Jan 1-15 and Jan 10-25 ranges overlap so they merge into Jan 1-25, while the February range stays separate; U2's March range ends on Mar 31 and the next starts Apr 1, which is a new interval because the solution only merges when the next start is not after the previous end.

Input: user_subscriptions user_id | start_date | end_date U1 | 2024-01-01 | 2024-01-10 U1 | 2024-01-03 | 2024-01-07 Output: user_id | coverage_start | coverage_end U1 | 2024-01-01 | 2024-01-10 Why this passes: The second period is fully contained within the first and merges into it.

Topics: lakebench, sql, intervals, gaps-and-islands.

More SQL interview questions · All interview problems · Learn data engineering