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.
Overview: 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.
Using the transactions table, write a SQL query to compute each customer's total spend in 2023 and their average monthly spend in 2023. The average monthly spend should be computed over the customer's monthly totals for months in 2023 that have at least one transaction.
Tables
transactions(tran_id INTEGER, customer_id INTEGER, amount DECIMAL(10,2), tran_date DATE, merchant_cat VARCHAR(50))
Hints
- First aggregate to monthly spend per customer using GROUP BY DATE_TRUNC('month', tran_date).
- Then aggregate those monthly results per customer to compute total and average monthly spend for 2023.