Interview practice

SQL Ranking and Top-N Interview Questions

Practice top rows, tie handling, rank functions, and top-N per group reports.

Ranking questions test whether you can turn "best", "latest", "highest", or "top" into a deterministic sort. The simplest version uses ORDER BY and LIMIT. The interview version often asks for top N inside each group, which needs a window function.

Start by naming the metric, the grain, and the tie rule. A top-products query is not complete until it says what happens when two products have the same price, rating, or revenue.

Common ranking interview prompts

Answer each one yourself before you look: write your query or your reasoning in the box, or open the playground and run it. Then reveal the model answer and compare. Nothing you type is saved or sent anywhere.

Return the five most expensive products.
Your answer Try it in the playground →
Reveal a model answer
```
select product_name, price
from products
order by price desc, product_name
limit 5
```
ORDER BY, then LIMIT, with a tie-breaker so the fifth place is the same on every run. If a sixth product tied on price, ask whether it should be included.
Find the top three movies by rental revenue.
Your answer Try it in the playground →
Reveal a model answer
```
select m.title,
       sum(r.amount) as revenue
from rentals as r
join movies as m on m.movie_id = r.movie_id
group by m.title
order by revenue desc, m.title
limit 3
```
Aggregate first, then rank the totals. Ranking individual rentals and then summing answers a different question.
Return the highest-rated movie in each genre.
Your answer Try it in the playground →
Reveal a model answer
```
with ranked as (
  select genre,
         title,
         rating,
         rank() over (partition by genre order by rating desc) as rnk
  from movies
)
select genre, title, rating
from ranked
where rnk = 1
order by genre, title
```
Top-N per group needs a window, not LIMIT. rank() returns every movie tied for first; row_number() would silently pick one. Say which the business wants.
Find the latest order for each customer.
Your answer Try it in the playground →
Reveal a model answer
```
with ranked as (
  select customer_id,
         order_id,
         order_date,
         row_number() over (
           partition by customer_id
           order by order_date desc, order_id desc
         ) as rn
  from orders
)
select customer_id, order_id, order_date
from ranked
where rn = 1
order by customer_id
```
Partition by customer, newest first, keep row 1. max(order_date) alone gives the date but not the order; joining back on the date breaks when a customer ordered twice that day, which is why row_number() with a tie-breaker is the safer answer.
Explain when to use ROW_NUMBER versus RANK.
Your answer

Reveal a model answer

row_number() when you need exactly N rows per group, with a tie-breaker that makes the choice deterministic. rank() when tied rows deserve the same place, accepting that a filter on rank <= 3 can then return more than three rows.

Simple Top-N

with products_with_categories as (
  select p.product_name,
         c.category_name,
         p.price
  from products as p
  join categories as c on c.category_id = p.category_id
)
select product_name,
       category_name,
       price
from products_with_categories
order by price desc, product_name
limit 5

What matters

A global Top-N report sorts the whole result and keeps the first few rows. A top-N-per-group report sorts inside each group, so it needs row_number(), rank(), or dense_rank() with partition by.

ROW_NUMBER can pick a fixed number of rows when its ordering fully breaks ties. RANK gives tied rows the same rank and can therefore return more than N rows when you filter to rank <= N. Ask which tie behavior the interviewer wants before choosing.

Top-N per group

with products_with_categories as (
  select p.product_name,
         c.category_name,
         p.price
  from products as p
  join categories as c on c.category_id = p.category_id
),
ranked as (
  select product_name,
         category_name,
         price,
         row_number() over (
           partition by category_name
           order by price desc, product_name
         ) as rn
  from products_with_categories
)
select product_name, category_name, price
from ranked
where rn <= 3
order by category_name, rn

How to talk it through

Say: "The result is one row per product candidate. I will sort by the business metric descending, add a deterministic tiebreaker, assign a row number within each category, then filter the numbered result in an outer query."

If the prompt says "top customer by revenue", compute revenue first, then rank the grouped result. Ranking raw rows before aggregation answers a different question.