HR Payroll Dataset SQL Aggregation Practice with 20 Challenges

This final lesson of the module consolidates every GROUP BY and aggregation concept — COUNT, SUM, AVG, MIN, MAX, HAVING, multiple-column grouping, JOIN-based aggregation, ROLLUP, and NULL handling — into a single, coherent HR payroll practice project. HR payroll data is an excellent practice domain because it naturally invites nearly every kind of aggregation question a business could ask: headcounts, salary totals and averages, departmental comparisons, and threshold-based filtering for compliance or budgeting purposes.

Key Definitions

• Payroll dataset: A collection of employee compensation records typically including salary, department, bonus, and tenure information, used for HR and financial analysis.
• Compliance threshold: A business rule, such as a minimum or maximum salary requirement, that a query might need to check for using HAVING or WHERE.
• Headcount: The total number of employees, often broken down by department, tenure band, or other grouping criteria.

What You'll Learn

• Design and populate a realistic HR payroll database schema suitable for comprehensive aggregation practice.
• Apply COUNT, SUM, AVG, MIN, and MAX across various payroll analysis scenarios.
• Practice HAVING-based filtering for compliance and budget-threshold style questions.
• Apply ROLLUP to produce departmental subtotals and a company-wide payroll grand total.
• Solve 20 progressively challenging exercises spanning the entire module's toolkit in one connected project.

Detailed Explanation

This practice project uses three interconnected tables: departments (department_id, department_name), employees (employee_id, employee_name, department_id, salary, hire_date, bonus), and a payroll_adjustments table (adjustment_id, employee_id, adjustment_type, amount, adjustment_date) representing raises, deductions, or one-time bonus payments over time. This structure supports both straightforward per-department aggregation and more advanced multi-table aggregation once payroll_adjustments is joined in.

The 20 challenges are organized into two tiers of ten questions each. The first tier (1-10) focuses on core aggregation skills directly on the employees and departments tables: counting headcount per department, calculating total and average salary per department, finding the highest and lowest paid employee per department, and filtering departments using HAVING based on salary thresholds. The second tier (11-20) requires combining these skills with JOIN against payroll_adjustments, multi-column grouping (such as department and hire year together), ROLLUP for company-wide payroll subtotals, and careful NULL handling for employees with no recorded bonus or adjustment history.

As with the JOIN module's hospital practice project, learners should attempt each challenge independently before reviewing the provided solution pattern, focusing on identifying which specific combination of GROUP BY, HAVING, JOIN, and ROLLUP techniques each question requires, rather than memorizing the exact query text alone.

Visual Summary

Draw an entity-relationship diagram with three boxes: 'departments' connected to 'employees' via department_id, and 'employees' connected to 'payroll_adjustments' via employee_id. Below the diagram, add two labeled tiers: 'Tier 1 (Challenges 1-10): Core aggregation on employees and departments' and 'Tier 2 (Challenges 11-20): JOIN, multi-column grouping, ROLLUP, and NULL handling with payroll_adjustments'.

Quick Reference

Tier	Challenge Range	Focus	Example Challenge
Tier 1	1–10	COUNT, SUM, AVG, MIN, MAX, HAVING	Total and average salary per department
Tier 2	11–20	JOIN, multi-column grouping, ROLLUP, NULL handling	Total bonus payout per department with a grand total

SQL Example

-- Full schema setup for the HR payroll aggregation practice project
CREATE TABLE departments (
  department_id   INT PRIMARY KEY AUTO_INCREMENT,
  department_name VARCHAR(100)
);

CREATE TABLE employees (
  employee_id    INT PRIMARY KEY AUTO_INCREMENT,
  employee_name  VARCHAR(100),
  department_id  INT,
  salary         DECIMAL(10,2),
  hire_date      DATE,
  FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

CREATE TABLE payroll_adjustments (
  adjustment_id     INT PRIMARY KEY AUTO_INCREMENT,
  employee_id       INT,
  adjustment_type   VARCHAR(50),
  amount            DECIMAL(10,2),
  adjustment_date   DATE,
  FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
);

-- CHALLENGE 1 (Tier 1): Headcount per department
SELECT d.department_name, COUNT(e.employee_id) AS headcount
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name;

-- CHALLENGE 6 (Tier 1): Departments with average salary above 70000
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 70000;

-- CHALLENGE 14 (Tier 2): Total bonus payout per department, with a grand total
SELECT
  IFNULL(d.department_name, 'Grand Total') AS department_name,
  SUM(pa.amount) AS total_bonus_payout
FROM departments d
JOIN employees e ON d.department_id = e.department_id
JOIN payroll_adjustments pa ON e.employee_id = pa.employee_id
WHERE pa.adjustment_type = 'Bonus'
GROUP BY d.department_name WITH ROLLUP;

-- CHALLENGE 20 (Tier 2): Employees with no recorded payroll adjustments
SELECT e.employee_name
FROM employees e
LEFT JOIN payroll_adjustments pa ON e.employee_id = pa.employee_id
WHERE pa.adjustment_id IS NULL;

The schema models a realistic payroll structure connecting departments, employees, and their individual payroll adjustment history. Challenge 1 uses LEFT JOIN with COUNT to correctly show headcount per department, including departments with zero employees. Challenge 6 applies HAVING to filter departments by an average salary threshold. Challenge 14 combines a multi-table JOIN, a WHERE filter for bonus-type adjustments specifically, and WITH ROLLUP to show both per-department bonus totals and a company-wide grand total. Challenge 20 applies the LEFT JOIN anti-join pattern to find employees with no payroll adjustment history at all, together spanning the full aggregation toolkit covered across this module.

Real-World Examples

• Real HR and payroll systems use nearly this exact schema structure to power compensation review dashboards, budget planning tools, and compliance audits.
• Finance teams use department-level payroll subtotal and grand-total reports, structurally identical to Challenge 14, during annual budget planning cycles.
• Compliance teams use HAVING-based salary threshold queries similar to Challenge 6 to identify departments that may require pay equity review.
• HR analytics teams use anti-join patterns similar to Challenge 20 to identify employees whose records may be incomplete or who have not yet received any payroll processing.
• This schema pattern — a core entity table connected to both an organizational hierarchy and a historical adjustments log — recurs across countless business domains beyond payroll, including inventory adjustments, account transaction histories, and academic grade revisions.

Common Mistakes to Avoid

• Using INNER JOIN instead of LEFT JOIN when a report should include categories (like departments) with zero matching records.
• Placing an aggregate-based filtering condition in WHERE instead of HAVING, causing a query error.
• Forgetting to filter for the correct adjustment_type before summing payroll_adjustments, accidentally including deductions in a bonus-specific total.
• Not testing edge cases such as departments with zero employees or employees with zero payroll adjustments, which are exactly the scenarios these challenges are designed to expose.

Interview Questions

Q1. Why does Challenge 1 use LEFT JOIN instead of a plain INNER JOIN to calculate headcount per department?

Using LEFT JOIN from departments to employees ensures that departments with zero employees still appear in the report with a headcount of zero, rather than being excluded entirely as would happen with an INNER JOIN, which only includes departments that have at least one matching employee.

Q2. How would you find departments where the average salary exceeds a specific threshold?

Group the employees table by department_id, compute AVG(salary) for each group, and use a HAVING clause to filter out any department whose average salary does not exceed the specified threshold.

Q3. How would you calculate total bonus payout per department, including a company-wide grand total?

Join departments to employees and then to payroll_adjustments, filter for bonus-type adjustments specifically using WHERE, group by department_name, and add WITH ROLLUP to automatically append a final grand total row summarizing bonus payouts across the entire company.

Q4. How would you find employees with no recorded payroll adjustments at all?

Perform a LEFT JOIN from employees to payroll_adjustments on employee_id, then filter with WHERE payroll_adjustments.adjustment_id IS NULL, which isolates employees with no matching adjustment records — the standard anti-join pattern.

Practice MCQs

1. Why is LEFT JOIN preferred over INNER JOIN when calculating headcount per department in this schema?

1. LEFT JOIN runs faster in all cases
2. LEFT JOIN preserves departments with zero employees in the result
3. INNER JOIN cannot be used with COUNT
4. LEFT JOIN is required by MySQL syntax rules

Answer: B. LEFT JOIN preserves departments with zero employees in the result

Explanation: INNER JOIN would exclude any department with no matching employees entirely, while LEFT JOIN ensures every department appears, showing a headcount of zero where appropriate.

2. To filter departments by an average salary threshold after grouping, you need:

1. WHERE AVG(salary) > threshold
2. HAVING AVG(salary) > threshold
3. ORDER BY AVG(salary)
4. GROUP BY alone with no filter

Answer: B. HAVING AVG(salary) > threshold

Explanation: Since this condition depends on an aggregated AVG value calculated per group, HAVING is required rather than WHERE, which cannot filter on aggregate results.

3. In Challenge 14, why is WHERE used to filter for adjustment_type = 'Bonus' before grouping?

1. To improve query readability only
2. To ensure only bonus-type adjustments are included in the SUM before departmental totals are calculated
3. WHERE is required syntactically whenever JOIN is used
4. It has no effect on the final result

Answer: B. To ensure only bonus-type adjustments are included in the SUM before departmental totals are calculated

Explanation: Filtering with WHERE before grouping ensures that only relevant bonus-type records contribute to each department's total, rather than including other adjustment types like deductions.

4. Which pattern is used to find employees with no payroll adjustment records at all?

1. INNER JOIN with GROUP BY
2. LEFT JOIN combined with WHERE payroll_adjustments.adjustment_id IS NULL
3. CROSS JOIN with DISTINCT
4. RIGHT JOIN with HAVING

Answer: B. LEFT JOIN combined with WHERE payroll_adjustments.adjustment_id IS NULL

Explanation: This is the standard anti-join pattern: LEFT JOIN preserves all employees, and filtering for a NULL adjustment column isolates those with no matching adjustment record.

Quick Revision Points

• This HR payroll schema exercises COUNT, SUM, AVG, MIN, MAX, HAVING, multi-column GROUP BY, JOIN-based aggregation, ROLLUP, and NULL handling together.
• LEFT JOIN is essential whenever a report must include categories or entities with zero matching related records.
• WHERE should filter relevant rows (like a specific adjustment_type) before aggregation; HAVING should filter based on the resulting aggregated values.
• The anti-join pattern (LEFT JOIN + WHERE ... IS NULL) is directly reusable here to find employees with no adjustment history.

Conclusion

• Practicing aggregation concepts against one coherent, realistic payroll schema builds far deeper understanding than isolated examples.
• HR payroll systems naturally exercise nearly every aggregation pattern covered in this module in a single connected project.
• Working through progressively harder challenges mirrors how real interviews and job tasks escalate in complexity.
• This project can serve as a strong portfolio piece demonstrating comprehensive, applied SQL aggregation skills.

This mini-project applies every GROUP BY and aggregation concept covered in this module — COUNT, SUM, AVG, MIN, MAX, HAVING, multi-column grouping, JOIN-based aggregation, ROLLUP, and NULL handling — to a single, realistic HR payroll database schema involving departments, employees, and payroll adjustments. Through 20 progressively challenging exercises organized into two tiers, learners move from simple per-department aggregation to complex multi-table, ROLLUP-enhanced payroll reports, building the kind of practical, applied SQL fluency that real interviews and real jobs actually demand.