SQL and Data Manipulation Assessment

MediumSQL · PostgreSQL

Problem

Describe how you would handle a multi-part assessment that includes data manipulation and an ML component, and walk through your approach to the SQL and data preparation steps.

For the SQL portion, prepare one modeling row per candidate using the latest completed assessment attempt and its answer records. Exclude candidates without a completed attempt.

Output

Return candidate_id, region, assessment_date, assessment_score, sql_accuracy, data_cleaning, missing_answer_count, and outcome_label.
Use the completed attempt date and aggregate answer values by question code; preserve candidates with no answers.
Order by candidate_id ascending.

Schema

candidate_profiles

Column	Type	Description
candidate_idPK	INT	Candidate identifier
full_name	VARCHAR(100)	Candidate name
region	VARCHAR(50)	Candidate region

assessment_attempts

Column	Type	Description
attempt_idPK	INT	Assessment attempt identifier
candidate_id	INT	Candidate associated with the attempt
completed_at	TIMESTAMP	Attempt completion timestamp
status	VARCHAR(20)	Assessment status
assessment_score	DECIMAL(5,2)	Overall assessment score
outcome_label	INT	Supervised learning outcome label

assessment_answers

Column	Type	Description
answer_idPK	INT	Answer record identifier
attempt_id	INT	Assessment attempt identifier
question_code	VARCHAR(40)	Feature question category
numeric_value	DECIMAL(5,2)	Normalized numeric answer value

Tablescandidate_profilesassessment_attemptsassessment_answers