0xaimanHow much Aurorian NFTs are users holding?
Updated 2022-07-18
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 raw as (with k as (select mint -- count(distinct CONTRACT_ADDRESS)
from solana.core.dim_nft_metadata
where project_name ='Aurory')
select block_timestamp, purchaser, k.mint from solana.core.fact_nft_sales m inner join k on m.mint=k.mint),
cp as (select post_token_balances,POST_TOKEN_BALANCES[0]:mint as mint,POST_TOKEN_BALANCES[0]:owner as owner, txn.block_timestamp as t ---raws
from solana.core.fact_transactions txn
inner join raw on raw.mint=txn.POST_TOKEN_BALANCES[0]:mint),
lat as ( select mint, max(t) as tm --laterst timestmap
from cp
group by 1 order by 1),
ch as (select tm, lat.mint, cp.owner ----current holder via purchase
from cp inner join lat on cp.t=lat.tm)
select holdings, count(distinct owner) as n_address from (select owner, count(distinct mint ) as n_nft, case when n_nft=1 then 'Holds 1 NFT'
when n_nft=2 then 'Holds 2 NFT'
when n_nft=3 then 'Holds 3 NFT'
when n_nft=4 then 'Holds 4 NFT'
when n_nft>=5 and n_nft<10 then 'Holds 5 or more NFT'
when n_nft>=10 and n_nft<20 then 'Holds 10 or more NFT'
when n_nft>=20 then 'Holds 20 or more NFT' end as holdings
from ch
group by 1 order by 2 desc)
group by 1 order by 2 desc
limit 10000
Run a query to Download Data