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…