Your question is Handling Missing Data. Start with the requirements and the three tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
How do you handle missing data in a dataset?
Using the provided observation, category, and source data, produce a cleaned result. Treat scores outside the range 0 through 100 as missing, retain every observation, and apply category-level then dataset-level fallbacks for missing scores. Replace missing or blank text values with the appropriate category or generic fallback.
entity_id, observed_on, and observation_idobservation_id, entity_id, observed_on, category_name, cleaned_score, score_was_missing, cleaned_status, source_name, and entity_sequenceUnclassified, Unknown, or 0 only when no more specific fallback exists| Column | Type | Description |
|---|---|---|
| observation_idPK | INT | Unique observation identifier |
| entity_id | VARCHAR(20) | Entity associated with the observation |
| category_id | INT | Optional category reference |
| observed_on | DATE | Date of the observation |
| score | NUMERIC(6,2) | Observed score, expected between 0 and 100 |
| status | VARCHAR(30) | Observed status |
| source_id | INT | Optional data source reference |
| Column | Type | Description |
|---|---|---|
| category_idPK | INT | Unique category identifier |
| category_name | VARCHAR(40) | Category label |
| default_status | VARCHAR(30) | Fallback status for the category |
| Column | Type | Description |
|---|---|---|
| source_idPK | INT | Unique source identifier |
| source_name | VARCHAR(40) | Source label |
| source_type | VARCHAR(30) | Source classification |