NakedCollectorgrand-chocolate
    Updated 2024-08-19
    WITH tokens AS (
    SELECT contract_address, symbol FROM (
    SELECT '0x2260FAC5E5542a773Aa44fBCfeDf7C193bc2C599' AS contract_address, 'WBTC' AS symbol
    UNION ALL SELECT '0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2', 'WETH'
    -- ... [other token selections remain unchanged]
    ) t
    ),
    token_activity_current AS (
    SELECT
    t.CONTRACT_ADDRESS,
    t.SYMBOL,
    COUNT(DISTINCT tx_hash) AS transaction_count,
    COUNT(DISTINCT FROM_ADDRESS) AS unique_users,
    SUM(AMOUNT_USD) AS total_volume
    FROM
    ethereum.core.ez_token_transfers e
    JOIN tokens t ON e.CONTRACT_ADDRESS = t.CONTRACT_ADDRESS
    WHERE
    block_timestamp >= DATEADD(month, -1, CURRENT_DATE())
    GROUP BY
    t.CONTRACT_ADDRESS,
    t.SYMBOL
    ),
    token_activity_previous AS (
    SELECT
    t.CONTRACT_ADDRESS,
    COUNT(DISTINCT tx_hash) AS transaction_count,
    COUNT(DISTINCT FROM_ADDRESS) AS unique_users
    FROM
    ethereum.core.ez_token_transfers e
    JOIN tokens t ON e.CONTRACT_ADDRESS = t.CONTRACT_ADDRESS
    WHERE
    block_timestamp BETWEEN DATEADD(month, -2, CURRENT_DATE()) AND DATEADD(month, -1, CURRENT_DATE())
    GROUP BY
    t.CONTRACT_ADDRESS
    ),
    QueryRunArchived: QueryRun has been archived