SELECT

date_trunc('hour',block_timestamp) as hour,

symbol,

median(edge_PRICE_AMOUNT) as price_base

FROM blast.blitz.ez_edge_trades

WHERE edge_is_taker = 'TRUE'

AND block_timestamp::date >= dateadd(day, -7, current_date)

GROUP by 1 , 2

, pric_delta as (

SELECT

a.*,

100*(price_base-user_PRICE_AMOUNT)/price_base as price_delta_pcnt

FROM blast.blitz.ez_edge_trades a

LEFT JOIN price_base b

ON date_trunc('hour',block_timestamp) = b.hour

and a.symbol = b.symbol

where block_timestamp::date >= dateadd(day, -7, current_date)

and user_is_taker = 'TRUE'

SELECT

ceil(price_delta_pcnt,0) as price_level,

symbol,

sum(edge_amount_usd) as volume

FROM pric_delta

GROUP BY 1 , 2