with base as ( select block_timestamp, tx_hash, 'Open Trove' as action_, from_address, value as collateral, regexp_substr_all(SUBSTR(INPUT_data, 11, len(INPUT_data)), '.{64}') AS segmented_data, --substr using for cut from position 11 until the end of input, regexp_substr_all using to split the raw data into different parts of 64 characters ethereum.public.udf_hex_to_int(segmented_data[1])::float / POW(10, 18) AS borrow from mezo.testnet.fact_transactions where to_address = '0x20faea18b6a1d0fcdbccfffe3d164314744baf30' --BorrowerOperations contract and tx_succeeded = 'True' and ORIGIN_FUNCTION_SIGNATURE = '0x8f09162b' --openTrove union all select block_timestamp, tx_hash, 'Adjust Trove', from_address, value, regexp_substr_all(SUBSTR(INPUT_data, 11, len(INPUT_data)), '.{64}') AS segmented_data, ethereum.public.udf_hex_to_int(segmented_data[2])::float / POW(10, 18) AS borrow from mezo.testnet.fact_transactions where to_address = '0x20faea18b6a1d0fcdbccfffe3d164314744baf30' --BorrowerOperations contract and tx_succeeded = 'True' and ORIGIN_FUNCTION_SIGNATURE = '0x8e54c119' --adjustTrove ), repay as ( select datetime, count(distinct repayer) as total_repayer, sum(musd_amount) as total_musd_repayed from (select date(block_timestamp) as datetime, -- tx_hash, -- 'Repay', from_address as repayer, --value, regexp_substr_all(SUBSTR(INPUT_data, 11, len(INPUT_data)), '.{64}') AS segmented_data, ethereum.public.udf_hex_to_int(segmented_data[0])::float / POW(10, 18) AS musd_amount from mezo.testnet.fact_transactions where to_address = '0x20faea18b6a1d0fcdbccfffe3d164314744baf30' --BorrowerOperations contract and tx_succeeded = 'True' and ORIGIN_FUNCTION_SIGNATURE = '0xdc283bb3') --repayMUSD group by 1 ) select count(distinct borrower) as borrower_cnt, case when total_collateral > 0 and total_collateral <= 0.0002 then '01. <0.0002 BTC' when total_collateral > 0.0002 and total_collateral <= 0.0005 then '02. 0.0002 - 0.0005 BTC' when total_collateral > 0.0005 and total_collateral <= 0.001 then '03. 0.0005 - 0.001 BTC' when total_collateral > 0.001 and total_collateral <= 0.005 then '04. 0.001 - 0.005 BTC' when total_collateral > 0.005 and total_collateral <= 0.01 then '05. 0.005 - 0.01 BTC' when total_collateral > 0.01 and total_collateral <= 0.05 then '06. 0.01 - 0.05 BTC' when total_collateral > 0.05 and total_collateral <= 0.1 then '07. 0.05 - 0.1 BTC' when total_collateral > 0.1 and total_collateral <= 0.3 then '08. 0.1 - 0.3 BTC' when total_collateral > 0.3 and total_collateral <= 0.6 then '09. 0.3 - 0.6 BTC' when total_collateral > 0.6 and total_collateral <= 1 then '10. 0.6 - 1 BTC' when total_collateral > 1 then '11. >1 BTC' end as total_collateral from (select --date(block_timestamp) as datetime, from_address as borrower, sum(collateral) as total_collateral from base group by 1) where total_collateral > 0 group by 2

Query	Creator	Created
mezo_borrower_repayer mezo evm	tkvresearch	Apr 19, 2025
bare-harlequin mezo evm	tkvresearch	Apr 20, 2025
universal-azure mezo evm	tkvresearch	Apr 21, 2025

tkvresearch