with prices as(

select date(block_hour) as pdate, avg(price_usd) as avg_price

from algorand.core.ez_price_pool_balances

group by pdate

),

all_data as (

select

purchaser, initcap(nft_marketplace) as nft_marketplace,

count(distinct tx_group_id) as sale_count,

count(distinct purchaser) as purchaser_count,

count(distinct collection_name) as collections,

count(distinct nft_asset_id) as nft_count,

sum(total_sales_amount) as algo_vol,

sum(total_sales_amount * avg_price) as usd_vol,

avg(total_sales_amount) as avg_algo_vol,

avg(total_sales_amount * avg_price) as avg_usd_vol

from algorand.nft.ez_nft_sales t1 join prices t2 on t1.block_timestamp::date = pdate

where nft_marketplace ilike any ('{{nft_marketplace}}', '{{nft_marketplace2}}')

and date(block_timestamp) >= CURRENT_DATE - {{n_days}}

and total_sales_amount > 0

GROUP by 1,2

select initcap(nft_marketplace) as nft_marketplace,

case when usd_vol < 10 then 'Less Than $10'

when usd_vol >= 10 and usd_vol < 100 then '$10 - $100'

when usd_vol >= 100 and usd_vol < 1000 then '$100 - $1000'

else 'More Than $1000' end as type,

count(*) as purchaser_counter

from all_data

group by 1,2