Subquery vs Window Function — Which Wins?

Key Takeaways

This query fetches the highest paid employee from each department. It works, but look at what's happening. SQL ends up evaluating that correlated subquery repeatedly. Once for every single row in the table. In a small data set, fine, it's perfectly fine. >> [music] >> But in production, this kills your performance. There's a better [music] way to write this, and it's what interviewers actually want to see. We write this using row number, a window function. Partition by department {underscore} ID. This tells SQL [music] to restart ranking for each department. Order by salary descending. Highest salary gets rank one. Now, [music] every employee has a rank within their department. We just filter where RN equals to one. But here is where it gets powerful. Your manager changes his mind, [music] and now asks for top three performers per department. With the old query, you were rewriting the logic. It gets messy fast, right? But with this new approach, just change it to where RN less than equal to three. That's the [music] only change, one line. Window functions don't just make your query shorter, they make it scalable, readable, and production ready. I have covered every major window functions with real-world use cases and interview patterns [music] in my ebook Mastering SQL Window Functions. If you are serious about leveling up at work, >> [music] >> just check the pinned comment for the link of this book.