SELECT

HOUR

, split(ticker_id,'_')[0] as symbol

, MIN(CASE WHEN orderbook_side='asks' THEN price ELSE NULL END) as price_ask

, MAX(CASE WHEN orderbook_side='bids' THEN price ELSE NULL END) as price_bid

, ROUND(abs(((price_ask-price_bid)/price_ask)*100), 3) as spread

, SUM(CASE WHEN orderbook_side='asks' THEN volume ELSE NULL END) as vol_ask

, SUM(CASE WHEN orderbook_side='bids' THEN volume ELSE NULL END) as vol_bid

from arbitrum.vertex.ez_market_depth_stats

WHERE 1=1

AND HOUR::date BETWEEN '{{start_date}}' AND '{{end_date}}'

AND split(ticker_id,'_')[0] ilike '{{ticker}}%'

--AND ticker_id = 'BTC-PERP_USDC'

--AND orderbook_side = 'asks'

GROUP BY HOUR, symbol

ORDER BY HOUR ASC, symbol ASC