flipside

    Solana New Users - Most common first transaction after creation
    KaskoazulSolana New Users - Most common first transaction after creation
    Updated 2022-02-13
    WITH FIRST_TRANSACTION AS(
    SELECT
    tx_from_address as new_user,
    min(block_timestamp) as creation_date
    FROM
    solana.transactions
    WHERE succeeded = 'TRUE'
    GROUP BY 1
    ),

    FIRST_PROGRAM AS(
    SELECT
    FIRST_TRANSACTION.new_user,
    FIRST_TRANSACTION.creation_date,
    t.program_id
    FROM
    FIRST_TRANSACTION
    INNER JOIN solana.transactions t
    ON t.tx_from_address = FIRST_TRANSACTION.new_user AND t.block_timestamp > FIRST_TRANSACTION.creation_date
    WHERE
    creation_date >= '2022-02-01'
    )

    SELECT
    program_id,
    CASE program_id
    WHEN 'ATokenGPvbdGVxr1b2hvZbsiqW5xWH25efTNsLJA8knL' THEN 'USDT Token account'
    WHEN '11111111111111111111111111111111' THEN 'WrappedSOL'
    WHEN 'TokenkegQfeZyiNwAJbNbGKPFXCWuBvf9Ss623VQ5DA' THEN 'Unknown Token account'
    WHEN 'FLEET1qqzpexyaDpqb2DGsSzE2sDCizewCg9WjrA6DBW' THEN 'Unknown - no public name'
    WHEN 'JUP2jxvXaqu7NQY1GmNF4m1vodw12LVXYxbFL2uJvfo' THEN 'Jupiter Aggregator v2'
    WHEN 'vau1zxA2LbssAUEF7Gpw91zMM1LvXrvpzJtmZ58rPsn' THEN 'Metaplex Token Vault'
    WHEN 'EhhTKczWMGQt46ynNeRX1WfeagwwJd7ufHvCDjRxjo5Q' THEN 'Raydium Stake'
    WHEN '9xQeWvG816bUx9EPjHmaT23yvVM2ZWbrrpZb9PusVFin' THEN 'Serum DEX V3'
    WHEN 'MEisE1HzehtrDpAAT8PnLHjpSSkRYakotTuJRPjTpo8' THEN 'Magic Eden NFT Marketplace'
    END AS Program_name,
    Run a query to Download Data