Compute Customer Spend and Engineer Features for 2023
Company: Capital One
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
transactions
+-----------+-------------+--------+------------+--------------+
| tran_id | customer_id | amount | tran_date | merchant_cat |
+-----------+-------------+--------+------------+--------------+
| 1001 | 501 | 45.67 | 2023-01-12 | GROCERIES |
| 1002 | 502 | 120.00 | 2023-01-13 | TRAVEL |
| 1003 | 501 | 13.50 | 2023-01-14 | DINING |
| 1004 | 503 | 250.00 | 2023-01-14 | ELECTRONICS |
+-----------+-------------+--------+------------+--------------+
##### Scenario
Capital One Data Science Manager interview – take-home data challenge using historical credit-card transactions.
##### Question
Write SQL to compute each customer's total and average monthly spend for 2023. In Python, engineer features summarizing spend by merchant category and prepare a modeling dataset.
##### Hints
Think window functions, GROUP BY month, and pivot/one-hot in pandas.
Quick Answer: This question evaluates proficiency in SQL-based temporal aggregation and Python feature engineering on transactional datasets, including computing monthly spend summaries and engineering category-level spending features.
