The Hiring Curve
The talent team at Kestrel Labs is asking for next year's recruiting budget and has been told to bring evidence rather than a feeling. The evidence they want is the hiring curve: how many people the company took on in each year it has been hiring, oldest year first, so the trend reads left to right on a chart.
Nothing in the data records a year on its own. Every start date is a full date stored as text, so the year has to be recovered from it before anything can be counted per year.
employees — one row per person. department_id is the team they sit on, given as the matching id in departments; salary is annual pay in dollars; hire_date is the day they started, written year-month-day; manager_id is the id of the person they report to, and is NULL for anyone who reports to nobody.
| id | name | department_id | salary | hire_date | manager_id |
|---|---|---|---|---|---|
| 1 | Ava Chen | 1 | 145000 | 2021-03-14 | NULL |
| 2 | Ben Ortiz | 1 | 118000 | 2022-06-01 | 1 |
| 3 | Cara Novak | 1 | 121000 | 2023-01-10 | 1 |
| 4 | Deshawn Lee | 2 | 95000 | 2020-09-23 | NULL |
| 5 | Elin Kask | 2 | 88000 | 2022-11-05 | 4 |
| 6 | Farid Amiri | 3 | 76000 | 2023-04-18 | NULL |
| 7 | Grace Kim | 4 | 132000 | 2021-07-30 | NULL |
| 8 | Hugo Silva | 4 | 110000 | 2023-02-14 | 7 |
| 9 | Ines Duarte | 4 | 104000 | 2023-08-01 | 7 |
| 10 | Jonas Weber | 2 | 91000 | 2021-12-19 | 4 |
The table already exists in the database — there is nothing to create or load.
Task: Write a query returning two columns, hire_year and hires, one row per year in which at least one person was hired, oldest year first. hire_year is the four-digit year as text, so "2020" rather than the number 2020. hires is how many people started in that year. A year in which nobody was hired does not appear as a zero — it does not appear at all.
Example output — shape only, on invented years. Note the gap: nobody started in 2018, and that year is absent rather than zero:
| hire_year | hires |
|---|---|
| 2016 | 5 |
| 2017 | 9 |
| 2019 | 12 |
Sign in to solve this problem
Reading problems is free for everyone — solving them (Run, Submit, and tracking what you've solved) needs an account.
Sign in