[BUG] tokens.transfers - missing symbols & amount

[kenny0514]

token.transfers have null values for symbols & amount_usd for certain rows, mainly observed with ethereum transfers.

Example transaction: 0x05bb9ac76af99606fad079227a12baae5044865b7aa15f4b86f305766d741dad

For this example, contract address is supposed to be ZRO on Ethereum, but it is empty.

[jeff-dude]

@kenny0514 thank you for raising. it looks like symbol, amount and amount_usd are all null due to missing entry in tokens.erc20 for this contract address (ZRO).

with
  transfer as (
    select
      *
    from
      tokens.transfers
    where
      block_date >= date '2024-08-01'
      and blockchain = 'ethereum'
      and tx_hash = 0x05bb9ac76af99606fad079227a12baae5044865b7aa15f4b86f305766d741dad
  ),
  price as (
    select
      contract_address,
      symbol,
      max(price)
    from
      prices.usd
    where
      blockchain = 'ethereum'
      and contract_address = 0x6985884c4392d348587b19cb9eaaf157f13271cd
    group by
      contract_address,
      symbol
  ),
  erc20 as (
    select
      *
    from
      tokens.erc20
    where
      blockchain = 'ethereum'
      and contract_address = 0x6985884c4392d348587b19cb9eaaf157f13271cd
  )
select
  *
from
  erc20

if we switch the final select to look at each:

• we can see the empty values in transfers
• we can see prices contains ZRO, which makes sense as the transfer row has a price value
• we can see no results on erc20 query

the transfers spell uses decimals from tokens.erc20 to calculate amount and amount_usd. it also uses symbol from there. due to no results on the join, we get this output.

i will have to look into a potential fix, but in the meantime, i would suggest writing your query to join tokens.transfers to prices.usd and use the symbol / decimals from there to calculate these fields.