SQL Interview Prep · Lesson

Longest Streak Per User

Computing the maximum consecutive run length within each group.

Longest Streak Per User is a free SQL Interview Prep lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

The Question

A frequent follow-up to consecutive-day detection: "For each user, what is their longest run of consecutive active days?" Product and growth teams ask this constantly to measure engagement.

You already know how to identify each run. The new step is to find the maximum run length per user and, often, to return the dates of that best streak too. This lesson builds directly on the gaps-and-islands skeleton.

Recall the Island Builder

From the previous lesson, the per-run grouping uses login_date - ROW_NUMBER() as the island anchor. Each user can have several islands; we will compute one row per island first, then reduce to one row per user.

Keep this two-layer plan in mind: first build islands, then aggregate the islands.

WITH numbered AS (
  SELECT user_id, login_date,
    ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY login_date
    ) AS rn
  FROM logins
)
SELECT user_id, login_date - rn AS grp
FROM numbered;

One Row Per Island

Collapse each island to a single summary row carrying its length and date range. Group by user and the anchor, and compute the metrics.

We name this CTE islands so the next layer can read from it cleanly.

WITH numbered AS (
  SELECT user_id, login_date,
    ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY login_date
    ) AS rn
  FROM logins
),
islands AS (
  SELECT user_id,
    MIN(login_date) AS streak_start,
    MAX(login_date) AS streak_end,
    COUNT(*)        AS streak_len
  FROM numbered
  GROUP BY user_id, login_date - rn
)
SELECT * FROM islands;

Simple Answer: MAX Length

If the interviewer only wants the length, the final step is a one-liner: group the islands by user and take the maximum length.

This is the cleanest answer when start/end dates are not required.

-- ...numbered and islands CTEs as before...
SELECT
  user_id,
  MAX(streak_len) AS longest_streak
FROM islands
GROUP BY user_id
ORDER BY user_id;

Also Returning the Dates

Often the interviewer adds: "and show when that streak happened." A plain MAX cannot tell you which island won. You need to rank islands within each user and keep rank 1.

Use ROW_NUMBER ordered by length descending so each user's best streak gets rank 1. Add a tiebreaker so ties resolve deterministically.

ROW_NUMBER() OVER (
  PARTITION BY user_id
  ORDER BY streak_len DESC, streak_start ASC
) AS rnk

Rank and Filter

Wrap the ranking in a CTE, then filter to rnk = 1. You cannot filter on a window function directly in WHERE, so the extra layer is mandatory.

WITH numbered AS (
  SELECT user_id, login_date,
    ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY login_date
    ) AS rn
  FROM logins
),
islands AS (
  SELECT user_id,
    MIN(login_date) AS streak_start,
    MAX(login_date) AS streak_end,
    COUNT(*)        AS streak_len
  FROM numbered
  GROUP BY user_id, login_date - rn
),
ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY streak_len DESC, streak_start
    ) AS rnk
  FROM islands
)
SELECT user_id, streak_start, streak_end, streak_len
FROM ranked
WHERE rnk = 1;

RANK vs ROW_NUMBER for Ties

What if a user has two streaks of equal maximum length and the interviewer wants both returned? Swap ROW_NUMBER for RANK and keep rnk = 1.

ROW_NUMBER — exactly one winner per user (arbitrary on ties unless you add a tiebreaker).
RANK — all tied longest streaks share rank 1 and are all kept.

Clarify which behavior they want; it signals attention to edge cases.

RANK() OVER (
  PARTITION BY user_id
  ORDER BY streak_len DESC
) AS rnk  -- keep all rnk = 1

Worked Example

Suppose user 7 logged in Jan 1-4, then Jan 10-11, then Jan 20-23. Three islands of length 4, 2, and 4. The longest length is 4, and there is a tie.

With ROW_NUMBER + tiebreaker streak_start: returns only the Jan 1-4 run.
With RANK: returns both the Jan 1-4 and Jan 20-23 runs.

Stating this aloud demonstrates you reasoned about duplicates.

Handling Users With No Logins

An interviewer may ask: "What about users who never logged in?" Those users have no rows in logins, so they vanish from the result. If they must appear with a streak of 0, LEFT JOIN the full users table and COALESCE.

SELECT u.user_id,
  COALESCE(MAX(i.streak_len), 0) AS longest_streak
FROM users u
LEFT JOIN islands i ON i.user_id = u.user_id
GROUP BY u.user_id;

Performance Notes

This pattern makes a single ordered pass over the data plus a grouping. To keep it fast:

Ensure an index on (user_id, login_date) so the window ORDER BY avoids a sort.
Deduplicate early if the source has multiple events per day.
Avoid wrapping login_date in functions in the ORDER BY, which can block index use.

For very large tables this comfortably outperforms any self-join approach.

Full Interview Answer

Here is the complete, polished query returning each user's longest streak with its dates — the version to write on the whiteboard.

WITH numbered AS (
  SELECT user_id, login_date,
    ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY login_date
    ) AS rn
  FROM logins
),
islands AS (
  SELECT user_id,
    MIN(login_date) AS streak_start,
    MAX(login_date) AS streak_end,
    COUNT(*)        AS streak_len
  FROM numbered
  GROUP BY user_id, login_date - rn
),
ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY streak_len DESC, streak_start
    ) AS rnk
  FROM islands
)
SELECT user_id, streak_start, streak_end, streak_len
FROM ranked
WHERE rnk = 1
ORDER BY user_id;

Quick Check

Pick the right tool for the requirement.

Recap

To compute the longest streak per user:

Build islands with the login_date - ROW_NUMBER() anchor.
Collapse each island to length plus date range.
For length only, MAX(streak_len) grouped by user.
For the dates too, rank islands per user and keep rank 1 — RANK to include ties, ROW_NUMBER for a single winner.
LEFT JOIN users to surface zero-streak users.

Next: detecting N consecutive rows that meet a condition.

Frequently asked questions

Is the “Longest Streak Per User” lesson free?

Yes — the full text of “Longest Streak Per User” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “Longest Streak Per User”?

Computing the maximum consecutive run length within each group. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Longest Streak Per User” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Interview Prep