Nested ecommerce JSON flattening
Fernhill Ceramics (fictional)
Flatten paged order exports from a shop API (customers, line items, discount lists, optional shipping) into a line table and an order summary.
- Level
- advanced
- Time
- ~70 min
- Deliverables
- 2
- Rubric
- v1
Access
Pro. This brief is public. The data pack, workspace, hints, grading and solution explanations need Pro, Lifetime Learning or a seat in an organization. See plans.
Each deliverable's solution explanation unlocks when it passes. You can also unlock it early once you have used every hint for that deliverable (this needs an account so the help is recorded, and the deliverable then counts as completed with help).
Business goal
Give the owner a line-level sales table with net amounts after discounts and an order summary that matches what customers actually paid, for every page the shop API returns.
Fernhill sells handmade ceramics online. The shop platform exports orders through an API that returns pages of JSON; a small script saves each page as a file in one folder. Each order holds the customer (with an address only if one was given), a list of lines, each line's list of discount codes, and a shipping object that is null for click-and-collect. Refunded orders must not count.
Graded deliverables
1. Order Lines
55 pointsOne row per order line with customer country, product collection, discount and net amount.
- Columns: OrderID, OrderDate, CustomerID, Country, SKU, Collection, Qty, UnitPrice, Discount, NetAmount
- Every page file included; refunded orders excluded
- Discount 0 when there are no discounts
- Country null only when the customer has no address
Expected output schema for Order Lines Column Type OrderID text OrderDate date CustomerID text Country text or null SKU text Collection text Qty whole number UnitPrice decimal Discount decimal NetAmount decimal - Grain:
- One row per line of a paid or pending order.
- Order:
- Not graded.
- Nulls:
- Country is null when the customer has no address. Nothing else is null.
2. Order Summary
45 pointsOne row per non-refunded order with line count, net total, shipping and order total — including orders with no lines.
Builds on: Order Lines
- Columns: OrderID, OrderDate, Country, Lines, NetTotal, ShippingFee, OrderTotal
- Every non-refunded order, including ones without lines
- ShippingFee 0 for click-and-collect
- Sorted by OrderID
Expected output schema for Order Summary Column Type OrderID text OrderDate date Country text or null Lines whole number NetTotal decimal ShippingFee decimal OrderTotal decimal - Grain:
- One row per paid or pending order.
- Order:
- Graded: OrderID ascending.
- Nulls:
- Country can be null; Lines, NetTotal and ShippingFee are 0 rather than null.