case when balance > 0 then 'Buy' else 'Sell' end as color,

sum(buy_amount) over (order by date) as cum_buy,

sum(sell_amount) over(order by date) as cum_sell

from (

select

date_trunc('hour', block_timestamp) as date,

sum(case when trade_type = 'buy/long' then amount_usd end) as buy_amount,

-sum(case when trade_type = 'sell/short' then amount_usd end) as sell_amount,

buy_amount + sell_amount as balance,

buy_amount / -sell_amount as ratio ,

from blast.blitz.ez_perp_trades

where block_timestamp::date >= current_date - 7

and is_taker = 'TRUE'

group by 1