SQL Ranking and Top-N Interview Questions

Ranking questions test whether you can turn "best", "latest", "highest", or "top" into a deterministic sort. The simplest version usesORDER 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

1. Return the five most expensive products.
2. Find the top three movies by rental revenue.
3. Return the highest-rated movie in each genre.
4. Find the latest order for each customer.
5. Explain when to use ROW_NUMBER versus RANK.

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 needsrow_number(), rank(), or dense_rank()with partition by.

ROW_NUMBER picks a fixed number of rows if your sort is fully deterministic.RANK keeps ties but can return more than N rows for a group. Ask which behavior the interviewer wants before choosing.

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.

Practice next

Practice ORDER BY and LIMIT,Top-N reports, andwindow functions. Then try themovie Top-5 report and reviewwhy LIMIT needs ORDER BY.