SQL for Engagement at Scale

HardSQL · PostgreSQL

Problem

Write a performant SQL query to aggregate user engagement metrics across billions of rows of gaming telemetry.

Use the provided users, games, and telemetry_events tables. Include only non-bot users, valid game records, and telemetry from January and February 2025.

Output

One row per month and user region.
Columns: month_start, region, active_users, sessions, telemetry_events, play_minutes, wins, matches, win_rate, and active_user_change_pct.
Include regions with activity, retain NULL regions, sort by month and region with NULL regions last, and calculate the percentage change from the previous month for each region.

Schema

users

Column	Type	Description
user_idPK	BIGINT	Unique Discord user identifier
username	VARCHAR(80)	Discord username
region	VARCHAR(20)	User's operational region
created_at	TIMESTAMPTZ	Account creation timestamp
is_bot	BOOLEAN	Whether the account is an automated bot

games

Column	Type	Description
game_idPK	BIGINT	Unique game identifier
game_name	VARCHAR(120)	Game title
genre	VARCHAR(40)	Game genre

telemetry_events

Column	Type	Description
event_idPK	BIGINT	Unique telemetry event identifier
user_id	BIGINT	User who generated the event
game_id	BIGINT	Referenced game
session_id	VARCHAR(40)	Gaming session identifier
event_ts	TIMESTAMPTZ	Event occurrence timestamp
event_type	VARCHAR(40)	Telemetry event type
duration_seconds	INTEGER	Duration associated with the event
event_value	VARCHAR(40)	Event outcome or categorical value

Tablesusersgamestelemetry_events