Transforming Data with SQL | Dataford Interview Questions

Process this data and make these transformations.

Use entities, raw_records, and record_updates. Exclude void records, retain the latest record for each entity and month, apply the latest available correction, normalize negative or missing values to zero, and calculate month-over-month and cumulative values.

Output

One row per entity and retained month.
Columns: entity_name, region, record_month, normalized_value, previous_value, change_amount, cumulative_value.
Order by entity_name, then record_month.

Schema

entities

Column	Type	Description
entity_idPK	INT	Unique entity identifier
entity_name	VARCHAR(100)	Entity display name
region	VARCHAR(50)	Entity region

raw_records

Column	Type	Description
record_idPK	INT	Unique raw record identifier
entity_id	INT	Referenced entity
recorded_at	DATE	Date the record was captured
raw_value	NUMERIC(12,2)	Original numeric value
status	VARCHAR(20)	Record status

record_updates

Column	Type	Description
update_idPK	INT	Unique update identifier
record_id	INT	Referenced raw record
updated_at	TIMESTAMP	Update timestamp
corrected_value	NUMERIC(12,2)	Replacement value, when supplied

Your question is Transforming Data with SQL. Start with the requirements and the three 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