SQL Tutorial › SQL Projects › Project: HR & Payroll Analytics

SQL Project: HR and Payroll Analytics

Q: How do you get the latest record per employee in SQL?

Number each employee's rows with ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY effective_date DESC) and keep row number 1.

Q: How do you calculate a salary increase percentage in SQL?

Use LAG(salary) OVER (PARTITION BY emp_id ORDER BY effective_date) to get the previous salary, then 100 (salary - previous) / previous.

ProjectProject 3 of 530 min read13 queries run on MySQL 8.4

The People team of a software company wants data instead of spreadsheets. Salaries are stored as a history (one row per change), employees report to managers, and some people have left. In this project you answer real HR questions: headcount and attrition, current payroll, who got which raise, who is underpaid compared with their team, and what the organisation chart looks like. The reference date is 31 December 2025.

In this lesson

Get the current value from a history table with ROW_NUMBER
Measure headcount, attrition and tenure
Analyse raises with LAG and compare pay with window averages
Walk the management hierarchy with a recursive CTE

The database

Table	One row per	Notes
`departments`	department	5 departments
`employees`	person ever employed	`manager_id` points to another employee; `termination_date` is NULL for current staff
`salary_history`	salary change	`annual_salary` valid from `effective_date` until the next change

departments · 5 rows

dept_id	dept_name	location
10	Engineering	Bengaluru
20	QA	Pune
30	Sales	Mumbai
40	Support	Hyderabad
50	People	Delhi

employees · 26 rows

emp_id	first_name	last_name	dept_id	manager_id	job_title	hire_date	termination_date
1	Ananya	Rao	10	NULL	CTO	2016-03-01	NULL
2	Rohan	Kapoor	20	1	Head of QA	2017-02-02	NULL
3	Meera	Menon	30	1	Head of Sales	2017-09-03	NULL
4	Kabir	Qureshi	40	1	Head of Support	2019-01-29	NULL
5	Sara	Sinha	50	1	Head of People	2017-07-19	NULL
6	Vikas	Joshi	10	1	Senior Engineer	2020-06-11	NULL
7	Pooja	Mehta	10	6	Engineer	2022-04-25	NULL
8	Imran	Joshi	10	6	Engineer	2021-03-07	NULL
9	Leela	Nair	10	6	Senior Engineer	2020-05-27	NULL
10	Aditya	Iyer	10	6	Engineer	2019-10-10	NULL
11	Nisha	Sharma	10	6	Engineer	2023-10-20	2025-04-20
12	Farhan	Sharma	20	2	Senior QA Engineer	2024-01-16	NULL
13	Gauri	Khan	20	2	QA Engineer	2021-12-25	NULL
14	Harsh	Das	20	2	Automation Engineer	2021-03-04	NULL
15	Jaya	Nair	20	2	Senior QA Engineer	2020-08-15	NULL
16	Karthik	Patel	20	2	QA Engineer	2022-03-27	NULL
17	Lata	Das	30	3	Account Executive	2019-08-26	NULL
18	Manish	Reddy	30	3	Sales Associate	2020-11-04	NULL
19	Nidhi	Bose	30	3	Account Executive	2020-03-21	NULL
20	Pranav	Nair	30	3	Sales Associate	2021-04-21	NULL
21	Ritu	Khan	40	4	Support Lead	2020-12-27	NULL
22	Siddharth	Mehta	40	4	Support Agent	2024-11-06	NULL
23	Tanvi	Joshi	40	4	Support Agent	2019-11-05	NULL
24	Uday	Iyer	40	4	Support Lead	2022-01-22	NULL
25	Vani	Khan	50	5	HR Partner	2024-03-31	2025-10-17
26	Zubin	Sharma	50	5	Recruiter	2021-04-22	NULL

salary_history · first 10 of 123 rows

emp_id	effective_date	annual_salary
1	2016-03-01	428000
1	2017-04-01	462000
1	2018-04-01	480000
1	2019-04-01	518000
1	2020-04-01	549000
1	2021-04-01	570000
1	2022-04-01	638000
1	2023-04-01	676000
1	2024-04-01	703000
1	2025-04-01	773000

hr.sql

-- sqatools.in SQL project 3: HR and payroll analytics (generated by gen_projects.py)CREATE TABLE departments (  dept_id   INT PRIMARY KEY,  dept_name VARCHAR(30) NOT NULL,  location  VARCHAR(30) NOT NULL);CREATE TABLE employees (  emp_id        INT PRIMARY KEY,  first_name    VARCHAR(20) NOT NULL,  last_name     VARCHAR(20) NOT NULL,  dept_id       INT NOT NULL,  manager_id    INT,  job_title     VARCHAR(30) NOT NULL,  hire_date     DATE NOT NULL,  termination_date DATE,  FOREIGN KEY (dept_id) REFERENCES departments (dept_id),  FOREIGN KEY (manager_id) REFERENCES employees (emp_id));CREATE TABLE salary_history (  emp_id         INT,  effective_date DATE,  annual_salary  INT NOT NULL,  PRIMARY KEY (emp_id, effective_date),  FOREIGN KEY (emp_id) REFERENCES employees (emp_id));INSERT INTO departments VALUES(10, 'Engineering', 'Bengaluru'),(20, 'QA', 'Pune'),(30, 'Sales', 'Mumbai'),(40, 'Support', 'Hyderabad'),(50, 'People', 'Delhi');INSERT INTO employees VALUES(1, 'Ananya', 'Rao', 10, NULL, 'CTO', '2016-03-01', NULL),(2, 'Rohan', 'Kapoor', 20, 1, 'Head of QA', '2017-02-02', NULL),(3, 'Meera', 'Menon', 30, 1, 'Head of Sales', '2017-09-03', NULL),(4, 'Kabir', 'Qureshi', 40, 1, 'Head of Support', '2019-01-29', NULL),(5, 'Sara', 'Sinha', 50, 1, 'Head of People', '2017-07-19', NULL),(6, 'Vikas', 'Joshi', 10, 1, 'Senior Engineer', '2020-06-11', NULL),(7, 'Pooja', 'Mehta', 10, 6, 'Engineer', '2022-04-25', NULL),(8, 'Imran', 'Joshi', 10, 6, 'Engineer', '2021-03-07', NULL),(9, 'Leela', 'Nair', 10, 6, 'Senior Engineer', '2020-05-27', NULL),(10, 'Aditya', 'Iyer', 10, 6, 'Engineer', '2019-10-10', NULL),(11, 'Nisha', 'Sharma', 10, 6, 'Engineer', '2023-10-20', '2025-04-20'),(12, 'Farhan', 'Sharma', 20, 2, 'Senior QA Engineer', '2024-01-16', NULL),(13, 'Gauri', 'Khan', 20, 2, 'QA Engineer', '2021-12-25', NULL),(14, 'Harsh', 'Das', 20, 2, 'Automation Engineer', '2021-03-04', NULL),(15, 'Jaya', 'Nair', 20, 2, 'Senior QA Engineer', '2020-08-15', NULL),(16, 'Karthik', 'Patel', 20, 2, 'QA Engineer', '2022-03-27', NULL),(17, 'Lata', 'Das', 30, 3, 'Account Executive', '2019-08-26', NULL),(18, 'Manish', 'Reddy', 30, 3, 'Sales Associate', '2020-11-04', NULL),(19, 'Nidhi', 'Bose', 30, 3, 'Account Executive', '2020-03-21', NULL),(20, 'Pranav', 'Nair', 30, 3, 'Sales Associate', '2021-04-21', NULL),(21, 'Ritu', 'Khan', 40, 4, 'Support Lead', '2020-12-27', NULL),(22, 'Siddharth', 'Mehta', 40, 4, 'Support Agent', '2024-11-06', NULL),(23, 'Tanvi', 'Joshi', 40, 4, 'Support Agent', '2019-11-05', NULL),(24, 'Uday', 'Iyer', 40, 4, 'Support Lead', '2022-01-22', NULL),(25, 'Vani', 'Khan', 50, 5, 'HR Partner', '2024-03-31', '2025-10-17'),(26, 'Zubin', 'Sharma', 50, 5, 'Recruiter', '2021-04-22', NULL);INSERT INTO salary_history VALUES(1, '2016-03-01', 428000),(1, '2017-04-01', 462000),(1, '2018-04-01', 480000),(1, '2019-04-01', 518000),(1, '2020-04-01', 549000),(1, '2021-04-01', 570000),(1, '2022-04-01', 638000),(1, '2023-04-01', 676000),(1, '2024-04-01', 703000),(1, '2025-04-01', 773000),(2, '2017-02-02', 256000),(2, '2018-04-01', 281000),(2, '2019-04-01', 314000),(2, '2020-04-01', 339000),(2, '2021-04-01', 372000),(2, '2022-04-01', 416000),(2, '2023-04-01', 457000),(2, '2024-04-01', 484000),(2, '2025-04-01', 542000),(3, '2017-09-03', 257000),(3, '2019-04-01', 282000),(3, '2020-04-01', 293000),(3, '2021-04-01', 310000),(3, '2022-04-01', 334000),(3, '2024-04-01', 354000),(4, '2019-01-29', 266000),(4, '2020-04-01', 287000),(4, '2021-04-01', 309000),(4, '2022-04-01', 333000),(4, '2023-04-01', 372000),(4, '2024-04-01', 401000),(4, '2025-04-01', 417000),(5, '2017-07-19', 256000),(5, '2018-04-01', 266000),(5, '2020-04-01', 297000),(5, '2021-04-01', 326000),(5, '2022-04-01', 345000),(5, '2024-04-01', 365000),(6, '2020-06-11', 173000),(6, '2021-04-01', 186000),(6, '2022-04-01', 204000),(6, '2023-04-01', 220000),(6, '2024-04-01', 237000),(6, '2025-04-01', 260000),(7, '2022-04-25', 72000),(7, '2023-04-01', 80000),(7, '2025-04-01', 83000),(8, '2021-03-07', 69000),(8, '2022-04-01', 74000),(8, '2023-04-01', 82000),(8, '2024-04-01', 86000),(8, '2025-04-01', 89000),(9, '2020-05-27', 171000),(9, '2021-04-01', 184000),(9, '2022-04-01', 202000),(9, '2023-04-01', 214000),(9, '2025-04-01', 239000),(10, '2019-10-10', 69000),(10, '2020-04-01', 71000),(10, '2021-04-01', 73000),(10, '2023-04-01', 81000),(10, '2024-04-01', 87000),(10, '2025-04-01', 90000),(11, '2023-10-20', 65000),(11, '2024-04-01', 68000),(11, '2025-04-01', 74000),(12, '2024-01-16', 167000),(12, '2025-04-01', 180000),(13, '2021-12-25', 69000),(13, '2022-04-01', 75000),(13, '2023-04-01', 78000),(13, '2024-04-01', 81000),(13, '2025-04-01', 84000),(14, '2021-03-04', 72000),(14, '2022-04-01', 80000),(14, '2023-04-01', 89000),(14, '2025-04-01', 92000),(15, '2020-08-15', 159000),(15, '2022-04-01', 178000),(15, '2023-04-01', 192000),(15, '2025-04-01', 203000),(16, '2022-03-27', 68000),(16, '2023-04-01', 74000),(16, '2024-04-01', 78000),(16, '2025-04-01', 84000),(17, '2019-08-26', 98000),(17, '2020-04-01', 105000),(17, '2021-04-01', 113000),(17, '2022-04-01', 119000),(17, '2024-04-01', 128000),(18, '2020-11-04', 74000),(18, '2021-04-01', 76000),(18, '2025-04-01', 80000),(19, '2020-03-21', 100000),(19, '2021-04-01', 106000),(19, '2023-04-01', 112000),(19, '2024-04-01', 123000),(19, '2025-04-01', 135000),(20, '2021-04-21', 66000),(20, '2022-04-01', 68000),(20, '2023-04-01', 72000),(20, '2024-04-01', 80000),(21, '2020-12-27', 119000),(21, '2022-04-01', 128000),(21, '2023-04-01', 135000),(21, '2024-04-01', 151000),(21, '2025-04-01', 163000),(22, '2024-11-06', 67000),(22, '2025-04-01', 73000),(23, '2019-11-05', 70000),(23, '2020-04-01', 74000),(23, '2021-04-01', 79000),(23, '2023-04-01', 85000),(23, '2024-04-01', 90000),(23, '2025-04-01', 93000),(24, '2022-01-22', 112000),(24, '2024-04-01', 116000),(24, '2025-04-01', 127000),(25, '2024-03-31', 102000),(26, '2021-04-22', 64000),(26, '2023-04-01', 70000),(26, '2024-04-01', 72000),(26, '2025-04-01', 76000);

— sqatools.in SQL project 3: HR and payroll analytics (generated by gen_projects.py)
CREATE TABLE departments (
  dept_id   INT PRIMARY KEY,
  dept_name VARCHAR(30) NOT NULL,
  location  VARCHAR(30) NOT NULL
);
CREATE TABLE employees (
  emp_id        INT PRIMARY KEY,
  first_name    VARCHAR(20) NOT NULL,
  last_name     VARCHAR(20) NOT NULL,
  dept_id       INT NOT NULL,
  manager_id    INT,
  job_title     VARCHAR(30) NOT NULL,
  hire_date     DATE NOT NULL,
  termination_date DATE,
  FOREIGN KEY (dept_id) REFERENCES departments (dept_id),
  FOREIGN KEY (manager_id) REFERENCES employees (emp_id)
);
CREATE TABLE salary_history (
  emp_id         INT,
  effective_date DATE,
  annual_salary  INT NOT NULL,
  PRIMARY KEY (emp_id, effective_date),
  FOREIGN KEY (emp_id) REFERENCES employees (emp_id)
);
INSERT INTO departments VALUES
(10, 'Engineering', 'Bengaluru'),
(20, 'QA', 'Pune'),
(30, 'Sales', 'Mumbai'),
(40, 'Support', 'Hyderabad'),
(50, 'People', 'Delhi');
INSERT INTO employees VALUES
(1, 'Ananya', 'Rao', 10, NULL, 'CTO', '2016-03-01', NULL),
(2, 'Rohan', 'Kapoor', 20, 1, 'Head of QA', '2017-02-02', NULL),
(3, 'Meera', 'Menon', 30, 1, 'Head of Sales', '2017-09-03', NULL),
(4, 'Kabir', 'Qureshi', 40, 1, 'Head of Support', '2019-01-29', NULL),
(5, 'Sara', 'Sinha', 50, 1, 'Head of People', '2017-07-19', NULL),
(6, 'Vikas', 'Joshi', 10, 1, 'Senior Engineer', '2020-06-11', NULL),
(7, 'Pooja', 'Mehta', 10, 6, 'Engineer', '2022-04-25', NULL),
(8, 'Imran', 'Joshi', 10, 6, 'Engineer', '2021-03-07', NULL),
(9, 'Leela', 'Nair', 10, 6, 'Senior Engineer', '2020-05-27', NULL),
(10, 'Aditya', 'Iyer', 10, 6, 'Engineer', '2019-10-10', NULL),
(11, 'Nisha', 'Sharma', 10, 6, 'Engineer', '2023-10-20', '2025-04-20'),
(12, 'Farhan', 'Sharma', 20, 2, 'Senior QA Engineer', '2024-01-16', NULL),
(13, 'Gauri', 'Khan', 20, 2, 'QA Engineer', '2021-12-25', NULL),
(14, 'Harsh', 'Das', 20, 2, 'Automation Engineer', '2021-03-04', NULL),
(15, 'Jaya', 'Nair', 20, 2, 'Senior QA Engineer', '2020-08-15', NULL),
(16, 'Karthik', 'Patel', 20, 2, 'QA Engineer', '2022-03-27', NULL),
(17, 'Lata', 'Das', 30, 3, 'Account Executive', '2019-08-26', NULL),
(18, 'Manish', 'Reddy', 30, 3, 'Sales Associate', '2020-11-04', NULL),
(19, 'Nidhi', 'Bose', 30, 3, 'Account Executive', '2020-03-21', NULL),
(20, 'Pranav', 'Nair', 30, 3, 'Sales Associate', '2021-04-21', NULL),
(21, 'Ritu', 'Khan', 40, 4, 'Support Lead', '2020-12-27', NULL),
(22, 'Siddharth', 'Mehta', 40, 4, 'Support Agent', '2024-11-06', NULL),
(23, 'Tanvi', 'Joshi', 40, 4, 'Support Agent', '2019-11-05', NULL),
(24, 'Uday', 'Iyer', 40, 4, 'Support Lead', '2022-01-22', NULL),
(25, 'Vani', 'Khan', 50, 5, 'HR Partner', '2024-03-31', '2025-10-17'),
(26, 'Zubin', 'Sharma', 50, 5, 'Recruiter', '2021-04-22', NULL);
INSERT INTO salary_history VALUES
(1, '2016-03-01', 428000),
(1, '2017-04-01', 462000),
(1, '2018-04-01', 480000),
(1, '2019-04-01', 518000),
(1, '2020-04-01', 549000),
(1, '2021-04-01', 570000),
(1, '2022-04-01', 638000),
(1, '2023-04-01', 676000),
(1, '2024-04-01', 703000),
(1, '2025-04-01', 773000),
(2, '2017-02-02', 256000),
(2, '2018-04-01', 281000),
(2, '2019-04-01', 314000),
(2, '2020-04-01', 339000),
(2, '2021-04-01', 372000),
(2, '2022-04-01', 416000),
(2, '2023-04-01', 457000),
(2, '2024-04-01', 484000),
(2, '2025-04-01', 542000),
(3, '2017-09-03', 257000),
(3, '2019-04-01', 282000),
(3, '2020-04-01', 293000),
(3, '2021-04-01', 310000),
(3, '2022-04-01', 334000),
(3, '2024-04-01', 354000),
(4, '2019-01-29', 266000),
(4, '2020-04-01', 287000),
(4, '2021-04-01', 309000),
(4, '2022-04-01', 333000),
(4, '2023-04-01', 372000),
(4, '2024-04-01', 401000),
(4, '2025-04-01', 417000),
(5, '2017-07-19', 256000),
(5, '2018-04-01', 266000),
(5, '2020-04-01', 297000),
(5, '2021-04-01', 326000),
(5, '2022-04-01', 345000),
(5, '2024-04-01', 365000),
(6, '2020-06-11', 173000),
(6, '2021-04-01', 186000),
(6, '2022-04-01', 204000),
(6, '2023-04-01', 220000),
(6, '2024-04-01', 237000),
(6, '2025-04-01', 260000),
(7, '2022-04-25', 72000),
(7, '2023-04-01', 80000),
(7, '2025-04-01', 83000),
(8, '2021-03-07', 69000),
(8, '2022-04-01', 74000),
(8, '2023-04-01', 82000),
(8, '2024-04-01', 86000),
(8, '2025-04-01', 89000),
(9, '2020-05-27', 171000),
(9, '2021-04-01', 184000),
(9, '2022-04-01', 202000),
(9, '2023-04-01', 214000),
(9, '2025-04-01', 239000),
(10, '2019-10-10', 69000),
(10, '2020-04-01', 71000),
(10, '2021-04-01', 73000),
(10, '2023-04-01', 81000),
(10, '2024-04-01', 87000),
(10, '2025-04-01', 90000),
(11, '2023-10-20', 65000),
(11, '2024-04-01', 68000),
(11, '2025-04-01', 74000),
(12, '2024-01-16', 167000),
(12, '2025-04-01', 180000),
(13, '2021-12-25', 69000),
(13, '2022-04-01', 75000),
(13, '2023-04-01', 78000),
(13, '2024-04-01', 81000),
(13, '2025-04-01', 84000),
(14, '2021-03-04', 72000),
(14, '2022-04-01', 80000),
(14, '2023-04-01', 89000),
(14, '2025-04-01', 92000),
(15, '2020-08-15', 159000),
(15, '2022-04-01', 178000),
(15, '2023-04-01', 192000),
(15, '2025-04-01', 203000),
(16, '2022-03-27', 68000),
(16, '2023-04-01', 74000),
(16, '2024-04-01', 78000),
(16, '2025-04-01', 84000),
(17, '2019-08-26', 98000),
(17, '2020-04-01', 105000),
(17, '2021-04-01', 113000),
(17, '2022-04-01', 119000),
(17, '2024-04-01', 128000),
(18, '2020-11-04', 74000),
(18, '2021-04-01', 76000),
(18, '2025-04-01', 80000),
(19, '2020-03-21', 100000),
(19, '2021-04-01', 106000),
(19, '2023-04-01', 112000),
(19, '2024-04-01', 123000),
(19, '2025-04-01', 135000),
(20, '2021-04-21', 66000),
(20, '2022-04-01', 68000),
(20, '2023-04-01', 72000),
(20, '2024-04-01', 80000),
(21, '2020-12-27', 119000),
(21, '2022-04-01', 128000),
(21, '2023-04-01', 135000),
(21, '2024-04-01', 151000),
(21, '2025-04-01', 163000),
(22, '2024-11-06', 67000),
(22, '2025-04-01', 73000),
(23, '2019-11-05', 70000),
(23, '2020-04-01', 74000),
(23, '2021-04-01', 79000),
(23, '2023-04-01', 85000),
(23, '2024-04-01', 90000),
(23, '2025-04-01', 93000),
(24, '2022-01-22', 112000),
(24, '2024-04-01', 116000),
(24, '2025-04-01', 127000),
(25, '2024-03-31', 102000),
(26, '2021-04-22', 64000),
(26, '2023-04-01', 70000),
(26, '2024-04-01', 72000),
(26, '2025-04-01', 76000);

Task 1: Headcount and attrition

MySQL

SELECT d.dept_name,       COUNT(*)                          AS ever_employed,       SUM(e.termination_date IS NULL)   AS current_staff,       SUM(e.termination_date IS NOT NULL) AS leavers,       ROUND(100 * SUM(e.termination_date IS NOT NULL) / COUNT(*), 1) AS attrition_pctFROM employees eJOIN departments d ON d.dept_id = e.dept_idGROUP BY d.dept_nameORDER BY attrition_pct DESC, d.dept_name;

ResultMySQL 8.4

dept_name	ever_employed	current_staff	leavers	attrition_pct
People	3	2	1	33.3
Engineering	7	6	1	14.3
QA	6	6	0	0.0
Sales	5	5	0	0.0
Support	5	5	0	0.0

5 rows in set

How this query works, step by step

Every employee row is counted once per department.
termination_date IS NULL is 1 for current staff and 0 for leavers, so SUM counts them.
Attrition % = leavers ÷ everyone ever employed in the department.

Task 2: Current salary from the history

The salary in force today is the row with the latest effective_date for each employee: a top-1-per-group problem (Top N per Group):

SQL

WITH latest AS (  SELECT emp_id, annual_salary, effective_date,         ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY effective_date DESC) AS rn  FROM salary_history)SELECT e.emp_id, e.first_name, e.last_name, e.job_title, l.annual_salary, l.effective_date AS sinceFROM employees eJOIN latest l ON l.emp_id = e.emp_id AND l.rn = 1WHERE e.termination_date IS NULLORDER BY l.annual_salary DESCLIMIT 8;

ResultMySQL 8.4

emp_id	first_name	last_name	job_title	annual_salary	since
1	Ananya	Rao	CTO	773000	2025-04-01
2	Rohan	Kapoor	Head of QA	542000	2025-04-01
4	Kabir	Qureshi	Head of Support	417000	2025-04-01
5	Sara	Sinha	Head of People	365000	2024-04-01
3	Meera	Menon	Head of Sales	354000	2024-04-01
6	Vikas	Joshi	Senior Engineer	260000	2025-04-01
9	Leela	Nair	Senior Engineer	239000	2025-04-01
15	Jaya	Nair	Senior QA Engineer	203000	2025-04-01

8 rows in set

How this query works, step by step

ROW_NUMBER() ... ORDER BY effective_date DESC numbers each employee’s salary rows from newest to oldest.
Joining on rn = 1 keeps only the current salary.
WHERE termination_date IS NULL limits the list to current staff.

Task 3: Payroll by department

SQL

WITH latest AS (  SELECT emp_id, annual_salary,         ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY effective_date DESC) AS rn  FROM salary_history)SELECT d.dept_name,       COUNT(*)                    AS staff,       SUM(l.annual_salary)        AS annual_payroll,       ROUND(AVG(l.annual_salary)) AS avg_salary,       MIN(l.annual_salary)        AS min_salary,       MAX(l.annual_salary)        AS max_salaryFROM employees eJOIN latest l      ON l.emp_id = e.emp_id AND l.rn = 1JOIN departments d ON d.dept_id = e.dept_idWHERE e.termination_date IS NULLGROUP BY d.dept_nameORDER BY annual_payroll DESC;

ResultMySQL 8.4

dept_name	staff	annual_payroll	avg_salary	min_salary	max_salary
Engineering	6	1534000	255667	83000	773000
QA	6	1185000	197500	84000	542000
Support	5	873000	174600	73000	417000
Sales	5	777000	155400	80000	354000
People	2	441000	220500	76000	365000

5 rows in set

Task 4: Pay compared with the team

Who earns below their department’s average, and by how much?

SQL

WITH latest AS (  SELECT emp_id, annual_salary,         ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY effective_date DESC) AS rn  FROM salary_history),current_pay AS (  SELECT e.emp_id, e.first_name, e.dept_id, e.job_title, l.annual_salary,         ROUND(AVG(l.annual_salary) OVER (PARTITION BY e.dept_id, e.job_title)) AS role_avg  FROM employees e  JOIN latest l ON l.emp_id = e.emp_id AND l.rn = 1  WHERE e.termination_date IS NULL)SELECT emp_id, first_name, job_title, annual_salary, role_avg,       annual_salary - role_avg AS gapFROM current_payWHERE annual_salary < role_avgORDER BY gap;

ResultMySQL 8.4

emp_id	first_name	job_title	annual_salary	role_avg	gap
24	Uday	Support Lead	127000	145000	-18000
12	Farhan	Senior QA Engineer	180000	191500	-11500
9	Leela	Senior Engineer	239000	249500	-10500
22	Siddharth	Support Agent	73000	83000	-10000
7	Pooja	Engineer	83000	87333	-4333
17	Lata	Account Executive	128000	131500	-3500

6 rows in set

How this query works, step by step

AVG(...) OVER (PARTITION BY dept_id, job_title) is the average pay of people with the same role in the same department, shown on every row.
The outer query keeps the people paid below that average, biggest gap first: a list for the next salary review.

Task 5: Top 2 earners per department

SQL

WITH latest AS (  SELECT emp_id, annual_salary,         ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY effective_date DESC) AS rn  FROM salary_history),ranked AS (  SELECT e.dept_id, e.first_name, e.job_title, l.annual_salary,         DENSE_RANK() OVER (PARTITION BY e.dept_id ORDER BY l.annual_salary DESC) AS rnk  FROM employees e  JOIN latest l ON l.emp_id = e.emp_id AND l.rn = 1  WHERE e.termination_date IS NULL)SELECT d.dept_name, r.first_name, r.job_title, r.annual_salary, r.rnkFROM ranked rJOIN departments d ON d.dept_id = r.dept_idWHERE r.rnk <= 2ORDER BY d.dept_name, r.rnk;

ResultMySQL 8.4

dept_name	first_name	job_title	annual_salary	rnk
Engineering	Ananya	CTO	773000	1
Engineering	Vikas	Senior Engineer	260000	2
People	Sara	Head of People	365000	1
People	Zubin	Recruiter	76000	2
QA	Rohan	Head of QA	542000	1
QA	Jaya	Senior QA Engineer	203000	2
Sales	Meera	Head of Sales	354000	1
Sales	Nidhi	Account Executive	135000	2
Support	Kabir	Head of Support	417000	1
Support	Ritu	Support Lead	163000	2

10 rows in set

Task 6: Every raise and its percentage

MySQL

SELECT emp_id, effective_date, annual_salary,       LAG(annual_salary) OVER (PARTITION BY emp_id ORDER BY effective_date) AS previous_salary,       ROUND(100 * (annual_salary - LAG(annual_salary) OVER (PARTITION BY emp_id ORDER BY effective_date))             / LAG(annual_salary) OVER (PARTITION BY emp_id ORDER BY effective_date), 1) AS raise_pctFROM salary_historyWHERE emp_id IN (6, 12)ORDER BY emp_id, effective_date;

ResultMySQL 8.4

emp_id	effective_date	annual_salary	previous_salary	raise_pct
6	2020-06-11	173000	NULL	NULL
6	2021-04-01	186000	173000	7.5
6	2022-04-01	204000	186000	9.7
6	2023-04-01	220000	204000	7.8
6	2024-04-01	237000	220000	7.7
6	2025-04-01	260000	237000	9.7
12	2024-01-16	167000	NULL	NULL
12	2025-04-01	180000	167000	7.8

8 rows in set

How this query works, step by step

LAG(annual_salary) inside each employee’s history (PARTITION BY emp_id) gives the salary before each change.
The first row of each employee is the hiring salary, so its previous salary and raise are NULL.

Average raise per department in the April 2025 appraisal:

MySQL

WITH changes AS (  SELECT emp_id, effective_date, annual_salary,         LAG(annual_salary) OVER (PARTITION BY emp_id ORDER BY effective_date) AS prev  FROM salary_history)SELECT d.dept_name,       COUNT(*) AS raised,       ROUND(AVG(100 * (c.annual_salary - c.prev) / c.prev), 1) AS avg_raise_pctFROM changes cJOIN employees e   ON e.emp_id = c.emp_idJOIN departments d ON d.dept_id = e.dept_idWHERE c.effective_date = '2025-04-01' AND c.prev IS NOT NULLGROUP BY d.dept_nameORDER BY avg_raise_pct DESC;

ResultMySQL 8.4

dept_name	raised	avg_raise_pct
Sales	2	7.5
Engineering	7	7.3
QA	6	6.7
Support	5	6.7
People	1	5.6

5 rows in set

Task 7: Who missed the last appraisal?

Current employees, hired before 2025, without a salary change on 1 April 2025:

SQL

SELECT e.emp_id, e.first_name, e.job_title, MAX(s.effective_date) AS last_changeFROM employees eJOIN salary_history s ON s.emp_id = e.emp_idWHERE e.termination_date IS NULL AND e.hire_date < '2025-01-01'GROUP BY e.emp_id, e.first_name, e.job_titleHAVING MAX(s.effective_date) < '2025-04-01'ORDER BY last_change;

ResultMySQL 8.4

emp_id	first_name	job_title	last_change
3	Meera	Head of Sales	2024-04-01
5	Sara	Head of People	2024-04-01
17	Lata	Account Executive	2024-04-01
20	Pranav	Sales Associate	2024-04-01

4 rows in set

Task 8: Tenure bands

MySQL

SELECT CASE         WHEN TIMESTAMPDIFF(YEAR, hire_date, '2025-12-31') < 2 THEN '0-1 years'         WHEN TIMESTAMPDIFF(YEAR, hire_date, '2025-12-31') < 5 THEN '2-4 years'         ELSE '5+ years'       END AS tenure_band,       COUNT(*) AS staffFROM employeesWHERE termination_date IS NULLGROUP BY tenure_bandORDER BY tenure_band;

ResultMySQL 8.4

tenure_band	staff
0-1 years	2
2-4 years	8
5+ years	14

3 rows in set

Average time leavers stayed before leaving:

MySQL

SELECT COUNT(*) AS leavers,       ROUND(AVG(TIMESTAMPDIFF(MONTH, hire_date, termination_date))) AS avg_months_stayedFROM employeesWHERE termination_date IS NOT NULL;

ResultMySQL 8.4

leavers	avg_months_stayed
2	18

1 row in set

Task 9: The organisation chart

MySQL

WITH RECURSIVE org AS (  SELECT emp_id, first_name, job_title, manager_id, 1 AS lvl,         CAST(first_name AS CHAR(300)) AS path  FROM employees  WHERE manager_id IS NULL  UNION ALL  SELECT e.emp_id, e.first_name, e.job_title, e.manager_id, org.lvl + 1,         CONCAT(org.path, ' > ', e.first_name)  FROM employees e  JOIN org ON e.manager_id = org.emp_id  WHERE e.termination_date IS NULL)SELECT CONCAT(REPEAT('    ', lvl - 1), first_name, ' (', job_title, ')') AS org_chart, lvlFROM orgORDER BY path;

ResultMySQL 8.4

org_chart	lvl
Ananya (CTO)	1
Kabir (Head of Support)	2
Ritu (Support Lead)	3
Siddharth (Support Agent)	3
Tanvi (Support Agent)	3
Uday (Support Lead)	3
Meera (Head of Sales)	2
Lata (Account Executive)	3
Manish (Sales Associate)	3
Nidhi (Account Executive)	3
Pranav (Sales Associate)	3
Rohan (Head of QA)	2
Farhan (Senior QA Engineer)	3
Gauri (QA Engineer)	3
Harsh (Automation Engineer)	3
Jaya (Senior QA Engineer)	3
Karthik (QA Engineer)	3
Sara (Head of People)	2
Zubin (Recruiter)	3
Vikas (Senior Engineer)	2
Aditya (Engineer)	3
Imran (Engineer)	3
Leela (Senior Engineer)	3
Pooja (Engineer)	3

24 rows in set

How this query works, step by step

The anchor is the CTO, who has no manager.
Each round adds the current employees who report to someone already in the chart (Recursive CTE).
Sorting by the path places everyone under their manager; REPEAT indents by level.

Task 10: Span of control

SQL

SELECT m.emp_id, m.first_name, m.job_title, COUNT(e.emp_id) AS direct_reportsFROM employees mJOIN employees e ON e.manager_id = m.emp_id AND e.termination_date IS NULLGROUP BY m.emp_id, m.first_name, m.job_titleORDER BY direct_reports DESC, m.emp_id;

ResultMySQL 8.4

emp_id	first_name	job_title	direct_reports
1	Ananya	CTO	5
2	Rohan	Head of QA	5
3	Meera	Head of Sales	4
4	Kabir	Head of Support	4
6	Vikas	Senior Engineer	4
5	Sara	Head of People	1

6 rows in set

Task 11: Salary quartiles

SQL

WITH latest AS (  SELECT emp_id, annual_salary,         ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY effective_date DESC) AS rn  FROM salary_history),q AS (  SELECT e.emp_id, l.annual_salary, NTILE(4) OVER (ORDER BY l.annual_salary) AS quartile  FROM employees e  JOIN latest l ON l.emp_id = e.emp_id AND l.rn = 1  WHERE e.termination_date IS NULL)SELECT quartile, COUNT(*) AS staff, MIN(annual_salary) AS from_salary, MAX(annual_salary) AS to_salaryFROM qGROUP BY quartileORDER BY quartile;

ResultMySQL 8.4

quartile	staff	from_salary	to_salary
1	6	73000	84000
2	6	84000	127000
3	6	128000	239000
4	6	260000	773000

4 rows in set

Your turn

Exercise: Hires per year

Count employees hired in each calendar year.

Expected resultMySQL 8.4

hire_year	hires
2016	1
2017	3
2019	4
2020	6
2021	5
2022	3
2023	1
2024	3

8 rows in set

Hint 1

Group by YEAR(hire_date).

MySQL

SELECT YEAR(hire_date) AS hire_year, COUNT(*) AS hiresFROM employeesGROUP BY hire_yearORDER BY hire_year;

ResultMySQL 8.4

hire_year	hires
2016	1
2017	3
2019	4
2020	6
2021	5
2022	3
2023	1
2024	3

8 rows in set

Exercise: Starting salary vs today

For current employees, show the first salary, the current salary and the total growth in %.

Expected resultMySQL 8.4

first_name	first_salary	current_salary	growth_pct
Rohan	256000	542000	111.7
Ananya	428000	773000	80.6
Kabir	266000	417000	56.8
Vikas	173000	260000	50.3
Sara	256000	365000	42.6

5 rows in set

Hint 1

MIN(effective_date) and MAX(effective_date) rows, or FIRST_VALUE / LAST_VALUE windows.

MySQL

WITH s AS (  SELECT emp_id,         FIRST_VALUE(annual_salary) OVER w AS first_salary,         LAST_VALUE(annual_salary)  OVER w AS current_salary  FROM salary_history  WINDOW w AS (PARTITION BY emp_id ORDER BY effective_date               ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING))SELECT DISTINCT e.first_name, s.first_salary, s.current_salary,       ROUND(100 * (s.current_salary - s.first_salary) / s.first_salary, 1) AS growth_pctFROM sJOIN employees e ON e.emp_id = s.emp_idWHERE e.termination_date IS NULLORDER BY growth_pct DESCLIMIT 5;

ResultMySQL 8.4

first_name	first_salary	current_salary	growth_pct
Rohan	256000	542000	111.7
Ananya	428000	773000	80.6
Kabir	266000	417000	56.8
Vikas	173000	260000	50.3
Sara	256000	365000	42.6

5 rows in set

What you practised

Task	SQL skills
Current salary, payroll, quartiles	`ROW_NUMBER` latest-row pattern, aggregates, `NTILE`
Pay vs team, top earners	`AVG() OVER (PARTITION BY)`, `DENSE_RANK`
Raises, missed appraisal	`LAG`, `HAVING MAX(...)`
Headcount, attrition, tenure	conditional `SUM`, `TIMESTAMPDIFF`, `CASE` bands
Org chart, span of control	recursive CTE, self join

Project FAQ

How do you get the latest record per employee in SQL?

Number each employee’s rows with ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY effective_date DESC) and keep row number 1.

How do you calculate attrition rate in SQL?

Divide the number of employees with a termination date by the number of employees in the period, usually per department: 100 * SUM(termination_date IS NOT NULL) / COUNT(*).

How do you calculate a salary increase percentage in SQL?

Use LAG(salary) OVER (PARTITION BY emp_id ORDER BY effective_date) to get the previous salary, then 100 * (salary - previous) / previous.