with deposits as (

select

time_slice(block_timestamp, 30, 'MINUTE') as halfhour,

date_trunc('hour',block_timestamp) as hour,

count(distinct(tx_id)) as n_deposits,

sum(value) as amount_to_exch,

max(value) as max_dep,

median(value) as median_dep,

avg(value) as mean_dep,

mean_dep - median_dep as mean_med_delta

bitcoin.core.fact_outputs

where

block_timestamp < current_date - {{minago}} and

block_timestamp > current_date - {{maxago}} and

pubkey_script_address in (

select address from bitcoin.core.dim_labels

where label_type = 'cex')

group by 1,2

order by 1 desc

),

prices as (

select *,

price - first_value(price) OVER (ORDER by HOUR) as price_zeroed,

price - LAG(price, 1, 0) OVER (ORDER BY HOUR) AS price_diff

from crosschain.price.ez_hourly_token_prices

where blockchain = 'ethereum'

and lower(token_address) = lower('0x2260FAC5E5542a773Aa44fBCfeDf7C193bc2C599')

and hour < current_date - {{minago}}

and hour > current_date - {{maxago}}

select

deposits.*,