Dune SQL Tutorial for Beginners: 10 Queries on Real Onchain Data

The fastest way to learn Dune SQL is to write ten small queries on real blockchain tables, in order of difficulty, and check every result. Reading the docs and watching walkthroughs gets you familiar with the editor. Writing the queries yourself, and finding out why your first answer was wrong, is what makes you able to use it.

This Dune SQL tutorial for beginners gives you those ten queries. Each one is a real exercise from WizardCamp, with the actual question, the reference solution, and the real result, sampled from the same Dune tables analysts use at work: ethereum.transactions, erc20_ethereum.evt_transfer, tokens.erc20, dex.trades, and tokens_solana.transfers. Every query also hides one piece of blockchain logic that generic SQL courses never teach, and that is the part worth remembering. You can practice all of them in WizardCamp, our blockchain SQL course.

TL;DR:

• DuneSQL is Trino for blockchains. DuneSQL is Dune's query engine, a fork of Trino with blockchain types such as varbinary for addresses and uint256 for token amounts.
• Standard SQL covers most of it. If you know standard SQL, you already know 90% of the syntax.
• Ten queries, basics to ranking. They go from COUNT and GROUP BY to CASE WHEN, DATE_TRUNC, joins, window functions, and DENSE_RANK on real Ethereum data, plus a Solana bonus.
• The data is the hard part. Failed transactions, token decimals, missing hours, and numbers too big for a normal integer trip people up, never the syntax.
• SQL gets you hired. 61% of the 456 live data roles on the Unchain Data job board mention SQL, while only 4% name Dune.
• Practice with a grader. Every query here is a graded WizardCamp exercise, the first three are free, and the grader shows exactly which rows are wrong.

What is Dune SQL?

Dune SQL (DuneSQL) is the SQL dialect you write on Dune, the onchain data platform. It is a fork of the Trino query engine, extended for blockchain data: addresses and hashes are stored as varbinary, and very large token amounts use uint256 and int256 types. Everything else is standard SQL: SELECT, WHERE, GROUP BY, joins, CTEs, and window functions all work the way you expect.

What makes Dune different from a SQL course is the data. You query decoded mainnet tables directly: raw transactions in ethereum.transactions, token transfer events in erc20_ethereum.evt_transfer, token metadata in tokens.erc20, swaps across DEXs in dex.trades, and Solana token transfers in tokens_solana.transfers. The ten queries below use samples of exactly those tables.

Practice as you read: try the first three queries free in WizardCamp. Every query is graded instantly on real Ethereum data, and the grader shows exactly which rows are wrong.

How to use this tutorial

Run each query in WizardCamp, where it is graded instantly, then rewrite it on Dune against the full table. WizardCamp runs on samples of real Dune data in your browser, with a few small changes you should know before you switch:

• Column names. Dune calls the sender and recipient columns from and to, which are reserved words in SQL, so they need double quotes on Dune. The WizardCamp tables rename them from_address and to_address, and rename hash to tx_hash where noted.
• Big integers. On Dune, raw token amounts are uint256. The WizardCamp sandbox uses HUGEINT, a 128-bit integer, for the same job, so where you see CAST(... AS HUGEINT) below, the Dune equivalent is a cast to uint256.
• Addresses. On Dune, addresses and hashes are varbinary and display as 0x... hex. The sandbox stores them as binary too, which is why a few solutions wrap them in lower(hex(...)) to print them.
• Samples, not full history. Each exercise uses a small, documented slice of mainnet, such as three blocks or one hour. The numbers are real, but they describe the sample. On Dune you run the same logic on the full table, and you filter on a date or block column first so the query stays fast.

Query 1: Count transactions by type

From WizardCamp's Ethereum Blockchain Fundamentals module ("Transaction Count by Type", Ethereum path). Free.

SQL concept: COUNT(*) and GROUP BY. Dune table: ethereum.transactions.

The first query every Dune beginner should write counts rows per category. Here the category is the transaction type: legacy, access list, EIP-1559, or blob.

SELECT type, COUNT(*) AS tx_count
FROM transactions
GROUP BY type
ORDER BY tx_count DESC, type ASC

The sample is 28 transactions from blocks 19500000 to 19500049, on March 23, 2024, just after the Dencun upgrade, picked so every type appears. It returns DynamicFee 10, AccessList 8, Legacy 6, and 3 with 4.

The lesson: on Dune, type is a text column. Types 0 to 2 have labels (Legacy, AccessList, DynamicFee), but EIP-4844 blob transactions are stored as the literal string '3'. A filter like WHERE type = 'BlobTransaction' returns zero rows and no error. Always look at the distinct values of a column before you filter on it.

Query 2: Total gas cost per sender

From WizardCamp's Ethereum Blockchain Fundamentals module ("Total Gas Cost Per Sender", Ethereum path). Free.

SQL concept: WHERE, SUM, and casting before you multiply. Dune table: ethereum.transactions.

The gas cost of one transaction is gas_used * gas_price, in wei. The question asks for the total per sender, counting successful transactions only.

SELECT
  from_address,
  SUM(CAST(gas_used AS HUGEINT) * CAST(gas_price AS HUGEINT)) AS total_gas_wei
FROM transactions
WHERE success = TRUE
GROUP BY from_address
ORDER BY total_gas_wei DESC

The sample holds 100 transactions from early blocks on January 13, 2024. 77 senders appear in it, but only 59 have at least one successful transaction. The other 18 only sent reverted transactions, 22 rows in all, so forgetting WHERE success = TRUE returns 77 rows instead of 59.

The lesson: reverted transactions still burn gas, so whether you include them depends on the question. And multiply first, then sum, with both columns cast to a big integer type. A regular BIGINT total overflows once a sender spends about 9.2 ETH of gas, which busy senders on mainnet can reach in a day. On Dune, the cast is to uint256.

Query 3: Classify transactions with CASE WHEN

From WizardCamp's Ethereum Blockchain Fundamentals module ("Transaction Size Classification", Ethereum path). Free.

SQL concept: CASE WHEN buckets. Dune table: ethereum.transactions.

Bucketing is how you turn a raw number into something a dashboard can show. Here, each transaction's ETH value (in wei, where 1 ETH is 10^18 wei) goes into a zero, small, or large bucket, and all transactions count, whether they succeeded or not.

SELECT
  CASE
    WHEN value = 0 THEN 'zero'
    WHEN value <= 1000000000000000000 THEN 'small'
    ELSE 'large'
  END AS size_label,
  COUNT(*) AS tx_count
FROM transactions
GROUP BY size_label
ORDER BY tx_count DESC

On 47 transactions from January 13, 2024, the result is zero 30, small 13, large 4.

The lesson: 30 of the 47 transactions carry no ETH at all. Zero-value transactions are typically contract calls, such as swaps and approvals, so value is a poor proxy for activity. And three of the four large transactions failed, all from the same sender, so they never moved any ETH. Add WHERE success = TRUE and large drops from 4 to 1. Neither answer is wrong in general. You need to know which one the question asks for, and say it out loud.

Query 4: Break an EIP-1559 fee into base fee and tip

From WizardCamp's Ethereum Blockchain Fundamentals module ("EIP-1559 Fee Breakdown", Ethereum path).

SQL concept: arithmetic on columns and filtering out NULLs. Dune table: ethereum.transactions.

Since EIP-1559, every fee has two parts: a base fee that is burned and a priority fee (the tip) that goes to the validator. ethereum.transactions has no base_fee_per_gas column, so you derive it as gas_price - priority_fee_per_gas.

SELECT
  lower(hex(hash)) AS tx_hash,
  CAST(gas_used AS HUGEINT) * CAST(gas_price - priority_fee_per_gas AS HUGEINT) AS base_fee_wei,
  CAST(gas_used AS HUGEINT) * CAST(priority_fee_per_gas AS HUGEINT) AS priority_fee_wei,
  CAST(gas_used AS HUGEINT) * CAST(gas_price AS HUGEINT) AS total_fee_wei
FROM transactions
WHERE type = 'DynamicFee'
ORDER BY total_fee_wei DESC

The sample is the first 40 transactions of block 19500000: 32 EIP-1559 transactions and 8 legacy ones. The most expensive paid 5,295,828,040,901,376 wei in total, about 0.0053 ETH, of which 495,670,984,152,432 wei, about 9.4%, was tip.

The lesson: the derived base fee is exactly 15,578,805,264 wei on every row, because the base fee is set per block, not per transaction. And the filter matters: legacy transactions have NULL in the priority fee columns, so without WHERE type = 'DynamicFee' their fee math returns NULL rows.

Query 5: Compute a 3-block rolling average gas price

From WizardCamp's Ethereum Blockchain Fundamentals module ("3-Block Rolling Average Gas Price", Ethereum path).

SQL concept: CTEs and a window function with a frame. Dune table: ethereum.transactions.

A rolling average smooths a noisy series. This query first averages gas price per block in a CTE, then averages that over the current block and the two before it.

WITH block_avgs AS (
  SELECT block_number, ROUND(AVG(gas_price) / 1e9, 2) AS avg_gas_price_gwei
  FROM transactions GROUP BY block_number
),
rolling AS (
  SELECT block_number, avg_gas_price_gwei,
    ROUND(AVG(avg_gas_price_gwei) OVER (ORDER BY block_number ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS rolling_3_block_avg_gwei
  FROM block_avgs
)
SELECT block_number, avg_gas_price_gwei, rolling_3_block_avg_gwei
FROM rolling ORDER BY block_number ASC

On the first four transactions of blocks 19000000 to 19000005, the result is:

The lesson: block 19000003 averages 150.72 gwei because one transaction in it paid 548 gwei, an MEV outlier. A single transaction is enough to drag a block average far above its neighbors, which is why analysts often prefer medians for gas. Also notice the first two rows: the window only has one and two blocks to average, so early values of any rolling metric are not comparable with later ones.

Query 6: Count transfers per hour with DATE_TRUNC

From WizardCamp's Token Analytics module ("Hourly Transfer Buckets", Ethereum path).

SQL concept: DATE_TRUNC for time series. Dune table: erc20_ethereum.evt_transfer.

Almost every Dune chart starts with a timestamp rounded down to an hour, day, or week. This query counts USDC transfers per hour.

SELECT
  DATE_TRUNC('hour', block_time) AS bucket_hour,
  COUNT(*) AS transfer_count
FROM erc20_transfers
GROUP BY DATE_TRUNC('hour', block_time)
ORDER BY bucket_hour ASC

On 15 USDC transfers from January 13, 2024, it returns three rows: 13:00 UTC with 5 transfers, 14:00 with 6, and 16:00 with 4.

The lesson: there is no 15:00 row. The exercise leaves that hour empty on purpose, because DATE_TRUNC never creates rows for hours with no data. On a chart, a missing hour looks like a smooth line instead of a drop to zero, so if gaps matter you need a calendar of hours to join against. Also, transfers at 14:59:59 belong to the 14:00 bucket, which the grader checks. On Dune the timestamp column on event tables is evt_block_time.

Query 7: Convert raw token amounts with decimals

From WizardCamp's Token Analytics module ("Scale ERC-20 Amounts by Decimals", Ethereum path).

SQL concept: JOIN. Dune tables: erc20_ethereum.evt_transfer and tokens.erc20.

Token transfer events store amounts as integers in the token's smallest unit. To get a human-readable amount you join each transfer to its token metadata and divide by 10 to the power of the token's decimals.

SELECT
  lower(hex(t.contract_address)) AS contract_address,
  m.symbol,
  ROUND(CAST(t.value AS DOUBLE) / POWER(10.0, m.decimals), 6) AS human_amount
FROM erc20_transfers t
JOIN token_metadata m ON t.contract_address = m.contract_address
ORDER BY human_amount DESC

The largest transfer in the sample is a raw value of 750000000000, which is 750,000 USDC because USDC has 6 decimals. A WETH transfer of 4919200245253660003 raw units is about 4.9192 WETH, because WETH has 18. The exercise also adds a synthetic token with 0 decimals to check that your query handles that edge case.

The lesson: never compare raw amounts across tokens. USDC uses 6 decimals, WBTC 8, and WETH 18, so a raw USDC number and a raw WETH number are not on the same scale. On Dune, join to tokens.erc20 on contract_address and filter it to blockchain = 'ethereum' so you do not match the same address on another chain.

Query 8: Daily DEX volume by project

From WizardCamp's DeFi Analytics module ("DEX Daily Swap Volume", Ethereum path).

SQL concept: grouping by two columns. Dune table: dex.trades.

dex.trades is one of the most used tables on Dune: one row per swap across DEXs, with a USD amount already computed. Grouping by day and project gives you the classic volume chart.

SELECT
  block_date,
  project,
  COUNT(*) AS swap_count,
  SUM(amount_usd) AS total_volume_usd
FROM trades
GROUP BY block_date, project
ORDER BY block_date ASC, project ASC

The sample takes the first seven swaps per project per day for Curve, SushiSwap, and Uniswap, from January 13 to 15, 2024, so it returns 9 rows of 7 swaps each. Curve's seven swaps on January 15 add up to about $2.78 million, against about $135,000 for Uniswap's seven.

The lesson: swap count and volume tell different stories. The same number of swaps can carry 20 times more dollars on one venue, so always chart both. On Dune, dex.trades covers many chains, so add a blockchain filter, and filter on block_date or block_month first to keep the query fast. SUM(amount_usd) also skips swaps with no USD price without warning you.

Query 9: Stablecoin market share with a window function

From WizardCamp's Stablecoin Analytics module ("Stablecoin Market Share", Ethereum path).

SQL concept: SUM(...) OVER () for a share of total. Dune source: stablecoin transfers from tokens.transfers.

Market share means each group divided by the grand total. You can get the grand total without a subquery by wrapping the aggregate in a window function.

SELECT
  token_symbol,
  ROUND(SUM(amount_usd), 2) AS total_volume_usd,
  ROUND(100.0 * SUM(amount_usd) / SUM(SUM(amount_usd)) OVER(), 2) AS market_share_pct
FROM stablecoin_transfers
GROUP BY token_symbol
ORDER BY market_share_pct DESC

On the 35 stablecoin transfers in Ethereum block 20100000 (June 15, 2024), USDT has 84.16% of the volume, USDC 11.85%, and USDe 4.0%.

The lesson: SUM(SUM(amount_usd)) OVER () reads oddly at first. The inner SUM runs per group, then the window SUM adds the group totals across all rows. Also notice that the shares add up to 100.01, because each one is rounded separately. Use 100.0 rather than 100 so no engine falls back to integer division.

Query 10: Find the top gas spender each day with DENSE_RANK

From WizardCamp's Ethereum Blockchain Fundamentals module ("Daily Top Gas Spender", Ethereum path).

SQL concept: DENSE_RANK() OVER (PARTITION BY ...) and filtering in an outer query. Dune table: ethereum.transactions.

"Top N per group" is the most common window function question in onchain analytics, and in interviews. You aggregate per day and sender, rank within each day, then keep rank 1.

WITH daily_gas AS (
  SELECT block_date, lower(hex(from_address)) AS from_address,
    SUM(CAST(gas_used AS HUGEINT) * CAST(gas_price AS HUGEINT)) AS total_gas_cost
  FROM transactions GROUP BY block_date, from_address
),
ranked AS (
  SELECT block_date, from_address, total_gas_cost,
    DENSE_RANK() OVER (PARTITION BY block_date ORDER BY total_gas_cost DESC) AS daily_rank
  FROM daily_gas
)
SELECT block_date, from_address, total_gas_cost, daily_rank FROM ranked WHERE daily_rank = 1 ORDER BY block_date ASC, from_address ASC

The sample is the 15 most expensive transactions in a 20-block slice of each day from January 13 to 15, 2024. The same address, 0xae2fc483527b8ef99eb5d9b44875f005ba1fae13, tops January 13 and 14 with 333,094,865,187,677,929 and 332,217,996,621,293,712 wei, about 0.33 ETH each. On January 15, 0x8346c4d409f790622afe550e07ab7e207f52c000 leads with about 0.17 ETH.

The lesson: you cannot filter on a window function in the same SELECT, so the rank goes into a CTE and the outer query filters it. DENSE_RANK keeps every address tied for first, where ROW_NUMBER would silently drop one. And both totals are 18-digit numbers, far above 2^53, the largest integer a DOUBLE can store exactly. Keep wei values as big integers until the very last step.

Bonus: the same patterns on Solana

From WizardCamp's SPL Tokens and the Accounts Model module ("USDC and Wrapped SOL Transfers and Volume per Day", Solana path).

Everything above transfers to Solana, with new traps. This query counts daily transfers and volume for USDC and wrapped SOL, from a seven-day aggregate of tokens_solana.transfers joined to tokens_solana.fungible.

SELECT
  t.block_date,
  t.token_mint_address,
  sum(t.transfers) AS transfers,
  round(sum(t.amount_raw_sum) / power(10, max(f.decimals)), 2) AS volume
FROM token_transfers_7d t
JOIN fungible_tokens f ON f.token_mint_address = t.token_mint_address
WHERE t.token_mint_address IN ('EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v', 'So11111111111111111111111111111111111111112')
GROUP BY t.block_date, t.token_mint_address
ORDER BY t.block_date, t.token_mint_address

On August 24, 2026, USDC had 13,105,420 transfers on Solana, moving about 16.1 billion USDC, and wrapped SOL had 67,284,717 transfers. Between August 24 and 30, USDC ran between about 10.7 million and 15.7 million transfers a day, and wrapped SOL between 62.7 million and 74.1 million.

The lesson: it is the same decimals join as Query 7, but USDC has 6 decimals and wrapped SOL has 9, so a hardcoded power of ten gets one of them wrong. And you join on the mint address, never the symbol: 16 of the 100 symbols in this dataset belong to more than one mint. The full Solana path is on the Solana WizardCamp page.

The 10 Dune SQL patterns at a glance

Do you need Dune to get a crypto data job?

No. You need SQL, and Dune is the best free place to practice it on real chain data. As of October 25, 2026, the Unchain Data job board lists 456 live data roles at 145 companies, across analyst, engineer, scientist, head of data, research, and compliance analyst roles. 276 of them (61%) mention SQL. Only 18 (4%) name Dune, and 15 of those 18 also ask for SQL. For data analyst roles alone, 70 of 90 (78%) mention SQL and 2 name Dune.

The companies that do name Dune are worth knowing, though: they include 1inch, Binance, Kraken, LI.FI, Mysten Labs, Ondo Finance, Solana Foundation, and Dune itself. You can browse the live roles on the Web3 data analyst job board.

The practical takeaway: learn Dune to practice and to build a public portfolio, but prepare for interviews as SQL interviews on blockchain tables. The patterns above, especially decimal scaling and ranking within groups, come up again in crypto data analyst interview questions.

What to learn after these 10 queries

Once these feel easy, move from single tables to the logic of real protocols:

1. Rebuild balances from transfers and count holders correctly. Both are covered with real WizardCamp exercises in the interview questions guide.
2. Learn lending and DEX mechanics. The Aave utilization walkthrough in Learn Onchain SQL: Inside WizardCamp shows why a correct query can still produce a misleading number.
3. Build one serious dashboard. How to Build a Dune Dashboard for Your DeFi Protocol covers TVL, volume, retention, and how to reconcile your numbers.
4. Plan the career side. How to Become a Crypto Data Analyst covers portfolios and where to apply, and the Web3 data analyst salary breakdown covers what the role pays.

If you would rather practice with instant feedback than debug alone on Dune, WizardCamp has 85 Ethereum and 78 Solana exercises in this format. Practice mode lets you pick a topic and a difficulty, and the grader tells you which rows are wrong.

Frequently Asked Questions

Is Dune SQL the same as regular SQL?

Mostly. DuneSQL is a fork of Trino, so standard SQL works: SELECT, joins, CTEs, and window functions. The differences are blockchain types, such as varbinary for addresses and hashes and uint256 for large token amounts, and a few column names like from and to that need double quotes because they are reserved words.

How long does it take to learn Dune SQL?

If you already know basic SQL, you can write the ten queries in this tutorial in a few sessions. If you are new to SQL, start with the basics first: WizardCamp's free SQL Foundations module covers SELECT, filtering, aggregation, joins, and CASE WHEN in six exercises before you touch chain data. The slower part is learning the data itself: decimals, failed transactions, routers, and Solana token accounts.

Is Dune free to use?

Dune has a free tier that lets you write and run your own queries on its public tables. What it does not do is tell you whether your answer is right. WizardCamp's first exercises on each path are free too, and they are graded instantly against the expected result.

What tables should a beginner start with on Dune?

Start with ethereum.transactions for transactions and gas, erc20_ethereum.evt_transfer with tokens.erc20 for token transfers and decimals, and dex.trades for swaps. On Solana, start with tokens_solana.transfers and tokens_solana.fungible. Every query in this tutorial uses a sample of one of these.

Do crypto companies test Dune SQL in interviews?

They test SQL on blockchain data, usually in their own warehouse rather than on Dune. On the Unchain Data job board, 61% of live data roles mention SQL, while 4% name Dune. A Dune portfolio helps you get the interview. Fluent SQL on transaction, transfer, and trade tables helps you pass it.

Key Takeaways

• DuneSQL is Trino plus blockchain types. Standard SQL works. Watch for varbinary addresses, uint256 amounts, and quoted from and to columns.
• Learn in order: GROUP BY, CASE WHEN, DATE_TRUNC, joins, then window functions and ranking.
• The trap is never the syntax. Failed transactions, token decimals, empty hours, and big integers produce clean but wrong numbers.
• Check distinct values before you filter. Blob transactions are '3', not a label.
• Keep raw amounts as big integers until the final step, and never compare raw amounts across tokens.
• SQL gets you hired, Dune gets you practice. 61% of live data roles on the Unchain Data job board mention SQL, 4% name Dune.

Practice these queries on WizardCamp for free →