select

HOUR::date as pdate,

avg(price) as prices

from kaia.price.ez_prices_hourly

where symbol = 'KLAY'

group by 1

select

'From CEX' as type,

PROJECT_NAME,

count(distinct tx_hash) as txs,

count(distinct TO_ADDRESS) as users,

sum(value) as amount,

sum(value * prices) as usd_amount

from kaia.core.fact_transactions

join kaia.core.dim_labels on FROM_ADDRESS = ADDRESS

left join pricet on BLOCK_TIMESTAMP::date = pdate::date

where LABEL_TYPE = 'cex'

and LABEL_SUBTYPE = 'hot_wallet'

and TX_SUCCEEDED = 'TRUE'

group by 1,2

select

'To CEX' as type,

PROJECT_NAME,

count(distinct tx_hash) as txs,

count(distinct FROM_ADDRESS) as users,

sum(value) as amount,

sum(value * prices) as usd_amount

from kaia.core.fact_transactions

join kaia.core.dim_labels on TO_ADDRESS = ADDRESS

left join pricet on BLOCK_TIMESTAMP::date = pdate::date