Drill 10 of 12 in HR questions
0

Interview Question Bank · generate_series · month-end dates · range join

10. Headcount at the end of every month

HardHRQuestion #190

Concept: generate_series · month-end dates · range joinshow

Headcount is a snapshot, not an event count: on a given day it's everyone hired by then and not yet gone. Generate the snapshot days with generate_series, turn each month's first day into its last with + INTERVAL '1 month' - INTERVAL '1 day', and join every employee whose employment covers that day. A NULL end date means still employed and needs its own IS NULL branch, and an inclusive end date (the last day worked) needs >=.

Example (different data):

SELECT d.day::date, COUNT(r.id) AS rooms_occupied
FROM generate_series(DATE '2025-03-01', DATE '2025-03-07', INTERVAL '1 day') AS d(day)
LEFT JOIN room_stays r ON r.check_in <= d.day
  AND (r.check_out IS NULL OR r.check_out > d.day)
GROUP BY d.day
ORDER BY d.day;

Your task

Quillmoor's finance team needs 2024 headcount on the last day of each month for the annual report. termination_date is an employee's last day, so people still count on it.

For each month of 2024, return month_end (the month's last day, as a date) and headcount (employees with hire_date on or before month_end and termination_date NULL or on or after month_end). Sort by month_end.

Starting Postgres in your browser…

Checking your access…