binhachonUntitled Query
Updated 2021-09-28
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 (
SELECT BLOCK_HOUR, SYMBOL, SUPPLY_RATE, BORROW_RATE_VARIABLE, BORROWED_TOKENS, SUM(BORROWED_TOKENS) OVER (PARTITION BY SYMBOL ORDER BY BLOCK_HOUR) as TOTAL_BORROWED
FROM
(SELECT BLOCK_HOUR, SYMBOL, SUM(SUPPLY_RATE) as SUPPLY_RATE, SUM(BORROW_RATE_VARIABLE) as BORROW_RATE_VARIABLE, SUM(BORROWED_TOKENS) as BORROWED_TOKENS, 0 AS TOTAL_BORROWED FROM--,BORROW_RATE_MODE, SUM(BORROW_RATE_STABLE) ,
(
select BLOCK_HOUR, AAVE_MARKET, LENDING_POOL_ADD, SUPPLY_RATE, BORROW_RATE_STABLE , BORROW_RATE_VARIABLE, 0 AS BORROWED_TOKENS, '' AS BORROW_RATE_MODE, RESERVE_NAME AS SYMBOL
from aave.market_stats
WHERE AAVE_VERSION = 'Aave V2'
AND RESERVE_NAME IN ('USDT', 'USDC', 'DAI', 'SUSD')
AND BLOCK_HOUR >= '2021-09-15T00:00:50Z'
UNION ALL
(SELECT DATE_TRUNC('hour', BLOCK_TIMESTAMP) as BLOCK_HOUR, '' as AAVE_MARKET, '' LENDING_POOL_ADD, 0 as SUPPLY_RATE, 0 as BORROW_RATE_STABLE , 0 as BORROW_RATE_VARIABLE,
SUM(BORROWED_TOKENS) as BORROWED_TOKENS, BORROW_RATE_MODE, SYMBOL FROM aave.borrows
WHERE BORROWER_ADDRESS = '0x712d0f306956a6a4b4f9319ad9b9de48c5345996'
AND AAVE_VERSION = 'Aave V2'
GROUP BY 1, 2, 3, 4, 5, 6, 8, 9))
GROUP BY 1, 2)
ORDER BY 1, 2
AND SYMBOL = 'DAI') as T,
with recursive CTE_R as
(
select T.BLOCK_HOUR, T.BORROW_RATE_VARIABLE, T.BORROWED_TOKENS, 0 as Calculation
from temp as T
where T.BLOCK_HOUR = '2021-09-15T01:00:00Z'
union all
select T.BLOCK_HOUR, T.BORROW_RATE_VARIABLE, T.BORROWED_TOKENS, C.Calculation * T.BORROW_RATE_VARIABLE + T.BORROWED_TOKENS as Calculation
from CTE_R as C
inner join temp as T on T.BLOCK_HOUR = C.BLOCK_HOUR + INTERVAL'1 hour'
)
select *
from CTE_R
Run a query to Download Data