Level 2 · Holiday season review in pandas

Same question, same definitions

This is the same review as the SQL level of this case, built in pandas. Keep the definitions identical or the numbers will not match: completed orders, lines with quantity > 0, revenue quantity * unit_price * (1 - discount_pct / 100), shipping excluded. When a SQL and a pandas version of the same report disagree, nine times out of ten one of them filtered differently.

Build the base table once

In SQL you repeat the join in every query. In pandas you build one line-level table and reuse it:

completed = orders[orders["status"] == "completed"]
lines = order_items[order_items["quantity"] > 0].merge(
    completed[["order_id", "order_date", "channel"]],
    on="order_id", how="inner", validate="many_to_one",
)
lines["revenue"] = lines["quantity"] * lines["unit_price"] * (1 - lines["discount_pct"] / 100)

Filtering orders before the merge is the pandas version of WHERE o.status = 'completed': the inner merge drops every line whose order is not in completed. validate="many_to_one" makes pandas raise if an order id is ever duplicated, which is how you would catch a fan-out before it reaches a slide.

Three date and label tools this case uses

Month labels with .dt.strftime. On a datetime column, .dt.strftime("%Y-%m") returns text like "2025-11". %Y is the four-digit year and %m the two-digit month. Text in that shape sorts in calendar order, so it makes a good groupby key.

orders["order_date"].dt.strftime("%Y-%m").head(2)
# 0    2023-01
# 1    2023-01

Month numbers with .dt.month. .dt.month gives the month as a number, 1 to 12, so lines["order_date"].dt.month == 11 is True for November rows.

Labels from a condition with np.where. np.where(condition, value_if_true, value_if_false) builds a column row by row, like a two-branch if:

orders["shipping_type"] = np.where(orders["shipping_fee"] == 0, "free shipping", "paid shipping")

Every row whose fee is 0 gets "free shipping", every other row "paid shipping". In this case you use it to label each line with its season or month, then pivot on that label.

Finally, sort_index() sorts a DataFrame by its index, which after a pivot is the row key (channel in step 3), so it is the same as sorting by channel before reset_index().

Step 1: groupby with named aggregation

COUNT(DISTINCT order_id) becomes ("order_id", "nunique"). Using "count" instead would count lines, the same mistake as COUNT(*) in SQL. Average order value is a ratio of two aggregates, so compute it after the groupby, from the unrounded revenue, and round the columns last.

Date filters compare a datetime64 column with a string: pandas parses "2026-01-01" as midnight, so < "2026-01-01" keeps every timestamp on December 31.

Steps 2 and 3: two periods as columns

SQL’s SUM(CASE WHEN period THEN revenue END) has a direct pandas equivalent: label every row with its period, then pivot.

holiday = lines[in_24 | in_25].copy()
holiday["season"] = np.where(holiday["order_date"] < "2025-01-01", "rev_2024", "rev_2025")
wide = holiday.pivot_table(index="category", columns="season", values="revenue", aggfunc="sum")

The result has category as its index and one column per season. Two clean-up lines make it match the SQL output: wide.columns.name = None removes the leftover “season” label on the columns, and reset_index() turns the category index back into a column. Naming the period labels after the final column names (rev_2024, nov_revenue) saves a rename.

An alternative is groupby(["category", "season"])["revenue"].sum().unstack(). It produces the same wide table; pivot_table just says it in one call.

SQL and pandas side by side

Step	SQL	pandas
filter rows	WHERE before GROUP BY	boolean mask before groupby
distinct count	COUNT(DISTINCT ...)	nunique
periods side by side	SUM(CASE WHEN ...)	pivot_table(columns=...)
sort	ORDER BY ... DESC	sort_values(..., ascending=False)

Pitfalls

• Rounding before dividing. Round the revenue columns after computing the growth, exactly as the SQL version does in its outer SELECT.
• Category order. pivot_table sorts the index alphabetically; the task asks for growth order, so sort explicitly.
• Forgetting reset_index. A result with category as its index looks the same in a notebook but is not the table the task asks for.
• Missing periods. If a category had no sales in one season, pivot_table would leave a missing value (NaN) in that cell. Here every category sells in both seasons, but check before you divide.