Cleaning Dataset with SQL | Dataford Interview Questions

Write a script that cleans a dataset by removing duplicates and filling in missing values.

Use the provided event and customer tables. Keep the latest record for each source event key, fill missing usage values with zero, and use the customer default plan when available.

Output

One row per unique source event key
Columns: source_event_key, customer_id, event_date, usage_units, plan_name
Replace unresolved missing plans with Unknown and order by source_event_key

Schema

octane_usage_events

Column	Type	Description
event_idPK	INT	Unique ingestion record identifier
source_event_key	VARCHAR(30)	Business key used to identify duplicate events
customer_id	INT	Customer associated with the event
event_date	DATE	Date on which usage occurred
usage_units	INT	Usage units recorded for the event
plan_name	VARCHAR(40)	Plan recorded in the event source
ingested_at	TIMESTAMP	Timestamp when the record was ingested

customer_profiles

Column	Type	Description
customer_idPK	INT	Unique customer identifier
default_plan	VARCHAR(40)	Fallback plan assigned to the customer

Your question is Cleaning Dataset with SQL. 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