Level 3 · Revenue by category, completed orders only

Three tables, one question

“Revenue by category for completed orders” touches three tables:

• order_items has the revenue ingredients (quantity, price paid, discount).
• orders has the status.
• products has the category.

There’s no single merge that does it all, but merges chain naturally, one key at a time:

items.merge(orders, on="order_id").merge(products, on="product_id")

Compute before you merge

Calculate line revenue on order_items before merging with products. After the merge there are two unit_price columns, and it’s easy to pick the list price by accident. Today’s list price is wrong for revenue on lines sold before the March 2025 price rise.

items = order_items.assign(
    line_revenue=order_items["quantity"] * order_items["unit_price"]
    * (1 - order_items["discount_pct"] / 100)
)

Merge only what you need

Bring in just the key and the columns you need from each table. It keeps the result narrow and avoids name clashes:

completed_ids = orders.loc[orders["status"] == "completed", ["order_id"]]
merged = (
    items
    .merge(completed_ids, on="order_id", how="inner", validate="many_to_one")
    .merge(products[["product_id", "category"]], on="product_id", how="inner", validate="many_to_one")
)

Notice the first merge does the filtering. An inner join with a list of completed order IDs keeps only lines from completed orders. That’s a common and tidy pattern: filtering by joining. You could do the same with items[items["order_id"].isin(completed_ids["order_id"])]; both are fine.

Each merge is many-to-one (many lines per order, many lines per product), and validate checks it.

Then group

(
    merged.groupby("category", as_index=False)
    .agg(revenue=("line_revenue", "sum"))
    .round({"revenue": 2})
    .sort_values("revenue", ascending=False)
    .reset_index(drop=True)
)

Furniture comes out on top, with Outdoor close behind, even though every category has the same number of products: high prices make up for fewer units sold. A natural follow-up is revenue per unit sold, or the share of total, to see which categories punch above their weight.

Sanity check against the total

The category revenues should add up to the total revenue of completed orders:

result["revenue"].sum()
items[items["order_id"].isin(completed_ids["order_id"])]["line_revenue"].sum()

If the first is bigger, a merge duplicated rows. If it’s smaller, a merge dropped some. This check takes one line and is the best defence against join mistakes.

Common mistakes

• Using products.unit_price (list price) instead of the line’s price paid.
• Filtering on status after merging all of orders, then forgetting to filter at all.
• Merging the full orders table, which brings in customer_id, channel and more columns you don’t need.

If you know SQL

This is FROM order_items JOIN orders USING (order_id) JOIN products USING (product_id) WHERE status = 'completed' GROUP BY category.