Crypto Data Analyst Interview Questions: 7 Onchain SQL Problems

Most crypto data analyst interview questions are not about crypto trivia. They are SQL problems on transaction tables, and the trap in each one is a piece of blockchain logic that generic SQL practice never taught you. I have interviewed analysts for a centralized exchange and for DeFi protocols, and the candidates who fail rarely fail on syntax. They fail because their query runs, returns a clean number, and the number is wrong.

This guide walks through seven onchain SQL problems in the style crypto data teams actually ask. Every one of them is a real exercise from WizardCamp, with its actual table, question, and reference solution, plus the trap the grader is built to catch. WizardCamp tables are samples of real Ethereum and Solana data from Dune tables such as ethereum.transactions, dex.trades, and solana_utils.latest_balances, with a few columns renamed for the sandbox, so the numbers below are real numbers.

TL;DR:

• Crypto data analyst interviews are SQL interviews. 75 of the 97 live data analyst roles on the Unchain Data job board (77%) mention SQL, while only 2 name Dune.
• The SQL itself is rarely the hard part. Each question hides a blockchain trap: tie-breaks, raw token units, rows counted as people, NULL flows, Solana token accounts.
• The seven problems below are real WizardCamp exercises: first transaction per wallet, chain market share, top holders from transfers, day-over-day volume, new vs returning traders, lending utilization, and Solana holder counts.
• Say your assumptions out loud. Interviewers grade your metric definition as much as your query.
• Drill on real chain data before a live round. WizardCamp grades every query instantly and shows exactly which rows are wrong.

What do crypto data analyst interviews actually test?

They test SQL first, then whether you understand what the onchain data means. At most crypto companies the technical round is a live or take-home SQL exercise on transaction, transfer, or trade tables, followed by questions about how you defined the metric.

The job data backs this up. As of October 18, 2026, the Unchain Data job board lists 97 live data analyst roles. 75 of them (77%) mention SQL in the title or description, 62 (64%) mention Python, and only 2 name Dune. Just 15 use the word onchain at all. In other words, companies hire for strong SQL and expect the blockchain context to show up in how you use it. You can browse the same roles on the Web3 data analyst job board.

That is why memorizing "What is a blockchain?" answers does not help much. The questions that decide the round look like the seven below.

Question 1: Find each wallet's first-ever transaction

From WizardCamp's Ethereum Blockchain Fundamentals module ("First Transaction Per Wallet", Ethereum path).

The strong answer ranks each wallet's transactions with ROW_NUMBER() and keeps rank 1 in an outer query. First transactions are how teams measure new users and activation, so some version of this question shows up in almost every loop.

The WizardCamp table is transactions, sampled from ethereum.transactions (blocks 19000000 to 19000049), with Dune's from and hash renamed to from_address and tx_hash. The task: return each wallet's earliest tx_hash, block_time, and block_number, ordered by block.

The usual first attempt is SELECT from_address, MIN(block_time) ... GROUP BY from_address. It gives you the time but not the transaction, and joining back on block_time returns two rows whenever a wallet sent two transactions in the same block. The reference solution:

WITH ranked AS (
  SELECT
    from_address,
    tx_hash,
    block_time,
    block_number,
    ROW_NUMBER() OVER (
      PARTITION BY from_address
      ORDER BY block_time, tx_hash
    ) AS rn
  FROM transactions
)
SELECT
  lower(hex(from_address)) AS from_address,
  lower(hex(tx_hash))      AS tx_hash,
  block_time,
  block_number
FROM ranked
WHERE rn = 1
ORDER BY block_number ASC, from_address ASC

Two details earn the points. The tx_hash tiebreaker guarantees exactly one row per wallet when two transactions share a timestamp, so 12 wallets in the sample produce exactly 12 rows. And WHERE rn = 1 has to live in the outer query, because window functions run after WHERE in SQL's logical order. On Dune itself, remember to quote "from" and "hash".

Question 2: What share of DEX volume does each chain have?

From WizardCamp's Uniswap Analytics pack ("Chain Market Share by Volume", Ethereum path).

Sum volume per chain, then divide by the grand total with a window function over the aggregate. Interviewers use market share questions to check that you know which total you are dividing by.

The table is uniswap_swaps, one row per chain per day, derived from Uniswap Labs' public Uniswap V4 volume query on dex.trades: six chains over five days. The reference solution is one line of logic:

SELECT
  blockchain,
  ROUND(100.0 * SUM(volume) / SUM(SUM(volume)) OVER (), 2) AS volume_share_pct
FROM uniswap_swaps
GROUP BY blockchain
ORDER BY volume_share_pct DESC

SUM(SUM(volume)) OVER () is the move many candidates have never seen: the inner SUM aggregates per chain, the outer window SUM adds those up across every chain. The trap the grader tests is the denominator. Partition the window by day and you get daily shares, which is a different question. In this sample Unichain comes out at 89.54% of volume and Ethereum at 10.37%, a split that should make you ask what is driving it before you present it.

In a live round, add the caveats yourself: 100.0 rather than 100 avoids integer division in some engines, a trades table counts each trade from both token sides, and tokens without a price have a NULL USD amount that SUM skips silently.

Question 3: Rebuild token balances from transfers and rank the top holders

From WizardCamp's Token Analytics module ("Top Token Holders by Net Balance", Ethereum path).

Add every inbound transfer, subtract every outbound one, sum per address and token, then rank. This is the most common "do you really understand the data" question, because a balance is a stock you rebuild from flows.

The tables are erc20_transfers (from erc20_ethereum.evt_transfer, with value as a raw uint256) and token_metadata (from tokens.erc20) for WBTC, USDC, and WETH. The task: the top 3 holders per token with DENSE_RANK.

WITH flows AS (
  SELECT contract_address, to_address AS address, CAST(value AS HUGEINT) AS amount
  FROM erc20_transfers
  UNION ALL
  SELECT contract_address, from_address AS address, -CAST(value AS HUGEINT) AS amount
  FROM erc20_transfers
),
balances AS (
  SELECT f.contract_address, lower(hex(f.address)) AS address, m.symbol,
    SUM(f.amount) AS net_balance
  FROM flows f
  JOIN token_metadata m ON f.contract_address = m.contract_address
  GROUP BY f.contract_address, f.address, m.symbol
),
ranked AS (
  SELECT symbol, address, net_balance,
    DENSE_RANK() OVER (PARTITION BY symbol ORDER BY net_balance DESC) AS holder_rank
  FROM balances
)
SELECT symbol, address, net_balance, holder_rank
FROM ranked WHERE holder_rank <= 3
ORDER BY symbol ASC, holder_rank ASC

Interviewers watch for four details. UNION ALL, not UNION, because UNION deduplicates and two identical transfers would collapse into one. Precision: raw token values overflow a regular BIGINT, which is why the solution casts to HUGEINT. Decimals: the result is in raw units, so the top USDC holder's 3,734,158,092,747 is about 3.73 million USDC once you divide by 10^6, and comparing raw numbers across tokens is meaningless (WizardCamp drills this separately in "Scale ERC-20 Amounts by Decimals"). And time range: transfers over a sample window give net flow, not a true balance. A real balance needs full history, and on full history the zero address shows a huge negative number because mints come from it.

Question 4: Compute day-over-day volume change per chain

From WizardCamp's Uniswap Analytics pack ("Day-over-Day Volume Change", Ethereum path).

Use LAG() partitioned by chain and ordered by day, then subtract. The traps are forgetting the partition and not knowing what happens on the first day.

Same uniswap_swaps table as question 2. The reference solution:

SELECT
  blockchain,
  period,
  volume,
  LAG(volume) OVER (PARTITION BY blockchain ORDER BY period) AS prev_volume,
  volume - LAG(volume) OVER (PARTITION BY blockchain ORDER BY period) AS volume_change
FROM uniswap_swaps
ORDER BY blockchain ASC, period ASC

Without PARTITION BY blockchain, Arbitrum's first day gets compared with the last day of whatever chain sorted before it. With it, the first day of each chain correctly returns NULL for both prev_volume and volume_change, which is exactly what the grader's edge case checks. Say why NULL is right: there is no previous day, and a zero would read as a real change.

Then add what the exercise does not ask but a good analyst mentions unprompted. On a raw trades table, drop today's partial day or it will always look like a crash. And LAG compares with the previous row, not the previous calendar day, so a day with no trades needs a date spine to fill the gap. Day-over-day change is one of the six questions in the Uniswap Analytics certificate.

Question 5: How many traders each week are new vs returning?

From WizardCamp's DEXs, Venues and Volume module ("New vs Returning Traders per Week", Solana path).

Find each trader's first week with its own aggregate, join it back, and classify every active week. This is the building block of every retention cohort.

The table is trader_weeks_sample: one row per trader per active week, a 1 in 10,000 sample of dex_solana.trades from August 1 to August 30, 2026. The reference solution:

WITH first_week AS (
  SELECT trader_id, min(week_start) AS first_week
  FROM trader_weeks_sample
  GROUP BY trader_id
)
SELECT
  w.week_start,
  count(*) AS traders,
  count_if(w.week_start = f.first_week) AS new_traders,
  count_if(w.week_start > f.first_week) AS returning_traders
FROM trader_weeks_sample w
JOIN first_week f ON f.trader_id = w.trader_id
GROUP BY w.week_start
ORDER BY w.week_start

The exercise is built around three traps. First, the table has a tempting first_day_in_week column, but it is the first active day inside that week, not the trader's first day ever, so a row cannot classify itself. Second, the sample has 1,320 rows but only 944 traders. Count rows as new and you inflate new users by 40%. The grader checks that the new traders sum to exactly 944. Third, "new" only means new inside the window. The window starts on a Saturday, so the first bucket is a two-day stub that is 100% new by construction (99 new, 0 returning). Label it or drop it, never present it as a finding.

On a raw trades table there is a fourth trap: the user column. Aggregator and router contracts can appear as the trader and look like one hyper-loyal user. I go deeper on retention definitions in How to Measure Protocol Retention Right.

Question 6: What is the utilization of a lending market?

From WizardCamp's Lending module ("Utilization Proxy from Cumulative Flows", Solana path).

Utilization is net borrowed divided by net supplied, per reserve and per token. Interviewers ask it to see whether you net out withdrawals and repayments, handle NULLs, and know the difference between a stock and a flow.

The table is reserve_flows: 35 days of Kamino and marginfi activity, one row per day, protocol, market, reserve, and mint, with raw deposit, withdraw, borrow, and repay amounts. The task: utilization for the top 10 USDC reserves by net supply.

WITH cumulative AS (
  SELECT
    protocol,
    market_or_group,
    reserve_or_bank,
    max(symbol) AS symbol,
    sum(coalesce(deposit_raw, 0) - coalesce(withdraw_raw, 0)) AS net_supply_raw,
    sum(coalesce(borrow_raw, 0) - coalesce(repay_raw, 0)) AS net_borrow_raw
  FROM reserve_flows
  WHERE mint = 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v'
  GROUP BY protocol, market_or_group, reserve_or_bank
)
SELECT
  protocol,
  market_or_group,
  reserve_or_bank,
  symbol,
  round(net_supply_raw, 2) AS net_supply_raw,
  round(net_borrow_raw, 2) AS net_borrow_raw,
  round(100.0 * net_borrow_raw / nullif(net_supply_raw, 0), 2) AS utilization_pct
FROM cumulative
WHERE net_supply_raw > 0
ORDER BY net_supply_raw DESC, protocol, market_or_group, reserve_or_bank
LIMIT 10

Each line answers a trap. coalesce comes before the subtraction because on a day with only withdrawals, deposit_raw is NULL, NULL - withdraw_raw is NULL, and the whole row silently drops out of the sum. The USDC filter comes before the ranking because raw base units mean different amounts for tokens with different decimals. nullif protects the division. And both sides are summed before dividing, never averaged day by day. The largest Kamino USDC reserve in the sample lands at 85.34%.

The point that separates strong candidates: these sums start at zero on the first day of the window, so this is utilization of the flows since then, not of the reserve's actual balance. A reserve that started the window large can show a small or even negative change, and the exercise explicitly says not to clamp it. If you want the true level, you need balances from the start of history. For an Ethereum version of lending analytics, see the Aave walkthrough in Learn Onchain SQL: Inside WizardCamp.

Question 7: How many wallets hold a token on Solana?

From WizardCamp's SPL Tokens and the Accounts Model module ("Count the Holders of a Token", Solana path).

Count distinct owners with a positive balance, not token accounts. On Solana a wallet does not hold tokens directly. It owns token accounts, one or more per mint.

The table is token_holder_accounts: every token account of one mint from solana_utils.latest_balances, with the token account in address and the owning wallet in token_balance_owner. The reference solution:

SELECT count(DISTINCT token_balance_owner) AS holders
FROM token_holder_accounts
WHERE token_mint_address = 'nmHW2MM5PziaJG1K7wxuHCpJHVPCcV7jVf9kyFehTbE'
  AND token_balance > 0

The exercise shows how far off the wrong answers are. Counting rows returns 3,188, which is token accounts. Counting distinct owners without the balance filter returns 3,187, because almost every dormant account belongs to a different wallet. The right answer is 76. The balance filter is what moves the number most, and the distinct owner is what makes it a holder count.

The follow-ups are about program-owned accounts: liquidity pools, vaults, and exchange wallets also own token accounts, so a serious holder analysis labels or excludes them. Solana addresses are base58 strings, not hex, which also trips people who move from EVM tables.

The conceptual questions that come after the SQL

After the live query, most interviewers switch to short conceptual questions. Answer each one in two or three sentences and tie it to a metric.

• Why does one swap show up as several rows? A single transaction can route through multiple pools, and each hop emits its own swap event and token transfers. Aggregate at the transaction level when you want "trades by users."
• What is the difference between a transaction and a trace? A transaction is what the sender signed. Traces are the internal calls it triggered between contracts, which is where value often actually moves.
• How would you spot wash trading or sybil activity? Look for wallets funded from the same source, trading in loops with each other, or appearing right before an airdrop snapshot and disappearing after.
• Why do decimals matter? Raw token amounts are integers. If you compare raw amounts without dividing by 10 to the power of the token's decimals, one USDC (6 decimals) looks a trillion times smaller than one unit of an 18 decimal token.
• How would you define an active user for this protocol? There is no universal answer, which is the point. Propose a definition tied to the protocol's value (a swap, a deposit, a borrow), then say what it excludes.

How should you prepare in the two weeks before the interview?

Do reps on real chain data, timed, without looking at solutions. Reading answers trains recognition; interviews test recall on tables you have not seen before.

A plan that works:

1. Days 1 to 3: lock in the fundamentals. Joins, GROUP BY, CASE WHEN, CTEs. If any of these are slow, start with SQL Foundations, which is free on WizardCamp.
2. Days 4 to 7: window functions on chain data. ROW_NUMBER for first actions, LAG for change, SUM() OVER () for shares. These cover questions 1 to 4 above.
3. Days 8 to 11: the ecosystem you are interviewing for. EVM DeFi roles test swaps, lending, and traces. Solana roles test the accounts model, DEX aggregators, and memecoin launches, which is where questions 5 to 7 come from.
4. Days 12 to 14: timed drills. Give yourself 20 minutes per question and talk through your assumptions out loud, as you would in the room.

WizardCamp was built for exactly this stretch. All seven problems above come from it, alongside 85 Ethereum and 78 Solana exercises in total, and every query is graded instantly against real Ethereum or Solana data, showing exactly which rows are wrong. Practice mode lets you pick any topic and any difficulty, from beginner to brutal. If you want the broader career picture, including portfolios and which companies to target first, read How to Become a Crypto Data Analyst and 7 Best Web3 Analytics Portfolios to Study. For what the role pays once you land it, see the Web3 data analyst salary breakdown.

Frequently Asked Questions

What SQL questions are asked in a crypto data analyst interview?

Most are aggregation and window function problems on transaction, transfer, and trade tables: first transaction per wallet, market share, balances rebuilt from transfers, day-over-day change, new vs returning users, and lending utilization. The SQL is standard. The difficulty is the blockchain logic, such as raw token units, NULL flows, routers, and Solana token accounts.

Do I need to know Dune for a crypto data analyst interview?

Not necessarily. Of the 97 live data analyst roles on the Unchain Data job board on October 18, 2026, only 2 named Dune, while 75 mentioned SQL. Dune is a great place to practice and build a portfolio, but the interview usually tests SQL and onchain reasoning on the company's own warehouse.

How hard are blockchain SQL interview questions?

The syntax is intermediate: CTEs, joins, and window functions. What makes them hard is that a wrong query still returns a plausible number. In WizardCamp's Solana holder exercise, the two most common wrong answers return 3,188 and 3,187 while the right answer is 76.

Are Solana analyst interviews different from Ethereum ones?

Yes, mainly because of the accounts model. Solana wallets own token accounts, so holder counts, balances, and transfers all need a join or a distinct on the owner. Addresses are base58 strings, and DEX activity is heavily routed through aggregators, so attributing a trade to a user takes extra care.

How can I practice onchain SQL for free?

WizardCamp includes free exercises on both paths: SQL Foundations plus the first three Ethereum or Solana exercises, all graded instantly on real chain data. The seven exercises in this guide sit in the paid modules. Dune is also free for writing your own queries, though it does not check whether your answer is right.

Key Takeaways

• Crypto data analyst interviews are SQL interviews. 77% of live data analyst roles on the Unchain Data job board mention SQL. Only 2 of 97 name Dune.
• The trap is never the syntax. Missing tie-breaks, raw token units, rows counted as people, and NULL flows produce clean but wrong numbers.
• Define the metric before you query it. Saying what "new," "share," or "holder" means is half of the grade.
• Stocks are not flows. Balances and utilization built from a window of transfers are changes, not levels.
• Solana changes the unit of analysis. Count owners with a positive balance, not token accounts.
• Practice on real chain data, timed. Recall under pressure is what the live round tests.

Start practicing on WizardCamp for free →