Drill 2 of 3 in Streaming questions
0

Interview Question Bank · RANK() · top-1 per group with ties

2. Top artist per country, ties included

MediumStreamingQuestion #128

Concept: RANK() · top-1 per group with tiesshow

For 'the top item in each group', rank inside each group and keep rank 1. ROW_NUMBER gives every row a different number, so when two items tie it silently picks one; RANK and DENSE_RANK give tied rows the same number, so both survive. Aggregate first and rank the totals: window functions run after GROUP BY, so RANK() OVER (... ORDER BY SUM(x) DESC) works in the same query.

Example (different data):

SELECT region, rep, total_sales
FROM (
  SELECT region, rep, SUM(amount) AS total_sales,
    DENSE_RANK() OVER (PARTITION BY region ORDER BY SUM(amount) DESC) AS rnk
  FROM deals
  GROUP BY region, rep
) AS ranked
WHERE rnk <= 3;

Your task

The editors at Tunevale, a music streaming app, are building a 'Number one in your country' playlist for March 2025. When two artists tie, both get the spot.

Using March 2025 streams only, return country, artist and total_streams for the artist with the most total streams in each country. If artists tie for first, return all of them. Sort by country, artist.

Starting Postgres in your browser…

Checking your access…