NakedCollectorSNX Case Study
    Updated 2024-08-12
    -- forked from Aave Case Study @ https://flipsidecrypto.xyz/studio/queries/d7e4c854-1b66-40a5-ba7b-cace44ed8e62

    SELECT
    DATE_TRUNC('week', t.BLOCK_TIMESTAMP) AS Date,
    t.symbol,
    AVG(t.AMOUNT_USD) AS "Average Transfer",
    SUM(t.AMOUNT_USD) AS "Total Transfer",
    COUNT(DISTINCT t.ORIGIN_FROM_ADDRESS) AS "Address Count",
    AVG(p.price) AS "Average Price"
    FROM
    ethereum.core.ez_token_transfers t
    LEFT JOIN (
    SELECT
    date_trunc('week', HOUR) as price_date,
    SYMBOL,
    AVG(PRICE) as price
    FROM
    ethereum.price.ez_prices_hourly
    WHERE
    TOKEN_ADDRESS IN ('0xc011a73ee8576fb46f5e1c5751ca3b9fe0af2a6f')
    GROUP BY
    1, 2
    ) p ON DATE_TRUNC('week', t.BLOCK_TIMESTAMP) = p.price_date AND t.symbol = p.SYMBOL
    WHERE
    t.CONTRACT_ADDRESS IN ('0xc011a73ee8576fb46f5e1c5751ca3b9fe0af2a6f')
    AND t.BLOCK_TIMESTAMP < '2021-03-01'
    GROUP BY 1, 2
    ORDER BY 1, 2


    QueryRunArchived: QueryRun has been archived