stats as (

SELECT

case when price=9.99 then 'Kickoff pack'

when price=34.99 then 'Standard pack'

when price=119.99 then 'Rare pack'

end as type,

price,

COUNT(DISTINCT x.tx_id) AS packs_sold,

case when type='Kickoff pack' then packs_sold+2 when type='Standard pack' then packs_sold+1 else packs_sold end as total_packs_sold, --adding missing txs

case when type = 'Kickoff pack' then 125 when type = 'Standard pack' then 160

else 80 end as total_available,

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

case when type='Kickoff pack' then COUNT(DISTINCT buyer)+2 when type='Standard pack' then COUNT(DISTINCT buyer)+1 else COUNT(DISTINCT buyer) 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>='2024-11-06 22:00' --and x.block_timestamp<'2024-07-01 17:40'

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