adambalapv11
Updated 2023-05-14
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
›
⌄
with a as
(SELECT
sum(PRICE) as volume ,MIN (PRICE) as prices ,DATE_TRUNC('day',BLOCK_TIMESTAMP) as date --,percentile_cont(0.100) within group (order by PRICE) as doodles_estimated_floor
FROM ethereum.core.ez_nft_sales
WHERE EVENT_TYPE = 'sale' and CURRENCY_SYMBOL = 'ETH' and LOWER(NFT_ADDRESS)=lower('0x960b7a6bcd451c9968473f7bbfd9be826efd549a')
group by date HAVING prices >0.1)
, B AS (SELECT PRICESS ,DATE ,
LAG(PRICESS,1) IGNORE NULLS OVER (ORDER BY date) as a,
((PRICESS-a)/PRICESS)*100 as PRICE_daily_var
FROM
(SELECT avg(prices) over(
order by date
rows between 1 preceding and current row ) PRICESS, DATE
FROM A ))
SELECT sum(PRICE_daily_var) as PRICE_daily_var FROM B WHERE date BETWEEN DATEADD(day, -30, GETDATE()) AND GETDATE() -- group by 1-- LIMIT 1
Run a query to Download Data