Top N Per Group Interview Questions

I would use a window function. SELECT * FROM (SELECT e.*, DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS r FROM employees e) t WHERE r <= 3. If they want exactly three rows even when salaries tie, I use ROW_NUMBER instead of DENSE_RANK. DENSE_RANK can return more than three people if several share a salary. That is the same pattern as second-highest salary, just partitioned by department. On old databases without windows I would use a correlated subquery that counts how many people in the department earn more.

DENSE_RANK or ROW_NUMBER PARTITION BY department ORDER BY salary DESC, filter rank <= 3 in a CTE. I ask ties: three people with the same salary in fourth place — RANK would include them, ROW_NUMBER would cut. I would not correlated-subquery LIMIT 3 per department on a large table without checking the plan. This is the poster-child window query.

In Postgres I can LATERAL join each department to a LIMIT N subquery. It can be very fast with an index on (department_id, salary DESC). ROW_NUMBER scans and sorts partitions. I would mention both. On MySQL I might use ROW_NUMBER in 8+ or a trick in older versions. I pick based on dialect. Interviewers like that I did not only memorize one blog snippet.

ROW_NUMBER PARTITION BY customer_id ORDER BY order_date DESC, filter rn = 1. Or DISTINCT ON (customer_id) in Postgres. Or a join to MAX(order_date) grouped, which fails if two orders share the timestamp — I would break ties with id. Latest-row-per-entity is the same pattern as top N with N = 1. I use this weekly in real work.

A composite index that matches PARTITION and ORDER, like (department_id, salary DESC). Then the engine can avoid a full sort per group in some plans. I would not index salary alone and hope. I would EXPLAIN. If the table is small I would not over-index. This is how I connect windows back to performance.