Block Level and Daily Balances

In this guide, we're going to look at how to create a view of balances at a block level and daily level.

By using the snapshot data in ethereum.core.fact_token_balances we can create a block level view of balances or a daily view. The approach for both methods is similar, but we can start at a block level.

Please note: Balances data is very large. Queries without where filters on block_number of block_timestamp may take a long time. Please do your best to only query what you really need. However, we do realize that some use cases require full history.

We are going to use the same USDC-WETH Uniswap v3 pool from the prior example. We'll start by pulling the decimal transformed balances for the pool from the last 7 days.

select
  block_number,
  block_timestamp,
  user_address,
  contract_address,
  c.symbol,
  balance / pow(10, c.decimals) as balance
from
  ethereum.core.fact_token_balances b
  left join ethereum.core.dim_contracts c on b.contract_address = c.address
where
  user_address = '0x88e6a0c2ddd26feeb64f039a2c41296fcb3f5640'
  and block_timestamp >= current_date() - 7
  and contract_address in (
    '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2',
    '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48'
  )

The next step involves filling all the gaps in blocks with the prior value. This pool has a lot of volume as of writing, so there is not much filling to do, as there is already almost a swap every block. However, something like an EOA (wallet) will have much less volume, and therefore has more filling to do. We will fill by block_number here, but if you only needed daily balances, you could fill by block_timestamp::date.

Once we have our spine of block_number, user_address, contract_address, and symbol, we can left join in our balances data, and fill the gaps with the prior valid value. Let's do this with a few CTEs.

with balances as (
  select
    block_number,
    block_timestamp,
    user_address,
    contract_address,
    c.symbol,
    balance / pow(10, c.decimals) as balance
  from
    ethereum.core.fact_token_balances b
    left join ethereum.core.dim_contracts c on b.contract_address = c.address
  where
    user_address = '0x88e6a0c2ddd26feeb64f039a2c41296fcb3f5640'
    and block_timestamp >= current_date() - 7
    and contract_address in (
      '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2',
      '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48'
    )
),
-- create a spine of all the possible combos
spine as (
  select
    block_number,
    block_timestamp,
    user_address,
    contract_address,
    symbol
  from
    ethereum.core.fact_blocks b
    join (
      select
        distinct contract_address,
        user_address,
        symbol
      from
        balances
    ) on 1 = 1
  where
    block_number between (
      select
        min(block_number)
      from
        balances
    )
    and (
      select
        max(block_number)
      from
        balances
    )
),
-- join all options with transfer based data and fill gaps 
block_level_balances as (
  select
    s.block_number,
    s.block_timestamp,
    s.user_address,
    s.contract_address,
    s.symbol,
    b.balance,
    LAST_VALUE(b.balance ignore nulls) over(
      PARTITION BY s.user_address,
      s.contract_address
      ORDER BY
        s.block_number ASC rows unbounded preceding
    ) as balance_filled
  from
    spine s
    left join balances b using (block_number, user_address, contract_address)
)
select
  *
from
  block_level_balances;

This gives us the WETH and USDC balance, per block, for the Uniswap V3 pool. We could take this a step further by filtering to just the last balace per day using the following final step.

select
  block_timestamp :: date as block_date,
  block_number,
  user_address,
  contract_address,
  symbol,
  balance_filled as balance
from
  block_level_balances qualify (
    row_number() over (
      partition by user_address,
      contract_address,
      block_date
      order by
        block_number desc
    ) = 1
  )
order by
  block_date,
  user_address,
  contract_address

If you wanted to analyze the total supply of a token, you would want to remove the user_address filter. There are many possibilities with these tables, but remember to use as many filters as possible for optimal performance.