SQL: Active User Query | Dataford Interview Questions

Palantir Foundry telemetry includes user accounts and recorded activity events. Write a PostgreSQL query to identify the most active user during January 2025.

Requirements

Include every user in the activity calculation, including users with no qualifying events.
Count each activity event from 2025-01-01 inclusive through 2025-02-01 exclusive.
Return the user ID, user name, activity count, and timestamp of the user's latest qualifying activity.
Return only the most active user. Break ties by latest activity timestamp, then by the smallest user ID.

Schema

users

Column	Type	Description
user_idPK	INTEGER	Unique user identifier
user_name	VARCHAR(100)	User display name
team_name	VARCHAR(100)	Foundry team associated with the user

activity_events

Column	Type	Description
event_idPK	INTEGER	Unique activity event identifier
user_id	INTEGER	User associated with the event, when known
event_type	VARCHAR(50)	Activity category
occurred_at	TIMESTAMP	Timestamp when the event occurred

Your question is SQL: Active User Query. 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