SELECT TX_ID,

PRE_BALANCES[0] - POST_BALANCES[0] as principal_s

FROM solana.core.fact_transactions

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

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

AND signers[0] = '6QvvZKGEHxyTKgkHQpfpJXPAgWUAzefXVHYPBFrJGPYP'

AND SUCCEEDED = 'true'

),

offers as (

SELECT TX_ID,

INSTRUCTION['accounts'][6] as ob_pubkey,

INSTRUCTION['accounts'][3] as loan_id

FROM solana.core.fact_events

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

AND LEFT(INSTRUCTION['data'], 6) = '2pxy3Z'

AND INSTRUCTION['accounts'][0] = '6QvvZKGEHxyTKgkHQpfpJXPAgWUAzefXVHYPBFrJGPYP'

AND SUCCEEDED = 'true'

),

nb_offers_per_tx as (

SELECT TX_ID,

count(*) as nb_offers

FROM offers

GROUP BY 1

),

principal_per_offer as (

SELECT

p.TX_ID,

principal_s / nb_offers as principal -- this assumes every loan in a tx have the same principal, always true from the UI but coult not be if tx built programmatically

FROM offers_principal_s p

JOIN nb_offers_per_tx n

ON p.TX_ID = n.TX_ID

),