Data Cleaning and Preprocessing | Dataford Interview Questions

How do you approach data cleaning and preprocessing in your analysis?

Write a PostgreSQL query that standardizes the available lead fields, excludes unusable records, keeps the latest valid record for each normalized email, and adds campaign names when available. Rows with a NULL or invalid signup_date must be ignored.

Output

One row per retained lead with email, source, campaign_code, campaign_name, and signup_date.
Use Unattributed when no campaign matches.
Order by signup_date ascending, then email ascending.

Schema

raw_leads

Column	Type	Description
lead_idPK	INTEGER	Unique raw lead identifier
email	VARCHAR(150)	Raw lead email address
source	VARCHAR(80)	Raw acquisition source
campaign_code	VARCHAR(40)	Raw campaign identifier
signup_date	VARCHAR(10)	Raw signup date text in expected YYYY-MM-DD format

campaigns

Column	Type	Description
campaign_codePK	VARCHAR(40)	Campaign identifier
campaign_name	VARCHAR(150)	Readable campaign name

Your question is Data Cleaning and Preprocessing. 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