Coding Fundamentals

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

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;

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."

✓ Check yourself

Answer from memory before opening each one.

1. Your merge of Workday workers to Ashby offers returns 4,212 rows from 4,000 workers, and nothing raised an error. What happened and what one change makes it fail loudly next time?

   Show answer

   Some workers matched more than one offer or key, such as a rehire with two applications, so the join fanned out. Pass validate="one_to_one" so pandas raises when the cardinality assumption is wrong, instead of silently inflating headcount. See Joining cross-system data.

2. A hire date arrives as a timezone-aware timestamp at 23:00 UTC on 1 March and a developer converts it to local time for a Singapore employee. What is the bug, and what type should the field be?

   Show answer

   The conversion shifts the date to 2 March, but a hire date is a calendar date with no time, so it is the same everywhere. Store and pass it as a date object and never put it through a timezone conversion. See Dates & effective-dating.

3. You fetch worker context from four systems at once, and Jira times out. What does gather with exceptions let the agent do that a plain gather does not, and what must the agent then do?

   Show answer

   It returns the other three results together with the error for Jira instead of failing the whole call. The agent must treat the missing context as a state to reason about, for example by saying what it could not check, rather than proceeding as if the data were absent or complete. See Async API patterns.

4. Two analysts report 2025 Engineering attrition at 11% and 13% from the same data. Name three reasons they could both be right, and say what you do.

   Show answer

   They may use different denominators (average of start and end, start only, or including new hires), and different termination-date conventions on the last day. State the definition in the output, agree one convention with the People team, and label the other as an alternative. See Attrition rate.

5. Your headcount query on 31 December shows 3 fewer people than payroll. Where do you look first?

   Show answer

   At whether termination_date means the last day worked or the day after, since the query compares with a strict greater-than. Workers whose last day is the 31st may be counted differently. Confirm the convention with the People team and apply it in every query. See Headcount on a date.

6. What would a test for idempotent proposals check, and why is a test with a single retry not enough for the guarantee you need?

   Show answer

   That proposing the same payload any number of times produces one stored proposal and one committed effect. A single retry covers one path, while property-based tests generate varied payloads and repeat counts, which is how you find key collisions or keys that change on retry. See Testing data code.

Takeaway

• Use pandas joins with explicit cardinality, because silent fan-out is the most common data bug in people data.
• Join on Worker ID, decide which population you are operating on, and handle rehires, multiple positions and termed workers deliberately.
• Hire and termination dates are dates, not timestamps, and any read of worker state takes an explicit as-of date.
• Gather concurrent reads with exceptions returned, and make the agent reason about what is missing.
• Cohort retention, headcount on a date and attrition all turn on stated conventions for termination date and denominator.
• Name edge cases as tests and prove idempotency with property-based tests: same action, many retries, one committed effect.

With the basics in hand, you can work the problems the loop is likely to pose. → Coding Problems Worked

Further reading

• pandas.DataFrame.merge — pandas docs. The validate and indicator options that make joins fail loudly.
• datetime — Python documentation. The date type and the arithmetic behind tenure and cutoffs.
• Coroutines and tasks — Python documentation. gather and return_exceptions for concurrent reads.
• Pydantic — Pydantic docs. Typed schemas and validation for proposed actions and audit entries.
• pytest — pytest docs. Fixtures and parametrisation for named edge-case tests.
• Hypothesis — Hypothesis docs. Property-based testing for idempotency guarantees.