littlenaomiAxl - bridge - retention
    Updated 2022-11-25
    with user_cohorts as (
    SELECT sender as address
    , min(block_timestamp::date) as cohortDate
    FROM axelar.core.fact_transfers
    where tx_succeeded = 'TRUE'
    and transfer_type in ('IBC_TRANSFER_IN', 'IBC_TRANSFER_OUT')
    GROUP BY address
    ),
    new_users as (
    SELECT cohortDate as day, count(DISTINCT address) as new_users_count
    FROM user_cohorts uc
    GROUP BY day
    ),
    all_users as (
    SELECT block_timestamp::date as day
    ,count(DISTINCT sender) as total_players
    FROM axelar.core.fact_transfers
    where tx_succeeded = 'TRUE'
    and transfer_type in ('IBC_TRANSFER_IN', 'IBC_TRANSFER_OUT')
    and day > CURRENT_DATE - 60
    GROUP BY day
    order by day
    )
    SELECT au.day
    , nu.new_users_count
    , au.total_players - nu.new_users_count AS Existing_Users
    , (nu.new_users_count/au.total_players)*100 as New_User_Percentage
    FROM all_users au
    LEFT JOIN new_users nu
    ON au.day = nu.day
    Run a query to Download Data