Welcome to the SQL screen.
The question is on your right: Impute Sparse Assessment Scores. Read through the requirements and the three tables first.
Run and submit your code as often as you need. You also have five interviewer messages this session - want to talk through your approach, or are you ready to start coding?
You are given sparse assessment score data from Mercor assessments, where many candidate-question scores are missing and some recorded scores fall outside the valid 0-100 range. Write a PostgreSQL query that returns one row per candidate and assessment with a cleaned average score. Treat negative scores and scores above 100 as invalid, normalize valid scores to the 0-1 range, and impute missing or invalid scores using the average cleaned score for that question within the same assessment. If a question has no valid scores at all, fall back to the assessment-level cleaned average. Return only candidate-assessment pairs that have at least one assignment.
| Column | Type | Description |
|---|---|---|
| assignment_idPK | INT | Unique assignment row |
| candidate_id | INT | Candidate taking the assessment |
| assessment_id | INT | Assessment identifier |
| question_id | INT | Question identifier within the assessment |
| assigned_at | DATE | Date the question was assigned |
| Column | Type | Description |
|---|---|---|
| score_idPK | INT | Unique score row |
| assignment_id | INT | Assignment being scored |
| raw_score | DECIMAL(5,2) | Raw score, may be NULL or outside the valid range |
| Column | Type | Description |
|---|---|---|
| assessment_idPK | INT | Assessment identifier |
| assessment_name | VARCHAR(100) | Assessment name |