Find top category by video time spent
Company: Pinterest
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
Pandas required. You are given a DataFrame df with columns: user_id (int), pin_id (int), pin_type (str), category (str or None), time_spent_sec (numeric). Goal: among video pins, find the canonical category with the highest average time_spent_sec. Requirements:
- Consider pin_type values case-insensitively and treat 'vedio' as 'video' (data quality issue).
- Normalize category by lowercasing and stripping whitespace, then map via category_map; if a key is missing after normalization or category is null/empty, map to 'unknown'.
- Exclude rows where time_spent_sec is null or non-positive.
- Return a two-field result: top_category (str), avg_time_spent_sec (float, rounded to 2 decimals).
Example inputs
category_map = {
'home': 'lifestyle',
'food & drink': 'food',
'recipe': 'food',
'travel': 'travel'
}
df (illustrative rows)
user_id | pin_id | pin_type | category | time_spent_sec
1 | 10 | 'video' | 'Food & Drink' | 120
2 | 11 | 'video' | None | 200
3 | 12 | 'static' | 'Home' | 90
4 | 13 | 'video' | 'Recipe' | 240
5 | 14 | 'video' | 'DIY' | 180
6 | 15 | 'vedio' | 'food & drink ' | 60
What is the top_category and its average time among video pins after mapping and cleaning?
Overview: This question evaluates proficiency in pandas-based data manipulation and aggregation, testing competencies in data cleaning, normalization, mapping, filtering, and computing summary statistics.
You are given engagement data for pins.
Task: Among video pins only, find the canonical category with the highest average `time_spent_sec` after cleaning and mapping.
Cleaning / mapping rules:
1) Treat `pin_type` case-insensitively and fix the data quality issue where `'vedio'` should be treated as `'video'`.
2) Normalize `category` by lowercasing and trimming whitespace.
- If `category` is NULL or becomes an empty string after trimming, treat it as missing.
- Map the normalized category using the `category_map` table.
- If there is no matching key in `category_map`, map the category to `'unknown'`.
3) Exclude rows where `time_spent_sec` is NULL or `time_spent_sec <= 0`.
Return exactly two fields:
- `top_category` (the canonical category string)
- `avg_time_spent_sec` (average time spent for that canonical category among video pins, rounded to 2 decimals)
If there is a tie, return the lexicographically smallest `top_category` among the tied categories.
Tables
pin_engagement(user_id INT, pin_id INT, pin_type VARCHAR(20), category VARCHAR(100), time_spent_sec DECIMAL(10,2))
category_map(category_key VARCHAR(100), canonical_category VARCHAR(50))
Hints
- Normalize strings with LOWER(TRIM(...)) and treat empty strings as NULL via NULLIF(..., '').
- Use a LEFT JOIN to the mapping table and COALESCE to default missing mappings to 'unknown'.
Community answers
Answer by sindhujakasula03
-- Write your SQL query hereWith cte as ( SELECT , lower(trim(category)) as categorym FROM pin_engagement where pin_type ilike '%v_d_o%' and time_spent_sec is not null and time_spent_sec > 0),x as ( SELECT , case when (canonical_category is Null) then 'unknown' else canonical_category end as category_canonicalm from cte left join category_map cm on cte.categorym=cm.category_key)select category_canonicalm as top_category, round(avg(time_spent_sec), 2) as avg_time_spent_sec from x group by category_canonicalmorder by avg_time_spent_sec desc limit 1