select tx_id

from solana.fact_events

where block_timestamp >= '2022-04-01'

and program_id = '76f9QiXhCc8YLJc2LEE4Uae4Xu3itc3JCGLmup3VQwRH' --Yawww.io

),

FUNDED_LOANS as (

select ft.block_timestamp as fecha,

ft.tx_id,

ft.signers[0] as lender,

ft.inner_instructions[0]:instructions[0]:parsed:info:lamports / pow(10,9) as paid_amount,

ft.inner_instructions[0]:instructions[0]:parsed:info:destination as borrower,

ft.inner_instructions[0]:instructions[1]:parsed:info:lamports / pow(10,9) as loan_fees

from solana.fact_transactions ft

where fecha >= '2022-04-01'

and ft.tx_id IN (select tx_id from YAWWW_TXS)

and ft.succeeded = 'TRUE'

and ft.log_messages[1] = 'Program log: Instruction: Accept loan request'

group by 1,2,3,4,5,6

),

PAID_LOANS as (

select ft.block_timestamp as fecha,

ft.tx_id,

ft.signers[0] as borrower,

ft.inner_instructions[0]:instructions[0]:parsed:info:lamports / pow(10,9) as payback_amount,

ft.inner_instructions[0]:instructions[0]:parsed:info:destination as lender,

ft.post_token_balances[0]:mint as NFT,

nft.project_name as NFT_collection

from solana.fact_transactions ft

left join solana.dim_nft_metadata nft

on ft.post_token_balances[0]:mint = nft.mint

where fecha >= '2022-04-01'

and ft.tx_id IN (select tx_id from YAWWW_TXS)

and ft.succeeded = 'TRUE'