select

hour,

ticker_id,

product_id,

price,

volume,

row_number () over (partition by hour, ticker_id, product_id order by price asc) as rank

from blast.blitz.ez_market_depth_stats

where orderbook_side = 'asks'

qualify rank = 1

,

bids as (

select

hour,

ticker_id,

product_id,

price,

volume,

row_number () over (partition by hour, ticker_id, product_id order by price desc) as rank

from blast.blitz.ez_market_depth_stats

where orderbook_side = 'bids'

qualify rank = 1

select

a.hour,

a.ticker_id,

median(ABS(a.price - b.price)) as spread,

median((a.price+b.price)/2) as mid_price,

100 * spread / mid_price as percentage_spread

from asks a

join bids b on a.hour = b.hour and a.ticker_id = b.ticker_id and a.product_id = b.product_id

and a.rank = b.rank