Design analytical tracking database

Q: Design analytical tracking database

This question evaluates a candidate's ability to design scalable analytics schemas and event-driven data flows for high-throughput, near-real-time tracking, testing competencies in data modeling, ingestion architecture, and query performance.

Q: How do I approach System Design interview questions?

System Design questions require understanding of core concepts and practice. PracHub provides solutions with explanations to help you master system design interviews.

Question

Design an Analytical Database for User Actions

Context

You are building an analytics data store to track user behavior and commerce events so that product and business teams can run reliable, fast queries. Assume web and mobile clients emit events at scale (millions/day). Latency target for analytics is minutes, not milliseconds.

Events to Track

login
search_item
view_item
add_to_cart
view_cart
purchase
view_order

Example Queries to Support

How many users from Asia viewed item A this month?
What are the three highest-value orders at present?

Task

Describe the schema, event model, and data flow for an analytical database that can efficiently answer the above and similar pre-written queries.