Capital One Assessment Test Advanced Applications 3 — Questions and Answers
Question 1: A Capital One data engineer needs to find customers who have accounts but no transactions in 2025. Which SQL approach is most efficient?
- LEFT JOIN transactions ON customer_id with WHERE transactions.id IS NULL and year = 2025 (Correct answer)
- INNER JOIN transactions ON customer_id WHERE year = 2025
- SELECT * FROM customers WHERE customer_id NOT IN (SELECT customer_id FROM transactions)
- CROSS JOIN transactions WHERE transactions.year = 2025
Correct answer: LEFT JOIN transactions ON customer_id with WHERE transactions.id IS NULL and year = 2025
A LEFT JOIN with IS NULL on the right table's key efficiently finds unmatched records without a subquery scan.
Question 2: A portfolio of 500 credit accounts has an average balance of $4,200 with a standard deviation of $800. Assuming normal distribution, approximately how many accounts have balances above $5,800?
- 11 (Correct answer)
- 25
- 50
- 80
Correct answer: 11
$5,800 is 2 standard deviations above the mean; approximately 2.3% of a normal distribution lies above +2σ, so 0.023 × 500 ≈ 11 accounts.
Question 3: In a Capital One credit scoring model, the Gini coefficient of 0.68 indicates what about the model's discriminatory power?
- Strong ability to separate defaulters from non-defaulters (Correct answer)
- Weak model performance close to random
- Perfect separation with no misclassification
- High recall but low precision
Correct answer: Strong ability to separate defaulters from non-defaulters
A Gini coefficient of 0.68 (AUC ≈ 0.84) indicates strong discriminatory power in distinguishing defaulters from non-defaulters.
Question 4: An analyst writes this Python code: df.groupby('state')['revenue'].agg(['mean','median','std']). What will this output?
- A DataFrame with mean, median, and standard deviation of revenue for each state (Correct answer)
- A single row with overall mean, median, and standard deviation across all states
- Three separate DataFrames, one for each aggregation
- A pivot table with states as columns
Correct answer: A DataFrame with mean, median, and standard deviation of revenue for each state
The groupby + agg call returns a DataFrame indexed by state with three columns: mean, median, and std of revenue.
Question 5: Capital One's fraud detection model has precision of 0.85 and recall of 0.70. What is the F1 score?
- 0.769 (Correct answer)
- 0.775
- 0.750
- 0.700
Correct answer: 0.769
F1 = 2 × (precision × recall) / (precision + recall) = 2 × (0.85 × 0.70) / (0.85 + 0.70) = 1.19 / 1.55 ≈ 0.769.
Question 6: Which approach correctly handles a many-to-many relationship between Capital One customers and credit products in a relational database?
- Create a junction table with foreign keys to both the customers and products tables (Correct answer)
- Add an array column to the customers table listing all product IDs
- Duplicate customer rows for each product they hold
- Store product data as JSON in a single customer column
Correct answer: Create a junction table with foreign keys to both the customers and products tables
A junction (bridge) table with foreign keys to both parent tables is the standard relational approach for many-to-many relationships.
Question 7: A Capital One analyst needs to calculate the cumulative sum of daily transactions partitioned by account. Which SQL window function achieves this?
- SUM(amount) OVER (PARTITION BY account_id ORDER BY date ROWS UNBOUNDED PRECEDING) (Correct answer)
- SUM(amount) OVER (PARTITION BY account_id)
- SUM(amount) GROUP BY account_id ORDER BY date
- CUMSUM(amount) PARTITION BY account_id ORDER BY date
Correct answer: SUM(amount) OVER (PARTITION BY account_id ORDER BY date ROWS UNBOUNDED PRECEDING)
The window function with ROWS UNBOUNDED PRECEDING computes a running total within each account partition ordered by date.
Capital One Assessment Test Advanced Applications 3
A Capital One data engineer needs to find customers who have accounts but no transactions in 2025.
Which SQL approach is most efficient?