Headcount monthly

1-- core.hibob.headcount_monthly
2-- Headcount, hires, leavers and turnover per month, department and site.
3-- HiBob's headcount and turnover analytics, as a table.
4-- Grain: month x department x site.
5--
6-- Employment spans come from each person's start date and termination date. A
7-- person counts in the headcount on a day from their start date up to and
8-- including their termination date (HiBob's termination date is the last day
9-- worked). Department and site are as of the month end, or as of the
10-- termination date for leavers, read from work history; people with no work
11-- history fall back to their current department and site.
12--
13-- The identity headcount_end = headcount_start + hires - leavers holds on every
14-- row, because each person-month sits in exactly one department. Moves between
15-- departments therefore show up as a change in headcount without a hire or
16-- leaver; there are no transfer columns.
17--
18-- The current month runs to CURRENT_DATE(), not to the month end.
19--
20-- Rehires: HiBob keeps one employee record, and start_date is the start of the
21-- current employment. A rehired person's earlier employment is not counted, and a
22-- termination date before the current start date is ignored.
23
24WITH employees AS (
25    SELECT
26        employee_id,
27        start_date,
28        IF(termination_date >= start_date, termination_date, NULL)  AS termination_date,
29        LOWER(termination_type)                                     AS termination_type,
30        department                                                  AS current_department,
31        site                                                        AS current_site
32    FROM {{staging.hibob.employee}}
33    WHERE start_date IS NOT NULL
34),
35
36months AS (
37    SELECT
38        month_start,
39        DATE_SUB(month_start, INTERVAL 1 DAY)                AS previous_month_end,
40        LEAST(LAST_DAY(month_start, MONTH), CURRENT_DATE())  AS month_end