Top 5% Daily Active Users | Dataford Interview Questions

Write a SQL query to identify the top 5% of users based on daily active usage over the last quarter at Okta.

Treat daily active usage as the number of distinct calendar days on which each user generated usage. Use the users and usage_events tables, and include users with no qualifying activity when determining the percentile.

Output

One row per qualifying user with user_id, user_name, active_days, and usage_rank
Include users in the top 5%, including ties at the cutoff
Sort by active_days descending, then user_id ascending

Schema

users

Column	Type	Description
user_idPK	INT	Unique Okta user identifier
user_name	VARCHAR(100)	User display name
email	VARCHAR(255)	User email address

usage_events

Column	Type	Description
event_idPK	INT	Unique usage event identifier
user_id	INT	User associated with the event
activity_date	DATE	Calendar date when usage occurred
usage_type	VARCHAR(50)	Type of Okta product activity

Your question is Top 5% Daily Active Users. 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