How would you combine customer and transaction DataFrames and describe join types?

What's really being asked

This question probes your ability to model a one-to-many relationship using pandas merge operations and your fluency with SQL-style join semantics. The interviewer wants to see that you recognize the cardinality between customers and transactions, know the appropriate API, and can explain the tradeoffs between join types rather than simply reciting syntax.

The full answer

First, state that you would use pd.merge to combine the DataFrames on the customer_id column, because merge is the idiomatic tool for SQL-style joins on keys. Second, note that after merging you would group by customer_id and sum the amount column to get totals, which respects the one-to-many structure. Third, describe the four common join types in order of practical usefulness: inner join keeps only customers who have at least one transaction; left join keeps every customer and shows NaN for missing transactions; right join keeps every transaction row and drops customers without transactions; outer join preserves the full union of both tables. Fourth, mention that for this specific business question a left join is usually safest because you want to retain customers with zero spend and fill missing totals with zero, unless the requirement explicitly excludes inactive customers. You can also note that DataFrame.join could work if customer_id were set as the index, but pd.merge is the standard approach for column-based keys.

The mistakes people make

A red flag is suggesting pd.concat or DataFrame.join without referencing the customer_id key, because concat stacks rows or columns and does not perform key-based relational joins. Another red flag is describing join types incorrectly, such as saying inner and left joins are the same or claiming a right join keeps all customers. Candidates also stumble by forgetting to group after the merge, instead trying to sum before joining, which breaks the one-to-many aggregation. Some also propose iterating rows with apply, which destroys vectorized performance.

What usually comes next

The interviewer may ask how you would handle customers with no transactions, which tests whether you chose left join and handled NaN values with fillna. They might ask about performance on large datasets, where you should mention ensuring the key is sorted or using PyArrow-backed strings. They could also ask about duplicate customer_ids in the customer table, which would turn the merge into a many-to-many relationship and inflate transaction totals. Another follow-up is asking when outer joins become memory-intensive, which happens when both tables contain large numbers of unmatched keys.

A concrete example

Imagine customers has ids 1, 2, 3 and transactions has ids 1 and 3. An inner merge returns customers 1 and 3 with their totals. A left merge returns all three customers, with customer 2 showing a NaN total that you would replace with zero after grouping. A right merge would return only 1 and 3 but preserve any orphan transactions with missing customer data. An outer merge returns the full set of 1, 2, and 3 plus any orphan transactions.