Aggregate video time and unique pins in Python
Company: Pinterest
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
Part A (category by average time for videos):
You receive a list of pin engagement rows and a category map.
pins = [
{"pin_id": 10, "category_id": 1, "time_spent": 18.0, "pin_format": "video"},
{"pin_id": 11, "category_id": 2, "time_spent": 6.0, "pin_format": "static"},
{"pin_id": 12, "category_id": 1, "time_spent": 22.0, "pin_format": "video"},
{"pin_id": 13, "category_id": 3, "time_spent": 15.0, "pin_format": "video"},
{"pin_id": 14, "category_id": 2, "time_spent": None, "pin_format": "video"}
]
category_map = {1: "Fashion", 2: "Food", 3: "DIY"}
Task: Among video pins only, compute the category_name with the highest average time_spent. Ignore records where time_spent is None. Break ties by category_name alphabetically. Return (category_name, average_time) with average_time rounded to 2 decimals. Implement an O(n) solution using only Python 3.10+ standard library.
Part B (average unique pins per user):
You receive a mapping of user -> list of pin_ids they interacted with.
user_pins = {"user1": [1,2,2], "user2": [1,2,3], "user3": []}
Task: Compute the average number of unique pin_ids per user. Empty lists count as 0. Return a float rounded to 2 decimals. Aim for O(total_items) time and O(U) extra space, where U is number of users.
Overview: This question evaluates competency in data aggregation, deduplication, and algorithmic efficiency by requiring computation of category-level averages with tie-breaking and per-user unique counts, demonstrating proficiency in Python and SQL-style data manipulation.
Category with Highest Average Time for Video Pins
You are given two tables: pins and categories. The pins table stores engagement records for pins, including how much time was spent on each pin and its format. The categories table maps category IDs to human-readable category names.
Write a SQL query to find, among video pins only, the category_name with the highest average time_spent. Ignore any pin records where time_spent is NULL. If multiple categories share the same highest average time_spent, break ties by category_name in alphabetical order. Return a single row with (category_name, average_time), where average_time is rounded to 2 decimal places.
Tables
categories(category_id INT, category_name VARCHAR(50))
pins(pin_id INT, category_id INT, time_spent DECIMAL(10,2), pin_format VARCHAR(20))
Hints
- Filter to rows where pin_format is 'video' and time_spent is not NULL before aggregating.
- Group by category_name and use a window function or ORDER BY with a limit to pick the top category by average time_spent, breaking ties alphabetically.
Average Number of Unique Pins per User
You are given a users table and a user_pins table. The users table lists all users. The user_pins table records which pin_ids each user has interacted with; users may have duplicate pin_ids in this table, and some users may have no rows in user_pins.
Compute the average number of unique pin_ids per user. Users with no interactions (no rows in user_pins) should be counted as having 0 unique pins. Return a single float value rounded to 2 decimal places as average_unique_pins.
Tables
users(user_id VARCHAR(50))
user_pins(user_id VARCHAR(50), pin_id INT)
Hints
- LEFT JOIN users to user_pins so that users with no interactions are still included.
- Use COUNT(DISTINCT pin_id) per user, then take the AVG of those per-user counts and round the result.