HR roster normalization
Pemberton Freight (fictional)
Rebuild a clean staff table from a printed roster report with title rows, department blocks and subtotals, apply the leavers list and report headcount by department.
- Level
- intermediate
- Time
- ~55 min
- Deliverables
- 2
- Rubric
- v1
Access
Pro. This brief is public. The data pack, workspace, hints, grading and solution explanations need Pro, Lifetime Learning or a seat in an organization. See plans.
Each deliverable's solution explanation unlocks when it passes. You can also unlock it early once you have used every hint for that deliverable (this needs an account so the help is recorded, and the deliverable then counts as completed with help).
Business goal
Give HR a clean staff table as of 31 March 2026 and a headcount by department that stays correct when the roster report's layout shifts.
Pemberton Freight's HR system can only print its roster as a report: a title block, then one block per department with a department line, the people in it and a subtotal. The roster also lags behind reality: people who have handed in their notice stay Active until payroll closes them, so HR keeps a separate leavers list. The HR lead wants one row per person with their real status as of 31 March 2026, and a headcount per department.
Graded deliverables
1. Staff
65 pointsOne clean row per person in the roster with department, cost centre and real status.
- Columns: EmpID, FirstName, LastName, Department, CostCentre, StartDate, FTE, Status
- One row per person; no title, department, subtotal, empty or footer rows
- Status is Active, On leave or Left as of 31 March 2026
- Departments without a cost centre show UNMAPPED
Expected output schema for Staff Column Type EmpID text FirstName text LastName text Department text CostCentre text StartDate date FTE decimal Status text - Grain:
- One row per person listed in the roster report.
- Order:
- Not graded.
- Nulls:
- No nulls. Unknown cost centres are "UNMAPPED".
2. Department Headcount
35 pointsCurrent headcount and FTE per department, built on your Staff query.
Builds on: Staff
- Columns: Department, CostCentre, Headcount, FTE, OnLeave
- Leavers excluded; people on leave counted
- Sorted by Department
Expected output schema for Department Headcount Column Type Department text CostCentre text Headcount whole number FTE decimal OnLeave whole number - Grain:
- One row per department with at least one current employee.
- Order:
- Graded: Department ascending.
- Nulls:
- No nulls.