Skip to content
Tutorials Center
My learning

Topics

Lesson 109 of 125 Β· Projects

Project: HR Analytics

Analyze a company's people data with SQL. Report headcount and payroll, build salary bands, compare pay within roles, draw the org chart with recursive CTEs and study tenure.

Human-resources teams ask some of the most interesting questions in any company. How many people work in each department, and what do they cost? Are people in the same role paid fairly? Who reports to whom, all the way up? Are we keeping the people we hire? Every one of these questions has an answer in SQL, and several of them need techniques that only make sense once you've seen them on real data.

In this project you'll be the analyst for the company in the company sample database: 36 employees in six departments, with managers, hire dates, salaries and project assignments. You'll build a set of reports stage by stage, check each result, and collect findings to present at the end.

The techniques come from LEFT JOIN, GROUP BY, CASE, pivoting, window functions, self joins, recursive CTEs, date functions and percentiles.

Stage 1: headcount and payroll

The first report any HR team wants is the simplest: how many people per department, and what does each department cost? Start from employees and LEFT JOIN the departments. That direction matters, because some employees may have no department:

SQL Headcount and payroll by department Β· database: company

Press Run (or Ctrl + Enter) to see the result here.

Engineering is the largest department with 10 people and 875,000 in salaries. The last row is the interesting one: two employees have no department. Look them up with WHERE department_id IS NULL and you'll find the CEO and a contractor. With an inner join they would silently vanish from the report, and the payroll total would be 258,000 short.

Check the total

Whenever you split a number into groups, make sure the groups add back up to the whole. One quick query gives the company-wide figures:

SQL Company totals Β· database: company

Press Run (or Ctrl + Enter) to see the result here.

36 employees and a payroll of 3,253,000, which is what the department rows add up to (875,000 + 566,000 + 398,000 + 478,000 + 346,000 + 332,000 + 258,000). The query also catches a small data-quality issue: COUNT(email) skips NULLs, so 3 employees have no email address on file. Worth passing on to whoever maintains the HR system.

Stage 2: salary bands

Averages hide spread. A department with salaries of 60,000 and 160,000 has the same average as one where everybody earns 110,000. Salary bands group people into ranges so you can see the distribution. Define the bands once with a CASE expression, then count:

SQL Salary bands Β· database: company

Press Run (or Ctrl + Enter) to see the result here.

The CASE branches are checked from top to bottom and the first match wins, so each branch only needs an upper limit. The letter prefixes make the bands sort in the right order; without them, '100k-149k' would sort before '60k-79k' because text compares character by character. Most people (25 of 36) earn between 60,000 and 99,999.

Now cross bands with departments. This is a pivot: one row per department, one column per band, built with conditional counts:

SQL Bands by department Β· database: company

Press Run (or Ctrl + Enter) to see the result here.

In SQLite a comparison is 1 or 0, so SUM(condition) counts matching rows. Other databases need SUM(CASE WHEN condition THEN 1 ELSE 0 END) or, in PostgreSQL, COUNT(*) FILTER (WHERE condition). The 100k+ column holds every department head, plus one more person in Support. Remember that: you'll meet them again in Stage 5. The double quotes around names like "60k-79k" are needed because those aliases start with a digit and contain characters like - and +, as covered in SQL Syntax and Style.

Stage 3: pay within roles

A department average mixes juniors with department heads. A fairer comparison is between people with the same title. A window function puts the title's average next to each person without collapsing the rows, which GROUP BY would do:

SQL Salary versus title average Β· database: company

Press Run (or Ctrl + Enter) to see the result here.

Some gaps are large. Among Software Engineers, Ivo Rossi earns 94,000 and Kofi Dubois 60,000, 23.1% below the title's average of 78,000. Support Lead has only two people, 93,000 and 118,000, so each is 11.8% from the average in opposite directions. A gap is not proof of unfairness (experience and performance differ), but it tells HR where to look. Change the WHERE list, or remove it, to see every title.

Notice people_in_title. Comparing someone with an "average" of two people says little, so always show the group size next to a group statistic.

Median salary per department

The average is pulled up by a few high salaries, and every department here includes its head. The median, the middle value, is more robust. SQLite has no MEDIAN or PERCENTILE_CONT function, but you can compute it with window functions: number the salaries in order, and take the middle one (or the average of the middle two):

SQL Median salary per department Β· database: company

Press Run (or Ctrl + Enter) to see the result here.

The WHERE line is the clever part. With integer division, (n + 1) / 2 and (n + 2) / 2 are the same position when n is odd (for 5 people, both are 3) and the two middle positions when n is even (for 10 people, 5 and 6). Averaging those rows gives the median in both cases.

Engineering's median is 81,500 while its mean is 87,500: the head's 157,000 salary pulls the mean up. Design shows the biggest gap, with a median of 69,000 and a mean of 83,000. In PostgreSQL you'd write PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary), as shown in Percentiles, Medians and Statistics.

Stage 4: the org chart

Every employee row has a manager_id pointing to another employee, and the CEO's is NULL. That's a tree stored in one table, and walking a tree of any depth is the job of a recursive CTE.

Top down

Start from the root (the employee with no manager), then repeatedly add the employees whose manager is already in the result. A sort_key built from padded ids keeps every person directly under their manager:

SQL Org chart from the CEO down Β· database: company

Press Run (or Ctrl + Enter) to see the result here.

The first SELECT is the anchor: it returns just Kofi Okafor, the CEO. The second is the recursive step: each round joins employees to the rows found in the previous round. Round one finds the six department heads and the contractor; round two finds everyone who reports to them; round three finds nobody, and the recursion stops. The dots indent each level so the hierarchy is visible in a plain table.

Why pad the ids with printf('%03d', id)? The sort key is text, and as text '10' sorts before '9'. Padding to three digits ('009', '010') makes text order match number order.

Bottom up: a chain of command

The same idea works upward. Start from one employee and repeatedly follow manager_id:

SQL Chain of command for one employee Β· database: company

Press Run (or Ctrl + Enter) to see the result here.

Olga Jensen reports to Nadia Okafor, who reports to the CEO. Change id = 15 to any other id to trace someone else.

But wait: Olga is a Software Engineer in Engineering, and Nadia is the Head of Sales. That looks wrong. Is it an error, or does Olga really work for Sales? Find every such case with a self join:

SQL Reporting across departments Β· database: company

Press Run (or Ctrl + Enter) to see the result here.

Four Engineering employees report to the heads of Design or Sales. This could be a deliberate "embedded engineer" arrangement or a data-entry mistake, and SQL can't tell you which. What it can do is make the question impossible to miss. (The CEO and contractor don't appear because the inner joins to departments drop rows with a NULL department.)

Team sizes

How many people does each manager lead, directly and indirectly? A recursive CTE can pair every manager with every person below them. The anchor pairs each employee with themself; each step adds one more level down:

SQL Total team size per manager Β· database: company

Press Run (or Ctrl + Enter) to see the result here.

The CEO leads all 35 other employees; each head leads 4 or 5. Subtracting 1 (and the manager's own salary) removes the self-pairs from the anchor. In this company the tree is only three levels deep, so a two-level self join could do the same, but the recursive version keeps working if the company adds team leads and the tree grows deeper.

If the data ever contained a loop (A manages B, B manages A), a recursive CTE with UNION ALL would run forever. Our runner stops long runs, but in production add a depth limit such as WHERE o.level < 20 to the recursive step, or use UNION (which drops repeated rows) when the rows contain no ever-growing column.

Stage 5: tenure

Tenure, how long people have been with the company, connects hiring, retention and pay. Fix an "as of" date so the numbers don't change every day; here it's the end of 2025. Start with hires per year and a running total:

SQL Hires per year Β· database: company

Press Run (or Ctrl + Enter) to see the result here.

2019 was the biggest hiring year (7 people), and the cumulative total reaches 36 in 2025. Be careful how you describe that column, though. It is not the headcount over time, because the table only contains current employees. Anyone who left is gone from the data, so earlier years look smaller than they really were. This is called survivorship bias, and the fix is a schema change: keep leavers with a left_on date instead of deleting them.

Now group people by length of service and compare salaries. julianday() differences give days; dividing by 365.25 gives years:

SQL Tenure buckets Β· database: company

Press Run (or Ctrl + Enter) to see the result here.

Sorting by MIN(years) puts the buckets in time order without needing letter prefixes. The 8+ years group earns by far the most (161,400 on average), but the executives column explains why: all five of them are the CEO and department heads. Here's a surprise, too: people with under one year of service earn more on average (76,333) than those with 1–5 years.

Is that real? Look inside a title, where tenure and pay can be compared fairly. RANK() gives each person's seniority and pay position within their title:

SQL Seniority versus pay rank Β· database: company

Press Run (or Ctrl + Enter) to see the result here.

Ivo Andersson joined as a Support Lead in October 2025 at 118,000, well above Aiden Costa, the Support Lead since 2019 (93,000). Olga Jensen, the newest Software Engineer, is the second best paid. This pattern, new hires earning more than experienced colleagues because market rates rose, is called pay compression, and it's a common reason good people leave. Your report should flag it, carefully: seniority isn't the only thing that sets pay.

Stage 6: workload

The assignments table records how many hours per week each person spends on each project. Total them per employee, keeping people with no projects:

SQL Weekly project hours Β· database: company

Press Run (or Ctrl + Enter) to see the result here.

Wen Kaya is booked for 60 hours a week across two projects, Ximena Yilmaz for 50 and Aiden Costa for 45. In a standard 40-hour week, those plans can't all be true. The CASE tests SUM(...) IS NULL rather than = 0 because an employee with no assignment rows gets NULL from SUM, not zero; that's why the display column wraps it in COALESCE.

Remove the LIMIT and the end of the list shows the other side: 10 employees have no project at all. That's not necessarily bad (support and sales work isn't always a "project"), but it's a useful number for planning.

Putting it together

Here's what you can now tell the HR director, with a query behind each point:

  • 36 employees, 3,253,000 in payroll. Engineering is the largest team (10). Two people sit outside any department, and 3 records have no email.
  • Pay is concentrated in the middle: 25 of 36 people earn 60,000–99,999; every department head earns 100,000 or more, and so does one Support Lead.
  • Within-title gaps are sizable, up to 23% below average for Software Engineers. Medians are more representative than means because department heads pull the mean up.
  • The reporting lines need a review: four engineers report to the heads of Design and Sales.
  • Pay compression: some of the newest hires out-earn colleagues with years of service in the same role.
  • Workload is uneven: three people are planned for more than 40 project hours a week, while 10 have no project.
  • Caveat: the data has no leavers, so hiring trends and tenure figures describe the survivors only.

Ideas to extend it

  • Gender or location pay analysis: the data has no such column, but the technique is the same as Stage 3. Add a column, then compare medians within titles.
  • Salary compa-ratio: define a midpoint salary for each title in a small table, then compute each person's salary / midpoint.
  • Span of control: count direct reports per manager and flag managers with more than 6 or fewer than 2.
  • Project costs: estimate each project's weekly cost as salary / 52 / 40 * hours_per_week summed over its assignments, and compare it with budget.
  • Raise simulation: in a transaction, give everyone more than 10% below their title average a raise to the average, report the new payroll, then ROLLBACK.
  • Org chart paths: add a path column to Stage 4 ('Kofi Okafor > Nadia Okafor > Olga Jensen') and find everyone whose path contains a given manager.
  • Anniversaries: list employees whose work anniversary falls in the next 30 days after the as-of date, with their years of service.

Check your understanding

Quick check Β· 5 questions

of 5 answered You scored / 5 Β· perfect!

  1. 1. Why does the headcount report use employees LEFT JOIN departments instead of an inner join?

    An inner join keeps only matching rows. The CEO and the contractor have no department, so they would be dropped and the payroll total would be wrong.
  2. 2. In the median query, which positions are selected for a department with 4 employees?

    With integer division, (4 + 1) / 2 = 2 and (4 + 2) / 2 = 3. Averaging the two middle values gives the median.
  3. 3. In the top-down org chart, what does the anchor part of the recursive CTE return?

    The anchor runs once and seeds the recursion with the root of the tree. Each recursive step then adds the next level of reports.
  4. 4. Why is the cumulative-hires column not the company's headcount over time?

    Without leavers in the data, earlier years are undercounted. That's survivorship bias; storing a leave date fixes it.
  5. 5. An employee has no rows in assignments. What does SUM(a.hours_per_week) return for them after the LEFT JOIN?

    The LEFT JOIN gives one row with NULL in every assignments column, and SUM over only NULLs is NULL. Use COALESCE to display 0.

Summary

  • Start people reports from employees and LEFT JOIN lookups, so nobody without a department disappears; check that group totals add up.
  • Salary bands are a CASE expression; crossing them with departments is a pivot with conditional SUMs.
  • AVG(...) OVER (PARTITION BY title) compares each person with their peers without collapsing rows; show group sizes next to group statistics.
  • Compute medians in SQLite with ROW_NUMBER() and COUNT(*) OVER, picking the middle one or two rows.
  • Recursive CTEs walk a manager_id hierarchy top down, bottom up, or into manager–descendant pairs for team sizes.
  • Fix an "as of" date for tenure, and remember that a table of current employees hides everyone who left.

Next: Project: School Report Cards, where you'll compute GPAs, rank students and pivot grades into report cards.