Design an Ad Click Aggregator for Last-Year Counts Using an Existing Logging API

Design a system that aggregates ad clicks. Unlike the commonly practiced version of this problem, which focuses on near-real-time counts over the last few minutes, this aggregator must answer questions about clicks over the **last year**, and it must integrate with an **existing logging API** through which click events are recorded. The scale and functional requirements differ from the usual version, so derive the design from the requirements you clarify rather than from a memorized template. ```hint What a one-year window changes Compare what it takes to count the last minute with what it takes to count the last year. Which of the usual components become unnecessary, and which become essential? ``` ```hint The logging API is your input Before designing the pipeline, pin down what the logging API gives you (a stream, batch exports or a query interface) and with which guarantees and limits. ``` ```hint Exact or approximate Decide how accurate the counts must be. Billing-grade counts and dashboard counts lead to different designs. ``` ### Constraints and Clarifications - Assume each click record identifies at least the ad and the time of the click. Ask what else it carries, such as a unique click ID, a user or session, the campaign or the placement. ### Clarifying Questions - Does "last year" mean a rolling 365-day window ending now, the previous calendar year, or any range within the past year? - What does the logging API provide: a real-time stream, periodic batch exports, or a query interface? What are its delivery guarantees, latency and rate limits, and can it replay history? - Which queries must be answered: total clicks per ad, per campaign or per advertiser, daily or hourly breakdowns, top ads? How fresh must the answers be? - Are the counts used for billing (exact, deduplicated, auditable) or for dashboards (approximate is acceptable)? - How many ads, clicks per day and queries per second should the system handle? - Must invalid or fraudulent clicks be excluded before counting? ### What a Strong Answer Covers - Requirements re-derived for a one-year horizon, with the differences from the real-time version made explicit - A concrete integration with the logging API: ingestion mode, checkpoints, backfill, and respect for its limits - Deduplication and handling of late or out-of-order events, with a defined correctness level - Storage that answers a one-year query quickly, typically pre-aggregated rollups at a chosen granularity, plus retention - How counts are corrected or recomputed when ingestion fails or a bug is found - Scaling, failure handling and data-quality monitoring ### Follow-up Questions - The logging API is unavailable for several hours and then replays its backlog. What happens to the counts? - The product now also wants per-minute counts for the last hour. What do you add? - How would you answer "top N ads by clicks over the last year" quickly? - A bug double-counted clicks for two weeks some months ago. How do you correct the history?