kakamora1. 5 Assets Together
Updated 2021-08-29
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
›
⌄
WITH five_assets_interest AS(
select
date_trunc('day', block_hour) as date,
reserve_name,
avg(BORROW_RATE_STABLE * 100) as stable_borrow_rate,
avg(BORROW_RATE_VARIABLE * 100) as variable_borrow_rate,
avg(SUPPLY_RATE * 100) as lending_rate,
avg(TOTAL_LIQUIDITY_TOKEN) as liquidity_token,
avg(TOTAL_LIQUIDITY_USD) as liquidity_usd,
avg(utilization_rate) as utilization
from aave.market_stats
where reserve_name IN ('WBTC', 'WETH', 'USDT', 'UNI', 'DAI')
and block_hour > CURRENT_DATE - INTERVAL '6 Months'
GROUP BY date, reserve_name
),
token_price AS (
SELECT
TRUNC(hour, 'day') AS date,
symbol,
AVG(price) AS avg_price,
MAX(price) AS max_price,
MIN(price) AS min_price
FROM ethereum.token_prices_hourly
WHERE symbol IN ('WBTC', 'WETH', 'USDT', 'UNI', 'DAI')
AND hour > CURRENT_DATE - INTERVAL '6 Months'
GROUP BY date, symbol
)
SELECT
fa.*,
tp.avg_price as token_price
FROM five_assets_interest fa
INNER JOIN token_price tp on fa.date = tp.date and fa.reserve_name = tp.symbol
ORDER By fa.date, fa.reserve_name
Run a query to Download Data