Days with No Logins in a Reporting Window

Write a query to return each calendar date from 1 June through 16 June 2026, including both dates, on which no user has a row in logins.

Any user’s login makes a date active. Ignore dates outside the reporting window and return each missing date once as missing_date.

Ordering: Sort by missing_date ascending.

Returnmissing_date

Columns

logins2 columns

columntype

user_idVARCHAR

login_dateDATE

Sample data & expected output

6 rows · 2 columns

user_idlogin_date

u_12026-06-02

u_12026-06-03

u_12026-06-06

u_22026-06-07

u_32026-06-11

If you get stuck

Show hint · 3 hints Walkthrough Reveal answer

About this problem

You practise finding what is absent by writing down what you expected and taking away what you got. Over a fixed fortnight you return every date on which nobody logged in. The skill is recognising a question that the pattern’s usual tooling cannot answer, and reaching for a date spine instead.

Almost everything in this pattern works by comparing each row with its neighbour, so the reflex is to lag the dates and report the holes. It genuinely works for the gaps in the middle and it is blind to both edges, because a gap is defined by the rows on either side of it and the edges have a row on only one side. That blindness is quiet: five rows come back, they are all correct, and six are simply absent. Deriving the window from min and max of the data feels safer and reintroduces the same bug, since the series can then never extend past the first and last rows.

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

Your query · runs in your browser⌘ Enter to run

Loading editor…

Output · run to see rows