Afonso_DiazRetention rate
    Updated 20 hours ago
    with

    main as (
    select
    tx_hash,
    block_timestamp,
    sender as user
    from
    aptos.core.fact_transactions
    where
    payload_function ilike '0xc0188ad3f42e66b5bd3596e642b8f72749b67d84e6349ce325b27117a9406bdf%'
    ),

    user_activity AS (
    SELECT
    user,
    date_trunc('{{ period }}', block_timestamp) AS date_start
    FROM main
    GROUP BY user, date_start
    )

    SELECT
    a.date_start AS date,
    COUNT(DISTINCT a.user) AS active_users,
    COUNT(DISTINCT b.user) AS retained_users,
    (COUNT(DISTINCT b.user) * 100.0 / COUNT(DISTINCT a.user)) AS retention_rate
    FROM user_activity a
    LEFT JOIN user_activity b
    ON a.user = b.user
    AND a.date_start = b.date_start + INTERVAL '1 {{ period }}'
    GROUP BY a.date_start
    ORDER BY a.date_start


    Last run: about 20 hours agoAuto-refreshes every 24 hours
    DATE
    ACTIVE_USERS
    RETAINED_USERS
    RETENTION_RATE
    1
    2022-12-01 00:00:00.00020300
    2
    2023-01-01 00:00:00.000827748.948005
    3
    2023-02-01 00:00:00.00069631445.114943
    4
    2023-03-01 00:00:00.00059119132.318105
    5
    2023-04-01 00:00:00.00052320238.623327
    6
    2023-05-01 00:00:00.0002488735.080645
    7
    2023-06-01 00:00:00.0004204711.190476
    8
    2023-07-01 00:00:00.0001074656.052142
    9
    2023-08-01 00:00:00.00019251467.584416
    10
    2023-09-01 00:00:00.000342248714.231444
    11
    2023-10-01 00:00:00.00061423786.154347
    12
    2023-11-01 00:00:00.000164919812.007277
    13
    2023-12-01 00:00:00.000270061422.740741
    14
    2024-01-01 00:00:00.000171861335.681024
    15
    2024-02-01 00:00:00.000249829911.969576
    16
    2024-03-01 00:00:00.00029752418.10084
    17
    2024-04-01 00:00:00.00022041215.490018
    18
    2024-05-01 00:00:00.000186178842.342826
    19
    2024-06-01 00:00:00.0003966716.919192
    20
    2024-07-01 00:00:00.00036711431.06267
    32
    1KB
    535s