SQL Quest › SQL Interview Questions › Date Functions

Hires per Year

EasyFreeQuerying BasicsDate FunctionsAggregation & Grouping

The head of people is preparing a slide on how fast the company grew. They want one row per year with the number of people hired that year.

Show exactly these 2 columns: hire_year (the year of hire_date, as four-digit text like 2019) and hires. Order by hire_year.

hire_date holds a full date (2019-03-15), so two people hired in the same year but on different days must still land in the same row. Seven years, seven rows.

Solve it in the browser editor →

Runs on SQLite in your browser, graded against the expected result, no signup. A wrong answer gets a diagnosis, not just "incorrect". Free accounts get 10 free challenge solves, and this can be one of them.

Schema

employees

emp_idnamedepartmentpositionsalaryhire_datemanager_idperformance_rating
1Alice JohnsonEngineeringSenior Developer950002019-03-1554.5
2Bob SmithEngineeringDeveloper750002020-06-0113.8
3Carol WilliamsMarketingMarketing Manager850002018-09-20NULL4.2

Expected output: 7 rows — e.g. 2016 · 1 and 2020 · 12

Hint

Group on the year, not on the date. Pull the year out of hire_date with a date function (SQLite's date formatter with the year pattern), use it as the grouping key, and count the rows in each group.

Concepts

SELECT Date Functions strftime GROUP BY COUNT

Practise the topic: SQL practice questions · Date function practice · GROUP BY exercises

Read the concept: Time-series SQL: the ordering trap

Related questions

January 2024 OrdersEasy · FreePre-1900 BanksEasy · Free2025 SalesEasy · FreeWeekend LoginsEasy · FreeEmployee Tenure BandsMedium · FreeQuarterly Hiring Cohort ReportMedium · Free

Where would this cost you points in an interview?

Ten questions, no signup: a Skillmap across nine SQL skills and the one to fix first.

Take the readiness test