Advanced Applications Flashcards

A Capital One analyst is querying a transactions table. Which SQL query correctly returns the top 3 customers by total spend in the last 30 days?

Answer: SELECT customer_id, SUM(amount) AS total FROM transactions WHERE date >= CURRENT_DATE - 30 GROUP BY customer_id ORDER BY total DESC LIMIT 3

The correct query filters by the last 30 days, groups by customer, sums spend, and orders descending before limiting to 3.

A dataset shows monthly credit card default rates: Jan 2.1%, Feb 2.3%, Mar 2.0%, Apr 2.6%, May 2.4%, Jun 3.1%. What is the 3-month moving average for June?

Answer: 2.70%

The 3-month moving average for June uses Apr, May, Jun: (2.6 + 2.4 + 3.1) / 3 = 2.70%.

In Python, which code snippet correctly identifies outliers in a list of transaction amounts using the IQR method?

Answer: Q1, Q3 = np.percentile(data, [25, 75]); IQR = Q3 - Q1; outliers = [x for x in data if x Q3 + 1.5*IQR]

The IQR method defines outliers as values below Q1 - 1.5*IQR or above Q3 + 1.5*IQR, with IQR = Q3 - Q1.

A risk model predicts credit defaults with 92% accuracy. In a dataset of 1,000 accounts where 50 actually default, how many false positives would you expect if the model flags 100 accounts total?

Answer: 54

With 50 true defaults and 92% accuracy on 1,000 accounts, approximately 46 true positives are caught; the remaining 54 flagged accounts are false positives.

Which Excel formula calculates the weighted average APR for a portfolio where column A has loan amounts and column B has APR rates?

Answer: =SUMPRODUCT(A2:A100, B2:B100) / SUM(A2:A100)

A weighted average is the sum of (weight × value) divided by the sum of weights, which SUMPRODUCT divided by SUM achieves.

A Capital One product manager reviews A/B test results: variant A has 1,200 conversions out of 8,000 users; variant B has 1,350 conversions out of 8,000 users. What is the relative uplift of variant B over A?

Answer: 12.5%

Relative uplift = (B rate - A rate) / A rate = (16.875% - 15%) / 15% = 12.5%.

When normalizing features for a machine learning model predicting credit risk, which transformation is most appropriate for a highly right-skewed income variable?

Answer: Log transformation

Log transformation compresses the long right tail of income distributions, making the variable more normally distributed for modeling.