stats as (

SELECT

case when price=14.99 then 'Kickoff pack'

when price=49.99 then 'Standard pack'

when price=159.99 then 'Rare pack'

end as type,

price,

COUNT(DISTINCT x.tx_id) AS packs_sold,

case when type='Kickoff pack' then packs_sold+1 when type='Standard pack' then packs_sold+3 else packs_sold+1 end as total_packs_sold, --adding mfl buys

case when type = 'Kickoff pack' then 300 when type = 'Standard pack' then 300

else 150 end as total_available,

((total_available-total_packs_sold)/total_available)*100 as pcg_left,

case when type='Kickoff pack' then COUNT(DISTINCT buyer)+1 when type='Standard pack' then COUNT(DISTINCT buyer)+3 else COUNT(DISTINCT buyer)+1 end AS users,

--total_available*price as volume

SUM(price) AS volume

FROM flow.nft.ez_nft_sales x

WHERE nft_collection ilike '%mfl%' and nft_collection ilike '%pack%'

and x.block_timestamp>='2025-01-29 22:00' and x.block_timestamp<'2025-01-29 23:02'

group by 1,2

having type is not null

order by 2 asc

)

select type, price, total_packs_sold, total_available, pcg_left,users,volume

from stats

order by 5,4 -- 4 desc