Data Analysis Books | Python |… on Telegram: “✅ SQL Interview Challenge

✅ SQL Interview Challenge – Filter Top N Records per Group 🧠💾

🧑‍💼 Interviewer: How would you fetch the top 2 highest-paid employees per department?

👨‍💻 Me: Use ROW_NUMBER() with a PARTITION BY clause—it's a window function that numbers rows uniquely within groups, resetting per partition for precise top-N filtering.

🔹 SQL Query:

SELECT *
FROM (
  SELECT name, department, salary,
         ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
  FROM employees
) AS ranked
WHERE rn <= 2;

✔ Why it works:
– PARTITION BY department resets row numbers (starting at 1) for each dept group, treating them as mini-tables.
– ORDER BY salary DESC ranks highest first within each partition.
– WHERE rn <= 2 grabs the top 2 per group—subquery avoids duplicates in complex joins!

💡 Pro Tip: Swap to RANK() if ties get equal ranks (e.g., two at #1 means next is #3, but you might get 3 rows); DENSE_RANK() avoids gaps. For big datasets, this scales well in SQL Server or Postgres.

💬 Tap ❤️ for more!