amelia-leeCopy of distr
    Updated 2022-12-01
    WITH

    PRICE AS (SELECT DATE_TRUNC ('HOUR',HOUR) AS TIME, AVG (PRICE) AS PRICE
    FROM ethereum.core.fact_hourly_token_prices WHERE TOKEN_ADDRESS = '0x44108f0223a3c3028f5fe7aec7f9bb2e66bef82f' GROUP BY 1)

    SELECT
    DATE_TRUNC ('HOUR',BLOCK_TIMESTAMP) AS DATE,
    COUNT (DISTINCT TX_HASH) AS TXN,
    COUNT (DISTINCT TO_ADDRESS) AS USERS,
    SUM (RAW_AMOUNT / 1e18) AS VOLUME,
    SUM ((RAW_AMOUNT / 1e18) * PRICE ) AS USD,
    CASE
    WHEN (RAW_AMOUNT / 1e18) < 50 THEN 'Less than 50 ACX'
    WHEN (RAW_AMOUNT / 1e18) BETWEEN 50 and 100 THEN '50 and 100 ACX'
    WHEN (RAW_AMOUNT / 1e18) BETWEEN 100 and 500 THEN '100 and 500 ACX'
    WHEN (RAW_AMOUNT / 1e18) BETWEEN 500 and 1000 THEN '500 and 1K ACX'
    WHEN (RAW_AMOUNT / 1e18) BETWEEN 1000 and 10000 THEN '1K and 10K ACX'
    WHEN (RAW_AMOUNT / 1e18) BETWEEN 10000 and 50000 THEN '10K and 50K ACX'
    WHEN (RAW_AMOUNT / 1e18) BETWEEN 50000 and 100000 THEN '50K and 100K ACX'
    ELSE 'More than 100K ACX' END AS TYPE,

    SUM (TXN) OVER (PARTITION BY TYPE ORDER BY DATE) AS CUM_TXN,
    SUM (USERS) OVER (PARTITION BY TYPE ORDER BY DATE) AS CUM_USERS,
    SUM (VOLUME) OVER (PARTITION BY TYPE ORDER BY DATE) AS CUM_VOLUME
    FROM ethereum.core.ez_token_transfers LEFT JOIN PRICE ON DATE_TRUNC ('HOUR',BLOCK_TIMESTAMP) = TIME
    WHERE CONTRACT_ADDRESS = '0x44108f0223a3c3028f5fe7aec7f9bb2e66bef82f' AND FROM_ADDRESS = '0xe50b2ceac4f60e840ae513924033e753e2366487'
    GROUP BY 1,TYPE


    Run a query to Download Data