Analyze Global Engagement and Impressions with SQL Queries
Company: Pinterest
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
impressions
+---------+---------+----------+----------------+
| user_id | country | category | impression_cnt |
+---------+---------+----------+----------------+
| 1 | US | Sports | 120 |
| 2 | US | News | 80 |
| 3 | CN | Games | 200 |
+---------+---------+----------+----------------+
user_activity
+---------+-------------+
| user_id | activity_dt |
+---------+-------------+
| 1 | 2023-09-01 |
| 1 | 2023-09-02 |
| 1 | 2023-09-04 |
| 2 | 2023-09-01 |
| 3 | 2023-09-05 |
+---------+-------------+
feature_usage
+---------+-------------+-----------+
| user_id | usage_dt | feature |
+---------+-------------+-----------+
| 1 | 2023-09-02 | search |
| 1 | 2023-09-02 | share |
| 1 | 2023-09-02 | save |
| 2 | 2023-09-03 | search |
| 3 | 2023-09-05 | save |
+---------+-------------+-----------+
##### Scenario
A global content platform wants to understand engagement and impressions across countries in the last week.
##### Question
Write SQL to
(a) return, for every country, the content category with the highest total impressions and its count;
(b) compute the weekly heavy-user rate, where a heavy user is one who was active on at least 4 distinct days in the last 7 and, on at least one of those days, used 3 or more distinct features.
##### Hints
Join the three tables, use GROUP BY with conditional aggregation, distinct counts for days/features, and window/CTE for ranking the top category per country.
Overview: This question evaluates proficiency in SQL-based data manipulation and analytical querying, covering aggregation, grouping, ranking, and the correlation of impressions with user engagement and feature usage metrics.
Top Category by Country
For each country, return the content category with the highest total impressions and its aggregated count. If multiple categories tie for the maximum impressions in a country, break ties by choosing the category with the lexicographically smallest name (ascending).
Tables
impressions(user_id INTEGER, country VARCHAR(2), category VARCHAR(50), impression_cnt INTEGER)
Hints
- Aggregate impressions by country and category first.
- Use ROW_NUMBER() partitioned by country and ordered by total_impressions DESC, category ASC to pick the top category per country.
Weekly Heavy-User Rate
Compute the weekly heavy-user rate over the last 7 days ending at the maximum date found across user_activity and feature_usage. A heavy user is one who was active on at least 4 distinct days in that 7-day window and, on at least one of those days, used 3 or more distinct features (on a day that is also an activity day). Return a single row with window_start, window_end, active_user_count (users with at least one activity day in the window), heavy_user_count, and heavy_user_rate = heavy_user_count / active_user_count.
Tables
user_activity(user_id INTEGER, activity_dt DATE)
feature_usage(user_id INTEGER, usage_dt DATE, feature VARCHAR(20))
Hints
- Define the 7-day window using the maximum date across both tables, then filter both tables to that window.
- Count distinct activity days per user from user_activity within the window.