Design a URL analytics platform that tracks every click on shortened URLs and provides real-time and historical analytics dashboards. For each click, capture: timestamp, referrer, country, device type, browser, and OS. The system processes 50,000 clicks per second at peak, and users expect near-real-time dashboards (< 30 second delay) showing click counts, geographic distribution, referrer breakdown, and time-series graphs.
In your 90-second answer, cover: - Click event ingestion pipeline at 50K events/sec - Storage strategy for raw events vs. pre-aggregated analytics - How you serve real-time dashboards with low latency - Data retention and archival strategy
Constraints: raw click data must be retained for 1 year, aggregated data forever. Dashboard queries must return in under 500ms.
How to approach it
- Hint 1
At 50K events/sec, you cannot write each click synchronously to a database -- use a streaming pipeline (Kafka) to decouple ingestion from processing.
- Hint 2
Pre-aggregate common queries (clicks per hour, per country, per referrer) so dashboards read from rollup tables instead of scanning raw events.
- Hint 3
Consider a time-series or columnar database (ClickHouse, TimescaleDB) for the analytics workload -- they excel at aggregation queries over time-stamped data.
Ready to answer it out loud?
Record your answer in 1:30 and Preptile scores it 1–10 with specifics — what landed, what you skipped, and what to say next time.
Practising needs an invite code. Join the waitlist and we’ll send you one.