flipside

    Wallets Categories
    datavortexWallets Categories
    Updated 2025-04-17
    WITH EthereumDailyPrices AS (
    SELECT
    DATE(hour) AS price_date,
    AVG(price) AS avg_daily_price
    FROM ethereum.price.ez_prices_hourly
    WHERE token_address = LOWER('0x54991328ab43c7d5d31c19d1b9fa048e77b5cd16') -- WETH
    GROUP BY DATE(hour)
    ),
    EthereumClassifiedSwaps AS (
    SELECT
    block_timestamp,
    DATE(block_timestamp) AS swap_date,
    tx_hash,
    origin_from_address AS wallet,
    contract_address AS contract,
    (decoded_log:amount0) / 1e18 AS soil_amount_tokens,
    CASE
    WHEN CAST(decoded_log:amount0 AS DECIMAL) < 0 THEN 'Buy SOIL'
    WHEN CAST(decoded_log:amount0 AS DECIMAL) > 0 THEN 'Sell SOIL'
    ELSE 'Unknown'
    END AS buy_soil_or_sell_soil,
    ABS((decoded_log:amount0) / 1e18) * dp.avg_daily_price AS volume_usd
    FROM ethereum.core.ez_decoded_event_logs
    LEFT JOIN EthereumDailyPrices dp ON DATE(block_timestamp) = dp.price_date
    WHERE
    contract_address = LOWER('0x7090Cc8DFA04b0BBAc6520063c9c1ea677C368a0') -- SOIL/WETH
    AND event_name = 'Swap'
    ),
    PolygonDailyPrices AS (
    SELECT
    DATE(hour) AS price_date,
    AVG(price) AS avg_daily_price
    FROM polygon.price.ez_prices_hourly
    WHERE token_address = LOWER('0x43C73b90E0C2A355784dCf0Da12f477729b31e77') -- USDT
    GROUP BY DATE(hour)
    ),
    Last run: about 1 month ago
    Volume Range (USD)
    Number of Wallets
    1
    		0-100 USD2979
    2
    		100-500 USD1863
    3
    		500-1000 USD673
    4
    		1000-5000 USD1003
    5
    		5000-10000 USD228
    6
    		10000-50000 USD147
    7
    		50000-100000 USD36
    8
    		100000-1000000 USD18
    9
    		1000000+ USD4
    9
    201B
    55s