Interview JSON array expansion

Return event_id, user_id, device, and sku for checkout payload item arrays.

Start by naming the output grain, then build CTEs that can be inspected one at a time.

Sample input

checkout payloadsExpanding $.items changes grain from event to event-item.

event_id	user_id	payload
2	1	{"device":"ios","items":["SKU-A","SKU-B"]}
4	2	{"device":"web","items":["SKU-C"]}

Expected output

Expected outputOne row per checkout item.

event_id	user_id	device	sku
2	1	ios	SKU-A
2	1	ios	SKU-B
4	2	web	SKU-C

Constraints

State the output contract first, use readable CTE layers, return the exact columns requested by the prompt, and use deterministic ordering.

Expected skills

Interview clarification, SQL structure, edge-case handling, and final-answer narration.

SQL

AI evaluation

Run the SQL query to inspect preview rows.