Real-Time User Interaction Schema | Dataford Interview Questions

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.

Output

One row per user with user_id, user_name, interaction_count, and last_interaction_at
Include every user, including users with no events
Sort by interaction count descending, then user ID ascending

Schema

users

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

user_interactions

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

Your question is Real-Time User Interaction Schema. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem