Find top video category by average time

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).

Find top video category by average time spent

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)

Table pins

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

Sample data:

category_id	pin_format	pin_id	time_spent_sec
10	video	1	12
10	static	2	20
11	video	3	30
NULL	video	4	25
11	video	5	0

Table categories

column	type	constraints
category_id	INT	PRIMARY KEY
category_name	VARCHAR(100)

Sample data:

category_id	category_name
10	Food
11	Travel

Expected output

Single row giving the category name and its highest average time_spent_sec among valid video pins, with average rounded to two decimals. On the sample data, only 'Food' and 'Travel' have valid video rows; 'Travel' has the highest average (30.00), so it is returned.

avg_time	category_name
30	Travel

Hints

1. First aggregate by category_name to compute the average time_spent_sec over filtered video pins.
2. After computing per-category averages, order by average descending and category_name ascending to break ties, then pick the first row.