adriaparcerisasMFL Player Drop Day 06/11/24: Stats 3
Updated 2024-11-12
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
with
stats as (
SELECT
'Regular Purchase' as purchase_type,
case when price=9.99 then 'Kickoff pack'
when price=34.99 then 'Standard pack'
when price=119.99 then 'Rare pack'
--when price=599.99 then 'Legendary pack'
end as pack_type,
-- price,
COUNT(DISTINCT x.tx_id) AS packs_sold,
case when pack_type = 'Kickoff pack' then 125 when pack_type = 'Standard pack' then 160
else 80 end as total_available,
COUNT(DISTINCT buyer) AS users
--total_available*price as volume
-- SUM(case when pack_type = 'Kickoff pack' then 9.99
--when pack_type = 'Standard pack' then 24.99 else 89.99 end ) 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'
group by 1,2
having pack_type is not null
),
stats2 as(
SELECT
'$MFL Purchase' as purchase_type,
pack_type as pack_type,
-- case when pack_type='Kickoff pack' then 8000
--when pack_type='Standard pack' then 20000
--else 72000 end as price,
total_available-packs_sold AS packs_sold,
case when pack_type = 'Kickoff pack' then 125 when pack_type = 'Standard pack' then 160
else 80 end as total_available,
total_available-packs_sold AS users,
SUM(case when pack_type = 'Kickoff pack' then 8000*(total_available-packs_sold)
when pack_type = 'Standard pack' then 28000*(total_available-packs_sold)
QueryRunArchived: QueryRun has been archived