FlorentGSharkfi Foreclosures
    Updated 2023-11-30
    WITH defaults as (
    SELECT TX_ID,
    INSTRUCTION['accounts'][0] as loan_id,
    INSTRUCTION['accounts'][3] as nft,
    BLOCK_TIMESTAMP
    FROM solana.core.fact_events
    WHERE date(BLOCK_TIMESTAMP) > current_date() - INTERVAL '7 DAYS'
    AND LEFT(INSTRUCTION['data'], 4) = 'PsML'
    AND SUCCEEDED = 'true'
    ),

    offers_principal_s as (
    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 '21 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 '21 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
    ),
    Run a query to Download Data