boonzadeh2024-04-23 12:15 PM
    Updated 2024-04-23
    WITH Event_Transfer_Join AS (
    SELECT
    fe.BLOCK_ID AS event_block_id,
    fe.PROGRAM_ID AS event_program_id,
    fe.INNER_INSTRUCTION,
    ft.AMOUNT AS transfer_amount,
    ft.BLOCK_TIMESTAMP AS transfer_block_timestamp,
    ft.FACT_TRANSFERS_ID AS transfer_id,
    ft.INDEX AS transfer_index,
    ft.INSERTED_TIMESTAMP AS transfer_inserted_timestamp,
    ft.MINT AS transfer_mint,
    ft.MODIFIED_TIMESTAMP AS transfer_modified_timestamp,
    ft.TX_FROM AS transfer_tx_from,
    ft.TX_ID AS transfer_tx_id,
    ft.TX_TO AS transfer_tx_to
    FROM
    solana.core.fact_events fe
    JOIN
    solana.core.fact_transfers ft
    ON
    fe.BLOCK_ID = ft.BLOCK_ID
    WHERE
    fe.PROGRAM_ID = 'FL3X2pRsQ9zHENpZSKDRREtccwJuei8yg9fwDu9UN69Q'
    AND date_trunc('day', fe.BLOCK_TIMESTAMP) = '2024-04-22 00:00:00.000'
    ),

    Parsed_New_Account AS (
    SELECT
    *,
    ii.value:parsed:info:newAccount::STRING AS newAccount
    FROM
    Event_Transfer_Join,
    LATERAL FLATTEN(input => INNER_INSTRUCTION:instructions) ii
    WHERE
    ii.value:parsed:type::STRING = 'createAccount'
    )
    QueryRunArchived: QueryRun has been archived