comp as (

select

date(block_hour) as date,

underlying_symbol as token,

supply_usd,

borrows_usd,

borrows_usd*100/supply_usd as util_ratio,

avg(borrows_usd*100/supply_usd) over (partition by token order by date rows between 30 preceding and current row) as util_ratio_ma,

supply_apy*100 as supply_apy,

avg(supply_apy*100) over (partition by token order by date rows between 30 preceding and current row) as lend_apr_ma,

borrow_apy*100 as borrow_apy,

avg(borrow_apy*100) over (partition by token order by date rows between 30 preceding and current row) as borrow_apr_ma

from compound.market_stats

where underlying_symbol in (

'DAI','USDC','USDT'

)

and date(block_hour) = block_hour

-- and date(block_hour) > '2021-10-31'

order by date desc,underlying_symbol

select * from comp