“Users who logged in 3 days in a row”, “longest streak”, “missing ids” — the gaps-and-islands family, solved elegantly with ROW_NUMBER or LAG.

SQL

SELECT user_id, group_concat(substr(login_date, 9), ', ') AS march_days
FROM (SELECT * FROM logins ORDER BY user_id, login_date)
GROUP BY user_id
ORDER BY user_id;

Output

user_id | march_days
--------+-------------------
1       | 01, 02, 03, 05, 06
2       | 01, 03
3       | 02, 03, 04, 05

The islands trick

Within one user, subtract the row number (in days) from the date. Consecutive dates produce the same anchor value; a gap changes it. Group by that anchor and each group is one streak (“island”).

SQL

SELECT user_id, login_date,
       ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn,
       date(login_date, '-' || ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) || ' days') AS grp
FROM logins
WHERE user_id = 1
ORDER BY login_date;

Output

user_id | login_date | rn | grp
--------+------------+----+-----------
1       | 2024-03-01 | 1  | 2024-02-29
1       | 2024-03-02 | 2  | 2024-02-29
1       | 2024-03-03 | 3  | 2024-02-29
1       | 2024-03-05 | 4  | 2024-03-01
1       | 2024-03-06 | 5  | 2024-03-01

SQL

WITH marked AS (
    SELECT user_id, login_date,
           date(login_date, '-' || ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) || ' days') AS grp
    FROM logins
)
SELECT user_id, MIN(login_date) AS streak_start, MAX(login_date) AS streak_end, COUNT(*) AS days
FROM marked
GROUP BY user_id, grp
ORDER BY user_id, streak_start;

Output

user_id | streak_start | streak_end | days
--------+--------------+------------+-----
1       | 2024-03-01   | 2024-03-03 | 3
1       | 2024-03-05   | 2024-03-06 | 2
2       | 2024-03-01   | 2024-03-01 | 1
2       | 2024-03-03   | 2024-03-03 | 1
3       | 2024-03-02   | 2024-03-05 | 4

Users with 3+ consecutive days, and longest streak

SQL

WITH marked AS (
    SELECT user_id,
           date(login_date, '-' || ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) || ' days') AS grp
    FROM logins
),
streaks AS (
    SELECT user_id, COUNT(*) AS days FROM marked GROUP BY user_id, grp
)
SELECT user_id, MAX(days) AS longest_streak
FROM streaks
GROUP BY user_id
HAVING MAX(days) >= 3
ORDER BY user_id;

Output

user_id | longest_streak
--------+---------------
1       | 3
3       | 4

The LAG alternative: detect breaks

SQL

-- 3 consecutive days = today, yesterday and the day before are all present
SELECT DISTINCT user_id
FROM (
    SELECT user_id, login_date,
           LAG(login_date, 2) OVER (PARTITION BY user_id ORDER BY login_date) AS two_back
    FROM logins
)
WHERE julianday(login_date) - julianday(two_back) = 2
ORDER BY user_id;

Output

user_id
-------
1
3

Gaps: missing values in a sequence

SQL

-- gaps in order ids (orders 101..108 exist; pretend 103 and 104 were deleted)
DELETE FROM order_items WHERE order_id IN (103, 104);
DELETE FROM orders WHERE order_id IN (103, 104);

SELECT order_id + 1 AS gap_start, next_id - 1 AS gap_end
FROM (SELECT order_id, LEAD(order_id) OVER (ORDER BY order_id) AS next_id FROM orders)
WHERE next_id - order_id > 1;

Output

gap_start | gap_end
----------+--------
103       | 104

Find users who logged in on 3 consecutive days.