3.Intermediate SQL Query Techniques
Window Functions – RANK, ROW_NUMBER, LEAD, LAG
Window functions perform calculations across a set of rows related to the current row. They are useful for ranking, comparing rows, and accessing data from other rows without using joins.
Sample Table: Sales
| Employee | Region | Sales | Month |
|---|---|---|---|
| Alice | North | 5000 | Jan |
| Bob | North | 7000 | Jan |
| Charlie | South | 6000 | Jan |
| Alice | North | 5500 | Feb |
| Bob | North | 7200 | Feb |
| Charlie | South | 5800 | Feb |
Example: RANK
SELECT Employee, Region, Sales,
RANK() OVER (PARTITION BY Region ORDER BY Sales DESC) AS SalesRank
FROM Sales;
Output: Ranks employees by sales within each region.
SELECT Employee, Sales,
LEAD(Sales) OVER (PARTITION BY Employee ORDER BY Month) AS NextMonthSales
FROM Sales;
Output: Shows the sales of the next month for each employee.
Common Table Expressions (CTEs)
CTEs are temporary result sets that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. They improve readability and maintainability of complex queries.
Example: CTE for Average Sales
WITH RegionalSales AS (
SELECT Region, AVG(Sales) AS AvgSales
FROM Sales
GROUP BY Region
)
SELECT * FROM RegionalSales;
Output: Displays average sales per region.
Recursive Queries
Recursive queries are used to query hierarchical data such as organizational charts or folder structures.
Sample Table: Employees
| EmployeeID | Name | ManagerID |
|---|---|---|
| 1 | Alice | NULL |
| 2 | Bob | 1 |
| 3 | Charlie | 2 |
| 4 | David | 2 |
Example: Recursive CTE
WITH EmployeeHierarchy AS (
SELECT EmployeeID, Name, ManagerID
FROM Employees
WHERE ManagerID IS NULL
UNION ALL
SELECT e.EmployeeID, e.Name, e.ManagerID
FROM Employees e
INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT * FROM EmployeeHierarchy;
Output: Builds a hierarchy of employees starting from the top-level manager.
Practice Exercises
- Use ROW_NUMBER() to assign a unique rank to each employee based on sales.
- Create a CTE that filters employees with sales above 6000.
- Write a recursive query to list all subordinates under a given manager.
Real-World Project: Employee Performance Tracker
Use window functions to rank employees, CTEs to calculate average performance, and recursive queries to build reporting hierarchies.
FAQs
What is the difference between RANK and ROW_NUMBER?
RANK assigns the same rank to ties, while ROW_NUMBER gives a unique number to each row.
When should I use a CTE?
Use CTEs for better readability and when you need to reference the same subquery multiple times.
What are recursive queries used for?
They are used to handle hierarchical or tree-structured data.
Visual Aid Placeholders
- ER Diagram for Sales and Employees
- Flowchart for Recursive Query Execution
- BI Dashboard Mockup for Employee Performance