WITH cex_flows AS (

SELECT

DATE_TRUNC('day', t.block_timestamp) as date,

SUM(t.transfer_amount) as btc_amount,

AVG(p.price) as avg_daily_price -- getting daily average price

FROM bitcoin.core.fact_clustered_transfers t

JOIN bitcoin.core.dim_labels l

--ON t.to_entity = l.address

ON t.from_entity = l.address

JOIN bitcoin.price.ez_prices_hourly p

ON DATE_TRUNC('hour', t.block_timestamp) = p.hour

AND p.symbol = 'BTC'

WHERE l.label_type = 'cex' -- filtering for CEX addresses

AND l.label_subtype = 'hot_wallet' -- adding hot wallet filter

AND t.block_timestamp >= DATEADD(day, -60, CURRENT_DATE())

AND t.block_timestamp < CURRENT_DATE()

GROUP BY 1

SELECT

date,

btc_amount,

btc_amount * avg_daily_price as value_usd

FROM cex_flows

ORDER BY date DESC;