Rank Users by Weekly Hours | Dataford Interview Questions

Write a query using window functions to rank users based on their total streaming hours within a specific calendar week on Roku streaming devices. Use the week from January 6 through January 12, 2025, and include only completed sessions on Roku devices.

Output

One row per user with at least one qualifying session.
Return user_id, user_name, total_streaming_hours, and streaming_rank.
Rank users by total hours descending, with ties sharing a rank. Order by rank ascending, then user_id ascending.

Schema

users

Column	Type	Description
user_idPK	INT	Unique Roku user identifier
user_name	VARCHAR(100)	Display name for the user

streaming_sessions

Column	Type	Description
session_idPK	INT	Unique streaming session identifier
user_id	INT	User who streamed during the session
device_type	VARCHAR(50)	Device platform used for streaming
started_at	TIMESTAMP	Session start timestamp
ended_at	TIMESTAMP	Session end timestamp, NULL for incomplete sessions

Your question is Rank Users by Weekly Hours. 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