Top 10% Users by Impressions | Dataford Interview Questions

Write a query to find the top 10% of users based on their total ad impressions, grouped by geographic region, for Quantcast reporting.

Use all users when determining each region's population. Round the regional 10% threshold up to the next whole user, and use ascending user_id as the tie-breaker.

Output

One row per selected user, with geographic_region, user_id, user_name, total_ad_impressions, and regional_rank.
Include only the top 10% of users within each region.
Order by geographic_region, regional_rank, and user_id ascending.

Schema

users

Column	Type	Description
user_idPK	INT	Unique identifier for a user
user_name	VARCHAR(100)	Display name of the user
geographic_region	VARCHAR(50)	Geographic reporting region for the user

ad_impressions

Column	Type	Description
impression_event_idPK	INT	Unique identifier for an impression record
user_id	INT	User associated with the impression record
impression_count	INT	Number of ad impressions represented by the record

Your question is Top 10% Users by Impressions. 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