OneDataAnalystPercent of genopets buyers that stake gene
Updated 2022-06-01Copy 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
28
29
30
31
32
33
34
35
36
›
⌄
WITH stkwallets AS
(
SELECT DISTINCT(INSTRUCTION:accounts[0]) AS unique_stakers
FROM flipside_prod_db.solana.fact_events
WHERE program_id = 'StaKe9nb7aUjXpjpZ45o6uJBsZxj2BWCDBtjk8LCg2v'
AND block_timestamp::date >= '2022-05-18'),
numofstakers AS
(
SELECT count(DISTINCT(INSTRUCTION:accounts[0])) AS unique_stakers,
row_number() OVER (ORDER BY unique_stakers) AS rownum1
FROM flipside_prod_db.solana.fact_events
WHERE program_id = 'StaKe9nb7aUjXpjpZ45o6uJBsZxj2BWCDBtjk8LCg2v'
AND block_timestamp::date >= '2022-05-18' ),
genopurchasers AS
(
SELECT date_trunc('day', block_timestamp) AS date,
purchaser
FROM solana.dim_labels
JOIN solana.fact_nft_sales
ON solana.dim_labels.address = solana.fact_nft_sales.mint
WHERE label LIKE '%genopets%'
AND date <= '2022-05-18' ),
uniquegenopurchaser AS
(
SELECT DISTINCT(purchaser)
FROM genopurchasers ),
count_geno_stake AS
(
SELECT count(unique_stakers) AS count_geno_stake,
row_number() OVER (ORDER BY count_geno_stake) AS rownum0
FROM stkwallets
INNER JOIN uniquegenopurchaser
ON stkwallets.unique_stakers = uniquegenopurchaser.purchaser)
SELECT concat('%',round((count_geno_stake/unique_stakers)*100,2)) AS percent_of_genopets_buyers_that_stake_gene
Run a query to Download Data