For each login row, flag whether the user's previous login was EXACTLY one day before (true = consecutive, false = gap or no prior).
Sort by user_id then login_date ascending.
Returnuser_idlogin_dateprev_dateis_consecutive
About this problem
This problem practises the first step of every gaps-and-islands question: comparing each row with the one before it and deciding whether the sequence continued. For each row in logins, you find that user’s previous login_date with LAG, show it as prev_date, and set is_consecutive to true only when it is exactly one calendar day earlier. The business use is streak tracking – later steps count runs of consecutive days – so the flag has to be right on every row, including the first login of each user, which has no previous date.
Two mistakes produce believable but wrong flags. The first is a window with no PARTITION BY user_id: rows are then compared across users, and in the sample u_2’s login on 4 June would be measured against u_1’s login on 3 June and wrongly marked consecutive. The second is the first row. Comparing a date with a NULL prev_date gives NULL, not false, so a plain comparison leaves blanks where the prompt asks for false. The blank looks harmless in a results grid, which is why it slips through. Also be careful with the comparison itself: “one day before” means a difference of exactly one day, so a test such as “earlier than today” or “within two days” flags gaps as consecutive. In the sample, u_1 skips 4 June, which is exactly the row a loose test gets wrong.
Read the lesson: Gaps, Islands & Streaks (P19) →