Nige7777Compound Liquidations
Updated 2021-05-24
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
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
›
⌄
WITH CTE_MS as (
SELECT avg(borrows_usd) as Total_borrowed-- OVER (PARTITION BY ctoken_address, date_trunc('day', block_hour) ORDER BY ctoken_address ) Total_borrowed,
,date_trunc('day', block_hour) Day
,ctoken_address
from compound.market_stats
group by
date_trunc('day', block_hour)
,ctoken_address
),
cte_l as (
select
--hour( block_timestamp) as hours,
date_trunc('day', l.block_timestamp) as date,
sum(l.liquidation_amount_usd) liquidation_amount,
count(distinct l.borrower) as Liquidated_Accounts,
count(distinct l.liquidator) as Liquidator,
count(distinct l.tx_id) transactions,
-- b.Total_borrowed,
l.ctoken_symbol as Token,
l.ctoken
-- (sum(l.liquidation_amount_usd)/Total_borrowed) * 100.00 as Percentage_liquidated
--, repay_contract_symbol
from compound.liquidations l
--join CTE_MS b on b.ctoken_address = l.ctoken and date_trunc('day', l.block_timestamp)= b.Day
WHERE
l.block_timestamp >= CURRENT_DATE -60 --date_trunc('hour', block_timestamp) = '2021-04-25T21:00:00Z' --
-- and l.ctoken_symbol = 'cBAT'
--and date_trunc('day' ,l.block_timestamp) = '2021-05-07T00:00:00Z'
GROUP by --repay_contract_symbol,
--hours,
date
,l.ctoken_symbol
,ctoken
-- ,b.Total_borrowed
--order by Percentage_liquidated desc
)
select
l.*,
b.*,
(l.liquidation_amount/Total_borrowed) * 100.00 as Percentage_liquidated
from cte_l l
join CTE_MS b on b.ctoken_address = l.ctoken and l.date= b.Day
order by Percentage_liquidated desc
-- select * from
-- compound.liquidations l
-- where date_trunc('day' ,l.block_timestamp) = '2021-05-07T00:00:00Z'
-- and l.ctoken = '0x6c8c6b02e7b2be14d4fa6022dfd6d75921d90e4e'
-- select * from compound.market_stats m
-- where date_trunc('day' ,m.block_hour) = '2021-05-07T00:00:00Z'
-- and m.ctoken_address = '0x6c8c6b02e7b2be14d4fa6022dfd6d75921d90e4e'
-- select
-- --hour( block_timestamp) as hours,
-- date_trunc('day', l.block_timestamp) as date,
-- sum(l.liquidation_amount_usd) liquidation_amount,
-- count(distinct l.borrower) as Liquidated_Accounts,
-- count(distinct l.liquidator) as Liquidator,
-- count(distinct l.tx_id) transactions,
-- -- b.Total_borrowed,
-- l.ctoken_symbol as Token
-- -- (sum(l.liquidation_amount_usd)/Total_borrowed) * 100.00 as Percentage_liquidated
-- --, repay_contract_symbol
-- from compound.liquidations l
-- --join CTE_MS b on b.ctoken_address = l.ctoken and date_trunc('day', l.block_timestamp)= b.Day
-- WHERE l.block_timestamp >= CURRENT_DATE -30 and date_trunc('day' ,l.block_timestamp) = '2021-05-07T00:00:00Z' --date_trunc('hour', block_timestamp) = '2021-04-25T21:00:00Z' --
-- and l.ctoken_symbol = 'cBAT'
-- GROUP by --repay_contract_symbol,
-- --hours,
-- date
-- ,l.ctoken_symbol
-- -- ,b.Total_borrowed
-- -- order by Percentage_liquidated desc
-- SELECT avg(borrows_usd) --OVER (PARTITION BY ctoken_address, date_trunc('day', block_hour) ORDER BY ctoken_address ) Total_borrowed,
-- ,date_trunc('day', block_hour) Day
-- ,ctoken_address
-- from compound.market_stats m
-- where date_trunc('day' ,m.block_hour) = '2021-05-07T00:00:00Z'
-- and m.ctoken_address = '0x6c8c6b02e7b2be14d4fa6022dfd6d75921d90e4e'
-- group by
-- date_trunc('day', block_hour)
-- ,ctoken_address
Run a query to Download Data