levanaCohort Retention
    Updated 2024-10-09
    -- forked from Levana Daily traders osmo @ https://flipsidecrypto.xyz/edit/queries/46940d71-c7f1-4f30-ac75-b1c6fb9e8f90

    with main AS (
    SELECT a_pos_owner.tx_id, a_pos_owner.block_timestamp,
    a_pos_owner.attribute_value AS trader
    FROM
    osmosis.core.fact_msg_attributes AS a_pos_owner
    WHERE a_pos_owner.msg_type like 'wasm-position%'
    AND a_pos_owner.attribute_key = 'pos-owner' and TX_SUCCEEDED=TRUE ),
    -- forked from NEAR Cohort Retention @ https://flipsidecrypto.xyz/TheLaughingMan/q/EETNzgMr7QCN/near-cohort-retention
    -- by

    base_table as (
    select
    Trader as user
    , date_trunc('week', block_timestamp) as date
    , min(date_trunc('week', block_timestamp)) over(partition by user) as earliest_date
    , datediff(
    'Week'
    , min(date_trunc('week', block_timestamp)) over(partition by user) -- earliest_date
    , date_trunc('week', block_timestamp) -- current date in Week
    ) as difference
    from main
    where 1=1
    and block_timestamp >= current_timestamp() - interval '1 year'
    )

    , count_new_users as(
    select
    earliest_date
    , count(distinct user) as new_users
    from base_table
    group by 1
    )

    , count_returning_users as(
    QueryRunArchived: QueryRun has been archived