Lesson 12 of 13 · 26 min
Headcount, joiners, leavers and turnover
By the end of this lesson you can calculate headcount at any date, joiners, leavers and turnover rate per year, a monthly headcount series with generate_series, and each employee's salary as of a date from a salary history.
The people dataset is the HR system of Masar Holding, a fictional Saudi group, from 2015 to the end of 2025. Leavers stay in employees: there is no 'current staff' table, only dates. That makes the definitions everything:
| Measure | Definition in this course |
|---|---|
| Active on date d | hire_date <= d AND (termination_date IS NULL OR termination_date >= d). termination_date is the last working day, so a person is still counted on it. |
| Headcount on d | number of employees active on d |
| Joiners in year Y | employees whose hire_date falls in Y |
| Leavers in year Y | employees whose termination_date falls in Y |
| Turnover rate | leavers in Y ÷ average headcount × 100, with average headcount = (headcount on 31 Dec of Y−1 + headcount on 31 Dec of Y) ÷ 2 |
Headcount counts people, whatever their contract (full-time, part-time, contractor). Organisations that report FTE (full-time equivalents) or exclude contractors must say so; here, the definition above is the rule.
This lesson, its 4 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.
Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons