Section C · Coding

Coding Fundamentals

Python for People data, the SQL that HR analytics actually runs, the DSA basics you should be cold on, and the testing posture that keeps payroll-affecting code honest.

Python for HR data — the working language

Expect to write Python on a shared editor in at least one round. The vocabulary the interviewer expects:

  • pandas — worker records, comp data, attrition computation, EIB CSV shaping
  • pydantic — typed schemas for proposed actions, tool inputs, audit entries
  • httpx / requests — Workday SOAP/REST, Ashby REST
  • asyncio / async httpx — concurrent fetches across systems
  • SQLAlchemy / psycopg — for the audit and proposals store
  • pytest — non-negotiable for any HR-touching code

pandas — the daily driver

Be fluent in:

import pandas as pd

# Read a Workday RaaS extract as JSON
workers = pd.read_json("workers_raas.json")

# Filter to active employees in EMEA hired in last 90 days
recent_emea = workers[
    (workers["status"] == "Active")
    & (workers["region"] == "EMEA")
    & (workers["hire_date"] > pd.Timestamp.today() - pd.Timedelta(days=90))
]

# Group by country, count headcount
by_country = (
    recent_emea
    .groupby("country", as_index=False)
    .agg(headcount=("worker_id", "count"),
         avg_fte=("fte", "mean"))
    .sort_values("headcount", ascending=False)
)

# Merge with Ashby offer data on candidate_id
offers = pd.read_json("ashby_offers.json")
merged = workers.merge(offers, left_on="ashby_candidate_id",
                       right_on="candidate_id", how="left",
                       validate="one_to_one")  # explicit cardinality
The senior tell

Always pass validate="one_to_one" / "one_to_many" to merge when the cardinality is known. It crashes loudly if your assumption is wrong — which is what you want when you're joining people data.

Joining cross-system data — the HR-specific traps

HR data joins are stickier than they look. The traps:

  • Identity instability — emails change, Slack handles change, names change. Join on Worker ID where possible.
  • Effective-dating asymmetry — your Workday extract is "as of now"; your Ashby snapshot may be "as of event time." A naive join can mismatch.
  • Multi-record workers — Workers with multiple positions, multiple historical hires (rehires). Naive joins explode rows.
  • Termed workers — Active vs all-workers extracts differ. Decide which population you're operating on, every time.
  • Time zones — hire dates and termination dates are typically dates (no time) in Workday. Don't accidentally convert to a timezone-aware datetime that shifts by a day.
def join_workers_to_offers(workers: pd.DataFrame,
                           offers: pd.DataFrame) -> pd.DataFrame:
    """Join Workday workers to Ashby offers on the canonical link.

    Defensive: explicit cardinality, NaN handling, dropped column conflicts.
    """
    # Ensure types and identity columns are clean
    workers = workers.copy()
    workers["worker_id"] = workers["worker_id"].astype("string").str.strip()
    workers["ashby_candidate_id"] = workers["ashby_candidate_id"].astype("string")

    offers = offers.copy()
    offers["candidate_id"] = offers["candidate_id"].astype("string").str.strip()

    # If an offer accepts but the worker isn't yet hired, candidate_id may not match
    merged = workers.merge(
        offers,
        left_on="ashby_candidate_id",
        right_on="candidate_id",
        how="left",
        suffixes=("_workday", "_ashby"),
        validate="one_to_one",  # one worker, one accepted offer
        indicator=True,
    )
    # Sanity: workers without an offer match
    missing_offer = merged[merged["_merge"] == "left_only"]
    if len(missing_offer) > 0:
        logger.warning(f"{len(missing_offer)} workers without matching Ashby offer")
    return merged.drop(columns=["_merge"])

Dates & effective-dating

The most-debugged code in HR pipelines is date code. Two principles:

  1. Use date objects (not datetime) for things that are dates. Workday hire date is a date. A 23:00 hire on 2026-03-01 in UTC is not "shifted by a day" in EMEA — it's just 2026-03-01 everywhere.
  2. "Effective as of" reads need an explicit date parameter — never default silently to today.
from datetime import date, timedelta

def tenure_in_months(hire_date: date, as_of: date) -> int:
    """Whole months of tenure, from hire_date to as_of."""
    if as_of < hire_date:
        return 0
    months = (as_of.year - hire_date.year) * 12 + (as_of.month - hire_date.month)
    if as_of.day < hire_date.day:
        months -= 1
    return max(0, months)

def next_payroll_cutoff(country: str, after: date) -> date:
    """Find the next payroll cutoff strictly after `after` for the country."""
    # In production: query the payroll-calendar service.
    # Pattern matters more than the calendar specifics.
    cutoffs = payroll_calendar.list_cutoffs(country)
    return next(c for c in cutoffs if c > after)

Async API patterns

Fetching from Workday + Ashby + Jira in a single agent run is the common case. Run reads concurrently:

import asyncio
import httpx

async def gather_worker_context(worker_id: str) -> dict:
    """Fetch worker context from multiple systems concurrently."""
    async with httpx.AsyncClient(timeout=30) as client:
        workday_task = workday_get_worker(client, worker_id)
        ashby_task = ashby_get_application_by_worker(client, worker_id)
        jira_task = jira_open_tickets_by_reporter(client, worker_id)
        okta_task = okta_get_user_groups(client, worker_id)
        results = await asyncio.gather(
            workday_task, ashby_task, jira_task, okta_task,
            return_exceptions=True,
        )
    keys = ["workday", "ashby", "jira", "okta"]
    out = {}
    for key, res in zip(keys, results):
        if isinstance(res, Exception):
            out[key] = {"error": str(res)}
        else:
            out[key] = res
    return out

Note the return_exceptions=True — one slow system shouldn't tank the whole context-gather. The agent reasons about which results are present and which are missing.

DSA basics for this role

Expect at least one DSA-style problem. The HR-adjacent ones cluster in:

  • Hash maps — deduplication, counting, "have I seen this worker before"
  • Sorting + sweep — interval problems (date ranges, payroll periods, leave overlaps)
  • Two-pointer / sliding window — running aggregates over a date range
  • Set operations — "who's in set A and not B" (workers in HRIS vs Ashby)
  • Date math — tenure, cohorting, period boundaries

See 11-coding-problems for worked examples.

Complexity literacy

State Big-O clearly. "This is O(n log n) because of the sort; could be O(n) with a hash map if we don't need ordering." Don't say "fast" or "slow" — say the complexity.

SQL for HR analytics — the dialect you'll write

People analytics is mostly SQL on a warehouse (BigQuery, Snowflake, Redshift, or in some shops Workday Prism). Three queries every candidate should be able to write from scratch.

Cohort retention

"Of workers hired in Q1 2024, what fraction were still active at 6 months, 12 months, 18 months?"

WITH cohort AS (
    SELECT
        worker_id,
        hire_date,
        termination_date
    FROM workers
    WHERE hire_date BETWEEN DATE '2024-01-01' AND DATE '2024-03-31'
),
retention AS (
    SELECT
        worker_id,
        CASE WHEN termination_date IS NULL
             OR termination_date > hire_date + INTERVAL '6 months'
             THEN 1 ELSE 0 END AS retained_6m,
        CASE WHEN termination_date IS NULL
             OR termination_date > hire_date + INTERVAL '12 months'
             THEN 1 ELSE 0 END AS retained_12m,
        CASE WHEN termination_date IS NULL
             OR termination_date > hire_date + INTERVAL '18 months'
             THEN 1 ELSE 0 END AS retained_18m
    FROM cohort
)
SELECT
    COUNT(*) AS cohort_size,
    AVG(retained_6m)::float AS pct_6m,
    AVG(retained_12m)::float AS pct_12m,
    AVG(retained_18m)::float AS pct_18m
FROM retention;

Headcount on a date

"What was headcount in EMEA on 2025-12-31?"

SELECT
    region,
    COUNT(*) AS headcount
FROM workers
WHERE hire_date <= DATE '2025-12-31'
  AND (termination_date IS NULL OR termination_date > DATE '2025-12-31')
  AND region = 'EMEA'
GROUP BY region;
The classic mistake

Treating termination_date as inclusive vs. exclusive of the last day of employment varies by org. Always confirm convention; usually "last day worked" means they were on the payroll through that date, so termination_date >= as_of would still count them. Mismatched conventions cost real headcount.

Attrition rate

"What was the annualized attrition rate for engineering in 2025?"

WITH start_pop AS (
    SELECT COUNT(*) AS n
    FROM workers
    WHERE function = 'Engineering'
      AND hire_date <= DATE '2025-01-01'
      AND (termination_date IS NULL OR termination_date > DATE '2025-01-01')
),
end_pop AS (
    SELECT COUNT(*) AS n
    FROM workers
    WHERE function = 'Engineering'
      AND hire_date <= DATE '2025-12-31'
      AND (termination_date IS NULL OR termination_date > DATE '2025-12-31')
),
terms AS (
    SELECT COUNT(*) AS n
    FROM workers
    WHERE function = 'Engineering'
      AND termination_date BETWEEN DATE '2025-01-01' AND DATE '2025-12-31'
)
SELECT
    terms.n AS terminations,
    start_pop.n AS start_headcount,
    end_pop.n AS end_headcount,
    (terms.n::float / ((start_pop.n + end_pop.n) / 2.0)) AS attrition_rate
FROM start_pop, end_pop, terms;

Note: there are several attrition conventions (avg of start+end, start only, denominator includes new hires). State which you're using.

Testing data code

The minimum bar for any HR-touching Python:

  • Unit tests with fixtures — small, named worker fixtures covering active, terminated, rehired, multi-position, EOR
  • Edge-case tests as named tests — test_rehire_continuous_service_date, test_termination_on_payroll_cutoff
  • Snapshot tests for EIB output shape — exact column order, exact header strings
  • Schema validation tests — every proposed-action payload validates against its Pydantic model
  • Property-based tests for idempotency — running the same logical action N times produces 1 committed effect
def test_idempotent_hire_proposal(db):
    payload = {"applicant_id": "ASH-1", "hire_date": "2026-06-01",
               "position_id": "P-99"}
    p1 = propose_hire(payload, db)
    p2 = propose_hire(payload, db)  # retry
    assert p1["proposal_id"] == p2["proposal_id"]
    assert db.count_proposals_by_idempotency_key(p1["idempotency_key"]) == 1

def test_termination_on_payroll_lock_halts(db):
    payload = {"worker_id": "W-12345", "effective_date": str(date.today()),
               "country": "US", "reason_code": "VOLUNTARY"}
    with mocked_payroll_lock(country="US", locked_until=date.today() + timedelta(days=1)):
        result = propose_termination(payload, db)
    assert result["status"] == "halted"
    assert "payroll" in result["rationale"].lower()

Interview framings worth memorizing

  • "For pandas joins on people data I always pass validate= to assert cardinality. Silent fan-out is the most common data bug in HR."
  • "Anything that reads worker state takes an effective_date parameter. Workday is effective-dated; treating it as 'now' is the path to subtle bugs."
  • "My async pattern is gather-with-exceptions — one slow system shouldn't block agent reasoning. Missing context is a state the agent must reason about."
  • "Three SQL queries I'd expect on the loop are cohort retention, headcount on a date, and attrition rate. The trick in all three is the convention you adopt for termination_date inclusivity."
  • "Idempotency keys are tested with property-based tests — same logical action, any number of retries, exactly one committed effect."