Find top category by video time spent

Q: Find top category by video time spent

This is a Data Manipulation (SQL/Python) interview question from Pinterest for Data Scientist roles. View the full question and solution on PracHub.

Q: How do I approach Data Manipulation (SQL/Python) interview questions?

Data Manipulation (SQL/Python) questions require understanding of core concepts and practice. PracHub provides solutions with explanations to help you master data manipulation (sql/python) interviews.

Question

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 top category by video time spent

Comments (0)