Home › Blog › Capital One CodeSignal Data Analyst Assessment

What the Capital One CodeSignal Data Analyst Assessment Actually Asks (Candidate Reports, 2021–2026)

Capital One does not publish the format of its data analyst screen. What exists is a trail of candidate reports on Blind from 2021 to 2025 and a handful of prep guides dated 2025 and 2026, and they mostly agree: about 70 minutes, around 14–15 questions, provided CSV or Excel datasets, mostly multiple choice, plus written SQL. This post lays out what those sources say and where they disagree, then works each named SQL pattern on a card-transactions schema — the shape the datasets are described as having — and ends with the two mistakes that cost points and a 2-week plan.

The format, as reported (and where reports disagree)

Everything in this section is attributed. None of it comes from Capital One, and one reply on the most recent Blind thread — from someone posting as a Capital One employee in June 2025 — warns that the format "has changed in last 4 years". Read the dates as part of the claim.

Where they agree: roughly 70 minutes, provided datasets (CSV or Excel), mostly multiple-choice questions answered from those datasets, and written SQL. Where they disagree:

• How many questions. Candidates say 14 to 15. The guides say "four to six" — which reads as the number of dataset sections, each carrying several multiple-choice items, rather than a different test. Plan for 14–15 individual answers.
• How much written SQL. One report says one database question; another says "few sql questions". Prepare for more than one.
• Which tool for the dataset questions. Candidates report a free choice — Excel, Python, R, or SQL. One February 2022 candidate found Excel "tedious" but quicker than SQL for the dataset items; a January 2023 reply on the same thread advises "Do not attempt the SQL until the very end. Work really quickly." Time is the constraint every report mentions.

The schema used in every example below

The February 2026 guide describes the datasets as resembling "customer, transaction, account, product, or campaign" tables. Capital One is a card issuer, so every example here runs on a card-transactions schema — the synthetic fraud ledger inside SQL Quest's Banking track, which you can query in the browser. Column names are real:

The last column is the one to internalise. Before any query, say the grain out loud: one row per what? Accounts have many transactions; transactions have zero or more chargebacks. Every mistake in the "two mistakes" section comes from forgetting one of those arrows.

INNER vs LEFT JOIN — and when a join duplicates rows

Both guides open with joins, and the February 2026 one adds the phrase that matters: "understanding when joins create duplicate rows". The classic dataset question is accounts that have never transacted. INNER JOIN cannot answer it — it drops exactly the rows you want.

SELECT a.account_id, a.country, a.signup_at
FROM accounts a
LEFT JOIN transactions t ON t.account_id = a.account_id
WHERE t.txn_id IS NULL
ORDER BY a.signup_at;

The mirror question — how many transactions per account, including zero — is where the duplicate-row trap lives. LEFT JOIN keeps every account, but each account now appears once per transaction. That is correct here, because you are about to count them; it is wrong the moment you SUM something from the accounts side.

SELECT a.account_id,
       COUNT(t.txn_id) AS txn_count      -- COUNT(*) would return 1 for a no-transaction account
FROM accounts a
LEFT JOIN transactions t ON t.account_id = a.account_id
GROUP BY a.account_id
ORDER BY txn_count DESC;

GROUP BY with COUNT, SUM, AVG, MIN, MAX

The guides list all five aggregates by name. A dataset question will ask for one number per category — which merchant category has the highest average transaction? — and the multiple-choice options will include the answers you get from the common mistakes (forgetting to filter refunds, averaging the wrong thing).

SELECT m.category,
       COUNT(*)        AS txn_count,
       SUM(t.amount)   AS total_spend,
       ROUND(AVG(t.amount), 2) AS avg_amount,
       MIN(t.amount)   AS smallest,
       MAX(t.amount)   AS largest
FROM transactions t
JOIN merchants m ON m.merchant_id = t.merchant_id
WHERE t.status = 'completed'          -- filter rows BEFORE grouping
GROUP BY m.category
HAVING COUNT(*) >= 20                 -- filter GROUPS after
ORDER BY avg_amount DESC;

Two things to have cold: the WHERE / HAVING split (row filter before grouping, group filter after), and that this join is safe — merchants is one row per merchant, so joining it onto transactions cannot multiply anything. The unsafe direction is the next section but one.

▶ Write this one yourself: Transaction Share by Merchant Category

CASE WHEN — conditional metrics and buckets

The February 2026 guide names "CASE WHEN for conditional metrics and buckets". Both shapes are one query each. Buckets:

SELECT
  CASE
    WHEN amount < 25   THEN 'small'
    WHEN amount < 250  THEN 'medium'
    ELSE 'large'
  END AS size_bucket,
  COUNT(*) AS txn_count
FROM transactions
GROUP BY size_bucket
ORDER BY MIN(amount);

Conditional metrics — a rate computed inside one aggregate — is the pattern that turns up as "what share of high-risk merchants' transactions were declined?":

SELECT m.risk_tier,
       COUNT(*) AS txn_count,
       ROUND(100.0 * AVG(CASE WHEN t.status = 'declined' THEN 1 ELSE 0 END), 2) AS decline_rate_pct
FROM transactions t
JOIN merchants m ON m.merchant_id = t.merchant_id
GROUP BY m.risk_tier
ORDER BY decline_rate_pct DESC;

CTEs and subqueries

Both guides name CTEs; the February 2026 one adds "for breaking a problem into readable steps", which is the point. A typical two-step question: which accounts spend more than the average account? The average account's spend is itself an aggregate of an aggregate, so it needs two levels.

WITH per_account AS (
  SELECT account_id, SUM(amount) AS total_spend
  FROM transactions
  WHERE status = 'completed'
  GROUP BY account_id
),
benchmark AS (
  SELECT AVG(total_spend) AS avg_spend FROM per_account
)
SELECT p.account_id, p.total_spend
FROM per_account p
CROSS JOIN benchmark b
WHERE p.total_spend > b.avg_spend
ORDER BY p.total_spend DESC;

The same query as a subquery is shorter and harder to read under a clock. When a question has two steps, write two CTEs and name them after the step. If the written SQL question is graded by a person, the names are free marks; if it is graded by output, they are free debugging.

Window functions — ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, SUM OVER

The February 2026 guide lists exactly six: ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD and SUM OVER. They cover three question shapes.

Top-N per group (ROW_NUMBER / RANK / DENSE_RANK)

WITH merchant_spend AS (
  SELECT m.country, m.name, SUM(t.amount) AS total_spend
  FROM transactions t
  JOIN merchants m ON m.merchant_id = t.merchant_id
  WHERE t.status = 'completed'
  GROUP BY m.country, m.name
),
ranked AS (
  SELECT *,
         RANK() OVER (PARTITION BY country ORDER BY total_spend DESC) AS rank_in_country
  FROM merchant_spend
)
SELECT country, name, total_spend, rank_in_country
FROM ranked
WHERE rank_in_country <= 3
ORDER BY country, rank_in_country;

The three ranking functions differ only on ties: ROW_NUMBER breaks them arbitrarily (1, 2, 3), RANK leaves a gap (1, 1, 3), DENSE_RANK does not (1, 1, 2). A multiple-choice question that asks "how many merchants are in the top 3" is really asking which one you used.

Previous / next row (LAG / LEAD)

On a card ledger the LAG question is velocity: how many minutes since this account's previous transaction? Two charges seconds apart is what a fraud rule looks for, and it is the same shape as month-over-month change on any other table.

WITH ordered AS (
  SELECT account_id, txn_id, amount, txn_at,
         LAG(txn_at) OVER (PARTITION BY account_id ORDER BY txn_at) AS prev_txn_at
  FROM transactions
)
SELECT account_id, txn_id, amount, txn_at,
       ROUND((julianday(txn_at) - julianday(prev_txn_at)) * 1440, 1) AS minutes_since_prev
FROM ordered
WHERE prev_txn_at IS NOT NULL
ORDER BY minutes_since_prev
LIMIT 20;

Running totals (SUM OVER)

SELECT account_id, txn_at, amount,
       SUM(amount) OVER (
         PARTITION BY account_id
         ORDER BY txn_at
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_spend
FROM transactions
WHERE status = 'completed'
ORDER BY account_id, txn_at;

Date filtering — day, week, month, quarter, rolling window

The February 2026 guide's last item is "date filtering by day, week, month, quarter, and rolling window". The syntax is dialect-specific — the examples below are SQLite, which is what runs in the browser on SQL Quest; the ideas move unchanged to PostgreSQL's date_trunc and INTERVAL.

-- by month
SELECT strftime('%Y-%m', txn_at) AS month,
       COUNT(*) AS txn_count, SUM(amount) AS spend
FROM transactions
WHERE status = 'completed'
GROUP BY month
ORDER BY month;

-- rolling window: the 7 days ending on the ledger's last day
SELECT COUNT(*) AS txns_last_7_days
FROM transactions
WHERE txn_at >= datetime((SELECT MAX(txn_at) FROM transactions), '-7 days');

-- quarter: derive it from the month number
SELECT strftime('%Y', txn_at) || '-Q' || ((CAST(strftime('%m', txn_at) AS INTEGER) + 2) / 3) AS quarter,
       SUM(amount) AS spend
FROM transactions
GROUP BY quarter;

The two mistakes that cost points on dataset questions

On a written SQL question a reviewer may give partial credit for a query that is nearly right. On a multiple-choice dataset question there is no such thing: your number either matches an option or it does not, and the wrong options are the numbers you get from these two mistakes.

Mistake 1 — join fan-out

Joining a one-to-many table and then summing from the "one" side. Every transaction that has two chargebacks appears twice, and SUM(amount) is inflated by exactly the rows you duplicated.

SELECT t.account_id, SUM(t.amount) AS total_spend, COUNT(c.chargeback_id) AS disputes
FROM transactions t
LEFT JOIN chargebacks c ON c.txn_id = t.txn_id
GROUP BY t.account_id;

WITH disputes_per_txn AS (
  SELECT txn_id, COUNT(*) AS disputes
  FROM chargebacks
  GROUP BY txn_id
)
SELECT t.account_id,
       SUM(t.amount) AS total_spend,
       SUM(COALESCE(d.disputes, 0)) AS disputes
FROM transactions t
LEFT JOIN disputes_per_txn d ON d.txn_id = t.txn_id
GROUP BY t.account_id;

The test for fan-out is one sentence: does this join multiply the thing I am summing? If the joined table can have more than one row per key on my side, yes. Pre-aggregate it, or count with COUNT(DISTINCT t.txn_id) and sum from a CTE that never saw the join.

Mistake 2 — the wrong grain

Answering "average transaction amount per country" by averaging each account's average. The numbers differ because an account with one transaction and an account with a thousand get equal weight. Neither query is "wrong SQL"; one of them answers a different question.

-- grain: transaction (what "average transaction amount" means)
SELECT a.country, ROUND(AVG(t.amount), 2) AS avg_txn_amount
FROM transactions t
JOIN accounts a ON a.account_id = t.account_id
GROUP BY a.country;

-- grain: account (what "average account's average transaction" means)
WITH per_account AS (
  SELECT account_id, AVG(amount) AS avg_amount
  FROM transactions
  GROUP BY account_id
)
SELECT a.country, ROUND(AVG(p.avg_amount), 2) AS avg_of_account_avgs
FROM per_account p
JOIN accounts a ON a.account_id = p.account_id
GROUP BY a.country;

Read the question for its noun. "Per transaction", "per account", "per merchant" is the grain; the GROUP BY must produce one row per that noun and nothing else. If a question is ambiguous, the option list usually contains both numbers — which is itself the hint that grain is what is being tested.

A 2-week prep plan

Built for someone who already writes SELECT-FROM-WHERE and has an evening a day. Every item maps to a section above; the timed runs at the end are there because every candidate report mentions the clock.

Week 1 — the patterns, one per evening

1. Day 1 — grain. Take the four-table schema above and, for ten plausible questions, write only the GROUP BY line and the expected row count. No SELECT list. This is the skill the dataset questions test.
2. Day 2 — joins. INNER vs LEFT, the IS NULL anti-join, and COUNT(col) vs COUNT(*) after a LEFT JOIN. Then deliberately fan out a join and watch the SUM change. Joins practice set.
3. Day 3 — aggregates. All five aggregates, WHERE before, HAVING after. Write each query twice: once with the filter in the right place, once wrong, and see which answer a multiple-choice question would list.
4. Day 4 — CASE WHEN. Buckets and conditional rates. CASE WHEN practice set.
5. Day 5 — CTEs. Rewrite every day-3 query that needed a subquery as named CTEs. Say each CTE's grain out loud.
6. Day 6 — windows. Top-N per group with all three ranking functions on the same data; LAG for gaps; SUM OVER for running totals. Window functions practice set.
7. Day 7 — dates. Month, quarter, rolling 7-day, and the end-of-range trap. In the tool you plan to use on the day, not just SQL.

Week 2 — speed, tools, and the clock

1. Day 8 — pick your dataset tool. Candidates report a free choice of Excel, Python, R or SQL for the multiple-choice items. Load a CSV into each and time a GROUP BY and a two-table join. Keep the fastest one; it will not be the one you like most.
2. Day 9 — fan-out drills. Ten questions on the card ledger where the wrong answer comes from a duplicated join. The Capital One practice cut is built around exactly this.
3. Day 10 — grain drills. Ten questions where two grains give two numbers; write both, pick the one the wording asks for.
4. Day 11 — window and date mix. Period-over-period change with LAG, then top-N within a date range.
5. Day 12 — timed run 1. 70 minutes, 14 questions of your own, CSV in your chosen tool, one written SQL question at the end. Note where the time went.
6. Day 13 — fix the slow spots. Whatever ate the time on day 12 — usually loading and cleaning the CSV, not the SQL.
7. Day 14 — timed run 2, then stop. A second 70-minute run. The January 2023 Blind advice, from one candidate and worth exactly that, was to leave the written SQL for the end and move quickly through the dataset items; decide in this run whether that order suits you.

FAQ

Sources

Read on 7 September 2026. Nothing on this page comes from Capital One; every format claim is one of these, dated.

• Blind — "Has anyone attempted Code Signal assessment for data analyst position at Capital One?" (thread from September 2021; the 1 December 2021 reply reports a "70 minutes code signal assessment" for a Senior Data Analyst role).
• Blind — "Captial one Code signal for Data Analysis" (February 2022; "14 questions instead of just 4", tool choice, "one question is a database question you have to use SQL for"; the January 2023 reply on pacing).
• Blind — "Capital One Senior Data Analyst Codesignal Assessment" (thread from March 2022; the November 2022 reply: "14 questions", "csv data to answer multiple choice questions", "few sql questions").
• Blind — "Capital One CodeSignal for Data Analysts" (June 2025; "around 14–15 questions in 70 minutes", CSV files; the reply from a Capital One account that the format "has changed in last 4 years").
• linkjob.ai — "Preparation for Capital One Data Analyst CodeSignal Question", Peter Liu, 1 August 2025.
• extrabrain.app — "Capital One Data Analyst CodeSignal Questions: SQL, Python, and OA Prep", 11 February 2026.
• finalroundai.com — "Capital One CodeSignal Questions for Business Analyst" (undated; the analyst-track format and SQL topics).