Description:
In this video, we break down an advanced SQL query step by step! Learn how to use Common Table Expressions (CTEs) to fetch the latest salary and department of employees, filter out those earning above the department’s average salary, and optimize queries using ROW_NUMBER() and JOINs. Perfect for SQL learners and database professionals! 🚀
🔹 Topics Covered:
✅ Understanding CTEs (Common Table Expressions)
✅ Using ROW_NUMBER() for ranking data
✅ Filtering employees with higher-than-average salary
✅ Writing optimized JOINs and subqueries
📌 Subscribe for more SQL & database tutorials! 👍
🔔 Turn on notifications so you never miss an update!
WITH LatestPay AS (
SELECT
eph.BusinessEntityID,
eph.Rate,
ROW_NUMBER() OVER (PARTITION BY eph.BusinessEntityID ORDER BY eph.[RateChangeDate] DESC) AS rn
FROM HumanResources.EmployeePayHistory eph
),
LatestDepartment AS (
SELECT
edh.BusinessEntityID,
edh.DepartmentID,
ROW_NUMBER() OVER (PARTITION BY edh.BusinessEntityID ORDER BY edh.StartDate DESC) AS rn
FROM HumanResources.EmployeeDepartmentHistory edh
)
SELECT
p.BusinessEntityID,
p.FirstName + ',' + p.LastName AS FullName,
lp.Rate AS [Emp Latest Sal],
e.JobTitle,
d.Name AS DepartmentName
FROM Person.Person p
JOIN HumanResources.Employee e ON p.BusinessEntityID = e.BusinessEntityID
JOIN LatestPay LP ON p.BusinessEntityID = lp.BusinessEntityID AND lp.rn = 1
JOIN LatestDepartment ld ON p.BusinessEntityID = ld.BusinessEntityID AND ld.rn = 1
JOIN HumanResources.Department d ON ld.DepartmentID = d.DepartmentID
WHERE lp.Rate > (
-- Correlated subquery to get the department’s average salary
SELECT AVG(lp2.Rate)
FROM LatestPay lp2
JOIN LatestDepartment ld2 ON lp2.BusinessEntityID = ld2.BusinessEntityID
WHERE ld2.DepartmentID = ld.DepartmentID
)
ORDER BY DepartmentName ASC;
Understanding the Query
In this example, a Common Table Expression (CTE) is used to retrieve the latest pay rate. The subquery calculates the average pay rate for each department, and the outer query determines whether each employee’s salary exceeds the average pay rate of their respective department.
This query retrieves details of employees whose latest salary (Rate) is higher than the average salary of their respective departments. It uses Common Table Expressions (CTEs) to fetch:
- Latest salary of each employee from the EmployeePayHistory table.
- Latest department of each employee from the EmployeeDepartmentHistory table.
After fetching these details, the query filters employees whose salary is above their department’s average.
Step 1: Creating the LatestPay CTE
WITH LatestPay AS (
SELECT
eph.BusinessEntityID,
eph.Rate,
ROW_NUMBER() OVER (PARTITION BY eph.BusinessEntityID ORDER BY eph.[RateChangeDate] DESC) AS rn
FROM HumanResources.EmployeePayHistory eph
)
Explanation:
- This CTE retrieves the latest pay rate (Rate) of each employee.
- It uses the ROW_NUMBER() function to rank salary records for each employee (PARTITION BY eph.BusinessEntityID).
- The ranking is done in descending order of RateChangeDate, so the latest salary is ranked rn = 1.
Step 2: Creating the LatestDepartment CTE
LatestDepartment AS (
SELECT
edh.BusinessEntityID,
edh.DepartmentID,
ROW_NUMBER() OVER (PARTITION BY edh.BusinessEntityID ORDER BY edh.StartDate DESC) AS rn
FROM HumanResources.EmployeeDepartmentHistory edh
)
Explanation:
- This CTE fetches the latest department (DepartmentID) of each employee.
- Similar to the first CTE, it uses ROW_NUMBER() to rank department records for each employee.
- The ranking is done in descending order of StartDate, so the most recent department is ranked rn = 1.
Step 3: Fetching Employee Details
SELECT
p.BusinessEntityID,
p.FirstName + ‘,’ + p.LastName AS FullName,
lp.Rate AS [Emp Latest Sal],
e.JobTitle,
d.Name AS DepartmentName
Explanation:
- Retrieves the BusinessEntityID of employees.
- Combines FirstName and LastName into a single field FullName (separated by a comma).
- Fetches the latest salary (Rate) from LatestPay.
- Retrieves the JobTitle from the Employee table.
- Fetches the Department Name from the Department table.
Step 4: Joining the Tables
FROM Person.Person p
JOIN HumanResources.Employee e ON p.BusinessEntityID = e.BusinessEntityID
JOIN LatestPay LP ON p.BusinessEntityID = lp.BusinessEntityID AND lp.rn = 1
JOIN LatestDepartment ld ON p.BusinessEntityID = ld.BusinessEntityID AND ld.rn = 1
JOIN HumanResources.Department d ON ld.DepartmentID = d.DepartmentID
Explanation:
- Person.Person is joined with Employee to get additional employee details.
- LatestPay (LP) is joined to get the latest salary, filtering only the row where rn = 1.
- LatestDepartment (LD) is joined to get the latest department, filtering only the row where rn = 1.
- Department is joined to get the department name.
Step 5: Filtering Employees with Above-Average Salary
WHERE lp.Rate > (
— Correlated subquery to get the department’s average salary
SELECT AVG(lp2.Rate)
FROM LatestPay lp2
JOIN LatestDepartment ld2 ON lp2.BusinessEntityID = ld2.BusinessEntityID
WHERE ld2.DepartmentID = ld.DepartmentID
)
Explanation:
- The WHERE clause filters out employees whose latest salary (lp.Rate) is higher than the department’s average salary.
- The subquery calculates the average salary for each department:
- It joins LatestPay (lp2) and LatestDepartment (ld2).
- It filters employees who belong to the same department as the current row (ld2.DepartmentID = ld.DepartmentID).
- It computes the average salary of that department using AVG(lp2.Rate).
Step 6: Sorting the Output
ORDER BY p.BusinessEntityID ASC;
Explanation:
- The result is ordered by BusinessEntityID in ascending order.
Final Output
The final query provides:
- Employee ID (BusinessEntityID).
- Full Name (FirstName, LastName).
- Latest Salary (Rate).
- Job Title.
- Department Name.
Only employees whose salary is higher than their department’s average salary are included.
Summary
- LatestPay CTE: Retrieves the latest salary for each employee.
- LatestDepartment CTE: Retrieves the latest department for each employee.
- Main query:
- Joins employee details.
- Filters employees whose salary is higher than the department’s average.
- Orders results by BusinessEntityID.
Let me know if you need further clarification! 😊