OCTOPUS😎Ethereum Token Fresh Wallet Accumulation Over 1D
    Updated 9 days ago
    With fresh_wallet_traders AS(
    SELECT from_address, min(block_timestamp)
    FROM ethereum.core.fact_transactions
    WHERE from_address IN (
    SELECT trader
    FROM crosschain.defi.ez_dex_swaps
    WHERE block_timestamp > CURRENT_TIMESTAMP() - interval '1 days'
    )
    GROUP BY 1
    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 days'
    GROUP BY 2
    HAVING SUM(amount_out_usd) > 1000
    ORDER BY 1 DESC
    )

    SELECT DISTINCT
    to_varchar(total, '$999,999,999') AS "Total Bought",
    symbol AS "Symbol",
    token_out AS "Token Address",
    total AS "Bought"
    FROM fresh_wallet_buys
    INNER JOIN crosschain.core.dim_contracts
    ON address = token_out
    WHERE symbol NOT LIKE '%USD%'
    AND symbol NOT LIKE '%DAI%'
    Last run: 9 days ago
    No Data to Display
    0
    2B
    22s