Window Functions for Ranking | Dataford Interview Questions

Your question is Window Functions for Ranking. Start with the requirements and the three tables on the right.

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

Problem

Whova wants to identify the most engaged attendees during a conference and monitor how their engagement accumulates across event dates. Write a PostgreSQL query using multiple CTEs and window functions.

Requirements

Include only engagement recorded for Whova conference events from June 1 through June 4, 2025, inclusive.
Aggregate engagement points by user and event date, treating NULL points as zero.
Calculate each user's total engagement and rank users from highest to lowest total. Users with equal totals must receive the same rank.
Calculate a chronological running engagement total for each user with SUM() OVER.
Return one row per user and event date, ordered by engagement rank, user ID, and event date.

Schema

whova_users

Column	Type	Description
user_idPK	INTEGER	Unique Whova attendee identifier
full_name	VARCHAR(100)	Attendee display name

conference_events

Column	Type	Description
event_idPK	INTEGER	Unique conference event identifier
event_name	VARCHAR(150)	Name of the conference session or activity
event_date	DATE	Calendar date of the event

user_engagement

Column	Type	Description
engagement_idPK	INTEGER	Unique engagement record identifier
user_id	INTEGER	References whova_users.user_id
event_id	INTEGER	References conference_events.event_id
engagement_type	VARCHAR(40)	Type of attendee interaction
points	INTEGER	Points assigned to the interaction