SajjadiiiFL Us Retention
    Updated 2022-12-19
    WITH base_Flow AS (
    SELECT date_trunc('MONTH' , block_timestamp) AS Date , Count(DISTINCT proposer) AS users_count, Count(DISTINCT tx_id) AS tx_count
    FROM flow.core.fact_transactions WHERE proposer IN (SELECT proposer FROM flow.core.fact_transactions WHERE block_timestamp::date >= '2022-01-01' AND tx_succeeded = TRUE GROUP BY 1 HAVING COUNT(DISTINCT tx_id) > 2000 )
    AND block_timestamp::date >= '2022-01-01' AND tx_succeeded = TRUE GROUP BY 1
    ),

    base_ethereum AS (
    SELECT date_trunc('MONTH' , block_timestamp)AS Date, Count(DISTINCT from_address) AS users_count , Count(DISTINCT tx_hash) AS tx_count
    FROM ethereum.core.fact_transactions WHERE from_address IN (SELECT from_address FROM ethereum.core.fact_transactions WHERE block_timestamp::date >= '2022-01-01' AND status = 'SUCCESS' GROUP BY 1 HAVING COUNT(DISTINCT tx_hash) > 2000 )
    AND block_timestamp::date >= '2022-01-01' AND status = 'SUCCESS' GROUP BY 1
    ),

    base_optimism AS (
    SELECT date_trunc('MONTH' , block_timestamp)AS Date, Count(DISTINCT from_address) AS users_count , Count(DISTINCT tx_hash) AS tx_count
    FROM optimism.core.fact_transactions WHERE from_address IN (SELECT from_address FROM optimism.core.fact_transactions WHERE block_timestamp::date >= '2022-01-01' AND status = 'SUCCESS' GROUP BY 1 HAVING COUNT(DISTINCT tx_hash) > 2000 )
    AND block_timestamp::date >= '2022-01-01' AND status = 'SUCCESS' GROUP BY 1
    ),

    base_gnosis AS (
    SELECT date_trunc('MONTH' , block_timestamp)AS Date, Count(DISTINCT from_address) AS users_count , Count(DISTINCT tx_hash) AS tx_count
    FROM gnosis.core.fact_transactions WHERE from_address IN (SELECT from_address FROM gnosis.core.fact_transactions WHERE block_timestamp::date >= '2022-01-01' AND status = 'SUCCESS' GROUP BY 1 HAVING COUNT(DISTINCT tx_hash) > 2000 )
    AND block_timestamp::date >= '2022-01-01' AND status = 'SUCCESS' GROUP BY 1
    ),

    base_avalanche AS (
    SELECT date_trunc('MONTH' , block_timestamp)AS Date, Count(DISTINCT from_address) AS users_count , Count(DISTINCT tx_hash) AS tx_count
    FROM avalanche.core.fact_transactions WHERE from_address IN (SELECT from_address FROM avalanche.core.fact_transactions WHERE block_timestamp::date >= '2022-01-01' AND status = 'SUCCESS' GROUP BY 1 HAVING COUNT(DISTINCT tx_hash) > 2000 )
    AND block_timestamp::date >= '2022-01-01' AND status = 'SUCCESS' GROUP BY 1
    ),

    base_osmosis AS (
    SELECT date_trunc('MONTH' , block_timestamp)AS Date, Count(DISTINCT tx_from) AS users_count , Count(DISTINCT tx_id) AS tx_count
    FROM osmosis.core.fact_transactions WHERE tx_from IN (SELECT tx_from FROM osmosis.core.fact_transactions WHERE block_timestamp::date >= '2022-01-01' AND tx_status = 'SUCCEEDED' GROUP BY 1 HAVING COUNT(DISTINCT tx_id) > 2000 )
    Run a query to Download Data