Write SQL for top categories and highly active users
Company: Pinterest
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: easy
Interview Round: Technical Screen
You are given three tables:
### 1) `impression`
Event-level table of user impressions.
- `impression_id` BIGINT (PK)
- `user_id` BIGINT (FK → `user.user_id`)
- `pin_id` BIGINT (FK → `pin_info.pin_id`)
- `surface` VARCHAR (e.g., 'home', 'search', 'profile', etc.)
- `impression_ts` TIMESTAMP (when the impression happened)
- `country_code` VARCHAR (ISO country code; assume it is populated for the event)
### 2) `user`
- `user_id` BIGINT (PK)
- `created_ts` TIMESTAMP
### 3) `pin_info`
- `pin_id` BIGINT (PK)
- `category` VARCHAR
Assumptions:
- Use UTC for date boundaries.
- “Today” means `DATE(impression_ts) = CURRENT_DATE`.
---
## Task A
For **each country**, find the **pin category** with the **highest number of impressions today**.
Requirements:
- If there is a tie for the highest impression count, return **all tied categories** for that country.
- Output columns:
- `country_code`
- `category`
- `impression_cnt`
---
## Task B
For **each country**, compute the **percent of active users** who are **highly active**.
Definitions (within the **last 7 days**, inclusive of today):
- **Active user**: a user with **≥ 1 impression event**.
- **Highly active user**: a user who satisfies **both**:
1) Active on **≥ 4 distinct days** (based on `DATE(impression_ts)`), AND
2) On **at least one day**, used **≥ 3 distinct surfaces** (based on distinct `surface` values that day).
Requirements:
- Output columns:
- `country_code`
- `active_users`
- `highly_active_users`
- `pct_highly_active` (as a decimal or percent; specify which in your answer)
- Clearly handle divide-by-zero when a country has 0 active users in the window.
Overview: This question evaluates proficiency in SQL-based data manipulation and analytics, including joins, aggregations, date/time handling, grouping, tie-handling for top-N results, and percentage calculations to produce per-country category rankings and user activity metrics.
Top pin category by impressions per country (today, using DENSE_RANK)
You are given three tables: users, impressions, and pin_info.
For each country, find the pin category (or categories, in case of ties) with the highest number of impressions on 2025-06-01.
Return: country, category, impression_count.
Requirement: use DENSE_RANK to handle ties (i.e., if multiple categories share the top impression count in a country, return all of them).
Tables
users(user_id INT, country VARCHAR(2))
pin_info(pin_id INT, category VARCHAR(50))
impressions(impression_id INT, user_id INT, pin_id INT, impression_date DATE, surface VARCHAR(20))
Hints
- Aggregate impressions by (country, category) for the given date.
- Use DENSE_RANK() partitioned by country and keep rank = 1 to include ties.
Percent of active users who are highly active per country (last 7 days)
You are given three tables: users, impressions, and pin_info.
Compute, for each country, the percent of active users who are highly active during the 7-day window FROM 2025-05-26 TO 2025-06-01 (inclusive).
Definitions:
- An "active user" is a user with at least 1 impression on any day in the 7-day window.
- A "highly active" user satisfies BOTH:
1) Active on at least 4 distinct days in the 7-day window.
2) On at least one day in the 7-day window, the user used at least 3 distinct surfaces (surfaces are stored in impressions.surface).
Return one row per country with:
- country
- active_users
- highly_active_users
- pct_highly_active (as a percentage from 0 to 100, rounded to 2 decimals).
Tables
users(user_id INT, country VARCHAR(2))
pin_info(pin_id INT, category VARCHAR(50))
impressions(impression_id INT, user_id INT, pin_id INT, impression_date DATE, surface VARCHAR(20))
Hints
- First roll up impressions to (user_id, date) so you can count distinct surfaces per day.
- Then roll up to per-user metrics: number of active days and whether any day has 3+ surfaces.
Community answers
Answer by zah.ziaei
**with c_c as (select i.country_code as country, p.category as category
, sum(i.impression_id) as total_imp
from impression as i
join user as u
on i.user_id=u.user_id
join pin_info as p
on p.pin_id=i.pin_id
where Date(i.impression_ts)=current_Date
group by i.country_code, p.category),
c_cc as (
select country, category, total_imp,
rank() over(partition by country, category order by total_imp desc) as rank_im
from c_c)
select select country, category, total_imp
from c_cc
where rank_im=1
**
Answer by sindhujakasula03
task A: Find Pin Category with highest impressions TODAY for EACH Country
Start with impressions table
-Filter all impressions to today impression_ts::date = current_date
-Group the result set by country_code, pin_id get count(impression_id) as impression_cnt
Get Max impressions for each countrycode and pid_id. Use Max fn or rank fn
join pin_info to get the category label
And output country_code, category, impression_cnt