connorhAAVE Market Stats
    Updated 2023-04-13
    -- market stats
    with astats as (
    select
    distinct date_trunc('day', block_hour) as date,
    aave_market,
    aave_version,
    last_value(total_stable_debt_usd) OVER (PARTITION BY date, atoken_address ORDER BY block_hour RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as borrows_stable,
    last_value(total_variable_debt_usd) OVER (PARTITION BY date, atoken_address ORDER BY block_hour RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as borrows_variable,
    last_value(total_liquidity_usd) OVER (PARTITION BY date, atoken_address ORDER BY block_hour RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as supply,
    last_value(total_liquidity_usd - total_variable_debt_usd - total_stable_debt_usd) OVER (PARTITION BY date, atoken_address ORDER BY block_hour RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as tvl
    from ethereum.aave.ez_market_stats
    ),
    totals as (
    select
    date,
    aave_market,
    aave_version,
    AVG(borrows_stable) as borrow_stable_3month_avg,
    AVG(borrows_variable) as borrow_variable_3month_avg,
    AVG(supply) as supply_3month_avg,
    AVG(tvl) as tvl_3month_avg
    from astats
    group by 1,2,3
    )

    select
    astats.date,
    astats.aave_market,
    astats.aave_version,
    astats.borrows_stable,
    AVG(astats.supply) OVER (
    ORDER BY astats.date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as xsupply,
    astats.tvl,
    totals.borrow_stable_3month_avg,
    totals.borrow_variable_3month_avg,
    Run a query to Download Data