flipside

    user retention rate [thala]
    SocioAnalyticauser retention rate [thala]
    Updated 2025-02-25

    with activity_user as (
    SELECT
    date_trunc('month', a.block_timestamp) as month,
    sender as user
    from
    aptos.core.fact_events a
    JOIN aptos.core.fact_transactions b ON a.tx_hash = b.tx_hash
    WHERE event_address = '0x48271d39d0b05bd6efca2278f22277d6fcc375504f9839fd73f74ace240861af'
    AND a.block_timestamp::DATE >= '2023-03-30'
    AND a.SUCCESS
    qualify row_number() over (partition BY user ORDER BY month) = 1
    )
    ,
    main as (
    SELECT
    month as first_date,
    user ,
    date_trunc('month', b.block_timestamp) as date,
    CASE when date = dateadd('month', 1, first_date) then 1 else NULL end as one,
    CASE when date = dateadd('month', 2, first_date) then 1 else NULL end as two,
    CASE when date = dateadd('month', 3, first_date) then 1 else NULL end as three,
    CASE when date = dateadd('month', 4, first_date) then 1 else NULL end as four,
    CASE when date = dateadd('month', 5, first_date) then 1 else NULL end as five,
    CASE when date = dateadd('month', 6, first_date) then 1 else NULL end as six,
    CASE when date = dateadd('month', 7, first_date) then 1 else NULL end as seven,
    CASE when date = dateadd('month', 8, first_date) then 1 else NULL end as eight,
    CASE when date = dateadd('month', 9, first_date) then 1 else NULL end as nine,
    CASE when date = dateadd('month', 10, first_date) then 1 else NULL end as ten,
    CASE when date = dateadd('month', 11, first_date) then 1 else NULL end as eleven,
    CASE when date = dateadd('month', 12, first_date) then 1 else NULL end as twelve
    FROM
    aptos.core.fact_events a
    JOIN aptos.core.fact_transactions b ON a.tx_hash = b.tx_hash
    JOIN activity_user c ON c.user = b.sender
    WHERE event_address = '0x48271d39d0b05bd6efca2278f22277d6fcc375504f9839fd73f74ace240861af'
    Last run: 2 months ago
    FIRST_DATE
    NEW_USER
    ONE_MONTH_LATER
    TWO_MONTH_LATER
    THREE_MONTH_LATER
    FOUR_MONTH_LATER
    FIVE_MONTH_LATER
    SIX_MONTH_LATER
    SEVEN_MONTH_LATER
    EIGHT_MONTH_LATER
    NINE_MONTH_LATER
    TEN_MONTH_LATER
    ELEVEN_MONTH_LATER
    TWELVE_MONTH_LATER
    1
    		2025-02-01 00:00:00.00012104
    2
    		2025-01-01 00:00:00.000638810.33 %
    3
    		2024-12-01 00:00:00.000589217.43 %8.04 %
    4
    		2024-11-01 00:00:00.0001122412.64 %4.84 %3.11 %
    5
    		2024-10-01 00:00:00.0001514237.67 %10.24 %4.02 %2.03 %
    6
    		2024-09-01 00:00:00.000635613.14 %9.85 %15.04 %9.69 %5.52 %
    7
    		2024-08-01 00:00:00.000193463.86 %2.84 %2.58 %2.83 %1.88 %1.44 %
    8
    		2024-07-01 00:00:00.0001064116.79 %4.59 %5.42 %3.84 %3.20 %2.54 %1.71 %
    9
    		2024-06-01 00:00:00.0001034530.04 %24.52 %21.74 %19.26 %18.28 %16.52 %12.45 %1.33 %
    10
    		2024-05-01 00:00:00.0001605822.20 %31.17 %14.77 %24.84 %10.09 %10.01 %6.16 %0.93 %0.61 %
    11
    		2024-04-01 00:00:00.0002399021.16 %14.22 %15.75 %8.13 %10.95 %3.57 %5.06 %3.10 %1.20 %0.82 %
    12
    		2024-03-01 00:00:00.0001572683.02 %1.66 %1.09 %0.76 %0.48 %0.62 %0.72 %0.85 %0.40 %0.27 %0.17 %
    12
    1KB
    212s