SELECT TX_ID,

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

FROM solana.core.fact_transactions o

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

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

AND o.SUCCEEDED = 'true'

),

offers as (

SELECT TX_ID,

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

o.INSTRUCTION['accounts'][3] as loan_id

FROM solana.core.fact_events o

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

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

AND o.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

FROM offers_principal_s p

JOIN nb_offers_per_tx n

ON p.TX_ID = n.TX_ID

),

complete_offers as (