Updated 2023-05-13
    WITH BASE AS (SELECT
    date_trunc ('day',hour) AS TIME,SYMBOL,TOKEN_ADDRESS,AVG(PRICE) AS PRICE,rank() over (partition by SYMBOL order by TIME asc ) as Day

    FROM crosschain.core.ez_hourly_prices

    WHERE TOKEN_ADDRESS IN(
    '0x6982508145454ce325ddbe47a25d4ec3d2311933', --PEPE
    '0x6b89b97169a797d94f057f4a0b01e2ca303155e4', --CHAD
    '0x95ad61b0a150d79219dcf64e1e6cc01f0b64c4ce', --SHIBA
    '0xba2ae424d960c26247dd6c32edc70b295c744c43' --Dogecoin
    )
    GROUP BY 1,2,3
    qualify day <= 30),


    first_p as (

    SELECT date_trunc ('day',hour) AS dataa,SYMBOL,median(PRICE) AS F_PRICE,rank() over (partition by SYMBOL order by dataa asc ) as Days FROM crosschain.core.ez_hourly_prices WHERE TOKEN_ADDRESS = '0x6982508145454ce325ddbe47a25d4ec3d2311933' GROUP BY 1,2 qualify Days <= 7 UNION -- PEPE
    SELECT date_trunc ('day',hour) AS dataa,SYMBOL,median(PRICE) AS F_PRICE,rank() over (partition by SYMBOL order by dataa asc ) as Days FROM crosschain.core.ez_hourly_prices WHERE TOKEN_ADDRESS = '0x6b89b97169a797d94f057f4a0b01e2ca303155e4' GROUP BY 1,2 qualify Days <= 7 UNION -- PEPE
    SELECT date_trunc ('day',hour) AS dataa,SYMBOL,median(PRICE) AS F_PRICE,rank() over (partition by SYMBOL order by dataa asc ) as Days FROM crosschain.core.ez_hourly_prices WHERE TOKEN_ADDRESS = '0x95ad61b0a150d79219dcf64e1e6cc01f0b64c4ce' GROUP BY 1,2 qualify Days <= 7 UNION -- PEPE
    SELECT date_trunc ('day',hour) AS dataa,SYMBOL,median(PRICE) AS F_PRICE,rank() over (partition by SYMBOL order by dataa asc ) as Days FROM crosschain.core.ez_hourly_prices WHERE TOKEN_ADDRESS = '0xba2ae424d960c26247dd6c32edc70b295c744c43' GROUP BY 1,2 qualify Days <= 7 -- PEPE
    )


    SELECT day, t1.SYMBOL, AVG(PRICE) AS PRICEs, avg (F_PRICE) as F_PRICEs,
    (PRICEs - F_PRICEs) / F_PRICEs * 100 as "% Change", PRICEs / F_PRICEs as "Growth n Times "
    FROM BASE t1 LEFT JOIN first_p t2 on t1.SYMBOL = t2.SYMBOL

    GROUP by 1,2


    Run a query to Download Data