← Boston Consulting Group Interview Insights

Boston Consulting Group·Data Scientist·Technical Phone Screen·Intermediate

Intermediate
Apr 2026

Summary

BCG data scientist interview with a pandas/data cleaning question that looked straightforward on the surface but had a few gotchas worth thinking through beforehand.

Questions Asked (1)

Q1

Given a customers table and an orders table, merge them in pandas keeping all customers, replace null order amounts with 0 to compute a total_spent column, fill missing country values with the mode of known countries, and return the result sorted by total_spent descending.

Data ModelingProduct Analytics & MetricsAlgorithms & Data Structures
Author's notes

I got the left merge and fillna parts pretty quickly but fumbled on the mode imputation.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the table structures and the desired output, then outline a step-by-step pandas workflow: merge with how='left', fill nulls appropriately, compute total_spent, and sort. Emphasize data integrity and edge cases like duplicate customers or missing modes.

Pro tip: Always validate the merge cardinality (e.g., check for duplicate customer IDs) and consider whether filling missing countries with the mode is the best business decision—sometimes a separate 'Unknown' category is more informative.

1. Understand the data and requirements

Clarify the schema of customers and orders tables, the join key, and confirm that all customers should be kept. Identify columns with missing values and the expected output format.

2. Merge tables with a left join

Use pd.merge(customers, orders, on='customer_id', how='left') to retain all customers. Check for duplicate customer IDs in orders and decide on aggregation if needed.

3. Handle missing values

Replace null order amounts with 0 using fillna(0) before computing total_spent. For missing country values, compute the mode of the country column and fill nulls with that mode.

4. Compute total_spent and sort

Create a new column total_spent as the sum of order amounts per customer (if multiple orders, group by customer first). Then sort the DataFrame by total_spent in descending order.

5. Validate and return the result

Check that no nulls remain in critical columns, verify the sorting, and ensure the output has the expected number of rows. Return the final DataFrame.

Key Points to Mention

  • Use of how='left' in merge to keep all customers
  • Handling missing values with fillna(0) for order amounts
  • Computing mode with .mode()[0] and filling missing countries
  • Aggregating multiple orders per customer before computing total_spent
  • Sorting with sort_values(by='total_spent', ascending=False)
  • Validating merge cardinality and checking for duplicates

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.