msafadoostmonthly user retention on flow in last 3 months
    Updated 2022-07-17
    WITH users as (
    SELECT DISTINCT(PROPOSER) as user,
    '1' as month
    FROM flow.core.fact_transactions
    WHERE BLOCK_TIMESTAMP >= '2022-04-20'
    AND BLOCK_TIMESTAMP < '2022-05-20'
    UNION
    SELECT DISTINCT(PROPOSER) as user,
    '2' as month
    FROM flow.core.fact_transactions
    WHERE BLOCK_TIMESTAMP >= '2022-05-20'
    AND BLOCK_TIMESTAMP < '2022-06-20'
    UNION
    SELECT DISTINCT(PROPOSER) as user,
    '3' as month
    FROM flow.core.fact_transactions
    WHERE BLOCK_TIMESTAMP >= '2022-06-20'
    AND BLOCK_TIMESTAMP < '2022-07-20'
    ),
    retention AS (
    SELECT USER,
    COUNT(user) as counts
    FROM users
    GROUP by 1
    ORDER by 2 DESC
    ),
    total_users AS (
    SELECT COUNT(DISTINCT(PROPOSER)) as total_user
    FROM flow.core.fact_transactions
    WHERE BLOCK_TIMESTAMP >= '2022-04-20'
    ),
    act as (
    SELECT
    CASE
    WHEN counts = 3 then '3 month active'
    WHEN counts = 2 then '2 month active'
    Run a query to Download Data