NakedCollector2024-06-01 12:51 PM
    Updated 2024-06-01
    WITH fresh_wallet_traders AS (
    SELECT from_address, min(block_timestamp) AS first_transaction
    FROM ethereum.core.fact_transactions
    WHERE from_address IN (
    SELECT trader
    FROM crosschain.defi.ez_dex_swaps
    WHERE block_timestamp > CURRENT_TIMESTAMP() - interval '1 day'
    )
    GROUP BY from_address
    HAVING MIN(block_timestamp) > CURRENT_TIMESTAMP() - interval '1 day'
    ),

    fresh_wallet_buys AS (
    SELECT
    SUM(amount_out_usd) AS total,
    token_out
    FROM crosschain.defi.ez_dex_swaps
    WHERE trader IN (SELECT from_address FROM fresh_wallet_traders)
    AND amount_out_usd IS NOT NULL
    AND blockchain = 'ethereum'
    AND block_timestamp > CURRENT_TIMESTAMP() - interval '1 day'
    GROUP BY token_out
    HAVING SUM(amount_out_usd) > 1000
    ORDER BY total DESC
    ),

    top_tokens AS (
    SELECT DISTINCT
    to_varchar(fb.total, '$999,999,999') AS total_bought,
    dc.symbol AS symbol,
    fb.token_out AS token_address,
    fb.total AS bought
    FROM fresh_wallet_buys fb
    INNER JOIN crosschain.core.dim_contracts dc
    ON dc.address = fb.token_out
    WHERE dc.symbol NOT LIKE '%USD%'
    QueryRunArchived: QueryRun has been archived