Find top category by video time spent

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?

Find the top canonical category by average video time spent

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.

Table pin_engagement

column	type	constraints
user_id	INT	NOT NULL
pin_id	INT	PRIMARY KEY, NOT NULL
pin_type	VARCHAR(20)	NOT NULL
category	VARCHAR(100)
time_spent_sec	DECIMAL(10,2)

Sample data:

category	pin_id	pin_type	time_spent_sec	user_id
Food & Drink	10	video	120	1
NULL	11	video	200	2
Home	12	static	90	3
Recipe	13	video	240	4
DIY	14	video	180	5
food & drink	15	vedio	60	6
Travel	16	Video	30	7
Home	17	video	NULL	8
Home	18	video	-10	9
Recipe	19	static	300	10
	20	video	210	11

Table category_map

column	type	constraints
category_key	VARCHAR(100)	PRIMARY KEY, NOT NULL
canonical_category	VARCHAR(50)	NOT NULL

Sample data:

canonical_category	category_key
lifestyle	home
food	food & drink
food	recipe
travel	travel

Expected output

A single-row result with the canonical category that has the highest average time_spent_sec among cleaned video pins, plus that average rounded to 2 decimals.

avg_time_spent_sec	top_category
196.67	unknown

Hints

1. Normalize strings with LOWER(TRIM(...)) and treat empty strings as NULL via NULLIF(..., '').
2. Use a LEFT JOIN to the mapping table and COALESCE to default missing mappings to 'unknown'.
3. Compute AVG per canonical category, then ORDER BY the average descending and take the top row.