Writing Efficient Queries

This guide provides practical do’s and don’ts specifically tailored to DuneSQL and Dune’s architecture, allowing you to optimize credit spend.

Key Principles

Dune’s query engine is optimized for blockchain data analysis with time-partitioned tables and columnar storage. Understanding the DuneSQL architecture will help you write queries that execute faster and consume fewer resources.

✅ DO’s: Query Optimization Best Practices

1. Leverage Time-Based Partitioning

Dune partitions most tables by block_date or by block_time Check on the data explorer (left-side panel) to see which fields are used as partitions. Always include time filters to enable partition pruning. Cross-chain tables: Large cross-chain tables like tokens.transfers, dex.trades, and evms.erc20_evt_transfers are most often also partitioned by blockchain in addition to time. Wherever possible, specify the blockchain filter along with time filters to dramatically reduce the amount of data scanned.

-- ✅ GOOD: Filters by block_date to enable partition pruning
SELECT 
    hash,
    "from",
    "to",
    value
FROM base.transactions
WHERE block_date >= TIMESTAMP '2025-09-01 00:00:00'
  AND block_date < TIMESTAMP '2025-10-02 00:00:00'
  AND "to" = 0x827922686190790b37229fd06084350E74485b72

-- ✅ EXCELLENT: Filters by both blockchain and time for cross-chain tables
SELECT 
    block_time,
    tx_hash,
    "from",
    "to",
    amount_usd
FROM dex.trades
WHERE blockchain = 'ethereum'
  AND block_time >= TIMESTAMP '2024-10-01'
  AND block_time < TIMESTAMP '2024-11-01'

2. Select Only Required Columns

Specify only the columns you need. Dune’s columnar storage makes this especially effective.

-- ✅ GOOD: Selects only necessary columns
SELECT 
    evt_block_time,
    "from",
    "to", 
    value
FROM erc20_ethereum.evt_Transfer
WHERE contract_address = 0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48
  AND evt_block_time >= NOW() - INTERVAL '7' DAY

3. Use Efficient JOIN Strategies

Put time filters in the ON clause and join on indexed columns when possible.

-- ✅ GOOD: Efficient join with time filter in ON clause
SELECT 
    t.hash,
    t.block_time,
    l.topic0,
    l.data
FROM ethereum.transactions t
INNER JOIN ethereum.logs l 
    ON t.hash = l.tx_hash
    AND t.block_date = l.block_date
    AND l.block_date >= TIMESTAMP '2024-10-01'
    AND l.block_date < TIMESTAMP '2024-10-02'
WHERE t."to" = 0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48
    AND t.block_date >= TIMESTAMP '2024-10-01'
    AND t.block_date < TIMESTAMP '2024-10-02'

4. Use CTEs for Complex Logic

Break complex queries into readable Common Table Expressions.

-- ✅ GOOD: Using CTEs for better performance and readability
WITH daily_volumes AS (
    SELECT 
        block_date as trade_date,
        SUM(amount_usd) as daily_volume
    FROM dex.trades
    WHERE blockchain = 'ethereum'
      AND block_date >= TIMESTAMP '2024-09-01'     
      AND block_date < TIMESTAMP '2024-10-01'
    GROUP BY block_date
),
volume_metrics AS (
    SELECT 
        trade_date,
        daily_volume,
        AVG(daily_volume) OVER (
            ORDER BY trade_date 
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ) as rolling_7day_avg
    FROM daily_volumes
)
SELECT * FROM volume_metrics
ORDER BY trade_date DESC

5. Use LIMIT with Large Result Sets

Always use LIMIT when you don’t need all results, especially for exploratory queries.

-- ✅ GOOD: Using LIMIT for large result sets
SELECT 
    hash,
    block_time,
    gas_used,
    gas_price
FROM ethereum.transactions
WHERE block_time >= TIMESTAMP '2024-10-01'
ORDER BY gas_price DESC
LIMIT 1000

Use Curated Data Tables

Leverage Dune’s curated tables which are pre-computed and optimized rather than use raw logs and traces.

Use Materialized Views

Materialized views are a way to store the results of a query in a table that can be queried like any other table. This is useful when you have a query that takes a long time to run, as you can re-use the results without having to re-execute your whole query.

Use Incremental Queries for Recurring Workloads

For scheduled queries or dashboards that refresh regularly, Incremental Queries can reduce costs by up to 100x by only processing new data since your last run. Instead of rescanning years of historical data on every refresh, incremental queries:

Load your previous results automatically
Query only the new data since your last checkpoint
Combine old + new results efficiently

WITH prev AS (
    SELECT * FROM TABLE(previous.query.result(
        schema => DESCRIPTOR(day TIMESTAMP(3), tx_count BIGINT)
    ))
),
checkpoint AS (
    SELECT COALESCE(MAX(day), TIMESTAMP '2015-07-30') - INTERVAL '1' DAY AS cutoff FROM prev
)
-- Keep old data
SELECT day, tx_count FROM prev WHERE day < (SELECT cutoff FROM checkpoint)
UNION ALL
-- Query only new data
SELECT date_trunc('day', block_time) AS day, COUNT(*) AS tx_count
FROM ethereum.transactions
WHERE block_time >= (SELECT cutoff FROM checkpoint)
GROUP BY 1

This is ideal for daily aggregations, hourly metrics, and any time-series analysis that runs on a schedule. See the full documentation for more patterns and examples.

Minimize Redundant Lookups in Automated Pipelines

In automated pipelines (dbt model runs, scheduled workflows), minimize redundant metadata and schema lookups such as repeated DESCRIBE, SHOW, and ref/source resolution. Each of these occupies the query engine and consumes credits, so reusing or caching results where possible keeps automated runs lean.

Query Performance Troubleshooting

Monitor query execution time in the Dune interface
Check the query plan for full table scans (run EXPLAIN ANALYZE YOUR_QUERY)
Consider query complexity vs. result value trade-offs

❌ DON’Ts: Query Anti-Patterns to Avoid

1. DON’T Use `SELECT *` on Large Tables

Avoid selecting all columns, especially on transaction and log tables.

-- ❌ BAD: Select all columns unnecessarily
SELECT * 
FROM ethereum.transactions
WHERE block_time >= TIMESTAMP '2024-10-01'

-- ✅ GOOD: Select only needed columns
SELECT hash, from_address, to_address, value
FROM ethereum.transactions  
WHERE block_time >= TIMESTAMP '2024-10-01'

2. DON’T Create Unnecessary Subqueries

Avoid nested subqueries when JOINs or window functions work better.

-- ❌ BAD: Inefficient correlated subquery
SELECT 
    b1.number,
    b1.gas_used,
    (
        SELECT AVG(b2.gas_used)
        FROM ethereum.blocks b2
        WHERE b2.time >= TIMESTAMP '2024-10-01'
          AND b2.number <= b1.number
          AND b2.number > b1.number - 100
    ) AS avg_100_blocks
FROM ethereum.blocks b1
WHERE b1.time >= TIMESTAMP '2024-10-01';

-- ✅ GOOD: Use window functions instead
WITH gas_used AS (
    SELECT 
        number,
        gas_used,
        AVG(gas_used) OVER (
            ORDER BY time
            ROWS BETWEEN 99 PRECEDING AND CURRENT ROW
        ) as avg_100_blocks
    FROM ethereum.blocks
    WHERE time >= TIMESTAMP '2024-10-01'
) 
SELECT 
    number,
    gas_used,
    avg_100_blocks
FROM gas_used

3. DON’T Use ORDER BY Without LIMIT on Large Results

Sorting large result sets is expensive.

-- ❌ BAD: Ordering large result set without limit
SELECT hash, gas_price
FROM ethereum.transactions
WHERE block_time >= TIMESTAMP '2024-10-01'
ORDER BY gas_price DESC

-- ✅ GOOD: Add LIMIT when ordering
SELECT hash, gas_price
FROM ethereum.transactions
WHERE block_time >= TIMESTAMP '2024-10-01'
ORDER BY gas_price DESC
LIMIT 10000

Summary

Efficient DuneSQL queries on Dune require understanding the platform’s time-partitioned architecture. Focus on:

Time filtering with time fields for partition pruning
Column selection to leverage columnar storage
Strategic joins with proper filter placement
Avoiding anti-patterns that prevent optimizations

Functionality

DuneSQL

Functions & Operators

Core SQL

Aggregate & Window

Data Types

String & Text

Math & Statistics

Date & Time

Blockchain Functions

External Data

System & Compatibility

Writing Efficient Queries

Key Principles

✅ DO’s: Query Optimization Best Practices

1. Leverage Time-Based Partitioning

2. Select Only Required Columns

3. Use Efficient JOIN Strategies

4. Use CTEs for Complex Logic

5. Use LIMIT with Large Result Sets

Use Curated Data Tables

Use Materialized Views

Use Incremental Queries for Recurring Workloads

Minimize Redundant Lookups in Automated Pipelines

Query Performance Troubleshooting

❌ DON’Ts: Query Anti-Patterns to Avoid

1. DON’T Use `SELECT *` on Large Tables

2. DON’T Create Unnecessary Subqueries

3. DON’T Use ORDER BY Without LIMIT on Large Results

Summary

​Key Principles

​✅ DO’s: Query Optimization Best Practices

​1. Leverage Time-Based Partitioning

​2. Select Only Required Columns

​3. Use Efficient JOIN Strategies

​4. Use CTEs for Complex Logic

​5. Use LIMIT with Large Result Sets

​Use Curated Data Tables

​Use Materialized Views

​Use Incremental Queries for Recurring Workloads

​Minimize Redundant Lookups in Automated Pipelines

​Query Performance Troubleshooting

​❌ DON’Ts: Query Anti-Patterns to Avoid

​1. DON’T Use SELECT * on Large Tables

​2. DON’T Create Unnecessary Subqueries

​3. DON’T Use ORDER BY Without LIMIT on Large Results

​Summary

Key Principles

✅ DO’s: Query Optimization Best Practices

1. Leverage Time-Based Partitioning

2. Select Only Required Columns

3. Use Efficient JOIN Strategies

4. Use CTEs for Complex Logic

5. Use LIMIT with Large Result Sets

Use Curated Data Tables

Use Materialized Views

Use Incremental Queries for Recurring Workloads

Minimize Redundant Lookups in Automated Pipelines

Query Performance Troubleshooting

❌ DON’Ts: Query Anti-Patterns to Avoid

1. DON’T Use `SELECT *` on Large Tables

2. DON’T Create Unnecessary Subqueries

3. DON’T Use ORDER BY Without LIMIT on Large Results

Summary