Reference: Consecutive Activity & Retention Aggregation

What it is

Consecutive-activity computation answers questions like “how many days in a row has this user been active” or “is this account active in 5 consecutive minutes” over a massive, high-concurrency event stream. Retention aggregation is its aggregate cousin — what fraction of a cohort stays active across consecutive periods. Both are window-aggregation problems where the naive approach (scan all raw events at query time) is the wrong answer at scale.

When you care

This shows up in any system that reports streaks, retention curves, or “active N periods in a row” — analytics platforms, anti-fraud, SRE alerting, growth dashboards. In an interview, the trap is proposing a query that scans raw logs, or a state object that stores every timestamp. Both collapse at scale. The signal is knowing three things: pre-aggregate at write time, slim the state, and route by time granularity.

The three governing principles

1. Write-time pre-aggregation. Never scan raw logs on the fly at query time. Slice and aggregate by dimension and time unit before persisting (or in an off-peak batch window). You trade write/compute latency for read speed — the right trade because reads vastly outnumber recomputes.
2. State slimming. Never store the full history of timestamps to compute a streak. Compress state into the minimal structure that answers the question — a counter, an anchor timestamp, or a bitmap. Storing history is what causes state/memory explosion.
3. Multi-granularity sharding. A minute-level streak and a month-level streak are different problems. Route to a different technique depending on whether the time grain is high (minute/hour), medium (day), or low (week/month/year).

The two core algorithms

Everything below composes from two fundamental models — one for batch, one for streaming.

Gaps and Islands (batch / SQL)

The standard offline technique for finding consecutive runs (“islands”) separated by gaps. It exploits a simple invariant: subtract a chronological row number from a monotonic time unit, and the result is constant within a consecutive run.

The logic:

1. For each user, order their active dates and assign a sequential ROW_NUMBER() (1, 2, 3, …).
2. Compute an anchor base date = active_date - ROW_NUMBER().
3. Within a consecutive run, active_date and ROW_NUMBER() both increment by 1 each step, so their difference is identical for every day in the run. A gap breaks the equality and starts a new anchor.
4. GROUP BY (user_id, anchor_base_date) and COUNT(*) gives the length of each consecutive run; the max per user is their longest streak.

Worked example — a user active on the 1st, 2nd, 3rd, then 5th, 6th:

Two islands: anchor 00 (length 3) and anchor 01 (length 2). The gap at the 4th shifts the anchor. This is exact, set-based, and parallelizes across users — ideal for batch.

Stateful streaming rolling logic (real-time)

For real-time streams, maintain one minimal state object per user: { last_active_unit, current_consecutive_units }. On each event, apply one of three transitions based on the event’s time unit:

1. new_unit == last_active_unit + 1 → consecutive. Increment the counter by 1; set last_active_unit = new_unit.
2. new_unit == last_active_unit → duplicate trigger within the same unit. Leave state unchanged (idempotent — this is what makes the stream safe under at-least-once delivery and retries).
3. new_unit > last_active_unit + 1 → a gap. Reset the counter to 1; set last_active_unit = new_unit.

State is two fields per user regardless of streak length or history — that’s the state-slimming principle in action. The idempotent case (2) is the load-bearing detail: streams redeliver, and a streak counter that double-counts duplicate events is wrong.

Architecture by time granularity

The same problem demands different machinery at different grains.

High granularity — state bitmapping

A per-user state object holding minute-by-minute history explodes at high frequency. Replace it with a bitmap: one binary bit per time unit, packed into an integer. A single 64-bit Long represents the active state of the last 60 minutes.

1. On an event, flip the bit for that minute to 1.
2. To test for N consecutive active minutes, use bitwise operations in O(1): repeatedly AND the value with a right-shifted copy of itself. After N-1 such shift-and-AND steps, a non-zero result means some run of N consecutive set bits exists.

The whole per-user state is a single machine word. Bitwise AND/shift are among the cheapest CPU operations, so the consecutiveness check is effectively free — exactly what high-frequency, high-cardinality workloads need.

Medium granularity — Lambda dual-track reconciliation

Day-level retention’s enemy is out-of-order and late-arriving data. A device that was offline reports yesterday’s activity today; a pure real-time stream sees the gap and falsely resets the streak. The fix is a Lambda-architecture split that runs both tracks and lets batch correct the stream:

The real-time layer gives you a fast, approximately-correct answer now; the batch layer gives you the exact answer later and reconciles away the streaming errors that out-of-order data introduced. The ad-click aggregation walkthrough applies this same batch-reconciliation pattern.

Low granularity — intermediate aggregation tables

Over months or years, maintaining live streaming state is wasteful and fragile. Instead, reduce dimensionality with a pre-aggregated intermediate table and never touch raw logs at query time.

1. Build a minimalist roll-up table: [user_id, week_id, is_active], where is_active = 1 if the user triggered at least one event that week.
2. This collapses billions of raw rows into one row per user per period — a ~99.9% reduction.
3. Run the analytical window functions (Gaps-and-Islands, retention curves) directly on this table for sub-second response.

The intermediate table is the write-time-pre-aggregation principle made concrete: the expensive reduction happens once, on ingest or in a nightly batch, not on every query.

Advanced engineering extensions

Global multi-timezone alignment

Challenge. Users are global. 23:59 in London is 07:59 in Beijing. Aggregating uniformly by server time (UTC) splits a user’s local nighttime activity across two server-days, incorrectly resetting their streak.

Solution. Capture the user’s local_timezone (or precomputed local_date) on the raw event. Change the aggregation key from user_id + server_date to user_id + local_date. The streak is then computed in the user’s own day boundaries, which is what “active every day” actually means to the user.

Tumbling vs. sliding consecutiveness

Challenge. Tumbling consecutiveness checks fixed natural periods (active in Week 1 and Week 2). Sliding consecutiveness checks any rolling duration (active across any rolling 7-day / 168-hour window). Sliding is much more expensive to compute naively.

Solution. Use slicing buckets. Chop the large sliding window into small fixed tumbling buckets (e.g., 1-hour buckets). Aggregate metrics only inside each small bucket at write time, then roll up a fixed number of consecutive buckets at read time.

The bucket count caps read-time cost: a 7-day sliding window over 1-hour buckets is a merge of 168 pre-aggregated values, not a scan of raw events.

Consecutiveness combined with cardinality

Challenge. Rules like “active for 5 consecutive days, where the user clicks at least 3 distinct ads each day.” A simple counter can’t express the distinctness requirement.

Solution. Integrate a cardinality-estimation sketch — HyperLogLog (HLL) — into the state. The object evolves to: { last_active_date, current_consecutive_days, today_hll_registers }.

1. During the day, add each clicked ad to today’s HLL registers.
2. At day end, evaluate the HLL cardinality. If ≥ 3, the day qualifies → apply the normal increment/reset rules. If < 3, the day fails the rule → reset the consecutive counter.
3. Roll the HLL registers for the next day.

HLL keeps the distinct-count state to a few kilobytes regardless of how many ads were clicked — preserving state-slimming even with a cardinality constraint layered on.

Tradeoff summary

• Default to write-time pre-aggregation. Recompute on a schedule, not per query. Reads dominate; pay the cost on the write/batch side.
• Pick the technique by grain. Minute/hour → bitmaps + bitwise ops. Day → Lambda dual-track (fast stream, batch reconciles late data). Week/month/year → intermediate roll-up tables.
• Slim the state, always. Counter + anchor for streaks; bitmap for high-frequency; HLL when distinctness matters. Never store raw timestamp history.
• Align to the user’s local day, not the server’s, or streaks break at timezone boundaries.
• For sliding windows, slice into tumbling buckets and roll up at read time — never recompute the sliding window from raw events.

Related

• Reference: SQL vs NoSQL Schema Design — the intermediate-table and time-bucketed partition keys these aggregations write to.
• Walkthrough: Designing an Ad Click Aggregation System — the Lambda batch-reconciliation pattern applied end-to-end.
• Walkthrough: Designing a Top K System — the same approximation-and-sketch philosophy (Count-Min, HLL) for a different aggregate.