Pinterest·Data Scientist·Technical Phone Screen
- Given a schema with daily impressions, users, and countries tables, find the category with the highest total impressions per country for August 2025. Return country name, top category, total impressions for that category, and its share of the country's August impressions rounded to 4 decimal places. Break ties by picking the lexicographically smallest category name.
- Using app_events and feature_usage tables, compute a heavy user rate per country for a 7-day window. A heavy user is someone active on at least 4 distinct days in the window AND who used 3 or more distinct features on at least one of those days. Active users are anyone with at least one app_events row in the window. Exclude users with no country mapping from both numerator and denominator.
- How would you index the daily_impressions table (with roughly a billion rows) to make both the August category aggregation query and the heavy user rate query performant?
“The three-table join part was fine.”