Clean a Messy CSV | Dataford Interview Questions

Describe a time you were given a messy CSV file and had to clean it and generate insights from the data.

Write a PostgreSQL query that cleans the provided service-visit data, excludes records with invalid dates, costs, mileage, statuses, or unmatched model codes, and summarizes valid completed visits.

Output

One row per vehicle model and service month.
Return model_name, service_month, completed_visits, total_cost, and average_cost.
Include only valid completed visits and order by service_month ascending, then model_name ascending.

Schema

raw_service_csv

Column	Type	Description
visit_idPK	INT	Unique service visit identifier
model_code	VARCHAR(20)	Vehicle model code as imported from the CSV
visit_date_text	VARCHAR(30)	Service date in inconsistent text format
odometer_text	VARCHAR(30)	Mileage value containing possible commas or invalid text
cost_text	VARCHAR(30)	Service cost containing possible currency symbols or invalid text
status_text	VARCHAR(30)	Visit status with inconsistent casing or whitespace

model_reference

Column	Type	Description
model_codePK	VARCHAR(20)	Canonical vehicle model code
model_name	VARCHAR(80)	Readable vehicle model name
vehicle_segment	VARCHAR(40)	Vehicle market segment

Your question is Clean a Messy CSV. 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