MLDZMNmad2
Updated 2023-06-03
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
32
33
34
35
36
›
⌄
with time as (
select
case
when '{{Period}}' = 'Last 24 Hours' then 1
when '{{Period}}' = 'Last 7 Days' then 7
when '{{Period}}' = 'Last 30 Days' then 30
when '{{Period}}' = 'All Time' then 500
else 30
end as time_gp
),
tb2 as (select
RECORDED_HOUR::date as day,
avg(close) as price_token
from solana.core.fact_token_prices_hourly where SYMBOL='SOL'
group by 1)
select
date_trunc('{{Time_basis}}',BLOCK_TIMESTAMP) as date,
count(distinct TX_ID) as sale_no,
count(distinct PURCHASER) as buyer_no,
count (distinct s.MINT) as no_NFTs,
sum(SALES_AMOUNT) as volume_SOL,
sum(SALES_AMOUNT*price_token) as volume_usd,
avg(SALES_AMOUNT) as average_volume_SOL,
avg(SALES_AMOUNT*price_token) as average_volume,
lag(average_volume) ignore nulls over(ORDER BY date ASC) as lag_volume,
((average_volume-lag_volume)/volume_usd)*100 as deviation_price,
avg(average_volume) OVER (ORDER BY date ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) as MA_7_Days,
min(SALES_AMOUNT*price_token) as floor_price,
max(SALES_AMOUNT*price_token) as highest_price,
median(SALES_AMOUNT*price_token) as median_price
from solana.core.fact_nft_sales s
left outer join solana.core.dim_labels b on s.mint=b.address
join tb2 y on s.BLOCK_TIMESTAMP::date=y.day
where SUCCEEDED='TRUE'
Run a query to Download Data