SELECT

'0x' || SUBSTR(topics[1], 27) AS borrower_address,

ethereum.public.udf_hex_to_int(SUBSTRING(data, 3, 64))::NUMERIC / 1e18 AS open_principal,

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,

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(a.adjust_principal), 0) AS total_adjust_principal,

COUNT(DISTINCT o.tx_hash) AS open_trove_transactions,

COUNT(DISTINCT a.tx_hash) AS adjust_trove_transactions

FROM open_trove o

LEFT JOIN adjust_trove a

ON o.borrower_address = a.borrower_address