> ## 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.

# EVM Daily Balance Updates

> Sparse validity-interval table for historical fungible token balances on EVM networks.

`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](#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

| Column               | Type        | Description                                                                                                                                                                 |
| -------------------- | ----------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `blockchain`         | `varchar`   | Chain identifier                                                                                                                                                            |
| `address_prefix`     | `varchar`   | First byte of `address`, lower-hex (partition key)                                                                                                                          |
| `address`            | `varbinary` | Account address                                                                                                                                                             |
| `token_address`      | `varbinary` | Token contract address. Native tokens use the standardized address from `dune.blockchains` (zero address on most EVMs, `0x0000…1010` on Polygon).                           |
| `token_standard`     | `varchar`   | `native` or `erc20`                                                                                                                                                         |
| `token_symbol`       | `varchar`   | Token symbol from `tokens_<chain>.erc20` (nullable)                                                                                                                         |
| `balance_raw`        | `uint256`   | Balance in raw units while the interval is valid                                                                                                                            |
| `balance`            | `double`    | Balance in display units (`balance_raw / 10^decimals`, falls back to 18 decimals when unknown)                                                                              |
| `balance_usd`        | `double`    | `balance * 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_from`         | `date`      | First day of the interval (inclusive)                                                                                                                                       |
| `valid_to`           | `date`      | First day after the interval (exclusive). `9999-12-31` for the active interval.                                                                                             |
| `balance_updated_at` | `timestamp` | Block 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`](/data-catalog/curated/utilities/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:**

```sql theme={null}
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:**

```sql theme={null}
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`](/data-catalog/curated/cex-flows/overview), a curated table of known centralized-exchange wallets. The same pattern works for any cohort table or uploaded address list.

```sql theme={null}
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
```