SocioCryptotimezone user segmentation copy
Updated 2023-05-02Copy Reference Fork
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
›
⌄
WITH nft_buyers as(
SELECT buyer,
count(DISTINCT tx_id) n_purchase,
count(DISTINCT CASE when nft_collection = 'A.0b2a3299cc857e29.TopShot' then tx_id end) as NBA_TOP_SHOT,
count(DISTINCT CASE when nft_collection = 'A.e4cf4bdc1751c65d.AllDay' then tx_id end) as NFL_ALL_DAY,
count(DISTINCT CASE when nft_collection = 'A.4eded0de73020ca5.CricketMoments' then tx_id end) as Cricket_FanCraze,
count(DISTINCT CASE when nft_collection = 'A.87ca73a41bb50ad5.Golazos' then tx_id end) as La_Liga_Golazos,
CASE when NFL_ALL_DAY =0 and Cricket_FanCraze =0 and La_Liga_Golazos = 0 then 'NBA'
when NBA_TOP_SHOT=0 and Cricket_FanCraze =0 and La_Liga_Golazos = 0 then 'NFL'
when NBA_TOP_SHOT=0 and NFL_ALL_DAY =0 and La_Liga_Golazos = 0 then 'Cricket'
when NBA_TOP_SHOT=0 and NFL_ALL_DAY =0 and Cricket_FanCraze =0 then 'La Liga'
else 'Diverse Sport Users' end as catagory,
avg(hour(block_timestamp)) as hours
FROM flow.core.ez_nft_sales
WHERE block_timestamp::date >= current_date - 90
AND nft_collection in ('A.0b2a3299cc857e29.TopShot', 'A.e4cf4bdc1751c65d.AllDay',
'A.4eded0de73020ca5.CricketMoments', 'A.87ca73a41bb50ad5.Golazos')
GROUP BY 1
)
SELECT catagory,
hours,
avg(n_purchase)
FROM nft_buyers
GROUP BY 1,2
Run a query to Download Data