Find top video category by average time

Q: How do I practice SQL interview questions?

PracHub provides an interactive SQL console where you can write and test queries against real database schemas. Get instant feedback and compare your solution with the expected output.

Q: What difficulty level is this coding question?

This is a medium difficulty Data Manipulation (SQL/Python) question, commonly asked during Technical Screen rounds at Pinterest.

Q: What role is this question designed for?

This question is commonly asked for Data Scientist candidates at Pinterest during technical interviews.

Question

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

PracHub · Accepted Answer

This question evaluates a candidate's ability to perform data manipulation and aggregation in Python (pandas), covering skills such as filtering, handling nulls and nonpositive values, mapping categorical IDs to names, rounding numerical results, and applying tie-breaking rules.

Quick Overview

Find top video category by average time