with metadata as (
select
nft_id,
play_type
from flow.core.dim_topshot_metadata
where nft_collection like '%TopShot%'
),
buyer_ as (
select
block_timestamp::date as date,
buyer,
seller,
nft_id
from flow.core.fact_nft_sales
where tx_succeeded = True
order by date asc
),
seller_ as (
select
block_timestamp::date as date,
seller,
buyer,
nft_id
from flow.core.fact_nft_sales
where tx_succeeded = True
order by date asc
),
Holdings as (
select
(seller_.date - buyer_.date) as holding_time,
metadata.play_type
from metadata
inner join buyer_ on buyer_.nft_id = metadata.nft_id
inner join seller_ on seller_.seller = buyer_.buyer and seller_.nft_id = metadata.nft_id
where seller_.date > buyer_.date
order by holding_time desc