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…