To find consecutive active days in SQL, first reduce events to one row per customer and calendar date. Number those dates within each customer, then subtract the row number from a day-based date value. Consecutive dates share the same result, which becomes a grouping key for each streak. This SQLite example returns every observed streak, including single-day streaks.
Define an active day before counting streaks
Suppose a fictional product team supports customers in the US, UK, and Germany. An active day means at least one qualifying product event on a calendar date in the customer’s assigned reporting time zone. The example starts with those dates already calculated. It does not convert timestamps or choose qualifying event types for you.
Customer US-A was active October 30, October 31 twice, November 1, and November 3. That is a three-day streak followed by a one-day streak. UK-B was active November 1 and November 3, giving two separate one-day streaks. DE-C has one active day, November 2. These are invented records for analyst interview practice.
Run the complete SQLite example
WITH events(customer, active_day) AS (
VALUES ('US-A','2026-10-30'),
('US-A','2026-10-31'),
('US-A','2026-10-31'),
('US-A','2026-11-01'),
('US-A','2026-11-03'),
('UK-B','2026-11-01'),
('UK-B','2026-11-03'),
('DE-C','2026-11-02')
), days AS (
SELECT DISTINCT customer, active_day
FROM events
), numbered AS (
SELECT customer, active_day,
ROW_NUMBER() OVER (
PARTITION BY customer ORDER BY active_day
) AS position
FROM days
), islands AS (
SELECT customer, active_day,
julianday(active_day) - position AS streak_key
FROM numbered
)
SELECT customer, MIN(active_day) AS streak_start,
MAX(active_day) AS streak_end,
COUNT(*) AS streak_days
FROM islands
GROUP BY customer, streak_key
ORDER BY customer, streak_start;
What should the query return?
The tested result contains five streaks. DE-C has November 2–2, length 1. UK-B has November 1–1 and November 3–3, each length 1. US-A has October 30–November 1, length 3, followed by November 3–3, length 1. The duplicated October 31 event contributes only one active day.
This output counts contiguous calendar dates, not consecutive rows in the raw event table. It includes weekends. It also crosses month boundaries correctly because the day value continues increasing across months.
Why does subtracting the row number work?
Within US-A, the first three unique dates advance one day at a time while their row numbers advance from 1 to 3. Subtracting the row number cancels that daily increase. November 3 is two days after November 1, but its row number advances by only one, so it produces a new streak key.
SQLite’s date-function documentation describes julianday(), which supplies the numeric day value here. Dates in this fixture are valid ISO-formatted calendar dates without time components. Other SQL engines use different date-arithmetic syntax, so translate the expression rather than copying it unchanged.
The SQLite window-function reference documents ROW_NUMBER and partitioning. Partitioning by customer resets row numbering for each person. Grouping by both customer and streak_key prevents unrelated customers whose arithmetic keys happen to match from being combined.
What goes wrong without deduplication?
Two events on October 31 have the same day value but different row numbers. That breaks the constant-key property and can incorrectly split a real streak or combine dates across a gap. SELECT DISTINCT customer, active_day establishes the correct grain before numbering. Deduplicating the final streak output is too late.
A customer ID must identify the same customer across the data being combined. If IDs are unique only within each market, use market plus customer ID in DISTINCT, PARTITION BY, and GROUP BY. The prefixed identifiers in this fixture are globally unique.
How do you report a longest or current streak?
To get the longest observed streak per customer, place the final grouped query in a streaks CTE and select customer, MAX(streak_days), grouped by customer. US-A’s longest observed streak is 3 days. If you also need its start and end, rank the streak rows by length descending and then start date, making your tie rule explicit.
A current streak needs an as-of rule. At the end of November 3, a strict rule requiring activity on November 3 gives US-A and UK-B current streaks of 1. DE-C has no streak ending on that date. During a partially completed day, a product might instead allow a streak ending yesterday; label that alternative and do not silently mix the two definitions.
-- Keep the CTEs through islands above.
-- Replace the final SELECT with this continuation.
-- Fixture ends on the as-of date, November 3.
-- For other data, filter days to <= your cutoff first.
, streaks AS (
SELECT customer,
MAX(active_day) AS streak_end,
COUNT(*) AS streak_days
FROM islands
GROUP BY customer, streak_key
)
SELECT customer,
MAX(streak_days) AS longest_observed,
MAX(CASE WHEN streak_end = '2026-11-03'
THEN streak_days ELSE 0 END)
AS current_streak
FROM streaks
GROUP BY customer
ORDER BY customer;
-- customer | longest_observed | current_streak
-- DE-C | 1 | 0
-- UK-B | 1 | 1
-- US-A | 3 | 1
-- Customers with no input activity need a roster
-- left join to appear in this summary.
What limits should an analyst explain?
A reporting-window filter can truncate a streak that began before the window. The three-day US-A streak becomes a one-day observed streak if you load only November events. Load sufficient history or label the result as a streak within the observed window. Late-arriving events can also bridge a previously observed gap.
Calendar-day streaks do not require exactly 24 elapsed hours between events. Dates derived for New York, London, and Berlin depend on the chosen reporting time zone, including daylight-saving rules. For the upstream conversion problem, work through daily active users across time zones.
Streak length is not a daily active-user count or a retention rate. For related but distinct calculations, see rolling seven-day distinct customers and sessions defined by inactivity gaps. An interview answer should name the identity, date grain, qualification rule, and observation window before presenting a streak metric.