abaQuery - Flow monthly user retention
    Updated 2022-07-17
    with raw as (
    SELECT PROPOSER, date_trunc('month', block_timestamp :: date) AS months, count(*) AS item_transactions,
    lag(date_trunc('month', block_timestamp :: date)
    ) OVER (PARTITION BY PROPOSER ORDER BY date_trunc('month', block_timestamp :: date)) = date_trunc('month', block_timestamp :: date) - interval '30 day'
    OR NULL AS repeat_transaction
    FROM flow.core.fact_transactions
    WHERE block_timestamp :: date >= '2022-04-18'
    GROUP BY 1, 2
    )
    SELECT months, sum(item_transactions) AS number_of_transactions, count(*) AS number_of_users, count(repeat_transaction) AS repeat_users,
    round(CASE WHEN sum(item_transactions) > 0 THEN count(repeat_transaction) / sum(item_transactions) * 100 ELSE 0 END, 2) AS user_retention
    FROM raw
    GROUP BY 1
    ORDER BY 1




    Run a query to Download Data