MoDeFi#Flow - Top Shots moment valuable (I) 7
Updated 2022-07-23Copy 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
›
⌄
with owners as
(select *
from
(select *,
row_number() over (partition by NFT_COLLECTION,NFT_ID order by BLOCK_TIMESTAMP desc) as rank
from
(select BLOCK_TIMESTAMP,NFT_COLLECTION,NFT_ID,PLAY_TYPE, EVENT_DATA:to as owner
from flow.core.fact_events a
join flow.core.dim_topshot_metadata b
on b.NFT_COLLECTION=a.EVENT_CONTRACT and b.NFT_ID=a.EVENT_DATA:id
where EVENT_TYPE='Deposit' and TX_SUCCEEDED=TRUE))
where rank='1'
),
top_holders as (
select OWNER, count(*) as nfts
from owners
group by OWNER
order by nfts desc
limit 20)
select ' '||OWNER as owners,PLAY_TYPE, count(*) as nfts
from owners
where OWNER in (select OWNER from top_holders)
group by OWNER,PLAY_TYPE
Run a query to Download Data