Each User’s Current Streak as of a Given Date

Write a query to return every user appearing in logins with current_streak_days as of 10 June 2026.

Count consecutive calendar dates with at least one login, ending on and including 10 June. If the user did not log in on 10 June, return 0. Count repeated same-day logins once and ignore activity after the report date when calculating the streak. Keep users whose calculated streak is zero.

Ordering: Sort by user_id ascending.

Returnuser_idcurrent_streak_days

Columns

logins2 columns

columntype

user_idVARCHAR

login_dateDATE

Sample data & expected output

6 rows · 2 columns

user_idlogin_date

u_22026-06-07

u_42026-06-08

u_42026-06-09

u_42026-06-10

u_42026-06-11

If you get stuck

Show hint · 3 hints Walkthrough Reveal answer

About this problem

You practise anchoring an island to a fixed point rather than measuring all of them. For every user you report the run of consecutive login days ending exactly on 2026-06-10, which is what a “current streak” badge shows. The skill is building the islands the usual way and then being strict about which one answers the question.

Three rules in the prompt each exist because of a specific wrong answer. Ignoring the logins after the reference date matters because u_4 kept logging in: include them and the streak reads 4, which is true of today and false of the date the dashboard is showing. Deduplicating matters because u_4 logged in twice on 06-09, and a repeated day shifts every row number after it, breaking one run into two and reporting 2. And returning a row for users with no current streak matters because the natural query selects from the islands, where u_2 simply does not appear – a dashboard that silently omits the users who broke their streak is showing the opposite of what it is for. The underlying island-building is the standard pattern; everything that makes this problem worth eleven minutes is in the three rules around it.

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

Your query · runs in your browser⌘ Enter to run

Loading editor…

Output · run to see rows