HardSQL challenge #227CASEGROUP BYAGGREGATION

Count Employees per Salary Band in Each Department

Return each department with the number of employees in the low, mid and high salary bands.

The challenge runs on the SQLabHub practice database (SQLite) and uses the employees table. You can solve it in the browser, with nothing to install.

Solve This SQL Challenge

The task

Find count of employees in salary buckets.

A correct answer returns 10 rows with 4 columns: department, low, mid, high.

Tables used in this challenge

employees 120 rows

id INTEGER (PK) · first_name TEXT · last_name TEXT · email TEXT · department TEXT · department_id INTEGER · title TEXT · salary REAL · hire_date TEXT · manager_id INTEGER · city TEXT · status TEXT

First 3 rows of the employees table
idfirst_namelast_nameemaildepartmentdepartment_idtitlesalaryhire_datemanager_idcitystatus
1WilliamLewiswlewis18@corp.ioCustomer Success7Senior CS Manager617742022-07-24NULLSan Diegoactive
2JohnMillerjmiller51@corp.ioLegal8Paralegal1916942021-08-09NULLSan Diegoactive
3DanielRiveradrivera50@corp.ioEngineering1VP Engineering1350672016-06-06NULLHoustonactive

The first rows of each table are shown. See how all the tables relate on the database schema page.

Expected output

Expected output of the challenge
departmentlowmidhigh
Customer Success0114
Design028
Engineering0110
Finance028
HR0111
Legal0016
Marketing0112
Operations0310
Product018
Sales029

The complete result: 10 rows. This is the output of the model solution on the practice database. The task does not fix a sort order, so your rows may come back in a different order.

What you practise

  • Filtering a range of values with BETWEEN
  • Summarising many rows into one value with COUNT, SUM, AVG, MIN or MAX
  • Grouping rows with GROUP BY to get one result row per group
  • Turning conditions into values with CASE WHEN

Hint and approach

Try the challenge first. Open these only when you need them.

Show the hint

CASE WHEN

Show the step-by-step approach

How the model solution is built, in the order the database evaluates it:

  1. Read from the employees table.
  2. Group the rows by department, so the result has one row for each value.
  3. For each group, compute a conditional count (SUM(CASE WHEN … THEN 1 ELSE 0 END)) of the rows where salary is less than 30000, a conditional count (SUM(CASE WHEN … THEN 1 ELSE 0 END)) of the rows where salary is between 30000 and 70000, both ends included and a conditional count (SUM(CASE WHEN … THEN 1 ELSE 0 END)) of the rows where salary is greater than 70000.

Model solution

Checking your session…

What to watch for

Each band is a SUM over a CASE that returns 1 or 0. BETWEEN is inclusive at both ends, so the mid band contains exactly 30,000 and 70,000. No salary in this dataset is below 30,000, so the low column is 0 throughout.

  • GROUP BY. Every selected column must either be in GROUP BY or sit inside an aggregate. SQLite accepts other columns and returns an arbitrary value for them; most databases raise an error.
  • CASE WHEN. CASE stops at the first WHEN that is true, so overlapping conditions must be ordered from most to least specific. Without an ELSE, unmatched rows get NULL.
  • BETWEEN. BETWEEN includes both boundary values. On dates stored with a time, the upper bound silently excludes most of the last day.
Solve This SQL Challenge

Keep learning