How do you find consecutive login days in SQL Server?

Short answer: Gaps-and-islands: ROW_NUMBER() ordered by date, subtract from date to form an island key, then GROUP BY user + island key and count days.

Sample solution

WITH d AS (
    SELECT UserId, LoginDate,
           DATEADD(DAY, -ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY LoginDate), LoginDate) AS grp
    FROM UserLogins
)
SELECT UserId, MIN(LoginDate) AS StartDate, MAX(LoginDate) AS EndDate, COUNT(*) AS Days
FROM d
GROUP BY UserId, grp
HAVING COUNT(*) >= 3;

Naming “gaps and islands” immediately signals senior SQL fluency.