theericstoneCompound Market Borrows, Supply, TVL
Updated 2023-03-27
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
-- 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,