Basic PARTITION BY - Department Analysis

basic

Compare each employee to their department average and rank within department

-- Employee analysis within departments

SELECT

employee_name,

department,

salary,

-- Department statistics

AVG(salary) OVER (PARTITION BY department) as dept_avg_salary,

COUNT(*) OVER (PARTITION BY department) as dept_size,

-- Individual comparisons

salary - AVG(salary) OVER (PARTITION BY department) as vs_dept_avg,

RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank,

-- Percentage calculations

ROUND(

100.0 * salary / SUM(salary) OVER (PARTITION BY department), 2

) as pct_of_dept_payroll

FROM employees

ORDER BY department, salary DESC;

employee_name | department | salary | dept_avg_salary | dept_size | vs_dept_avg | dept_rank | pct_of_dept_payroll

--------------|------------|--------|-----------------|-----------|-------------|-----------|--------------------

Bob Smith | Sales | 60000 | 55000.00 | 2 | 5000.00 | 1 | 54.55

Alice Johnson | Sales | 50000 | 55000.00 | 2 | -5000.00 | 2 | 45.45

Dave Wilson | IT | 80000 | 75000.00 | 2 | 5000.00 | 1 | 53.33

Carol Davis | IT | 70000 | 75000.00 | 2 | -5000.00 | 2 | 46.67

Eve Brown | HR | 45000 | 45000.00 | 1 | 0.00 | 1 | 100.00

This example shows the power of PARTITION BY - each employee gets department-level statistics while preserving individual row details. Notice how we can mix different window functions all partitioned by department.

All