prices as (

select

hour,

case when symbol = 'WETH' then 'ETH' else symbol end as symbol,

price

from ethereum.core.fact_hourly_token_prices

where symbol in (

'WETH','BICO','USDT','USDC'

) and date(hour) = hour

),

bal_updates as (

select

block_timestamp,

tx_hash,

'0x'|| substr(topics[1],27) as token_address,

case token_address

when '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48' then 'USDC'

when '0xdac17f958d2ee523a2206206994597c13d831ec7' then 'USDT'

when '0xf17e65822b568b3903685a7c9f496cf7656cc6c2' then 'BICO'

when '0xeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee' then 'ETH'

end as token,

case token_address

when '0xff970a61a04b1ca14834a43f5de4533ebddb5cc8' then 6

when '0xdac17f958d2ee523a2206206994597c13d831ec7' then 6

else 18

end as decimals,

ethereum.public.udf_hex_to_int(substr(topics[2],3))/pow(10,decimals) as liq,

rank() over(partition by token, date(block_timestamp) order by block_timestamp desc) as date_rank

from ethereum.core.fact_event_logs

where contract_address = '0xebab24f13de55789ec1f3ffe99a285754e15f7b9'

and topics[0] = '0xf28044030a28cf7d3fb8e8f7bbaa42aee92214081fd522b3a38afb279577db89'

order by block_timestamp desc

select