Level 6 · Boss: revenue by channel, by hand

Nested data

Real exports are rarely flat. Here each order is a dictionary, and one of its values, items, is itself a list of (quantity, unit_price) pairs. That’s one order with many lines, the same relationship that orders and order_items have in the Harbor & Pine database.

To work with nested data, peel one layer at a time:

order = orders[0]
order["items"]            # [(2, 49.99), (1, 34.0)]
order["items"][0]         # (2, 49.99)
qty, price = order["items"][0]   # unpack the pair

A pair in round brackets is a tuple: like a list, but fixed once created. Tuples are common for small groups of values that belong together.

Step 1: filter

You did this in the previous level. A comprehension with a condition keeps the IDs of completed orders:

[o["order_id"] for o in orders if o["status"] == "completed"]

Cancelled, refunded and pending orders aren’t revenue. Deciding which statuses count is a business decision; always state it in your answer so nobody compares your number with one that includes refunds.

Step 2: total each order

The total of one order is the sum of its lines. sum() accepts a generator expression, a comprehension without the square brackets, so you don’t need to build a temporary list:

sum(qty * price for qty, price in order["items"])

Wrap that in a dictionary comprehension and you get a lookup from order ID to total, for every completed order at once.

Step 3: group and sum

Revenue by channel is the counting pattern from earlier, except you add the order total instead of 1:

revenue = {}
for o in orders:
    if o["status"] != "completed":
        continue
    total = sum(qty * price for qty, price in o["items"])
    revenue[o["channel"]] = revenue.get(o["channel"], 0) + total

continue skips straight to the next order. It’s a tidy way to throw out rows early instead of nesting the rest of the body inside an if.

Round at the very end, once per channel. Then check: the channel totals should add up to the sum of the order totals from step 2. Cross-checking two numbers you computed in different ways is one of the cheapest and most effective habits an analyst can have.

What you just did

Filter rows, compute a value per row, group by a category and sum. That’s the shape of a huge share of business reporting. In chapter 6 you’ll do the same thing on 30,000 order lines with pandas in two lines of code, and you’ll know exactly what those two lines are doing.

Common mistakes

• Summing the unit prices without multiplying by quantity.
• Forgetting to filter out cancelled and refunded orders in step 3.
• Rounding each order before adding it to the channel, which can shift the total by a cent.