with initial_data_one as
(
select
nft_id,
max(block_timestamp) as last_date
from flow.core.fact_nft_sales
where nft_collection = 'A.0b2a3299cc857e29.TopShot'
and tx_succeeded = 'true'
group by nft_id
),
initial_data_two as
(
select
id1.last_date as last_date,
fns.buyer as buyer,
fns.nft_id as nft_id
from flow.core.fact_nft_sales fns
join initial_data_one id1
on id1.nft_id = fns.nft_id
and id1.last_date = fns.block_timestamp
where fns.nft_collection = 'A.0b2a3299cc857e29.TopShot'
and fns.tx_succeeded = 'true'
)
select
last_date::date as day,
play_type,
count(distinct buyer) as number_of_buyers,
sum(number_of_buyers) OVER (ORDER BY day) as cummulative_number_of_holders
from flow.core.dim_topshot_metadata dtm
join initial_data_two id2
on id2.nft_id = dtm.nft_id
where nft_collection = 'A.0b2a3299cc857e29.TopShot'
group by day, play_type