connorhAAVE Market Stats
Updated 2023-04-13
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
›
⌄
-- 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