10 SQL Query to Learn Window functions like Rank, Row_Number
Window functions are tough but very important tool and then can be used to solve a range of problems like top N results, de-duplication etc. Let's see popular SQL queries from Interviews to learn and Master window function.
1. Write an SQL Query to Find the Row Number for Each Employee in a Department?
This is the simplest example of window function, ROW_NUMBER which can be used to assign each row a row_number. This is the basic technique we are going to solve many complex problem with ranking
SELECT EmpID, EmpName, DeptID,
ROW_NUMBER() OVER (PARTITION BY DeptID ORDER BY Salary DESC) AS RowNum
FROM Employees;
What it teaches:
ROW_NUMBER() assigns a unique number to rows within a partition. Helpful for pagination or top-N per group.
2. Write SQL Query to Rank Employees Based on Salary Within Department
If you have been giving interviews then you may have seen this problem below, we can use rank() to solve this problem. Rank handles ties by giving same rank and there will be break between ranks.
SELECT EmpID, EmpName, DeptID,
RANK() OVER (PARTITION BY DeptID ORDER BY Salary DESC) AS SalaryRank
FROM Employees;You can see that RANK() gives equal ranks for ties but skips subsequent numbers.
3. Write SQL query to Find the Running Total of Salaries in Each Department
This is another popular SQL interview question which can be solved using window functions as shown below:
SELECT EmpName, DeptID, Salary,
SUM(Salary) OVER (PARTITION BY DeptID ORDER BY EmpID) AS RunningTotal
FROM Employees;If you don't know you can also calculate running totals without subqueries using SUM() OVER.
4. Compare Salary with Previous Employee in Same Department
You can solve this problem by using lag() window function as shown below:
SELECT EmpID, EmpName, Salary,
LAG(Salary) OVER (PARTITION BY DeptID ORDER BY Salary) AS PrevSalary
FROM Employees;The key thing you can learn from this example is that LAG() looks at the previous row’s value—used for comparisons or deltas.
5. Compare Salary with Next Employee in Same Department
This is another classic problem from interview, you can use lead() window function to solve it:
SELECT EmpID, EmpName, Salary,
LEAD(Salary) OVER (PARTITION BY DeptID ORDER BY Salary) AS NextSalary
FROM Employees;Another lesson here is that LEAD() window function fetches value from the next row—handy in time series analysis.
6. Write a Query to get Top 2 Paid Employees Per Department
This is a classic top N problems you can solve using Window functions.
SELECT * FROM (
SELECT EmpID, EmpName, DeptID, Salary,
ROW_NUMBER() OVER (PARTITION BY DeptID ORDER BY Salary DESC) AS rn
FROM Employees
) AS ranked
WHERE rn <= 2;In short, you can use Subquery + ROW_NUMBER() to fetch N records per group.
7. Write SQL query to Calculate Average Salary Over Department
You can use AVG() function over a window, which is a department in our case to calculate the average salary for each department
SELECT EmpID, EmpName, DeptID, Salary,
AVG(Salary) OVER (PARTITION BY DeptID) AS AvgDeptSalary
FROM Employees;This example also teaches you one very important lesson that you can aggregate stats without grouping data. You can see that we have not used group by clause, we have only partition data on windows.
8. Count Employees with Salary More Than Department Average
This is another classic interview problem you will frequently see on interviews. You can solve this easily using window function by partitioning data by department.
SELECT * FROM (
SELECT EmpID, EmpName, DeptID, Salary,
AVG(Salary) OVER (PARTITION BY DeptID) AS AvgDeptSalary
FROM Employees
) AS sub
WHERE Salary > AvgDeptSalary;The lesson here is that you can use filter using window aggregate comparison.
9. Write SQL query to find Time Difference Between Logins of Same User
Here is how you can solve this problem by using lag() and datediff() function
SELECT UserID, LoginTime,
LAG(LoginTime) OVER (PARTITION BY UserID ORDER BY LoginTime) AS PrevLogin,
DATEDIFF(MINUTE, LAG(LoginTime) OVER (PARTITION BY UserID ORDER BY LoginTime), LoginTime) AS TimeGap
FROM Logins;In short you can use LAG() + DATEDIFF() for session time analysis.
10. Calculate Percentage Contribution of Employee Salary in Department
This the last query in this article, try to solve yourself before looking at the solution
SELECT EmpID, EmpName, DeptID, Salary,
ROUND(100.0 * Salary / SUM(Salary) OVER (PARTITION BY DeptID), 2) AS PercentShare
FROM Employees;
This query teaches us that window aggregates can be used for calculating ratios or percentages.
Here is also a nice infographic to revise and remember these queries and most importantly the lessons you learned
Conclusion
That's all about these top 10 SQL queries from Interviews to learn and master Window functions. These SQL queries are must-haves in your toolbelt—whether you're preparing for interviews at product companies or tackling real-world reporting dashboards.
Window functions are extremely common in advanced data analysis, and they replace messy subqueries with elegant, readable code.
If you're serious about becoming a data-savvy software engineer, go beyond basic SELECT queries. Practice, play, and explore window functions!
- 10 Tricky Java Interview Questions
- How to find duplicate emails?
- How to find customers who have never ordered?
- Generate a report to display the Person and their Address
- How to find the Nth highest salary
- 10 Java Collection and Generics interview questions
- 10 JDBC Interview Questions in Java
- 20 Spring and REST Interview Questions
- 75 Coding Questions to Crack Any Programming Interview
- 100+ Data Structure and Algorithm Questions
- 10 Courses to learn SQL and Database for Beginners
- My Favorite books to learn SQL in depth
Have any more SQL query doubts or need practice sets? Drop a comment below or share this post with your fellow devs
Posts
No comments :
Post a Comment