theericstoneCompound Market Borrows, Supply, TVL
    Updated 2023-03-27
    -- with the compound.market_stats table
    -- we can easily summarize borrows, supply, and liquidity
    -- by market at whatever granularity we like

    -- here is the daily breakdown of borrow, supply, and tvl
    with cstats as (
    select
    distinct date_trunc('day', block_hour) as date,
    contract_name,
    last_value(borrows_usd) OVER (PARTITION BY date, ctoken_address ORDER BY block_hour RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as borrows,
    last_value(supply_usd) OVER (PARTITION BY date, ctoken_address ORDER BY block_hour RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as supply,
    last_value(supply_usd - borrows_usd) OVER (PARTITION BY date, ctoken_address ORDER BY block_hour RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as tvl,
    last_value(reserves_usd) OVER (PARTITION BY date, ctoken_address ORDER BY block_hour RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as reserves
    from compound.market_stats
    where block_hour > getdate() - interval '3 months'
    ),
    totals as (
    select
    date,
    sum(borrows) as borrow_total,
    sum(supply) as supply_total,
    sum(tvl) as tvl_total,
    sum(reserves) as reserves_total
    from cstats
    group by 1
    )

    select
    cstats.date,
    cstats.contract_name,
    cstats.borrows,
    cstats.supply,
    cstats.tvl,
    cstats.reserves,
    cstats.borrows / totals.borrow_total * 100 as percent_borrows,
    cstats.supply / totals.supply_total * 100 as percent_supply,