cswork / libraryOpen practice workspace

Coding questions · Meta

Return top-3 content per category

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.

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.

Return Top-3 Content per Category by Rating with Tiebreakers

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.

Table items

columntypeconstraints
content_idVARCHAR(20)PRIMARY KEY, NOT NULL
categoryVARCHAR(50)NOT NULL
ratingINTNOT NULL

Sample data:

categorycontent_idrating
sportsa15
sportsa24
sportsa35
sportsb13
newsc14
newsc24
newsc32
techd15
techd25
techd35
techd44

Expected output

For each category, the top 3 items by rating, with ties broken by lexicographically smaller content_id. The result is ordered by category ASC, then rating DESC, then content_id ASC.

categorycontent_idrating
newsc14
newsc24
newsc32
sportsa15
sportsa35
sportsa24
techd15
techd25
techd35

Hints

  1. Use a window function like ROW_NUMBER() partitioned by category.
  2. Order the window by rating DESC and content_id ASC to implement the tie-breaking rules.