Capital One Business Analyst Assessment Guide: Case, SQL, Excel, and Power Day

Capital One's Business Analyst (BA) track is a completely separate hiring pipeline from SWE. Many candidates assume "if SWE doesn't work out, switch to BA" - then discover that BA's OA is a different beast: Case Study + SQL + Excel/Logic three modules, not LeetCode algorithms. Studying SWE-style is almost guaranteed to fail you out at the first stage.

This article is the site's first BA-specific guide for Capital One, covering Assessment through the 5-round Power Day onsite. Each stage gets real questions, answer skeletons, and the evaluation focus. By the end you should be able to decide whether the BA track suits you and how to pace your prep.

Capital One BA Pipeline

Stage	Length	Format	Decision weight
Resume Screen	-	Resume + cover letter	Entry
Online Assessment	90 min	Case + SQL + Excel	Critical filter
Phone Interview	45 min	Case + Behavioral	Critical filter
Power Day Round 1	60 min	Case Study	Decisive
Power Day Round 2	60 min	Technical (SQL + Excel)	Decisive
Power Day Round 3	45 min	Behavioral / LP	Decisive

Compound pass rate: roughly 5 percent end-to-end, similar to SWE but evaluated on entirely different axes.

OA Module 1: Case Study (30 min)

Real question: pricing a new credit card

Capital One plans to launch a new credit card targeting middle-class households earning USD 50K-100K. Historical data: comparable products see 3% default rate, $8000 average annual spend, $95 annual fee. Tasks:

1. Build the profitability model.
2. Recommend the three most critical product parameters.
3. Project first-year ROI.

Answer skeleton (P-A-R: Profit / Assumptions / Recommendation):

1. Profit:

   • Revenue = interest + annual fee + interchange
   • Cost = default loss + acquisition + ops
   • Net per card = (8000 × 18% × 30%) + 95 + (8000 × 1.5%) - (8000 × 3%) - 50 ≈ $200/year

2. Assumptions:

   • 18% APR, 30% revolving, 1.5% interchange
   • $50 customer acquisition cost

3. Recommendation:

   • APR (drives interest revenue and the default tradeoff)
   • Credit line (drives balances and default exposure)
   • Reward rate (drives appeal and cost)

Key signal: structured language, no padding, every number has a derivation.

OA Module 2: SQL (30 min)

Real question: customer segmentation + retention analysis

Two tables: customers(id, signup_date, segment) and transactions(customer_id, txn_date, amount).

Tasks:

1. Find customers signed up in H1 2026 whose first-month spend exceeded $500.
2. By segment, compute their 6-month retention rate (any transaction counts).

WITH new_customers AS (
  SELECT id, signup_date, segment
  FROM customers
  WHERE signup_date BETWEEN '2026-01-01' AND '2026-06-30'
),
first_month AS (
  SELECT
    c.id,
    c.segment,
    c.signup_date,
    SUM(CASE
      WHEN t.txn_date BETWEEN c.signup_date AND c.signup_date + INTERVAL '30 day'
      THEN t.amount ELSE 0 END) AS first_month_amount
  FROM new_customers c
  LEFT JOIN transactions t ON t.customer_id = c.id
  GROUP BY c.id, c.segment, c.signup_date
  HAVING SUM(CASE
      WHEN t.txn_date BETWEEN c.signup_date AND c.signup_date + INTERVAL '30 day'
      THEN t.amount ELSE 0 END) > 500
),
retained AS (
  SELECT DISTINCT f.id, f.segment
  FROM first_month f
  JOIN transactions t ON t.customer_id = f.id
  WHERE t.txn_date BETWEEN f.signup_date + INTERVAL '5 month'
                       AND f.signup_date + INTERVAL '6 month'
)
SELECT
  f.segment,
  COUNT(DISTINCT f.id) AS qualified_customers,
  COUNT(DISTINCT r.id) AS retained,
  COUNT(DISTINCT r.id) * 1.0 / COUNT(DISTINCT f.id) AS retention_rate
FROM first_month f
LEFT JOIN retained r USING (id)
GROUP BY f.segment;

Key points:

• BA SQL routinely tests nested CTEs plus time windows.
• Define retention precisely (DAU vs MAU vs any transaction).
• Use first_month as denominator instead of new_customers to avoid missing the filter.

OA Module 3: Excel / Logic (30 min)

Real question: fraud rate calculation + what-if analysis

Table per transaction with columns txn_id, user_id, amount, is_fraud, channel. Tasks:

1. Use a pivot to compute fraud rate per channel.
2. If mobile fraud-detection recall improves from 60% to 80%, how much does annualized loss decrease?

Excel formula skeleton:

=COUNTIFS(C:C,"mobile",D:D,1) / COUNTIFS(C:C,"mobile")

What-if:

• Current mobile annualized loss = total mobile fraud amount × (1 - 60%)
• After uplift = total mobile fraud amount × (1 - 80%)
• Reduction = total mobile fraud amount × 20%

Key signal: in 30 minutes, can you produce correct numbers and clearly state assumptions and limits in writing.

Power Day Round 1: Case Study (60 min)

Real question: root-cause an uptick in fraud rate

Last month's Capital One credit card fraud rate rose from 0.5% to 0.8%. Use 30 minutes to root-cause and provide 3 actionable recommendations.

Answer skeleton (S-D-V-A: Segment / Drill / Verify / Action):

1. Segment: by region, product line, customer cohort, channel, time of day.
2. Drill: identify the abnormal subset, e.g. "East coast mobile new customers on weekends" account for 70% of the increase.
3. Verify: validate via A/B or a historical baseline.
4. Action:
   • Quick win: temporarily raise the mobile risk threshold.
   • Mid-term: add 3D Secure step-up auth in the mobile app.
   • Long-term: retrain the fraud model with device fingerprint features.

Power Day Round 2: Technical (SQL + Excel)

Real question: bulk data cleansing

user_id is int in customers and string in transactions, and roughly 5% of transactions carry a leading zero. Join the tables.

SELECT *
FROM customers c
JOIN transactions t
  ON c.id = CAST(REGEXP_REPLACE(t.user_id, '^0+', '') AS INTEGER);

Follow-up: what if user_id also has nulls or non-numeric chars? Answer: use TRY_CAST or a CASE WHEN filter.

Power Day Round 3: Behavioral / LP

Five must-prep BA prompts

1. "Tell me about a time you used data to influence a non-data person."
2. "Walk me through a time you discovered a flaw in your own analysis."
3. "Describe a time you balanced multiple stakeholders with conflicting priorities."
4. "Tell me about a project where the data was messy or incomplete."
5. "Why Business Analyst at Capital One specifically (not consulting or DS)?"

STAR template:

• Situation: project context
• Task: your role
• Action: what you did (must include numbers)
• Result: quantified outcome

Skills x Question Weight

Skill	OA Case	OA SQL	OA Excel	PD Case	PD Tech	Behavioral
Business reasoning	***	*	**	***	*	**
SQL speed	*	***	*	*	***	*
Excel	*	*	***	*	**	*
Communication	***	*	*	***	**	***
Reflection depth	*	*	*	**	*	***

FAQ

Q1: Can non-coders apply for BA? SQL is required, Python is not. Excel + SQL + Case Study is the BA "three pack"; programming is a plus.

Q2: BA vs Data Analyst vs Data Scientist?

• BA: business-driven, case study primary, SQL is the tool.
• DA: tech-driven, SQL + Tableau core, case secondary.
• DS: model-driven, Python + stats core.

Capital One's BA leans toward financial business consulting - more business judgment than typical tech BA roles.

Q3: Is 30 minutes enough for the Power Day case? Yes, but the first 5 minutes must produce a clear framework. Without it, even correct numbers later cannot save the round.

Q4: BA starting comp? New BA base at Capital One is 75-95k plus ~10% bonus and sign-on. Total comp 90-115k. Senior BA total comp can exceed 150k.

Q5: Can BA transfer to SWE or DS internally? Yes, but with a fresh technical loop. Internal mobility is friendly; the bottleneck is usually team headcount.

---

Preparing for the Capital One BA interview?

If you want a 3-module OA mock (Case + SQL + Excel), Power Day case framework drilling, or a real person doing VO proxy / VO assist live shadowing on Power Day, we can talk through a complete OA proxy / VO assist / VO proxy plan.

---

Contact

Need real interview questions and a custom prep plan? Add WeChat Coding0201 now to get questions.

Email: [email protected] Telegram: @OAVOProxy