ally2023-06-13 02:51 PM
    WITH
    users AS (
    SELECT
    nft_to_address AS user_address
    FROM
    ethereum.core.ez_nft_transfers
    WHERE
    project_name IS NOT NULL
    AND nft_to_address NOT LIKE '0x00000%'
    UNION ALL
    SELECT
    nft_to_address AS user_address
    FROM
    polygon.core.ez_nft_transfers
    WHERE
    project_name IS NOT NULL
    AND nft_to_address NOT LIKE '0x00000%'
    ),
    user_transactions AS (
    SELECT
    user_address,
    COUNT(*) AS transaction_count
    FROM
    users
    GROUP BY
    user_address
    ),
    transaction_ranges AS (
    SELECT
    CASE
    WHEN transaction_count = 1 THEN '1 transaction'
    WHEN transaction_count BETWEEN 2 AND 5 THEN '2-5 transactions'
    WHEN transaction_count BETWEEN 6 AND 10 THEN '6-10 transactions'
    WHEN transaction_count BETWEEN 11 AND 20 THEN '11-20 transactions'
    WHEN transaction_count BETWEEN 21 AND 30 THEN '21-30 transactions'
    ELSE '30+ transactions'