SQLhardGaps islands~22m

A video streaming platform tracks user viewing sessions

Problem

A video streaming platform tracks user viewing sessions. Find all gaps in viewing activity for each user where they were inactive for 3 or more consecutive days, and return the gap start date, gap end date, and gap duration in days. Only include users who have had at least one such gap, and order by user_id, then by gap_start_date.

Examples

Example 1

Example 2

Constraints

›Return user_id, gap_start_date, gap_end_date, gap_duration_days
›Only gaps of 3+ consecutive days
›Only users with at least one qualifying gap
›Order by user_id, then gap_start_date

Reference solution

Reference solution available after you attempt the question.

Ready to solve it?

Start a session on Mockbit #42. Write your SQL, run it against real data, and get graded on correctness, efficiency, query structure, and business understanding.