SELECT

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

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

tx_hash,

DATE(block_timestamp) AS transaction_day

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, 131, 64))::NUMERIC / 1e18 AS adjust_collateral,

tx_hash,

DATE(block_timestamp) AS transaction_day

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_collateral), 0) AS total_open_collateral,

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,

o.transaction_day