samgmx liquidation test
Updated 2022-08-16
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 raw_liquidation as (
select
block_timestamp,
tx_hash,
concat ( '0x', split ( substr(data, 67, 258) , '000000000000000000000000')[1] ) as user_that_got_liquidated ,
concat ( '0x', split ( substr(data, 67, 258) , '000000000000000000000000')[2] ) as collateral,
l.address_name as collateral_denom,
concat ( '0x', split ( substr(data, 67, 258) , '000000000000000000000000')[3] ) as asset_of_interest,
m.address_name as asset_of_interest_denom,
case when substr(data, 397, 64) = '0000000000000000000000000000000000000000000000000000000000000000' then 'Short'
else 'Long' end as position_type ,
case when topics[0] = '0x93d75d64d1f84fc6f430a64fc578bdd4c1e090e90ea2d51773e626d19de56d30' then 'Partially liquidated'
when topics[0] = '0x2e1f85a64a2f22cf2f0c42584e7c919ed4abe8d53675cff0f62bf1e95a1c676f' then 'Fully Liquidated'
end as liquidation_status
-- ethereum.public.udf_hex_to_int(substr(data, 67))
from avalanche.core.fact_event_logs f
left join avalanche.core.dim_labels l on concat ( '0x', split ( substr(data, 67, 258) , '000000000000000000000000')[2] ) = l.address
left join avalanche.core.dim_labels m on concat ( '0x', split ( substr(data, 67, 258) , '000000000000000000000000')[3] ) = m.address
where 1 = 1
and origin_function_signature = '0xde2ea948'
-- and tx_hash = '0x28d0934e87be33274abd4da3ac06bce6cfe846840ba566ca514dc5a8a4d538bc'
and topics[0] in (
'0x93d75d64d1f84fc6f430a64fc578bdd4c1e090e90ea2d51773e626d19de56d30' -- partially liquidate to decrease position
, '0x2e1f85a64a2f22cf2f0c42584e7c919ed4abe8d53675cff0f62bf1e95a1c676f' -- fully liquidate enough to close position
)
)
select
date_trunc('week', block_timestamp) as weekly,
asset_of_interest_denom,
count(distinct user_that_got_liquidated),
Run a query to Download Data