Compute Customer Spend and Engineer Features for 2023

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.

Customer total and average monthly spend for 2023

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.

Table transactions

column	type	constraints
tran_id	INTEGER	PRIMARY KEY, NOT NULL
customer_id	INTEGER	NOT NULL
amount	DECIMAL(10,2)	NOT NULL
tran_date	DATE	NOT NULL
merchant_cat	VARCHAR(50)	NOT NULL

Sample data:

amount	customer_id	merchant_cat	tran_date	tran_id
45.67	501	GROCERIES	2023-01-12	1001
120	502	TRAVEL	2023-01-13	1002
13.5	501	DINING	2023-01-14	1003
250	503	ELECTRONICS	2023-01-14	1004

Expected output

For each customer_id, return total spend in 2023 and average monthly spend for 2023, where the average is computed over the monthly totals for months in 2023 that have at least one transaction.

avg_monthly_spend_2023	customer_id	total_spend_2023
59.17	501	59.17
120	502	120
250	503	250

Hints

1. First aggregate to monthly spend per customer using GROUP BY DATE_TRUNC('month', tran_date).
2. Then aggregate those monthly results per customer to compute total and average monthly spend for 2023.
3. Ensure you filter to transactions where the year of tran_date is 2023.