MoDeFi#Flow - Top Shots moment valuable (I) 7
    Updated 2022-07-23
    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