tx_id,

nft_id,

sum(price) as usd_volume

from flow.core.fact_nft_sales

where tx_succeeded = 'TRUE'

and currency != 'A.1654653399040a61.FlowToken'

and nft_collection = 'A.0b2a3299cc857e29.TopShot'

group by 1,2),

base2 as (select

tx_id,

nft_id,

sum(price) * avg(price_usd) as flow_volume_usd

from flow.core.fact_nft_sales a

join flow.core.fact_prices b

on date_trunc('day', a.block_timestamp) = date_trunc('day', b.timestamp)

where tx_succeeded = 'TRUE'

and currency = 'A.1654653399040a61.FlowToken'

and nft_collection = 'A.0b2a3299cc857e29.TopShot'

group by 1,2),

base3 as (select * from base

union

select * from base2),

flow as (select play_type,

count(distinct(tx_id)) as count_tx_flow,

sum(usd_volume) as total_sales_volume,

avg(usd_volume) as avg_sales_volume,

median(usd_volume) as median_sales_volume,

max(usd_volume) as max_sales_volume,

min(usd_volume) as min_sales_volume,

1 as numbering,

sum(numbering) over (order by total_sales_volume desc) as sales_vol,

sum(numbering) over (order by avg_sales_volume desc) as avg_sales_vol,