Merge CSVs and build revenue pivot with pandas
Company: Capital One
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Online Assessment
You receive four CSVs and must replicate an Excel VLOOKUP + PivotTable workflow using Python/pandas.
CSV samples:
customers.csv
customer_id,signup_date,channel
1,2025-06-02,Organic
2,2025-06-10,Ads
3,2025-07-05,Referral
orders.csv
order_id,customer_id,order_date,total_amount
101,1,2025-06-12,120
102,2,2025-06-28,80
103,1,2025-07-15,200
104,3,2025-08-02,50
refunds.csv
order_id,refund_amount
102,20
104,50
targets.csv
month,channel,revenue_target
2025-06,Organic,100
2025-06,Ads,120
2025-07,Organic,150
2025-08,Referral,60
Task: Write pandas code to (a) robustly load these CSVs (assume one file path may initially fail—gracefully retry or fall back without crashing), (b) compute net_revenue per order = total_amount - COALESCE(refund_amount,0), (c) roll up to monthly net revenue by channel for months 2025-06 through 2025-08 based on order_date, (d) left-join the result to targets.csv on (month, channel) to compute target_gap = net_revenue - revenue_target (treat missing targets as 0), and (e) produce a pivot table with index=month (YYYY-MM), columns=channel, values=net_revenue, plus an additional similarly-shaped table for target_gap. Ensure date parsing is correct, missing refunds are handled, and channels with no orders in a month still appear with 0 in the pivot.
Overview: This question evaluates proficiency in data manipulation and ETL using pandas, covering robust file I/O, join/merge semantics, null/coalescing behavior, date parsing, aggregation, and pivoting.
Read the full Capital One Data Scientist interview experience this question came from
You are given four tables that mirror four CSV files: customers, orders, refunds, and targets.
Compute monthly net revenue by channel for months 2025-06 through 2025-08 (inclusive), where:
- net_revenue per order = total_amount - COALESCE(refund_amount, 0)
- month is derived from orders.order_date as YYYY-MM
Then left-join this monthly net revenue to targets on (month, channel) and compute:
- target_gap = net_revenue - COALESCE(revenue_target, 0)
Finally, output a pivoted result with one row per month and these columns:
- organic_net_revenue, ads_net_revenue, referral_net_revenue
- organic_target_gap, ads_target_gap, referral_target_gap
Requirements:
- Include all month/channel combinations for months 2025-06, 2025-07, 2025-08 and channels appearing in either customers or targets.
- If a channel has no orders in a month, its net revenue should be 0.
- If a (month, channel) target is missing, treat revenue_target as 0.
- Use the sample data below; the expected output should match it.
Tables
customers(customer_id INT, signup_date DATE, channel VARCHAR(50))
orders(order_id INT, customer_id INT, order_date DATE, total_amount DECIMAL(10,2))
refunds(order_id INT, refund_amount DECIMAL(10,2))
targets(month VARCHAR(7), channel VARCHAR(50), revenue_target DECIMAL(10,2))
Hints
- Compute net revenue at the order level by left joining refunds and using COALESCE(refund_amount, 0).
- To ensure missing month/channel combinations appear, build a month x channel grid via CROSS JOIN, then left join aggregates/targets onto it.