avg(price_usd) as avg_price

from flow.core.fact_prices

where day >= CURRENT_DATE - 180

and token = 'Flow'

group by 1),

top_shot as ( select trunc(block_timestamp,'day') as day ,

play_type,

tx_id,

buyer,

seller,

case when currency = 'A.ead892083b3e2c6c.DapperUtilityCoin' then price else price*avg_price end as price_usd

from flow.core.ez_nft_sales a join flow.core.dim_topshot_metadata b

on a.nft_id = b.nft_id

left outer join flow_price c on a.block_timestamp::date = c.day

where a.nft_collection = 'A.0b2a3299cc857e29.TopShot'

and a.block_timestamp >= CURRENT_DATE - 180)

select

play_type,

count(DISTINCT tx_id) as count_tx,

count(DISTINCT buyer) as total_buyer,

count(DISTINCT tx_id) as total_seller,

sum(price_usd) as volume,

avg(price_usd) as avg_nft_price

from top_shot

group by 1