select

date(hour) as day,

TOKEN_ADDRESS,

avg(price) as price_usd

from

ethereum.core.fact_hourly_token_prices

where

hour > CURRENT_DATE-30

group by 1,2

), ftx_addresses as (

select

address

from

ethereum.core.dim_labels

where

(LABEL_TYPE='cex' and LABEL ilike 'ftx') or ADDRESS_NAME ilike '%Alameda%'

), out_transfers as (

select

date_trunc('day', block_timestamp) as dt,

TX_HASH,

RAW_AMOUNT/pow(10,18)*p.price_usd as price,

case

when l.label ilike '%binance%' then 'Binance'

when l.label ilike '%kucoin%' then 'KuCoin'

when l.label ilike '%coinbase%' then 'CoinBase'

when l.label ilike '%kraken%' then 'Kraken'

when l.label ilike '%gate%' then 'Gate.IO'

when l.label ilike '%huobi%' then 'Huobi'

when l.label ilike '%bitfinex%' then 'BitFinex'

when l.label ilike '%bitstamp%' then 'BitStamp'

when l.label ilike '%bybit%' then 'ByBit'

when l.label ilike '%okx%' then 'OKX'

when l.label ilike '%gemini%' then 'Gemini'

when l.label ilike '%coincheck%' then 'CoinCheck'

when l.label ilike '%bithumb%' then 'Bithumb'