select

case when b.block_timestamp is not null then 'Edge' else 'Blitz' end as type,

a.*

from blast.blitz.ez_perp_trades a

left outer join blast.blitz.ez_edge_trades b ON

a.tx_hash = b.tx_hash and

a.event_index = b.edge_event_index and

a.block_timestamp = b.block_timestamp

, price_base as (

SELECT

date_trunc('hour',block_timestamp) as hour,

symbol,

median(PRICE_AMOUNT) as price_base

FROM data

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 data 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,

type,