drone-mostafaLaunch
Updated 2023-05-13
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
›
⌄
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