select case when '{{TimeSpan}}' = 'last month' then 30

when '{{TimeSpan}}' = 'last 3 months' then 90

when '{{TimeSpan}}' = 'last 6 months' then 180

when '{{TimeSpan}}' = 'last year' then 365

when '{{TimeSpan}}' = 'From the beginning until now' then 720

end type

from algorand.nft.ez_nft_sales

group by 1

),

ALgoPrice as (

select date_trunc(day,block_hour) date,

AVG (price_usd) price

from algorand.core.ez_price_pool_balances

group by 1

select COLLECTION_NAME ,

COUNT (DISTINCT tx_group_id) num_sales,

COUNT (DISTINCT purchaser) num_purchasers,

COUNT (DISTINCT collection_name) num_collections ,

COUNT (DISTINCT nft_asset_id) num_NFTs,

sum (total_sales_amount) Volume,

sum (total_sales_amount*price) USD_volume,

avg (total_sales_amount) AVG_volume,

avg (total_sales_amount*price) AVG_volume_usd,

median (total_sales_amount) median_volume,

median (total_sales_amount*price) MEdian_volume_usd,

min (total_sales_amount) min_volume,

min (total_sales_amount*price) min_volume_usd,

max (total_sales_amount) max_volume,

max (total_sales_amount*price) max_volume_usd

from algorand.nft.ez_nft_sales join ALgoPrice on block_timestamp::date = date

where nft_marketplace = '{{Marketplace}}'

and BLOCK_TIMESTAMP::date >= CURRENT_DATE - (select * from Timespan )

and total_sales_amount > 0

and COLLECTION_NAME is not NULL