flipside

    total-lavender
    datavortextotal-lavender
    Updated 4 days ago
    WITH open_trove AS (
    SELECT
    '0x' || SUBSTR(topics [1], 27) AS borrower_address,
    ethereum.public.udf_hex_to_int(SUBSTRING(data, 3, 64)) :: NUMERIC / 1e18 AS open_principal,
    ethereum.public.udf_hex_to_int(SUBSTRING(data, 131, 64)) :: NUMERIC / 1e18 AS open_collateral,
    tx_hash
    FROM
    mezo.testnet.fact_event_logs
    WHERE
    topics [0] = '0xf575eb5cdee005607f56587351e18943ddacd11756b9d37980ec251797ff136c'
    AND origin_function_signature = '0x8f09162b'
    AND contract_address = '0x20faea18b6a1d0fcdbccfffe3d164314744baf30'
    ),
    adjust_trove AS (
    SELECT
    '0x' || SUBSTR(topics [1], 27) AS borrower_address,
    ethereum.public.udf_hex_to_int(SUBSTRING(data, 3, 64)) :: NUMERIC / 1e18 AS adjust_principal,
    ethereum.public.udf_hex_to_int(SUBSTRING(data, 131, 64)) :: NUMERIC / 1e18 AS adjust_collateral,
    tx_hash
    FROM
    mezo.testnet.fact_event_logs
    WHERE
    topics [0] = '0xf575eb5cdee005607f56587351e18943ddacd11756b9d37980ec251797ff136c'
    AND origin_function_signature = '0x8e54c119'
    AND contract_address = '0x20faea18b6a1d0fcdbccfffe3d164314744baf30'
    ),
    combined AS (
    SELECT
    o.borrower_address,
    COALESCE(SUM(o.open_principal), 0) AS total_open_principal,
    COALESCE(SUM(o.open_collateral), 0) AS total_open_collateral,
    COALESCE(SUM(a.adjust_principal), 0) AS total_adjust_principal,
    COALESCE(SUM(a.adjust_collateral), 0) AS total_adjust_collateral,
    COUNT(DISTINCT o.tx_hash) AS open_trove_transactions,
    COUNT(DISTINCT a.tx_hash) AS adjust_trove_transactions
    FROM
    Last run: 4 days ago
    DEPOSITOR_CATEGORY
    TOTAL_BORROWERS
    TOTAL_COLLATERAL
    TOTAL_BORROWED
    TOTAL_OPEN_TROVE_TRANSACTIONS
    TOTAL_ADJUST_TROVE_TRANSACTIONS
    TOTAL_TRANSACTIONS
    1
    		0.002-0.005 BTC2350.76790613238338.1398267248515
    2
    		0.1-0.5 BTC144.57470374921853.0488536238
    3
    		0.005-0.01 BTC970.69255711902969.3537109134243
    4
    		0.05-0.1 BTC40.2729294740639.36535415
    5
    		0.001-0.002 BTC3230.4406997211825.6425342328670
    6
    		1 BTC or more136.289586659377994.8995517219
    7
    		0.01-0.05 BTC551.0883616796082.651466209275
    8
    		0.0001-0.001 BTC187124.07273268147004.0641518755117919934
    9
    		0.5-1 BTC21.56496428275830.4805222749
    9
    498B
    4s