Ranking Users From Usage Logs | Dataford Interview Questions

Parse through this dictionary with usage logs on a certain software and rank the top users based on screen time, active user status, and page views.

Use the user dictionary and usage logs for the software named Foundry. Consider only users marked active. Return the three highest-ranked users, using page views as the tie-breaker after screen time.

Output

One row per qualifying active user with user_id, username, is_active, total_screen_time, total_page_views, and user_rank
Include only users with a rank of 3 or better
Sort by user_rank ascending, then user_id ascending

Schema

user_dictionary

Column	Type	Description
user_idPK	INT	Unique user identifier
username	VARCHAR(100)	User display name
is_active	BOOLEAN	Whether the user is currently active

usage_logs

Column	Type	Description
log_idPK	INT	Unique usage-log identifier
user_id	INT	User associated with the usage event
software_name	VARCHAR(100)	Software associated with the usage event
screen_time_minutes	INT	Minutes spent using the software
page_views	INT	Number of pages viewed

Your question is Ranking Users From Usage Logs. 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