elsinaMagic Eden Top 5 Collection
Updated 2022-09-25Copy 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
›
⌄
with sol_daily_price as (
select block_timestamp::date as "day",sum(SWAP_from_AMOUNT)/sum(SWAP_TO_AMOUNT) as "price" from solana.fact_swaps
where SUCCEEDED=TRUE and swap_to_mint= 'So11111111111111111111111111111111111111112' and swap_from_mint = 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v' and "day" >= '2022-01-01'
group by "day"
order by "day"
),top_5_nft as (
select
count(*) as "number tx",
sum(sales_amount * "price") as "valume",
sum(1) over(order by "valume" desc) as "rank" ,
iff("rank">5,-1,"rank") as "nft orders",
iff("rank">5,'other',label) as "nft name"
from solana.core.fact_nft_sales, solana.core.dim_labels , sol_daily_price
where
block_timestamp::date >= '2022-01-01' AND
marketplace in ('magic eden v1','magic eden v2') and
SUCCEEDED = TRUE and
block_timestamp::date = "day" and
mint = address
group by label
), top_5 as (
select "nft orders" as "rank","nft name" as "project name", sum("valume") as "Volume" ,sum("number tx") as "Buy count"
from top_5_nft
group by "nft orders","nft name"
order by "rank"
)
select concat("rank"::string,'. ',"project name") as "Title",* from top_5 where "rank" > 0
Run a query to Download Data