How would you write a SQL query to group each user's logins into consecutive‑day runs (gaps and islands) and then find the longest run per user?

Question

Assisting AI · Accepted Answer

You can use the classic gaps‑and‑islands pattern with LAG and a running sum. First, order the logins per user and compute the difference in days between the current and previous login. If the difference is not 1, start a new island. Then assign a group id by summing that flag. Finally, aggregate per user and island to get the length, and pick the maximum. Example:

```sql
WITH ranked AS (
  SELECT
    user_id,
    login_date,
    CASE
      WHEN LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date) IS NULL
           OR DATE_DIFF('day', LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date), login_date) <> 1
      THEN 1 ELSE 0 END AS new_island
  FROM logins
),
islands AS (
  SELECT
    user_id,
    login_date,
    SUM(new_island) OVER (PARTITION BY user_id ORDER BY login_date ROWS UNBOUNDED PRECEDING) AS island_id
  FROM ranked
),
lengths AS (
  SELECT
    user_id,
    island_id,
    COUNT(*) AS run_length
  FROM islands
  GROUP BY user_id, island_id
)
SELECT user_id, MAX(run_length) AS longest_run
FROM lengths
GROUP BY user_id;
```

This returns the longest consecutive‑day login streak for each user.

How would you write a SQL query to group each user's logins into consecutive‑day runs (gaps and islands) and then find the longest run per user?

💡 Model Answer

🎤 Get questions like this answered in real-time