SQL Interview Prep · Lesson

Current Active Streak as of Today

Calculating an ongoing streak and resetting it on a break.

Current Active Streak as of Today is a free SQL Interview Prep lesson on CoddyKit — lesson 4 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 Product Question

Streak features (think Duolingo or Snapchat) need the current streak, not the historical longest. The interview prompt: "For each user, how many consecutive days ending today have they been active? Reset to 0 if they missed today."

This adds a twist: the streak must be anchored to today (or yesterday under a grace rule). Let us build it on the gaps-and-islands foundation you already know.

Defining Active

First clarify the rules with your interviewer — these decisions change the query:

Does the streak require activity today, or is yesterday acceptable (grace period)?
Are multiple events per day collapsed to one day?
What time zone defines a "day"?

We will assume one row per active day and that the streak is current if it includes today OR yesterday.

Build the Islands Again

Reuse the anchor: number each user's days by date and subtract. Every consecutive run shares an anchor. The current streak is simply the island whose last day is today or yesterday.

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;

Identify the Latest Island

Each user's most recent island is the one with the greatest streak_end. We test whether that end is recent enough to count as ongoing.

CURRENT_DATE gives today. CURRENT_DATE - 1 is yesterday. If streak_end equals either, the streak is live.

WITH /* ...numbered, islands... */
latest AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY streak_end DESC
    ) AS rn2
  FROM islands
)
SELECT * FROM latest WHERE rn2 = 1;

Apply the Recency Test

Now decide if the latest island is active. If its end date is today or yesterday, the current streak equals its length; otherwise the user has broken their streak and the current value is 0.

SELECT user_id,
  CASE
    WHEN streak_end >= CURRENT_DATE - 1
    THEN streak_len
    ELSE 0
  END AS current_streak
FROM latest
WHERE rn2 = 1;

The Full Current-Streak Query

Assemble all layers: number, build islands, pick the latest per user, then apply the recency CASE. This is the complete answer.

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,
    MAX(login_date) AS streak_end,
    COUNT(*)        AS streak_len
  FROM numbered
  GROUP BY user_id, login_date - rn
),
latest AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY streak_end DESC
    ) AS rn2
  FROM islands
)
SELECT user_id,
  CASE WHEN streak_end >= CURRENT_DATE - 1
       THEN streak_len ELSE 0 END AS current_streak
FROM latest
WHERE rn2 = 1
ORDER BY user_id;

Today-Only Strictness

If the business rule is strict — the streak is alive only if the user was active today — change the comparison to require streak_end = CURRENT_DATE.

The grace-period version (>= CURRENT_DATE - 1) is friendlier and common in real apps, where the day is not over yet. Always confirm which the interviewer wants.

CASE WHEN streak_end = CURRENT_DATE
     THEN streak_len ELSE 0 END AS current_streak

An Alternative: Walk Backward

A different mental model that some interviewers prefer: compute the gap from each day to today. A day is part of the current streak only if every day from it through today is present. Equivalently, compare login_date to CURRENT_DATE - (offset from the latest).

The island method is usually cleaner, but knowing an alternative shows depth. The key idea is the same: an unbroken chain anchored at today.

Including Inactive Users

Users absent from logins have no island and disappear. If every user must report a streak (likely 0), LEFT JOIN the user list and COALESCE the result to 0.

SELECT u.user_id,
  COALESCE(s.current_streak, 0) AS current_streak
FROM users u
LEFT JOIN current_streaks s ON s.user_id = u.user_id;

Time Zone Pitfalls

If activity is stored as timestamps, "today" depends on the time zone. Convert before truncating to a date.

Store and compare in UTC, or convert to the user's local zone first.
Use event_ts AT TIME ZONE 'UTC' AT TIME ZONE user_tz in Postgres, then cast to date.
A naive ::date on a UTC timestamp can place an evening event on the wrong calendar day.

Mentioning this preempts a classic gotcha follow-up.

SELECT user_id,
  (event_ts AT TIME ZONE 'UTC'
             AT TIME ZONE 'America/New_York')::date AS local_day
FROM events;

Performance and Wrap-Up

For dashboards refreshed often, this query runs per user in a single pass. To keep it snappy:

Index (user_id, login_date).
Optionally restrict the input to recent dates — a current streak cannot include data older than its own length, so a rolling window of, say, the last 400 days is plenty.
Materialize daily into a streak table if read very frequently.

Quick Check

Confirm you understand the anchor to "now."

Recap

To compute the current active streak as of today:

Build islands with the login_date - ROW_NUMBER() anchor.
Select each user's latest island by max streak_end.
Return its length only if streak_end is today (or yesterday for grace), else 0.
LEFT JOIN users for inactive accounts; handle time zones before truncating to a date.

You now command the full streak-analysis toolkit: detecting runs, longest streak, N-consecutive conditions, and the live streak.

Frequently asked questions

Is the “Current Active Streak as of Today” lesson free?

Yes — the full text of “Current Active Streak as of Today” 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 “Current Active Streak as of Today”?

Calculating an ongoing streak and resetting it on a break. 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 4 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Current Active Streak as of Today” 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