Design an Ad Click Aggregator

Real-Time Stream Aggregation System Design

Overview

An ad click aggregator counts clicks on ads and turns them into per-ad totals that advertisers watch in near real time. Every time someone clicks an ad, an event flows into the system; the advertiser's dashboard shows "this ad got 48,120 clicks in the last hour" and the number keeps ticking up. The product sounds like "count some events," but at billions of clicks a day it hides the problems that separate a shallow answer from a strong one: you cannot count raw events per query, clicks arrive out of order and late, and one viral ad becomes a hot key that a single aggregator can't keep up with.

It is labeled "Medium" because the naive design breaks in specific, teachable ways. If you plan to answer "how many clicks did this ad get?" by scanning the raw click events at query time, you are scanning billions of rows per question — hopeless. If you bucket clicks by the wall-clock time they arrive instead of the time they happened, a late-arriving mobile click lands in the wrong minute and your per-minute totals are wrong. And if you keep one running counter per ad, a viral ad's clicks all pile onto one key. A strong answer pre-aggregates clicks into per-ad-per-minute buckets on a streaming pipeline, uses event-time windows with watermarks to handle late data, and shards the hot ad's counter.

In this walkthrough you'll scope the system, size it, pick a data model, design the API, draw the ingest and read paths, and then go deep on the aggregation pipeline (stream vs batch), windowing with watermarks and late data, and exactly-once counting with hot ads.

Out of scope (say so): serving/choosing which ad to show (the ad-serving auction), click-through attribution to conversions, and advertiser billing logic itself — we produce the counts billing later reads, but the invoicing system is separate.

Functional requirements

• Ingest a click. Record every ad click as an event carrying which ad, when it happened, and the impression id it was served with (the dedup key).
• Per-ad totals. Return the total clicks for a given ad, optionally over a time range (last hour, last day, a custom window).
• Time-bucketed history. Support "clicks per minute/hour" over a range, so the dashboard can draw a chart, not just one number.
• Top-N ads. Return the most-clicked ads (e.g. top 100 by clicks in the last hour) for an advertiser or campaign.

Out of scope (state it): the ad auction that decides which ad to show, conversion attribution, and the billing/invoicing pipeline. This system owns click counting and the queries over the counts.

Non-functional requirements

• Scalability. Billions of clicks a day: the ingest path must absorb this write volume, and no single ad, shard, or counter can become a hot spot when one viral ad takes a huge share of the traffic.
• Latency. Near-real-time, not instant — a few seconds of lag between a click and the dashboard is acceptable; strict second-by-second exactness in real time is not required.
• Accuracy. The number an advertiser is billed on must be exact: a briefly approximate real-time count is fine as long as a corrected, exact total is available through reconciliation — and per-minute totals must still be correct after out-of-order and late clicks (seconds to minutes late from mobile) have settled.
• Idempotency. A single click must count exactly once even if its event is delivered more than once (retries, at-least-once queues), so the count never inflates.
• Fault tolerance. The ingest and aggregation pipeline must survive component failures without losing events — durable buffering and at-least-once delivery mean a click is never silently dropped, since a dropped click is lost revenue.

Estimations

State assumptions; the goal is to justify the pipeline and the sharding, not to be exact.

Rounded assumptions

• Clicks/day: ~10B. ÷ 86,400 s/day ≈ ~115K → ~100K clicks/sec average.
• Peak clicks/sec. Traffic is spiky (prime time, big events): call it ~3–5× average → ~300–500K clicks/sec at peak.
• Hot-ad skew. Clicks are not spread evenly across ads. A small number of ads take a large share — a single viral ad can take tens of thousands of clicks/sec onto one ad id, while the long tail sees a trickle.
• Raw click storage. ~10B clicks/day × ~200 bytes/event (ad id, timestamp, impression id, context) ≈ ~2 TB/day of raw events, or ~700 TB/year — far too big to scan per query, so raw events are for reprocessing, not for serving reads.
• Aggregate storage. The serving data is tiny by comparison: per-(ad, minute) buckets. With, say, ~1M active ads × 1,440 minutes/day, that's ~1.4B small rows/day of aggregates as an upper bound — already several times smaller than the ~10B raw events, and in practice orders of magnitude smaller, since most (ad, minute) cells are empty (a given ad isn't clicked every minute), so the table is sparse and cheap to query.

How the numbers affect the design

Core entities / data model

Three things are stored: the raw click event (the immutable source of truth), the per-(ad, minute) aggregate (the rolled-up count), and the serving store (what dashboards query).

The raw ClickEvent is the single source of truth. It is immutable — you never edit a click, you only append new ones — and it carries the impression_id (a unique, signed token minted when the ad is served and carried on the click) used to deduplicate, and the event_time (when the click actually happened) used to place it in the right time bucket. Raw events are kept in cheap, durable storage (a partitioned log, then object storage / a data lake) so the exact total can always be recomputed from them.

The AdMinuteCount aggregate is derived from the raw events: for each ad, for each one-minute bucket of event-time, how many clicks. This is what the streaming pipeline produces and what reads are served from. A per-minute grain rolls up cheaply into per-hour and per-day totals (sum the minutes in the range).

The serving store is really two different structures serving two different access patterns, because a range-sum and a top-N read want different layouts. For the range-sum ("total for ad_42 over [from, to)"), use a wide-column / OLAP / time-series store — Cassandra, Druid, or ClickHouse — keyed by (ad_id, minute) so a range of minute buckets is a cheap contiguous scan-and-sum. For top-N ("most-clicked ads this hour"), keep a Redis sorted set (ZSET) per window, score = clicks, so top-N is an O(log N) range read off the ranking. Both are small relative to the raw log and are derived caches of the aggregates — if lost, they rebuild from the aggregates, which are themselves rebuildable from the raw events.

API design

Three operations carry the system: ingest a click, query an ad's totals over a range, and get the top-N ads.

Ingest a click

POST /api/clicks
Body: { "impression_id": "imp-8f3a…", "ad_id": "ad_42", "event_time": "2026-07-01T18:04:07Z" }
202 Accepted
Returns: { "accepted": true }

Record one click. The impression_id is a unique, signed token minted when the ad is served and carried on the click (echoed on every retry), so the pipeline can deduplicate a retried or duplicated delivery. event_time is when the click happened, carried on the event (not assigned on arrival) so the pipeline can bucket by event-time. This returns 202 Accepted — the click is durably appended to the ingest log and processed asynchronously; the caller does not wait for it to be counted.

Query an ad's totals over a time range

GET /api/ads/{ad_id}/clicks?from=2026-07-01T17:00:00Z&to=2026-07-01T18:00:00Z&granularity=minute
200 OK
Returns: {
  "ad_id": "ad_42",
  "total": 48120,
  "series": [ { "minute": "2026-07-01T17:00:00Z", "clicks": 812 }, … ]
}

Return the total clicks for one ad over [from, to), plus a per-bucket time series at the requested granularity (minute/hour/day) so the dashboard can draw a chart. Served entirely from the pre-aggregated AdMinuteCount rollups — the total is a sum over the buckets in the range, never a scan of raw events.

Top-N ads

GET /api/campaigns/{campaign_id}/top-ads?window=1h&limit=100
200 OK
Returns: { "window": "1h", "ads": [ { "ad_id": "ad_42", "clicks": 48120 }, … ] }

Return the most-clicked ads in a campaign over a recent window, ranked. Served from a maintained top-N index (a per-window sorted structure) — not computed by sorting every ad's total on demand. Maintaining it over a rolling window is the subtle part: as each minute closes you must add the new minute AND subtract the minute that just fell out of the window, otherwise a ZSET only bumped on bucket-close keeps counting expired minutes and drifts high. The alternative is to recompute the window's top-N from the per-minute buckets on a cadence (say every minute) rather than mutate it incrementally. And when the ranking is sharded across partitions, each partition's local top-N is only partial — an exact global top-N needs a combine step that merges the partial rankings.

High-level architecture

Two paths matter: an ingest + aggregation path (clicks flow into a durable log, a stream processor rolls them up into per-(ad, minute) counts) and a read path (dashboards query the aggregates). We'll walk each, then combine them.

1. Ingest + aggregation path

Clicks land in a durable stream; a stream processor filters, dedups, buckets by event-time, and writes rollups.

flowchart LR
    Client["Ad click<br/>(browser / app / edge)"] -->|"POST /clicks"| GW["Ingest Service"]
    GW -->|"append (partitioned by ad_id)"| Log[["Durable click log<br/>(partitioned stream)"]]
    Log --> SP["Stream processor<br/>filter → dedup → window"]
    SP -->|"per-(ad, minute) counts"| Agg[("Aggregate / serving store")]

1. A click hits the Ingest Service, which validates it and appends it to a durable, partitioned click log (a stream). Partitioning by ad_id keeps one ad's clicks together and lets the pipeline scale horizontally across partitions. Pure ad_id partitioning isn't the final design, though — hot ads get sub-sharded across partitions so a single viral ad doesn't pin one partition (deep dive 3).
2. The stream processor consumes the log and, per ad, does three things: filters invalid/bot clicks, dedups by impression_id, and assigns each click to its event-time minute bucket (deep dives 2 and 3).
3. The processor continuously updates the current (open) minute's running count in the aggregate / serving store (early emission every ~1–2 s), so live totals are seconds-fresh; when the watermark later passes the window's end, that minute bucket is finalized as its stable per-(ad, minute) value (deep dive 2).

The rule that keeps totals cheap: you never count raw events at read time. The stream processor turns billions of raw clicks into a small set of per-minute counts once, up front. These live counts are approximate until they settle; the raw clicks are retained so a slower batch reconciliation pass can later produce the exact, billable total — that's Deep Dive 1, deliberately kept out of this high-level view.

2. Read path (dashboards)

Reads serve pre-aggregated counts, never raw events.

flowchart LR
    Dash["Advertiser dashboard"] -->|"GET ad totals / top-N"| Query["Query Service"]
    Query -->|"sum buckets in [from,to)"| Agg[("Aggregate / serving store")]
    Query -->|"read top-N index"| TopN[("Top-N index")]
    Query -->|"totals + series + ranking"| Dash

1. The dashboard asks the Query Service for an ad's total over a range, or a campaign's top-N ads.
2. For a range total, the service sums the per-minute buckets in [from, to) from the aggregate store (cheap — at most a few thousand small rows even for a day).
3. For top-N, it reads a maintained top-N index for the window rather than ranking every ad on demand.
4. It returns the total, the per-bucket series, and the ranking. All from small derived data — no raw scan.

3. Combined architecture

Putting them together: clicks flow into a durable log; a streaming path rolls them into per-(ad, minute) counts; reads serve those aggregates.

flowchart LR
    Client["Ad click"] -->|"POST /clicks"| GW["Ingest Service"]
    GW -->|"append (partition by ad_id)"| Log[["Durable click log"]]
    Log --> SP["Stream processor<br/>filter → dedup → event-time window"]
    SP -->|"per-(ad, minute) counts"| Agg[("Aggregate / serving store")]
    SP -->|"update ranking"| TopN[("Top-N index")]
    Dash["Advertiser dashboard"] -->|"GET totals / top-N"| Query["Query Service"]
    Query -->|"sum buckets"| Agg
    Query -->|"read ranking"| TopN

The click log is the buffer that decouples spiky ingest from steady processing; the streaming path turns raw clicks into near-real-time per-(ad, minute) counts; the serving store and top-N index are small derived data that dashboards read. Ingest and reads are decoupled — a viral ad's click storm hits the log and the streaming shards, never the dashboard's read path. These live counts are approximate until they settle; producing the exact, billable total via a batch reconciliation pass over the retained raw events is Deep Dive 1 (kept out of this high-level view on purpose).

Deep dives

1. The aggregation pipeline — stream vs batch, and why not count on read

The core decision is when you turn raw clicks into counts. You have three broad choices, and only the last is right at this scale.

Why you pre-aggregate at all. A dashboard asks "clicks for ad_42 in the last hour" constantly, across millions of ads. If each question scans the raw click log, you are reading through billions of rows per query — the raw log grows ~2 TB/day, and a viral ad alone (40,000 clicks/sec × 3,600 s ≈ 144M/hour) can hold over a hundred million clicks in an hour. Instead, the pipeline computes each per-(ad, minute) count once as clicks stream in, and every read is a cheap sum over a handful of small buckets. The expensive work happens once on write, not repeatedly on read.

The Lambda vs Kappa tension. The hard part is that advertisers want the count to move in near real time and to be exact for billing — and those pull in different directions. A pure streaming path is fast but, because of late data and at-least-once delivery, its live numbers are approximate until things settle. A batch reprocessing path over the archived raw events is slow but exact. The common resolution runs both: a fast streaming path for near-real-time approximate counts, and a batch/reprocessing path that periodically recomputes exact, corrected totals from the raw log (reconciliation). Running one codebase that can do both — stream live and reprocess history — is the Kappa simplification of the two-system Lambda approach.

GET total(ad_42, last 1h)
  → scan raw clicks WHERE ad_id=ad_42 AND event_time in [17:00,18:00)
  → 40,000 clicks/sec × 3,600 s = ~144,000,000 rows to scan
  → per query. Multiply by every ad and every dashboard refresh.

clicks stream in → dedup by impression_id → += event-time MINUTE bucket
  ad_42 @ 17:04 (open minute) running count: 812 → 813 → 814 …   ← pushed to store ~every 1–2 s
  dashboard sums the minute buckets; the open minute refreshes every ~1–2 s

flowchart LR
    Client["Ad click"] -->|"POST /clicks"| GW["Ingest Service"]
    GW -->|"append click"| Log[["Durable log (Kafka)<br/>keeps recent events"]]
    Log --> SP["Stream processor<br/>filter → dedup → window"]
    SP -->|"live counts (approx ~48,120)"| Agg[("Aggregate / serving store")]
    Log -.->|"copy every click"| Lake[("Long-term raw store<br/>(files on S3)")]
    Lake -->|"recompute exact counts"| Batch["Batch reconciliation"]
    Batch -->|"correct → exact 48,133"| Agg
    Dash["Advertiser dashboard"] -->|"GET totals"| Query["Query Service"]
    Query -->|"sum buckets"| Agg
    classDef batch fill:#fef3c7,stroke:#d97706,color:#78350f;
    class Lake,Batch batch
    linkStyle 4,5,6 stroke:#d97706,stroke-width:2px;

SELECT ad_id,
       date_trunc('minute', event_time) AS minute,  -- bucket by when the click happened
       COUNT(DISTINCT impression_id)     AS clicks   -- count each impression once
FROM   raw_clicks                                    -- the saved raw clicks
WHERE  event_time >= :from AND event_time < :to
GROUP  BY ad_id, minute;

2. Windowing, event-time & watermarks — out-of-order and late data

This is the crux most candidates miss. Clicks do not arrive in the order they happened, and some arrive minutes late — a phone loses signal, buffers the click, and sends it when it reconnects. If you bucket clicks by when the system saw them instead of when they happened, your per-minute chart is wrong.

Event-time vs processing-time. Processing-time is when the pipeline handled the event; event-time is when the click actually happened (carried on the event). You must bucket by event-time: a click that happened at 17:04 belongs in the 17:04 minute even if it arrives at 17:07.

Tumbling windows. You slice event-time into fixed, non-overlapping one-minute buckets — tumbling windows. Each click falls into exactly one bucket by its event-time. Per-minute is a good grain: fine enough for charts, and it rolls up into hours and days by summing.

Early emission — why the dashboard is seconds-fresh. A window is not invisible until it closes. The processor keeps a running count for the current (open) minute and pushes it to the serving store continuously — an early/incremental emission every ~1–2 s — so a live query reads finalized past buckets + the open bucket's running total and watches the number climb within seconds. Per-minute is the granularity (chart resolution and the unit that finalizes), not the update cadence. Closing the window (next paragraph) doesn't make the count appear — it makes that minute final. So freshness comes from early emission (seconds); the watermark controls finalization (tens of seconds later); and batch reconciliation makes it exact (deep dive 1).

Watermarks — deciding a window is done. Because clicks arrive late, you can't close the 17:04 bucket the instant wall-clock passes 17:05 — more 17:04 clicks may still be coming. A watermark is the pipeline's estimate that no further events with event-time earlier than T will arrive — a bound/heuristic, not a guarantee, and not "almost all." When the watermark passes 17:05, the pipeline decides the 17:04 window is complete and emits its count. The watermark trails real time by a chosen lag (say 30–60 s) to leave room for stragglers. Because a window can't be closed until every input agrees event-time has advanced, the watermark is the min across input partitions — so a single idle or lagging partition stalls window emission, which is why idle partitions need explicit idleness detection to advance the watermark past them.

Late and out-of-order events — allowed lateness. Even after a window is emitted, a very late click can show up. You configure an allowed lateness — a grace period during which a late click still updates the already-emitted window (the 17:04 count gets bumped and re-emitted), and that correction must also propagate to the top-N ranking index (the ZSET) so the ranking stays consistent with the corrected bucket. Events later than that are either dropped or routed to a side channel and folded in by the batch reconciliation pass (deep dive 1). So there are three tiers: on-time clicks land before the watermark; slightly-late clicks land within allowed lateness and correct the window; very-late clicks are caught by the batch recompute.

event-time minute buckets (tumbling), watermark lags ~45s:

 wall clock 17:05:45  ── watermark reaches 17:05 ──► close & emit 17:04 window
   click @ 17:04:10 arrives 17:04:12  → in time,   17:04 bucket +1
   click @ 17:04:50 arrives 17:05:30  → in time,   17:04 bucket +1  (out of order, still fine)
   click @ 17:04:30 arrives 17:06:10  → LATE (past watermark) but within allowed lateness (say 2m)
                                       → 17:04 bucket +1, window re-emitted with corrected count
   click @ 17:04:05 arrives 17:20:00  → VERY late, past allowed lateness
                                       → dropped from stream; picked up by batch reconciliation

Why wall-clock windows undercount. If instead you bucket by arrival time, every one of those late 17:04 clicks lands in a later minute (or is lost when its "real" minute was already finalized). The 17:04 total reads low, and the minute the late click landed in reads high — the chart is distorted. Only event-time windows with watermarks plus lateness handling put each click in its true minute and let the total settle correctly.

3. Exactly-once counting, idempotency & hot ads

Two things can corrupt the count: an event counted twice, and a single hot ad overwhelming one aggregator. Handle both.

Exactly-once as an effect, via dedup — not exactly-once delivery. Durable logs and queues generally guarantee at-least-once delivery — after a retry or a consumer restart, the same click can be processed twice. You do not get true exactly-once delivery; what you engineer is exactly-once effect: the click counts once no matter how many times it's delivered. Two mechanisms combine:

• Dedup by impression_id. Each click carries the impression id it was served with. The aggregator remembers which impression ids it has already counted and ignores repeats — so counting is idempotent. A duplicate (a retry, or a repeat click on the same rendered ad) is a no-op, not a second increment. This state must be bounded — you can't keep every impression id forever at billions of ids/day. Key the dedup set per window and TTL it to the watermark + allowed-lateness horizon: once a window is finalized past its grace period, its seen-ids set is evicted. A duplicate that arrives after that eviction is not caught by the stream — it's reconciled by the batch pass over the full raw log instead. Even bounded, this seen-ids state (billions of ids over the retained horizon) is a real memory bottleneck, often the pipeline's largest state.
• Checkpointed stream offsets. The processor periodically checkpoints how far it has consumed the log alongside its aggregation state. After a crash it resumes from the last checkpoint and replays only the un-checkpointed tail; combined with impression_id dedup, any replayed click that was already counted is ignored.
• Idempotent sink write. The third leg, easy to forget: the write into the serving store must itself be idempotent — overwrite the bucket to its computed value (or commit the offset and the bucket update in one transaction), never blind-increment. Otherwise a replay after the last checkpoint re-produces a bucket that was already written and double-increments it, defeating the dedup and offset work. Dedup + checkpointed offsets + idempotent sink write together yield exactly-once effect on the count even though delivery is at-least-once.

Fraud/bot filtering before counting. Before a click is counted it passes a filter stage — drop obvious bot and fraudulent clicks (basic bot signals, many clicks from one source, impossible click rates). This runs as a stage in the pipeline ahead of dedup and aggregation, so invalid clicks never reach the totals. Deep fraud analysis can be an offline pass that retroactively invalidates clicks and feeds the batch reconciliation.

Hot ads — the skew problem. A viral ad takes tens of thousands of clicks/sec onto one ad_id. Since the log is partitioned by ad_id, all of that ad's clicks land on one partition and are aggregated by one processor task — which can't keep up, while other tasks sit idle. This is the same hot-key pattern as a viral like counter. Here are three approaches, worst to best.

flowchart LR
    W["40,000 clicks/sec<br/>all for ad_42"] -->|"one partition"| T["single aggregator task<br/>counter(ad_42)"]
    T --> B["one task saturated<br/>lag grows, backpressure<br/>other ads' clicks delayed too"]
    classDef bad fill:#fee2e2,stroke:#dc2626,color:#7f1d1d;
    class T bad

ad_42 @ 40,000 clicks/sec
  count 1 in 10 → process ~4,000/sec, estimate = counted × 10
  live count ≈ 40,000/sec, but ±sampling error
  (all 40,000/sec still land in the raw log → batch recounts exactly)

flowchart LR
    W["40,000 clicks/sec<br/>hash impression_id → shard 0..19"] --> S0["shard ad_42#0<br/>~2,000/s partial"]
    W --> S1["shard ad_42#1<br/>~2,000/s partial"]
    W --> Sd["… shards #2–#18<br/>~2,000/s each"]
    W --> S19["shard ad_42#19<br/>~2,000/s partial"]
    S0 --> R["minute total = sum of 20 partials"]
    S1 --> R
    Sd --> R
    S19 --> R
    classDef good fill:#dcfce7,stroke:#16a34a,color:#14532d;
    class S0,S1,Sd,S19 good

Why a few-seconds-stale real-time count is acceptable. The live dashboard number is a fast estimate — it can lag by seconds and briefly disagree with the settled total, and that is fine, because the batch reconciliation pass is the source of truth for billing. That tolerance is exactly what lets you shard hot ads, close windows on a watermark, and serve pre-summed counts without coordinating every increment in real time.

Failure modes

Nothing here is a special case — each failure is absorbed by a mechanism the design already has, and the authoritative raw log is the ultimate backstop.

• Ingest service crashes. It's stateless, so the load balancer routes around the dead instance and clients retry. A retried click is deduped by its impression_id, so the retry neither loses the click nor double-counts it.
• Log / broker node fails. The log is replicated, so an acked append survives a lost node, and producers retry any unacked append. This is exactly why the ingest service returns 202 only after a durable, replicated append — before that, nothing is promised, so nothing is silently lost.
• Stream processor crashes. It resumes from its last checkpointed offset, replays only the un-checkpointed tail, and restores its aggregation state from the checkpoint. Combined with impression_id dedup and an idempotent sink write, replayed clicks are ignored rather than re-added — exactly-once effect across restarts (deep dive 3).
• Serving / aggregate store fails. It's derived, not authoritative — reads fail over to a replica, and the store can be rebuilt by replaying the log or recomputing from the raw event store. Losing it costs a rebuild, never the underlying counts.
• Silent corruption or a processing bug. Streaming numbers can drift with no visible failure. The batch reconciliation pass recomputes exact totals from the authoritative raw log and overwrites the drift (deep dive 1) — so any streaming-path failure is eventually corrected for billing.

The through-line: the raw click log is the single source of truth — durable and replayable — so everything derived from it (streaming state, serving store, top-N index) is disposable and rebuildable. That is what makes the pipeline fault-tolerant.

Tradeoffs & bottlenecks

• Pre-aggregate vs count on read. Pre-aggregating into per-(ad, minute) buckets makes reads cheap sums but adds a streaming pipeline and a fixed time grain; counting raw events per read is simple but does not survive billions of rows.
• Streaming vs batch (real-time vs exact). Streaming gives near-real-time approximate counts; batch reprocessing gives exact billable totals. Running both (or one replayable Kappa codebase) costs complexity but delivers responsiveness and auditability.
• Watermark lag: latency vs completeness. A longer watermark lag / allowed-lateness catches more late clicks before emitting (more accurate live count) but delays the dashboard; a shorter lag is snappier but produces more late corrections.
• Exactly-once effect, not delivery. You can't buy exactly-once delivery; you engineer exactly-once effect via impression_id dedup + checkpointed offsets, at the cost of tracking seen ids and checkpoint state.
• Hot-ad sharding vs read cost. Sharding a viral ad's counter removes the single-task bottleneck but makes the read sum N partials (mitigated by pre-summing); the long tail stays unsharded.
• Storage split. Raw events are large (~2 TB/day) and kept cold for reprocessing; aggregates are tiny and kept hot for serving — the cost is throughput and pipeline complexity, not per-row size.

Extensions if asked

If the interviewer wants to push beyond the core system, add only the extension that changes the design discussion:

• Sliding-window metrics. "Clicks in the last 5 minutes, updated every minute" needs sliding windows over the per-minute buckets rather than plain tumbling ones — you sum a rolling range of minute buckets.
• Click-through / conversion attribution. Joining clicks to later conversions is a separate stream-join problem (match a click to a conversion event within a window) — a different pipeline fed by this one.
• Multi-dimensional rollups. Counting by ad × geo × device × campaign is a cube of aggregates; you pre-aggregate along the dimensions advertisers filter on, trading storage for query flexibility.
• Advanced fraud detection. Beyond the inline filter, an offline model can retroactively invalidate fraudulent clicks and feed corrections into the batch reconciliation. There's no dedicated guide yet.

What interviewers look for & common mistakes

What interviewers usually reward:

• Pre-aggregating clicks into per-(ad, minute) buckets on a streaming pipeline, and serving reads as cheap sums — never counting raw events per query.
• Bucketing by event-time with watermarks and allowed lateness, and explaining why processing-time (arrival) windows undercount late mobile clicks.
• Exactly-once effect via impression_id dedup + checkpointed offsets, and being precise that this is effect, not exactly-once delivery.
• Naming the hot-ad skew and fixing it with sharded partial counters summed on read (the sharded-counter pattern).
• Streaming for real-time + batch reconciliation for exactness, with the batch/reconciled total as the source of truth for billing.

Before you finish, do a quick mistake check:

• Did you pre-aggregate into per-(ad, minute) buckets instead of counting raw events on read?
• Did you bucket by event-time, close windows on a watermark, and handle late/out-of-order clicks with allowed lateness — not by arrival time?
• Did you dedup by a unique impression_id for exactly-once effect, and note that the log is at-least-once?
• Did you name the viral-ad hot key and shard its counter (partials summed on read)?
• Did you filter bots/fraud before counting?
• Did you keep a fast streaming path for the live count and a batch reconciliation path as the exact source of truth for billing?