How would you design a schema for a new feature that tracks user interactions in real-time for Uber?
Define a normalized schema for users and timestamped interaction events, then write a PostgreSQL query that summarizes recorded activity for every user. Include users without interactions and support event metadata and client-platform tracking.
user_id, user_name, interaction_count, and last_interaction_at| Column | Type | Description |
|---|---|---|
| user_idPK | BIGINT | Unique Uber user identifier |
| user_name | VARCHAR(100) | Display name for the user |
| created_at | TIMESTAMPTZ | Timestamp when the user record was created |
| Column | Type | Description |
|---|---|---|
| interaction_idPK | BIGINT | Unique interaction event identifier |
| user_id | BIGINT | User who generated the interaction |
| interaction_type | VARCHAR(50) | Type of user interaction |
| platform | VARCHAR(30) | Client platform that emitted the event |
| occurred_at | TIMESTAMPTZ | UTC timestamp when the interaction occurred |
| metadata | JSONB | Flexible event-specific attributes |