binhachonAAVE: Safety Capital over total borrow in USD
Updated 2021-09-26
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
›
⌄
SELECT DATE_TRUNC('Day', BALANCE_DATE) AS BALANCE_DATE, SUM(CORRECTED_AMOUNT_USD)*0.3 AS TOTAL_SAFETY_CAPITAL, SUM(AMOUNT_BORROW) AS TOTAL_BORROW,
CASE WHEN TOTAL_BORROW > 0 THEN TOTAL_SAFETY_CAPITAL / TOTAL_BORROW * 100 ELSE 0 END AS COVERAGE_PERCENTAGE
FROM
(SELECT BALANCE_DATE AS BALANCE_DATE, SAFETY_MODULE, SYMBOL, BALANCE,
first_value(PRICE) over (partition by grp_close ORDER BY BALANCE_DATE) as CORRECTED_PRICE,
CASE WHEN AMOUNT_USD IS NULL THEN CORRECTED_PRICE*BALANCE
ELSE AMOUNT_USD END AS CORRECTED_AMOUNT_USD,
0 AS AMOUNT_BORROW
FROM(
SELECT BALANCE_DATE, PRICE, BALANCE, AMOUNT_USD, SYMBOL,
sum(case when PRICE is not null then 1 end) over (order by BALANCE_DATE) as grp_close,
CASE
WHEN USER_ADDRESS = '0x4da27a545c0c5b758a6ba100e3a049001de870f5' THEN 'StkAAVE'
WHEN USER_ADDRESS = '0xc697051d1c6296c24ae3bcef39aca743861d9a81' THEN 'StkaBPT'
END as SAFETY_MODULE
FROM ethereum.erc20_balances
WHERE USER_ADDRESS = '0x4da27a545c0c5b758a6ba100e3a049001de870f5'--, '0xc697051d1c6296c24ae3bcef39aca743861d9a81') --IN ('0xa1116930326d21fb917d5a27f1e9943a9595fb47', '0x41a08648c3766f9f9d85598ff102a08f4ef84f84')-- --
AND SYMBOL != ''
) UNION ALL
(SELECT BALANCE_DATE AS BALANCE_DATE, SAFETY_MODULE, SYMBOL, BALANCE,
first_value(PRICE) over (partition by grp_close ORDER BY BALANCE_DATE) as CORRECTED_PRICE,
CASE WHEN AMOUNT_USD IS NULL THEN CORRECTED_PRICE*BALANCE
ELSE AMOUNT_USD END AS CORRECTED_AMOUNT_USD,
0 AS AMOUNT_BORROW
FROM(
SELECT BALANCE_DATE, PRICE, BALANCE, AMOUNT_USD, SYMBOL,
sum(case when PRICE is not null then 1 end) over (order by BALANCE_DATE) as grp_close,
CASE
WHEN USER_ADDRESS = '0x4da27a545c0c5b758a6ba100e3a049001de870f5' THEN 'StkAAVE'
WHEN USER_ADDRESS = '0xc697051d1c6296c24ae3bcef39aca743861d9a81' THEN 'StkaBPT'
END as SAFETY_MODULE
FROM ethereum.erc20_balances
WHERE USER_ADDRESS = '0xc697051d1c6296c24ae3bcef39aca743861d9a81'--, '0xc697051d1c6296c24ae3bcef39aca743861d9a81') --IN ('0xa1116930326d21fb917d5a27f1e9943a9595fb47', '0x41a08648c3766f9f9d85598ff102a08f4ef84f84')-- --
AND CONTRACT_ADDRESS = '0x7fc66500c84a76ad7e9c93437bfc5ac33e2ddae9' --AAVE
)) UNION ALL
(SELECT BALANCE_DATE AS BALANCE_DATE, SAFETY_MODULE, SYMBOL, BALANCE,
Run a query to Download Data