Design a Service That Ranks the K Most-Viewed Items per Time Window

Design a backend service that continuously tracks how often items are viewed and answers "which K items have been viewed most?" for a few recent time windows. Clients send an event every time a user views an item. The service must absorb a high, bursty event stream and return the ranking quickly to many readers. The interviewer cares less about a box diagram than about the details underneath it: how events travel through a partitioned queue, how counts are batched and aggregated, which database you pick and what its schema looks like, the SQL that produces a ranking, and how caching and scaling keep reads fast. For practice, assume an item is anything with a stable ID (a video, a product, a song) and that each event carries the item ID, a user ID and a timestamp. ### Constraints and Clarifications - Writes (view events) vastly outnumber reads of the ranking, but ranking reads must be fast. - The ranking may trail real time by a short delay; state the freshness you are targeting. - Whether counts must be exact or may be approximate is not settled: ask, design for your answer, and explain what the other choice would change. - No traffic figures are given; state your own scale assumptions before relying on them. ### Clarifying Questions - Which windows are needed: a fixed set such as the last hour, day and week, or arbitrary time ranges? Sliding, or aligned to calendar boundaries? - What is K, and is it the same for every caller? - Is only a global ranking needed, or also rankings per category, region or another dimension? - Should repeat views by the same user, or suspected bot traffic, count? - How stale may the ranking be, and must it ever be exact? ### Part 1 — Requirements, API and high-level design Pin down the functional and non-functional requirements, define the read API and how events are submitted, make rough estimates, and sketch the components from the event producer to the ranking reader. ```hint Split the two paths The path that counts events and the path that answers ranking reads have very different load and latency needs; decide where they meet. ``` #### What This Part Should Cover - Requirements that settle windows, K, freshness and exactness - A read API and an event submission interface - Back-of-the-envelope load and storage estimates that later choices depend on - A component sketch that separates ingestion, aggregation, storage and serving ### Part 2 — Ingestion: partitioned queue, batching and aggregation Explain how events get from producers to aggregated counts. Choose the queue's partitioning key, decide where batching happens, and describe how per-window counts are maintained and how a global top K is assembled from many workers. Cover crash recovery and late or duplicate events. ```hint Who must meet whom Think about which events have to land on the same worker for a worker's partial ranking to be trustworthy when you merge the partial results. ``` #### What This Part Should Cover - The partitioning key and its effect on the correctness of the merged ranking - Where events are batched or pre-aggregated, and the delay that adds - How windows slide and how old counts expire - Recovery from worker crashes without double counting, and the handling of very popular items ### Part 3 — Storage: database choice, schema and the ranking query Pick the database type for aggregated counts and for the published rankings, give the table schema with keys and retention, and write the SQL query that returns the top K items for a window. ```hint Mind the scan Estimate how many rows a window query touches at the finest time granularity you store, and whether a coarser rollup could answer it instead. ``` #### What This Part Should Cover - A justified database choice for write-heavy counters versus small ranking reads - A schema with primary keys, time partitioning and retention - A correct SQL ranking query with a deterministic tie-break - Rollups that keep long-window queries affordable ### Part 4 — Serving, caching and scaling Describe the read path, what you cache and how it is refreshed, and how the system scales as event volume, item count and reader traffic grow. Include what readers see when a component fails. ```hint Count the distinct answers Notice how many different responses the read API can actually return, and what that implies for caching. ``` #### What This Part Should Cover - Cache contents, refresh trigger and behavior when the cache is cold - Horizontal scaling of each tier, including how partitions are added - Degraded behavior under failures, such as serving a stale ranking - Metrics that reveal lag and freshness problems ### What a Strong Answer Covers - Requirements and estimates that actually drive later decisions - A partitioning scheme under which the merged ranking is provably correct - Batching and pre-aggregation that trade a small delay for large write savings - A concrete schema and correct SQL, with rollups and retention - A read path served by a cheap, precomputed, cached result - Reasoned exact-versus-approximate trade-offs and failure handling ### Follow-up Questions - One item goes viral and receives a large share of all events. What breaks, and how do you fix it? - How would you support arbitrary time ranges instead of a fixed set of windows? - How would you add per-category or per-region rankings without multiplying cost by the number of categories? - When would you switch to an approximate counting structure, and how would you bound its error?