datavortextotal polygon
    Updated 2025-04-17
    WITH DailyPrices AS (
    SELECT
    DATE(hour) AS price_date,
    AVG(price) AS avg_daily_price
    FROM
    polygon.price.ez_prices_hourly
    WHERE
    token_address = LOWER('0x43C73b90E0C2A355784dCf0Da12f477729b31e77')
    GROUP BY
    DATE(hour)
    ),
    ClassifiedSwaps AS (
    SELECT
    block_timestamp,
    DATE(block_timestamp) AS swap_date,
    tx_hash,
    origin_from_address AS wallet,
    contract_address AS contract,
    decoded_log,
    (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
    polygon.core.ez_decoded_event_logs
    LEFT JOIN DailyPrices dp ON DATE(block_timestamp) = dp.price_date
    WHERE
    contract_address IN ('0x2f3c540b426ee34afaf6597d8e3575f54bd08ea8', '0x3c7271a4e0f9221605017fbb021926944ab8b6fb', '0x11bcc5b9fbcd882952fec8ae95214036a981797b')
    AND event_name = 'Swap'
    )
    SELECT
    COUNT(DISTINCT wallet) AS "total wallets",
    COUNT(DISTINCT tx_hash) AS "total distinct tx_hash",
    Last run: about 1 month ago
    total wallets
    total distinct tx_hash
    total sold soil
    total bought soil
    total volume usd
    1
    694012832913891894.286171213898603.801370924486146.3895753
    1
    66B
    88s