SELECT

DATE_TRUNC('day', block_timestamp) AS transaction_date,

tx_hash,

MAX(

CASE

WHEN function_name = 'isBorrower' THEN decoded_input_data:input_1::STRING

WHEN function_name = 'borrower' THEN decoded_output_data:output_1::STRING

END

) AS borrower_address,

MAX(

CASE

WHEN function_name = 'principal' THEN decoded_output_data:output_1::FLOAT / POW(10, 6)

END

) AS principal_amount,

'initial' AS loan_type

FROM ethereum.core.ez_decoded_traces

WHERE function_name IN ('isBorrower', 'borrower', 'principal')

AND from_address = LOWER('0xdc9b93a8a336fe5dc9db97616ea2118000d70fc0')

AND tx_hash NOT IN (

SELECT tx_hash

FROM ethereum.core.ez_decoded_traces

WHERE function_name = 'makePayment'

)

GROUP BY DATE_TRUNC('day', block_timestamp), tx_hash

HAVING borrower_address IS NOT NULL

),

refinance_loan AS (

SELECT

DATE_TRUNC('day', dt1.block_timestamp) AS transaction_date,

dt1.tx_hash,

dt2.decoded_input_data:"from"::STRING AS borrower_address,

MAX(

CASE

WHEN dt1.function_name = 'principal' THEN dt1.decoded_output_data:output_1::FLOAT / POW(10, 6)