Current Active Streak as of Today
Calculating an ongoing streak and resetting it on a break.
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.
All lessons in this course
- Detecting Consecutive Calendar Days
- Longest Streak Per User
- N Consecutive Rows Meeting a Condition
- Current Active Streak as of Today