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.
Quick Answer: 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.