The Transfer Request

A medium Data Modeling interview practice problem on DataDriven. Design the schema on the canvas: tables, keys and relationships, then get feedback on it.

Domain
Data Modeling
Difficulty
medium
Seniority
L4
Asked in
Amazon

Problem

Employees can apply for internal transfers between teams. HR reviews each application and either approves or denies it. The reporting team needs to know: how many transfer requests were approved vs denied per quarter, by originating team and destination team. Design the data model, then write the query.

Apply, wait, get approved or denied. Track all of it.

Worked solution and explanation

What this really is

This is a fact table with two role-playing references to the same dimension, presented as an HR workflow. The real question is the grain: one row per submitted request. That row carries its own copy of where the employee came from and where they wanted to go. Anyone can draw employees, teams and a requests table. The trap is reading the origin team off employees.current_team_id at query time. The moment a transfer is approved, that column changes, so every approved request reports the destination as its origin. Your quarterly approvals collapse into rows where from_team equals to_team.

`transfer_requests` is the fact at one row per submission, with two FKs each into `teams` and `employees`
  • employees: employee_id BIGINT PK, full_name TEXT, current_team_id BIGINT FK, role TEXT, hired_at DATE
  • teams: team_id BIGINT PK, name TEXT, org_unit TEXT
  • transfer_requests: request_id BIGINT PK, applicant_id BIGINT FK, from_team_id BIGINT FK, to_team_id BIGINT FK, reviewer_id BIGINT FK, submitted_at TIMESTAMP, decided_at TIMESTAMP, status TEXT
  • employees.current_team_id references teams.team_id
  • transfer_requests.applicant_id references employees.employee_id
  • transfer_requests.reviewer_id references employees.employee_id
  • transfer_requests.from_team_id references teams.team_id
  • transfer_requests.to_team_id references teams.team_id

Building it

Step 1: Declare the grain before any column

Make it one row per submission, keyed by a surrogate request_id. A denied request that is resubmitted gets a new row. Otherwise the denial vanishes and the 'denied' count for that quarter silently drops.

Step 2: Snapshot both teams on the request

from_team_id and to_team_id are written when the request is submitted and never change. They are facts about the request, not attributes of the employee, so they belong on the request row and not behind a join through employees.

Step 3: Point applicant_id and reviewer_id at employees

A reviewer is an employee playing a role, so there is no reviewers table. reviewer_id and decided_at stay NULL while the status is 'pending'.

Step 4: Count by status with FILTER

Join teams twice under the aliases ft and tt, bucket by DATE_TRUNC('quarter', submitted_at), and let COUNT(*) FILTER (WHERE ...) split approved from denied in a single pass.

Approved vs denied per quarter, by team pair
SELECT
    DATE_TRUNC('quarter', r.submitted_at) AS quarter,
    ft.name AS from_team,
    tt.name AS to_team,
    COUNT(*) FILTER (WHERE r.status = 'approved') AS approved,
    COUNT(*) FILTER (WHERE r.status = 'denied') AS denied
FROM transfer_requests r
JOIN teams ft ON ft.team_id = r.from_team_id
JOIN teams tt ON tt.team_id = r.to_team_id
GROUP BY 1, 2, 3

Pending-only team pairs still appear, with 0 in both columns

Origin derived at query time
Join transfer_requests to employees and read current_team_id. It looks right until the first approval. After that the applicant's team is the destination, and history rewrites itself every time someone moves.
Origin snapshotted on the request
from_team_id is captured at submission. Last year's report returns the same numbers today, and the query needs no join to employees at all.

Common follow-up questions