price, seller, tx_succeeded, tx_id

from flow.core.fact_nft_sales

where tx_succeeded='TRUE'),

tab2 as (select nft_id, nft_collection, player, team, play_type

from flow.core.dim_topshot_metadata)

SELECT buyer, count(tab1.nft_id) as purchase_nft, play_type

from tab1 left join tab2

on tab1.nft_id=tab2.nft_id

where play_type is not null

group by 1,3),

table2 as (with tab1 as (select block_timestamp::date as date, buyer, marketplace, nft_collection, nft_id,

price, seller, tx_succeeded, tx_id

from flow.core.fact_nft_sales

where tx_succeeded='TRUE'),

tab2 as (select nft_id, nft_collection, player, team, play_type

from flow.core.dim_topshot_metadata)

SELECT seller, count(tab1.nft_id) as sell_nft, play_type

from tab1 left join tab2

on tab1.nft_id=tab2.nft_id

where play_type is not null

group by 1,3)

select buyer, purchase_nft, sell_nft, purchase_nft-sell_nft, table1.play_type as play_type_

from table1 left join table2 on table1.buyer=table2.seller and table1.play_type=table2.play_type

order by 1)

select sum(purchase_nft)-sum(sell_nft), play_type_

from table3

group by 2