Skip to main content

Documentation Index

Fetch the complete documentation index at: https://docs.dune.com/llms.txt

Use this file to discover all available pages before exploring further.

balances_<chain>.daily_updates is the default table for historical balance analysis. It is sparse: one row is written per (address, token_address) balance change, not one row per day per pair. Each row carries a validity interval [valid_from, valid_to) covering the days during which that balance was held. Adjacent intervals share the boundary day on valid_from / valid_to (no gap, no overlap). Refreshed hourly. The interval layout lets you reconstruct the balance for any (address, token_address) on any past day by selecting the row where valid_from <= day < valid_to. To get per-day rows across a window, expand the interval against a calendar table (see Example Queries). valid_to = '9999-12-31' marks the active (ongoing) interval. USD value is joined from prices.day at valid_from. Zero-balance periods are kept as their own intervals, so filter balance > 0 if you want to exclude empty holdings.

​
Table Schema

ColumnTypeDescription
blockchainvarcharChain identifier
address_prefixvarcharFirst byte of address, lower-hex (partition key)
addressvarbinaryAccount address
token_addressvarbinaryToken contract address. Native tokens use the standardized address from dune.blockchains (zero address on most EVMs, 0x0000…1010 on Polygon).
token_standardvarcharnative or erc20
token_symbolvarcharToken symbol from tokens_<chain>.erc20 (nullable)
balance_rawuint256Balance in raw units while the interval is valid
balancedoubleBalance in display units (balance_raw / 10^decimals, falls back to 18 decimals when unknown)
balance_usddoublebalance * prices.day.price at valid_from. NULL when no price is available for that token; coalesce(balance_usd, 0) if you need to sum across mixed-coverage tokens.
valid_fromdateFirst day of the interval (inclusive)
valid_todateFirst day after the interval (exclusive). 9999-12-31 for the active interval.
balance_updated_attimestampBlock time of the underlying balance update

​
Usage Notes

The table is partitioned by address_prefix, the first byte of address rendered as a 2-character lower-hex string ('00'..'ff'). For best performance, include all three of:
  1. A cohort filter on address and/or token_address.
  2. A partition filter address_prefix IN (...) matching the addresses in your cohort. For a hardcoded address the prefix is the first hex byte (e.g. 0xd8dA… → 'd8'). For a dynamic cohort, materialize the distinct prefixes in a subquery so the planner can prune partitions: address_prefix IN (SELECT DISTINCT lower(to_hex(varbinary_substring(address, 1, 1))) FROM <cohort>).
  3. Day bounds on the interval: valid_from <= <end_date> AND valid_to > <start_date>. These let the scanner skip files that fall outside the window. The active row (valid_to = '9999-12-31') is naturally included by valid_to > <start_date>.
To expand validity intervals into per-day rows, the recommended pattern is to join the utils.days calendar table. For very large outputs (hundreds of millions of rows), CROSS JOIN UNNEST(sequence(...)) clips the day sequence at generation time and is typically faster.

​
Example Queries

Daily USDC balance for a wallet, last 30 days: 
WITH days AS (
    SELECT CAST(timestamp AS DATE) AS day
    FROM utils.days
    WHERE timestamp BETWEEN
        CAST(CURRENT_DATE - INTERVAL '30' day AS TIMESTAMP)
        AND CAST(CURRENT_DATE AS TIMESTAMP)
)
SELECT d.day, i.balance, i.balance_usd
FROM balances_ethereum.daily_updates i
JOIN days d
    ON d.day >= i.valid_from
   AND d.day <  i.valid_to
WHERE i.address        = 0xd8da6bf26964af9d7eed9e03e53415d37aa96045
  AND i.address_prefix = 'd8'
  AND i.token_address  = 0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48
  AND i.valid_from <= CURRENT_DATE
  AND i.valid_to   >  CURRENT_DATE - INTERVAL '30' day
ORDER BY d.day
Daily portfolio value for a wallet over a fixed window: 
WITH days AS (
    SELECT CAST(timestamp AS DATE) AS day
    FROM utils.days
    WHERE timestamp BETWEEN TIMESTAMP '2025-01-01 00:00:00' AND TIMESTAMP '2025-12-31 00:00:00'
)
SELECT d.day, sum(i.balance_usd) AS portfolio_usd
FROM balances_ethereum.daily_updates i
JOIN days d
    ON d.day >= i.valid_from
   AND d.day <  i.valid_to
WHERE i.address       = 0xd8da6bf26964af9d7eed9e03e53415d37aa96045
  AND i.address_prefix = 'd8'
  AND i.valid_from <= DATE '2025-12-31'
  AND i.valid_to   >  DATE '2025-01-01'
GROUP BY d.day
ORDER BY d.day
Total USDC held by centralized exchanges over time (CEX stablecoin float): The cohort is cex.addresses, a curated table of known centralized-exchange wallets. The same pattern works for any cohort table or uploaded address list. 
WITH cex_wallets AS (
    SELECT address FROM cex.addresses WHERE blockchain = 'ethereum'
),
days AS (
    SELECT CAST(timestamp AS DATE) AS day
    FROM utils.days
    WHERE timestamp BETWEEN TIMESTAMP '2025-01-01 00:00:00' AND TIMESTAMP '2025-12-31 00:00:00'
)
SELECT d.day, sum(i.balance) AS usdc_at_cexes
FROM balances_ethereum.daily_updates i
JOIN cex_wallets c ON i.address = c.address
JOIN days d
    ON d.day >= i.valid_from
   AND d.day <  i.valid_to
WHERE i.token_address = 0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48
  AND i.address_prefix IN (
        SELECT DISTINCT lower(to_hex(varbinary_substring(address, 1, 1)))
        FROM cex_wallets
      )
  AND i.valid_from <= DATE '2025-12-31'
  AND i.valid_to   >  DATE '2025-01-01'
GROUP BY d.day
ORDER BY d.day