Take-Home SQL Assessment | Dataford Interview Questions

How would you approach a take-home SQL assessment for Oscar Health, and what would you do to validate your results?

Assume the assessment includes assessment_orders and assessment_customers. Write a query that audits every order by checking its customer reference and amount, while preserving orders that fail validation.

Output

One row per order with order_id, customer_id, customer_name, amount, and validation_status
Include all orders, including missing or unmatched customer references
Use statuses valid, missing_customer_id, invalid_customer_reference, missing_amount, or non_positive_amount
Order by order_id ascending

Schema

assessment_orders

Column	Type	Description
order_idPK	INT	Unique order identifier
customer_id	INT	Referenced customer identifier
amount	DECIMAL(10,2)	Order amount

assessment_customers

Column	Type	Description
customer_idPK	INT	Unique customer identifier
customer_name	VARCHAR(100)	Customer display name

Your question is Take-Home SQL Assessment. 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