Transform Sample Data | Dataford Interview Questions

From this sample data, perform the necessary transformation to achieve a desirable end state.

Use raw_kyc_events and source_systems. Return the latest valid event for each subject, normalize its status, classify its risk score, and retain subjects whose source reference has no matching source record.

Output

One row per non-null subject reference.
Columns: subject_ref, source_name, observed_at, normalized_status, risk_tier, and risk_score.
Use Unknown source for unmatched or null sources and unclassified for null or unrecognized statuses.
Order by subject_ref ascending.

Schema

raw_kyc_events

Column	Type	Description
event_idPK	INT	Unique event identifier
subject_ref	VARCHAR(30)	External subject reference
source_id	INT	Reference to the originating source system
observed_at	TIMESTAMP	Time when the event was observed
raw_status	VARCHAR(30)	Unnormalized source status
risk_score	NUMERIC(5,2)	Numeric risk score

source_systems

Column	Type	Description
source_idPK	INT	Unique source system identifier
source_name	VARCHAR(80)	Display name of the source system

Your question is Transform Sample Data. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem