select

tx_hash,

from_address,

value as collateral,

regexp_substr_all(SUBSTR(INPUT_data, 11, len(INPUT_data)), '.{64}') AS segmented_data,

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

tx_hash,

from_address,

value as collateral,

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

select

case

when collateral < 0.000001 then 'A. < 0.000001 BTC'

when collateral < 0.00001 then 'B. [0.000001, 0.00001) BTC'

when collateral < 0.0001 then 'C. [0.00001, 0.0001) BTC'

when collateral < 0.0003 then 'D. [0.0001, 0.0003) BTC'

when collateral < 0.001 then 'E. [0.0003, 0.001) BTC'