NakedCollectoracademic-aqua
    Updated 2024-08-12
    WITH base_data AS (
    SELECT
    DATE_TRUNC('day', 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('day', 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('day', 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
    )

    SELECT
    b.*,
    AVG("Average Transfer") OVER (ORDER BY Date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS "MA4_Avg_Transfer",
    AVG("Average Transfer") OVER (ORDER BY Date ROWS BETWEEN 13 PRECEDING AND CURRENT ROW) AS "MA12_Avg_Transfer",
    AVG("Address Count") OVER (ORDER BY Date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS "MA4_Address_Count",
    AVG("Address Count") OVER (ORDER BY Date ROWS BETWEEN 13 PRECEDING AND CURRENT ROW) AS "MA12_Address_Count",
    AVG("Total Transfer") OVER (ORDER BY Date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS "MA4_Total_Transfer",
    AVG("Total Transfer") OVER (ORDER BY Date ROWS BETWEEN 13 PRECEDING AND CURRENT ROW) AS "MA12_Total_Transfer",
    QueryRunArchived: QueryRun has been archived