SQL Window Functions: Practical Examples Using HR Data
Window functions are one of SQL's most powerful features, yet they often remain underutilized. I've found them particularly useful when analyzing HR data, where we often need to compare employees within departments or track changes over time. Let's explore how these functions can simplify complex HR data analysis.
Note: All examples use SQL Server (T-SQL) syntax, though the concepts apply to other SQL flavors with minor modifications.
Understanding Window Functions
Window functions perform calculations across a set of rows related to the current row. Unlike regular aggregate functions, they don't collapse the results into a single row. This makes them perfect for scenarios where you need both detail and summary data together.
Basic Window Functions
Let's start with the most commonly used window functions using HR data scenarios.
ROW_NUMBER()
Perfect for creating unique identifiers or analyzing employee hire order:
SELECT
department_name,
employee_name,
hire_date,
ROW_NUMBER() OVER(PARTITION BY department_name ORDER BY hire_date) as hire_order
FROM hr.employees e
JOIN hr.departments d ON e.department_id = d.department_id
ORDER BY department_name, hire_date;RANK() vs DENSE_RANK()
These functions are great for salary analysis, showing how they handle ties differently:
SELECT
employee_name,
salary,
RANK() OVER(ORDER BY salary DESC) as salary_rank,
DENSE_RANK() OVER(ORDER BY salary DESC) as salary_dense_rank
FROM hr.employees
WHERE department_id = 3 -- e.g., Sales Department
ORDER BY salary DESC;Aggregate Window Functions
These functions let us see both individual and group statistics together.
Department Salary Analysis
SELECT
e.employee_name,
d.department_name,
e.salary,
AVG(salary) OVER(PARTITION BY d.department_id) as dept_avg_salary,
MAX(salary) OVER(PARTITION BY d.department_id) as dept_max_salary,
MIN(salary) OVER(PARTITION BY d.department_id) as dept_min_salary,
FORMAT(e.salary / SUM(salary) OVER(PARTITION BY d.department_id), 'P2') as pct_of_dept_total
FROM hr.employees e
JOIN hr.departments d ON e.department_id = d.department_id
ORDER BY department_name, salary DESC;Running Totals for Budget Analysis
SELECT
department_name,
month,
monthly_cost,
SUM(monthly_cost) OVER(PARTITION BY department_name
ORDER BY month
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as running_total,
SUM(monthly_cost) OVER(PARTITION BY department_name) as annual_total,
FORMAT(monthly_cost / SUM(monthly_cost) OVER(PARTITION BY department_name), 'P2') as pct_of_annual
FROM hr.department_costs
WHERE year = 2024
ORDER BY department_name, month;Advanced Window Functions
Now let's look at more sophisticated analyses using LAG, LEAD, and other advanced functions.
Salary Change Analysis
SELECT
employee_name,
effective_date,
salary,
LAG(salary) OVER(PARTITION BY employee_id ORDER BY effective_date) as previous_salary,
salary - LAG(salary) OVER(PARTITION BY employee_id ORDER BY effective_date) as salary_change,
FORMAT((salary - LAG(salary) OVER(PARTITION BY employee_id ORDER BY effective_date)) /
LAG(salary) OVER(PARTITION BY employee_id ORDER BY effective_date), 'P2') as pct_change
FROM hr.salary_history
ORDER BY employee_id, effective_date;Performance Rating Trends
SELECT
e.employee_name,
pr.review_year,
pr.rating,
LAG(rating, 1) OVER(PARTITION BY e.employee_id ORDER BY review_year) as previous_year_rating,
LEAD(rating, 1) OVER(PARTITION BY e.employee_id ORDER BY review_year) as next_year_rating,
FIRST_VALUE(rating) OVER(PARTITION BY e.employee_id ORDER BY review_year) as first_rating,
AVG(rating) OVER(PARTITION BY e.employee_id) as avg_rating
FROM hr.performance_reviews pr
JOIN hr.employees e ON pr.employee_id = e.employee_id
ORDER BY e.employee_id, pr.review_year;Employee Quartile Analysis
NTILE is excellent for creating salary bands or performance groups:
SELECT
employee_name,
department_name,
salary,
NTILE(4) OVER(PARTITION BY department_name ORDER BY salary) as salary_quartile,
CASE
WHEN NTILE(4) OVER(PARTITION BY department_name ORDER BY salary) = 1 THEN 'Bottom 25%'
WHEN NTILE(4) OVER(PARTITION BY department_name ORDER BY salary) = 2 THEN 'Lower Middle'
WHEN NTILE(4) OVER(PARTITION BY department_name ORDER BY salary) = 3 THEN 'Upper Middle'
ELSE 'Top 25%'
END as salary_bracket
FROM hr.employees e
JOIN hr.departments d ON e.department_id = d.department_id
ORDER BY department_name, salary DESC;Practical Tips
From my experience working with window functions:
- Always test window functions with small data sets first to understand their behavior
- Pay attention to your PARTITION BY and ORDER BY clauses - they significantly affect results
- Remember that window functions execute after WHERE but before ORDER BY
- Use meaningful aliases for complex window functions to improve readability
- Consider breaking down complex queries with multiple window functions into CTEs for better maintainability
Conclusion
Window functions are invaluable for HR data analysis, allowing us to combine detailed employee data with aggregated metrics in a single query. While they might seem complex at first, they often simplify what would otherwise require multiple subqueries or self-joins.