Katkat
Back

Collect and Query Recent User Activity

LinkedInSoftware Engineer · Onsite
MediumSoftware EngineerOnsiteSystem Design

Last updated

Design a scalable user activity ingestion and querying system capable of computing metrics over rolling time windows (1 minute, 1 hour, 24 hours) with deduplication and late-arriving event handling.

Scenario

Many consumer platforms need to track member interactions (e.g., profile views, post impressions, connection requests, clicks) to power live analytics, fraud detection, and engagement dashboards. You are tasked with designing an end-to-end user activity tracking system that continuously captures raw event telemetry and serves aggregated metrics over sliding or tumbling windows of the last minute, last hour, and last 24 hours.

Requirements

Functional Requirements

  • Event Collection: Ingest user activity events containing a user ID, action type, target ID, metadata, and a client-side timestamp.
  • Deduplication: Filter out duplicate events caused by client retries or network replays using unique event IDs.
  • Aggregation Queries: Expose an API to retrieve aggregated activity counts or lists of recent events for a given user over three specific windows: past 1 minute, past 1 hour, and past 24 hours.
  • Late Data Handling: Support out-of-order and late-arriving events within a reasonable tolerance window (e.g., up to 10 minutes).

Non-Functional Requirements

  • Scale: Support 500 million daily active users (DAU) generating roughly 50 billion events per day (average ~600,000 events/sec, peaking at 1.5 million events/sec).
  • Query Latency: P99 read latency under 50 ms for minute-level queries, and under 200 ms for hour/day queries.
  • Freshness: Ingested events should be reflected in query results within 5 seconds.
  • Availability & Durability: Highly available ingestion (99.99%) with zero data loss for committed events.

What Your Design Should Cover

  1. API Design: Define the ingestion endpoint and the query interface for metric retrieval.
  2. Data Pipeline & Architecture: Design the streaming ingestion tier, stream processor, and storage layers (e.g., message bus, stream engine, caching, persistent time-series or key-value stores).
  3. Aggregation Strategy: Explain how to compute aggregates efficiently across minute, hour, and day windows without full table scans on every query.
  4. Deduplication & Late-Arrival Handling: Describe state management and watermarking techniques used to ensure accurate counts.
  5. Trade-offs & Scalability: Discuss partitioning keys, hot-key mitigations, and storage retention policies.

Hints

Hint 1

Differentiate storage and computation tiers by window size: an in-memory sliding window or Redis buffer can serve minute-level data, while pre-aggregated hourly buckets in a wide-column store can serve 24-hour queries.

Hint 2

Use a unique event identifier with a short-lived cache (like Redis with a 10-15 minute TTL) to deduplicate incoming events before or during stream processing.

Hint 3

Incorporate event-time processing with watermarks in your stream processor (e.g., Apache Flink) so late events can update existing bucket counts accurately.

Follow-up questions

  • How does your design handle celebrity or influencer users whose activity generates sudden bursts of millions of events (hot-partition problem)?
  • If raw events must be audited or replayed to recalculate historic metrics from scratch, how does your storage layer support backfilling?
  • How would you adapt the system if users also need distinct counts (e.g., unique viewers in the last 24 hours) rather than total raw event counts?