--SELECT --*,

--TX_ID,

--BLOCK_TIMESTAMP,

--INSTRUCTIONS[0]['accounts'][0] as loan_id,

--(PRE_BALANCES[0] - POST_BALANCES[0]) / 10e8 as offered,

--SIGNERS[0] as lender

--FROM solana.core.fact_transactions

--WHERE date(BLOCK_TIMESTAMP) > DATE '2023-05-27' --2023-06-01

--AND LEFT(INSTRUCTIONS[0]['data'], 5) = 'LQ9bN'

--AND SUCCEEDED = 'true'

WITH borrows as (

SELECT --*,

--TX_ID,

BLOCK_TIMESTAMP,

(POST_BALANCES[0] - PRE_BALANCES[0]) / 10e8 as borrowed,

SIGNERS[0] as borrower,

INSTRUCTIONS[1]['accounts'][0] as loan_id,

INSTRUCTIONS[1]['accounts'][5] as lender

FROM solana.core.fact_transactions

WHERE date(BLOCK_TIMESTAMP) > DATE '2023-06-01' --2023-06-01

AND LEFT(INSTRUCTIONS[1]['data'], 5) = '6BFEB'

AND SUCCEEDED = 'true'

),

repays as (

SELECT --*,

TX_ID,

BLOCK_TIMESTAMP,

(PRE_BALANCES[0] - POST_BALANCES[0]) / 10e8 as repaid,

SIGNERS[0] as borrower,

INSTRUCTIONS[1]['accounts'][0] as loan_id,

INSTRUCTIONS[1]['accounts'][4] as lender