Find top video category by average time
Company: Pinterest
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
You are given a pandas DataFrame 'pins' with columns [pin_id:int, category_id:int, time_spent_sec:float, pin_format:string] and a dict 'category_map' mapping category_id -> category_name. Write Python to return a tuple (category_name, avg_time) for the category with the highest average time_spent_sec among rows where pin_format == 'video'. Requirements: exclude rows with null/NaN category_id or nonpositive time; break ties by lexicographically smallest category_name; time complexity O(n), extra space O(k) where k is distinct categories; round avg_time to two decimals.
Sample input:
'pins'
pin_id | category_id | time_spent_sec | pin_format
1 | 10 | 12 | video
2 | 10 | 20 | static
3 | 11 | 30 | video
4 | null | 25 | video
5 | 11 | 0 | video
category_map = {10: "Food", 11: "Travel"}
Expected output on sample: ("Travel", 30.00).
Overview: This question evaluates a candidate's ability to perform data manipulation and aggregation in Python (pandas), covering skills such as filtering, handling nulls and nonpositive values, mapping categorical IDs to names, rounding numerical results, and applying tie-breaking rules.
You are given two tables. Table `pins` stores user interactions with pins, and table `categories` maps `category_id` to `category_name`.
Write a single SQL query to return exactly one row containing `(category_name, avg_time)` for the category with the highest average `time_spent_sec` among rows where `pin_format = 'video'`.
Requirements:
- Exclude rows where `category_id` is NULL.
- Exclude rows where `time_spent_sec` is not positive (i.e., `time_spent_sec <= 0`).
- If multiple categories share the same highest average time, break ties by choosing the lexicographically smallest `category_name`.
- Round the average time to two decimal places in the output.
Return columns:
- `category_name` (the winning category's name)
- `avg_time` (the rounded average time spent in seconds for that category)
Tables
pins(pin_id INT, category_id INT, time_spent_sec DECIMAL(10,2), pin_format VARCHAR(20))
categories(category_id INT, category_name VARCHAR(100))
Hints
- First aggregate by category_name to compute the average time_spent_sec over filtered video pins.
- After computing per-category averages, order by average descending and category_name ascending to break ties, then pick the first row.