adambala..
Updated 2022-10-30Copy Reference Fork
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
›
⌄
with marketplaces as
(select marketplace,
BLOCK_TIMESTAMP::date AS time ,
count (distinct tx_id) as transactions,
count (distinct purchaser) as Buyers,
count (distinct seller) as Sellers,
count (distinct mint) as token,
sum (sales_amount) as SOL,
sum (sales_amount*solprice) as USD
from solana.core.fact_nft_sales AS PARAMETERS INNER JOIN
(select block_timestamp::date as time, avg (swap_to_amount/swap_from_amount) as SOLprice
from solana.fact_swaps where swap_from_mint = 'So11111111111111111111111111111111111111112' and swap_to_mint in ('EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v','Es9vMFrzaCERmJfrF4H2FYD4KCoNkY11McCe8BenwNYB') and swap_to_amount > 0 and swap_from_amount > 0 and succeeded = 'TRUE' group by 1) AS PRICESS
on PARAMETERS.block_timestamp::date = PRICESS.time
where (marketplace ILIKE '%Coral Cube%' or marketplace ILIKE 'hyperspace') and succeeded = 'TRUE' GROUP BY 1,2)
select
time ,
marketplace ,
transactions ,
Buyers ,
Sellers ,
token ,
SOL ,
USD ,
sum(transactions) over (order by time) as cum_transactions ,
sum(Buyers) over (order by time) as cum_Buyers ,
sum(Sellers) over (order by time) as cum_Sellers ,
sum(token) over (order by time) as cum_token ,
sum(SOL) over (order by time) as cum_SOL ,
sum(USD) over (order by time) as cum_USD
from marketplaces
Run a query to Download Data