SocioCryptouser segmentation copy
Updated 2023-05-02
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
›
⌄
-- forked from sadegh_golandam / user segmentation @ https://flipsidecrypto.xyz/sadegh_golandam/q/2023-05-02-12-20-pm-bDihv-
SELECT buyer,
count(DISTINCT tx_id) n_purchase,
count(CASE when nft_collection = 'A.0b2a3299cc857e29.TopShot' then tx_id end) as NBA_TOP_SHOT,
count(CASE when nft_collection = 'A.e4cf4bdc1751c65d.AllDay' then tx_id end) as NFL_ALL_DAY,
count(CASE when nft_collection = 'A.4eded0de73020ca5.CricketMoments' then tx_id end) as Cricket_Moments,
count(CASE when nft_collection = 'A.87ca73a41bb50ad5.Golazos' then tx_id end) as La_Liga_Golazos
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
ORDER BY n_purchase DESC
Run a query to Download Data