SELECT TX_ID,

o.INSTRUCTIONS[0]['accounts'][6] as ob_pubkey,

o.INSTRUCTIONS[0]['accounts'][3] as loan_id,

o.PRE_BALANCES[0] - o.POST_BALANCES[0] as principal

FROM solana.core.fact_transactions o

WHERE date(o.BLOCK_TIMESTAMP) > current_date() - INTERVAL '23 DAYS'

AND LEFT(o.INSTRUCTIONS[0]['data'], 6) = '2pxy3Z'

AND o.SUCCEEDED = 'true'

),

defaults as (

SELECT --TX_ID,

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

--INSTRUCTIONS[1]['accounts'][3] as nft,

BLOCK_TIMESTAMP,

1 as default

FROM solana.core.fact_transactions

WHERE date(BLOCK_TIMESTAMP) > current_date() - INTERVAL '7 DAYS'

AND LEFT(INSTRUCTIONS[1]['data'], 4) = 'PsML'

AND SUCCEEDED = 'true'

),

repays as(

SELECT

--TX_ID,

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

1 as repay

FROM solana.core.fact_transactions

WHERE date(BLOCK_TIMESTAMP) > current_date() - INTERVAL '7 DAYS'

AND LEFT(INSTRUCTIONS[1]['data'], 4) = 'HJhK'

AND SUCCEEDED = 'true'

),

extends as (

SELECT