Aggregate video time and unique pins in Python

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.

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.

Table categories

column	type	constraints
category_id	INT	PRIMARY KEY, NOT NULL
category_name	VARCHAR(50)	NOT NULL

Sample data:

category_id	category_name
1	Fashion
2	Food
3	DIY

Table pins

column	type	constraints
pin_id	INT	PRIMARY KEY, NOT NULL
category_id	INT	NOT NULL
time_spent	DECIMAL(10,2)
pin_format	VARCHAR(20)	NOT NULL

Sample data:

category_id	pin_format	pin_id	time_spent
1	video	10	18
2	static	11	6
1	video	12	22
3	video	13	15
2	video	14	NULL

Expected output

Single row containing the category_name of the video pins with the highest average non-NULL time_spent, with the average rounded to 2 decimals. Ties are broken by category_name alphabetically.

average_time	category_name
20	Fashion

Hints

1. Filter to rows where pin_format is 'video' and time_spent is not NULL before aggregating.
2. 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.

Table users

column	type	constraints
user_id	VARCHAR(50)	PRIMARY KEY, NOT NULL

Sample data:

user_id
user1
user2
user3

Table user_pins

column	type	constraints
user_id	VARCHAR(50)	NOT NULL
pin_id	INT	NOT NULL

Sample data:

pin_id	user_id
1	user1
2	user1
2	user1
1	user2
2	user2
3	user2

Expected output

Single row with the average number of unique pins per user, counting users with no pins as 0, rounded to 2 decimals.

average_unique_pins
1.67

Hints

1. LEFT JOIN users to user_pins so that users with no interactions are still included.
2. Use COUNT(DISTINCT pin_id) per user, then take the AVG of those per-user counts and round the result.