chispasLiquity Protocol v1: Liquidations (og query)
Updated 2024-07-12
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 LiquidationEvents AS (
SELECT
DATE(e.block_timestamp) AS date,
'0x' || SUBSTR(l.data, 27, 40) AS liquidator, -- Correctly extracting liquidator from EtherSent event
'0x' || SUBSTR(e.topics[1], 27, 40) AS borrower, -- Extracting borrower from TroveLiquidated event
regexp_substr_all(SUBSTR(e.data, 3), '.{64}') AS segmented_data,
CAST(livequery.utils.udf_hex_to_int(segmented_data[0]::string) AS DECIMAL(38, 0)) / POWER(10, 18) AS liquidated_debt,
CAST(livequery.utils.udf_hex_to_int(segmented_data[1]::string) AS DECIMAL(38, 0)) / POWER(10, 18) AS liquidated_eth_coll,
e.tx_hash,
e.event_index
FROM
ethereum.core.fact_event_logs e
LEFT JOIN ethereum.core.fact_event_logs l ON e.tx_hash = l.tx_hash
AND l.contract_address = LOWER('0xDf9Eb223bAFBE5c5271415C75aeCD68C21fE3D7F') -- Active Pool
AND l.topics[0] = LOWER('0x6109e2559dfa766aaec7118351d48a523f0a4157f49c8d68749c8ac41318ad12') -- EtherSent event signature
WHERE
e.contract_address = LOWER('0xA39739EF8b0231DbFA0DcdA07d7e29faAbCf4bb2') -- Trove Manager
AND e.topics[0] = LOWER('0xea67486ed7ebe3eea8ab3390efd4a3c8aae48be5bea27df104a8af786c408434') -- TroveLiquidated event signature
AND '0x' || SUBSTR(l.data, 27, 40) NOT IN (
LOWER('0xDf9Eb223bAFBE5c5271415C75aeCD68C21fE3D7F'), -- Active Pool
LOWER('0x66017D22b0f8556afDd19FC67041899Eb65a21bb'), -- Stability Pool
LOWER('0x4f9Fbb3f1E99B56e0Fe2892e623Ed36A76Fc605d') -- LQTY Staking
)
)
SELECT
date,
liquidator,
borrower,
MAX(liquidated_debt) AS total_lusd_debt_repaid, -- Using MAX to avoid summing duplicated values
MAX(liquidated_eth_coll) AS total_eth_collateral_liquidated,
tx_hash,
event_index
FROM
LiquidationEvents
GROUP BY
QueryRunArchived: QueryRun has been archived