Capital One Assessment Test Advanced Applications 2 — Questions and Answers
Question 1: 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?
- SELECT customer_id, SUM(amount) AS total FROM transactions WHERE date >= CURRENT_DATE - 30 GROUP BY customer_id ORDER BY total DESC LIMIT 3 (Correct answer)
- SELECT customer_id, COUNT(amount) AS total FROM transactions WHERE date >= CURRENT_DATE - 30 GROUP BY customer_id ORDER BY total LIMIT 3
- SELECT customer_id, SUM(amount) AS total FROM transactions GROUP BY customer_id ORDER BY total DESC LIMIT 3
- SELECT customer_id, SUM(amount) AS total FROM transactions WHERE date <= CURRENT_DATE - 30 GROUP BY customer_id ORDER BY total DESC LIMIT 3
Correct 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.
Question 2: 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?
- 2.70% (Correct answer)
- 2.50%
- 2.37%
- 2.80%
Correct answer: 2.70%
The 3-month moving average for June uses Apr, May, Jun: (2.6 + 2.4 + 3.1) / 3 = 2.70%.
Question 3: In Python, which code snippet correctly identifies outliers in a list of transaction amounts using the IQR method?
- Q1, Q3 = np.percentile(data, [25, 75]); IQR = Q3 - Q1; outliers = [x for x in data if x < Q1 - 1.5*IQR or x > Q3 + 1.5*IQR] (Correct answer)
- Q1, Q3 = np.percentile(data, [25, 75]); IQR = Q1 - Q3; outliers = [x for x in data if x < Q1 - 1.5*IQR or x > Q3 + 1.5*IQR]
- Q1, Q3 = np.percentile(data, [25, 75]); IQR = Q3 - Q1; outliers = [x for x in data if x < Q1 + 1.5*IQR or x > Q3 - 1.5*IQR]
- mean, std = np.mean(data), np.std(data); outliers = [x for x in data if abs(x - mean) > 1.5*std]
Correct answer: Q1, Q3 = np.percentile(data, [25, 75]); IQR = Q3 - Q1; outliers = [x for x in data if x < Q1 - 1.5*IQR or 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.
Question 4: 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?
- 54 (Correct answer)
- 8
- 46
- 100
Correct 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.
Question 5: Which Excel formula calculates the weighted average APR for a portfolio where column A has loan amounts and column B has APR rates?
- =SUMPRODUCT(A2:A100, B2:B100) / SUM(A2:A100) (Correct answer)
- =AVERAGE(B2:B100)
- =SUM(A2:A100 * B2:B100)
- =SUMPRODUCT(A2:A100, B2:B100) / COUNT(A2:A100)
Correct 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.
Question 6: 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?
- 12.5% (Correct answer)
- 1.875%
- 15%
- 1.5%
Correct answer: 12.5%
Relative uplift = (B rate - A rate) / A rate = (16.875% - 15%) / 15% = 12.5%.
Question 7: When normalizing features for a machine learning model predicting credit risk, which transformation is most appropriate for a highly right-skewed income variable?
- Log transformation (Correct answer)
- Min-max scaling
- One-hot encoding
- Z-score standardization
Correct answer: Log transformation
Log transformation compresses the long right tail of income distributions, making the variable more normally distributed for modeling.
Capital One Assessment Test Advanced Applications 2
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?