Pandas data engineering interview problem. Difficulty: intermediate. Pattern: JSON. About 16 minutes. Part of the Pro drill bank.
Turn a list of nested order dicts into one row per line item with the order fields repeated. Treat this as a production helper: match the contracted return shape, including empty and duplicate inputs.
orders is a list of dicts. Each order has order_id, a nested customer dict (id, tier) and a list items of line items (sku, qty, price). Build a DataFrame with one row per line item and the order fields repeated on each row. Columns: order_id, customer_id, tier, sku, qty, price. An order with no items produces no rows. Sort by order_id, then sku, reset the index. Assign the DataFrame to result.
Input: orders = [ {"order_id": 1, "customer": {"id": "c1", "tier": "gold"}, "items": [{"sku": "B", "qty": 1, "price": 9.5}, {"sku": "A", "qty": 2, "price": 5.0}]}, {"order_id": 2, "customer": {"id": "c2", "tier": "basic"}, "items": []}, {"order_id": 3, "customer": {"id": "c1", "tier": "gold"}, "items": [{"sku": "C", "qty": 4, "price": 1.25}]}, ] Output: order_id | customer_id | tier | sku | qty | price 1 | c1 | gold | A | 2 | 5 1 | c1 | gold | B | 1 | 9.5 3 | c1 | gold | C | 4 | 1.25 Order 1 has two items, order 2 has none and disappears, order 3 has one: three rows, each repeating its order and customer fields.
Topics: lakebench, pandas, json, nested, line items.
More interview problems · All interview problems · Learn data engineering
Interview-style drill: Turn a list of nested order dicts into one row per line item with the order fields repeated.
`orders` is a list of dicts. Each order has `order_id`, a nested `customer` dict (`id`, `tier`) and a list `items` of line items (`sku`, `qty`, `price`). Build a DataFrame with one row per line item and the order fields repeated on each row. Columns: `order_id`, `customer_id`, `tier`, `sku`, `qty`, `price`. An order with no items produces no rows. Sort by `order_id`, then `sku`, reset the index. Assign the DataFrame to `result`.