allyfirst time buyers
    Updated 2023-12-20
    WITH buy_vol AS (
    SELECT
    block_timestamp,
    tx_id,
    swapper,
    SUM(swap_to_amount) AS tokens
    FROM
    solana.defi.fact_swaps
    WHERE
    swap_to_mint LIKE 'EKpQGSJtjMFqKZ9KQanSqYXRcF8fBopzLHYxdM65zcjm'
    AND succeeded
    GROUP BY
    1, 2, 3
    ),
    first_buyers AS (
    SELECT
    swapper,
    MIN(block_timestamp) AS first_buy_timestamp
    FROM
    buy_vol
    GROUP BY
    swapper
    ),
    daily_first_time_buyers AS (
    SELECT
    DATE(b.block_timestamp) AS buy_date,
    COUNT(DISTINCT b.swapper) AS first_time_buyer_count,
    SUM(b.tokens) AS first_time_buyer_volume
    FROM
    buy_vol b
    JOIN
    first_buyers fb ON b.swapper = fb.swapper
    WHERE
    b.block_timestamp = fb.first_buy_timestamp
    GROUP BY
    buy_date
    QueryRunArchived: QueryRun has been archived