Return top-3 content per category
Company: Meta
Role: Data Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
Given a collection of items with fields (content_id, category, rating), implement top_k_by_category(items, k=
3) that returns, for each category, the top 3 items by rating. Break ties by higher rating first, then by lexicographically smaller content_id, and keep results sorted by descending rating. Aim for O(n log k) per category using heaps or partial selection.
Overview: This question evaluates the ability to perform grouped top-k selection, deterministic tie-breaking and ordering, and to reason about algorithmic efficiency when manipulating structured datasets in the Data Manipulation (SQL/Python) domain for a Data Engineer role.
Read the full Meta Data Engineer interview experience this question came from
You are given a table items(content_id, category, rating) representing content items, their category, and an integer rating. Write a SQL query that returns, for each category, at most the top 3 items by rating. Break ties by:
1) Higher rating first.
2) If ratings are equal, lexicographically smaller content_id first.
Return one row per selected item with columns: category, content_id, rating. Order the final result by category ASC, then rating DESC, then content_id ASC.
Tables
items(content_id VARCHAR(20), category VARCHAR(50), rating INT)
Hints
- Use a window function like ROW_NUMBER() partitioned by category.
- Order the window by rating DESC and content_id ASC to implement the tie-breaking rules.