0xHaM-dJolly user stats table
Updated 2024-05-09
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
›
⌄
-- forked from adriaparcerisas / Jolly user stats table @ https://flipsidecrypto.xyz/adriaparcerisas/q/mZGgkky7PAPB/jolly-user-stats-table
-- Two extra data point that came to mind to maybe add;
-- total unique holders AND accounts that hold 1, 5 (through 9), 10 ( through 14), or 15+ JJ's.
-- Holding these quantities gives you special access - on their site they have a list
with
mints as (
select
distinct proposer as user,
sum(1) as nft_minted
--count(distinct x.tx_id) as nft_minted
--sum(case when event_type='Mint' then transaction_result:events[0]:value:fields[0] end) as volume,
from flow.core.fact_events x
join flow.core.dim_contract_labels y on x.event_contract=y.event_contract
join flow.core.fact_transactions z on x.tx_id=z.tx_id
where y.event_contract like '%JollyJokers%' and event_type='Mint'
and z.tx_succeeded = true and x.block_timestamp>='2022-10-17'
group by 1
),
buys as (
select
distinct event_data:buyer as user,
count(distinct tx_id) as nft_bought
--CAST(event_data:salePrice AS DECIMAL(18,8)) as price
from flow.core.fact_events s
where
tx_succeeded = true and event_type='ListingCompleted'
and event_data:nftType='A.699bf284101a76f1.JollyJokers.NFT'
and event_data:buyer is not null and event_data:salePrice is not null
and event_data:purchased= 'true'
group by 1
),
sales as (
select
distinct event_data:storefrontAddress as user,
QueryRunArchived: QueryRun has been archived