SocioCryptoRetention Rate
    Updated 2024-01-15
    with
    ranked as (
    SELECT
    block_timestamp,
    origin_from_address as user,
    rank()over(partition by user ORDER BY block_timestamp) as rank
    FROM avalanche.core.fact_event_logs
    WHERE origin_to_address = lower('0xa695ea0C90D89a1463A53Fa7a02168Bc46FbBF7e')
    ),
    last_txns as (
    SELECT origin_from_address as user,
    max(block_timestamp) as last_txn
    FROM avalanche.core.fact_event_logs
    WHERE origin_to_address = lower('0xa695ea0C90D89a1463A53Fa7a02168Bc46FbBF7e')
    GROUP BY 1
    )

    SELECT x.date as month,
    100*n_sustained/(n_sustained+n_leaver+x.n_drop) as one_month_ret_
    FROM
    (SELECT date_trunc('month',s.last_txn) as date,
    count(DISTINCT s.user) as n_drop
    FROM last_txns s
    GROUP BY date)x
    LEFT JOIN (
    SELECT date_trunc('month',a.block_timestamp) as date,
    count(DISTINCT CASE WHEN datediff(week,a.block_timestamp, b.block_timestamp)>1 THEN a.user END) as n_leaver,
    count(DISTINCT CASE WHEN datediff(week,a.block_timestamp,b.block_timestamp)<=1 THEN a.user END) as n_sustained
    FROM ranked a, ranked b
    WHERE a.rank = b.rank - 1 AND a.user = b.user
    GROUP BY date
    )y
    ON x.date = y.date
    ORDER BY month

    QueryRunArchived: QueryRun has been archived