SELECT

date_trunc('hour',block_timestamp) as hour,

symbol,

median(PRICE_AMOUNT) as price_base

FROM blast.blitz.ez_perp_trades

WHERE is_taker = 'TRUE'

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

GROUP by 1 , 2

, pric_delta as (

SELECT

a.*,

100*(price_base-PRICE_AMOUNT)/price_base as price_delta_pcnt

FROM blast.blitz.ez_perp_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 is_taker = 'FALSE'

SELECT

ceil(price_delta_pcnt,0) as price_level,

symbol,

sum(amount_usd) as volume_of_orders

FROM pric_delta

GROUP BY 1 , 2