Quick summary
Summarize this blog with AI
These six Python interview questions focus on an analyst's daily work: reconcile revenue, choose the right denominator, validate joins, and explain missing data. They use one fictional order-line dataset and a second small conversion fixture. Each prompt has an executable answer and a common wrong approach.
Run the blocks in order. They were tested with Python 3.12.14, pandas 2.2.3 with Copy-on-Write enabled, and pandas 3.0.6. Score each question out of two: one point for the exact result and one for the business explanation. This practice score is not a hiring standard.
1. What is one row, and which sales qualify?
Prompt: Compute revenue from completed order lines only. unit_price is per unit; discount is for the entire line. All values are integer cents in one currency. Exclude fully refunded orders; partial refunds are outside this fixture.
import pandas as pd
if pd.__version__.split(".")[0] == "2":
pd.options.mode.copy_on_write = True
lines = pd.DataFrame({
"order_id": [1, 1, 2, 3, 4], "line_id": [1, 2, 3, 4, 5],
"customer_id": [7, 7, 8, 7, 9],
"status": ["completed", "completed", "completed", "refunded", "completed"],
"qty": [2, 1, 1, 99, 1], "unit_price": [1000, 2000, 1500, 1000, 500],
"discount": [0, 200, 0, 0, 0],
"date": ["2026-01-05", "2026-01-05", "2026-01-20", "2026-02-01", "2026-03-02"]
})
clean = lines.loc[lines["status"].eq("completed")].copy()
clean["revenue"] = clean["qty"] * clean["unit_price"] - clean["discount"]
print(clean["line_id"].tolist())
print(int(clean["revenue"].sum()))
Expected output:
[1, 2, 3, 5]
5800
One row is a line, not an order. The refunded line's large quantity is deliberately excluded. Do not multiply a line-level discount by quantity. Full credit includes naming the currency and refund policy; a real revenue-recognition report may need separate refund dates, tax, shipping, and partial-refund handling.
2. What is average order value?
Prompt: Compute AOV in cents and show the incorrect line average for comparison.
order_revenue = clean.groupby("order_id", sort=True)["revenue"].sum()
print(order_revenue.to_dict())
print(round(float(order_revenue.mean()), 2))
print(round(float(clean["revenue"].mean()), 2))
Expected output:
{1: 3800, 2: 1500, 4: 500}
1933.33
1450.0
Order 1 has two lines but one place in the denominator. The correct numerator is 5800 cents across three completed orders. Averaging four line amounts produces 1450 and answers a different question. Fractional cents in an analytical average do not imply that a payment should be charged with that precision.
3. Can a customer join change the sales total?
Prompt: Attach a region without multiplying order lines. Preserve unmatched customers as Unknown and fail if the dimension key is duplicated.
customers = pd.DataFrame({"customer_id": [7, 8], "region": ["East", "West"]})
joined = clean.merge(customers, on="customer_id", how="left",
validate="many_to_one", indicator=True)
joined["region"] = joined["region"].fillna("Unknown")
print(joined.groupby("region", sort=True)["revenue"].sum().to_dict())
print(joined.loc[joined["_merge"].eq("left_only"), "customer_id"].tolist())
try:
clean.merge(pd.concat([customers, customers.iloc[[0]]]),
on="customer_id", validate="many_to_one")
except pd.errors.MergeError:
print("duplicate customer key rejected")
Expected output:
{'East': 3800, 'Unknown': 500, 'West': 1500}
[9]
duplicate customer key rejected
The regional sum still reconciles to 5800. An inner join would lose customer 9's 500 cents. An unconstrained join to two versions of customer 7 would duplicate valid fact lines. Resolve dimension history according to a date/authority rule instead of dropping arbitrary duplicate rows after the join.
4. Should you average conversion percentages?
Prompt: Mobile has 1 conversion from 10 eligible sessions and desktop has 45 from 90. Compare the simple average of channel rates with the overall rate.
channels = pd.DataFrame({"channel": ["mobile", "desktop"],
"conversions": [1, 45], "sessions": [10, 90]})
rates = channels["conversions"] / channels["sessions"]
print(round(float(rates.mean() * 100), 2))
print(round(float(channels["conversions"].sum() /
channels["sessions"].sum() * 100), 2))
Expected output:
30.0
46.0
The overall rate uses total conversions divided by total eligible sessions, so it is weighted by exposure. The simple average gives the ten-session group the same weight as the ninety-session group. If the total denominator is zero, report an undefined rate. Also verify that sessions form disjoint, consistently defined populations before summing them.
5. Does no row mean zero revenue?
Prompt: Produce monthly totals, keeping February visible but unknown until source completeness is confirmed.
monthly = clean.assign(date=pd.to_datetime(clean["date"])).set_index("date")
monthly = monthly["revenue"].resample("ME").sum(min_count=1)
print(monthly.index.strftime("%Y-%m").tolist())
print([None if pd.isna(value) else float(value) for value in monthly])
Expected output:
['2026-01', '2026-02', '2026-03']
[5300.0, None, 500.0]
The fixture has no qualifying February lines. A complete transaction feed would justify filling that month's absent activity with zero. An incomplete feed would not. The first answer preserves the distinction so the assumption remains reviewable. Do not compare a partial current month with a complete prior month without a matched exposure rule.
6. Which repeated values are actually duplicate records?
Prompt: Check the unique line ID, then show how to identify a repeated source record without collapsing legitimate lines within an order.
replayed = pd.concat([lines, lines.iloc[[0]]], ignore_index=True)
print(lines["line_id"].is_unique)
print(replayed.loc[replayed["line_id"].duplicated(keep=False), "line_id"].tolist())
print(lines.loc[lines["order_id"].duplicated(keep=False), "line_id"].tolist())
Expected output:
True
[1, 1]
[1, 2]
A repeated line_id violates this fixture's event identity. Repeated order_id is expected when an order contains several products. If two copies of a line ID disagree on content, a casual drop_duplicates call chooses a row without proving authority. Reject or reconcile the conflict using source versions and an explicit winner rule.
Present an answer that can be checked
State the row grain, filter policy, currency unit, denominator, and reconciliation total. Explain one excluded or unmatched row. Then name what could change the conclusion: missing feeds, refunds, duplicate source events, late arrivals, or changing customer dimensions. Those details make a correct calculation usable in a business discussion.
References: pandas merge, group sums, duplicate detection, and resampling.
