headitmanagerTop 10 NFTs
Updated 2022-06-15
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
›
⌄
with users_firsttime as (select signers[0] as address, min(block_timestamp::date) as first_tx_date
from solana.fact_transactions
group by address)
, users_mint as (select PURCHASER,min(block_timestamp::date) as first_mint_date from solana.fact_nft_mints
group by PURCHASER)
,nft_users as (select PURCHASER,first_mint_date from users_mint inner join users_firsttime
on first_tx_date=first_mint_date and PURCHASER=address
)
,top10nft as ( select count(*) , label
from solana.fact_nft_mints inner join solana.dim_labels on mint=ADDRESS
where PURCHASER in (select PURCHASER from nft_users)
group by label
order by count(*) DESC
limit 10)
, operation as ( select TX_ID,PURCHASER,
case
when instructions[0] like '%mintAuthority%' then 'mint'
else 'transaction' end as opt
from solana.fact_transactions inner join nft_users ON
PURCHASER=signers[0] and solana.fact_transactions.block_timestamp::date > first_mint_date)
select * from top10nft
Run a query to Download Data