Interview Question Bank · DENSE_RANK vs RANK vs ROW_NUMBER
3. Top searches per country, ties included
MediumSearch & adsQuestion #258
Concept: DENSE_RANK vs RANK vs ROW_NUMBERshow
The three ranking functions differ only on ties. ROW_NUMBER gives every row a different number, so tied rows are split arbitrarily. RANK gives ties the same number and then skips (1, 1, 3), while DENSE_RANK gives ties the same number without gaps (1, 1, 2). 'Top N values, keep ties' is DENSE_RANK inside PARTITION BY the group, filtered in an outer query.
Example (different data):
WITH r AS (
SELECT league, team, points,
DENSE_RANK() OVER (PARTITION BY league ORDER BY points DESC) AS place
FROM standings
)
SELECT * FROM r WHERE place <= 3;Your task
Findwell's trends page shows the top searches in each country for 1 and 2 June 2025. When queries tie on volume, the page shows all of them.
Total each query's searches over both days. Then, per country, return the queries whose total is one of the two highest totals: tied queries share a place and the next total takes the next place, so a country can return more than 2 rows. Return country, query, total_searches and place. Sort by country, place, then query.
Starting Postgres in your browser…
Checking your access…