Missing Periods per User, Including the Window Edges

Write a query to return every continuous run of missing login dates for each user appearing in logins, within 1 June through 12 June 2026 inclusive.

Return gap_start, gap_end and missing_days, counting both endpoints. Include missing runs at either boundary. A user with no login inside the window has one run covering the whole window. A user with no missing dates has no gap rows.

Use only window dates to determine gaps. Repeated logins on a date count as one active day, and the user population includes users recorded outside the window.

Ordering: Sort by user_id, then gap_start in ascending order.

Returnuser_idgap_startgap_endmissing_days

Columns

logins2 columns

columntype

user_idVARCHAR

login_dateDATE

Sample data & expected output

7 rows · 2 columns

user_idlogin_date

u_12026-06-03

u_12026-06-04

u_12026-06-08

u_22026-06-01

u_22026-06-02

u_32026-06-10

If you get stuck

Show hint · 3 hints Walkthrough Reveal answer

About this problem

You practise the two halves of this pattern in one query: manufacture the rows that should exist, then group what is left into runs. For each user you report every stretch of silence inside a fixed window, including the stretches before their first login and after their last. The skill is seeing that the second half is routine and the first half is the part the data cannot give you.

The failure to watch for is the one that looks most like a correct answer: a LAG-based query over the observed logins returns u_1’s 06-05 to 06-07 run and nothing else – one row out of six, every one of them correct, with five silently absent. u_2 and u_3 produce nothing at all, which is the opposite of the truth, since they are the two users who were most inactive. Deriving the window from the data instead of the question has the same effect in a subtler way. Once the missing days exist as rows, the rest is the pattern’s standard machinery, and the only thing left to get wrong is counting a run by subtracting its endpoints rather than counting its rows.

Read the lesson: Gaps, Islands & Streaks (P19) →

Your query · runs in your browser⌘ Enter to run

Loading editor…

Output · run to see rows