BlockTrackerretention rate users phoenix
    Updated 2023-09-04

    with
    tb0 as (
    SELECT
    DISTINCT signers[0] as old_user,
    date_trunc('d', block_timestamp) as date
    FROM solana.core.fact_events
    WHERE program_id = 'PhoeNiXZ8ByJGLkxNfZRnkUfjvmuYqLR89jjFHGqdXY'
    AND block_timestamp > DATEADD('week', -2 , current_date)
    AND block_timestamp < current_date - 7
    ),
    tb1 as (
    SELECT
    DISTINCT signers[0] as retern_user,
    date_trunc('d', block_timestamp) as date
    FROM solana.core.fact_events
    WHERE program_id = 'PhoeNiXZ8ByJGLkxNfZRnkUfjvmuYqLR89jjFHGqdXY'
    AND block_timestamp > DATEADD('week', -1, current_date)
    AND block_timestamp < current_date
    AND signers[0] IN (SELECT old_user FROM tb0)

    )
    SELECT
    count(DISTINCT a.old_user) as old_users,
    count(DISTINCT b.retern_user) as reterned_users,
    (reterned_users/old_users ) * 100 as retention_rate
    FROM tb0 a
    LEFT OUTER JOIN tb1 b ON a.old_user = b.retern_user




    with base_day as (
    SELECT
    DISTINCT signers [0] as base_users
    FROM
    Run a query to Download Data