flipsidecryptoFlow Active NFT Buyers (MoM)
    Updated 2022-11-01
    with result_urls AS (
    SELECT
    result_url,
    split(result_url, '/')[array_size(split(result_url,'/'))-1]::string as dashboard_slug
    FROM bi_analytics.silver.bounties b
    LEFT JOIN bi_analytics.silver.claims cl
    ON b.id = cl.bounty_id
    LEFT JOIN bi_analytics.silver.submissions s
    ON cl.id = s.claim_id
    WHERE payout_currency = 'FLOW'
    AND bounty_type IN ('scavenger-hunt', 'hunt')
    AND wallet_address IS NOT NULL
    --and slug like '%returning-users'
    AND s.status <> 'rejected'
    ),

    dbs_and_queries AS (
    select
    id AS dashboard_id,
    cells.value :formula :queryId::STRING AS query_id
    from "BI_ANALYTICS"."VELOCITY_APP_PROD"."DASHBOARDS" d,
    lateral flatten(input => published :cells) cells
    where latest_slug IN (select dashboard_slug from result_urls)
    and published is not null
    and query_id is not null
    ),
    tx_parsing AS (
    select
    regexp_replace(statement, '\\''', '@') AS parsed
    from "BI_ANALYTICS"."VELOCITY_APP_PROD"."QUERIES"
    where id IN (select query_id from dbs_and_queries)
    ),

    transactions AS (
    select
    C.value::STRING AS tx_id
    Run a query to Download Data