SQL help

Hi powerbidev123​ ,

You can solve this using the gaps-and-islands approach. First remove duplicate login dates, then assign each consecutive sequence to the same group:

WITH distinct_logins AS (     SELECT DISTINCT         user_id,         CAST(login_date AS date) AS login_date     FROM user_logins ), grouped_logins AS (     SELECT         user_id,         login_date,         DATEADD(             day,             -ROW_NUMBER() OVER (                 PARTITION BY user_id                 ORDER BY login_date             ),             login_date         ) AS streak_group     FROM distinct_logins ), streaks AS (     SELECT         user_id,         MIN(login_date) AS streak_start,         MAX(login_date) AS streak_end,         COUNT(*) AS streak_length     FROM grouped_logins     GROUP BY         user_id,         streak_group ), ranked_streaks AS (     SELECT         *,         ROW_NUMBER() OVER (             PARTITION BY user_id             ORDER BY streak_length DESC, streak_end DESC         ) AS rn     FROM streaks ) SELECT     user_id,     streak_start,     streak_end,     streak_length FROM ranked_streaks WHERE rn = 1 ORDER BY user_id;

 The key idea is that subtracting the row number from each login date produces the same grouping value for consecutive dates.

DISTINCT is important here in case a user logs in multiple times on the same day.

You may need to adjust the table and column names to match your schema.

AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.