Katkat
Back

Compute Customer Spend and Engineer Features for 2023

Capital OneData Scientist · Onsite
MediumData ScientistOnsiteData Manipulation (SQL/Python)

Last updated

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.

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.