OneDataAnalystPercent of genopets buyers that stake gene
    Updated 2022-06-01
    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