flipside

    THORChain : New Users, Active Users, Returning Users And Churn User
    scorchTHORChain : New Users, Active Users, Returning Users And Churn User
    Updated 2024-08-29
    -- forked from Terrius / THORChain : New Users, Active Users, Returning Users And Churn User @ https://flipsidecrypto.xyz/Terrius/q/XuLnv3ReKY_d/thorchain-new-users-active-users-returning-users-and-churn-user

    WITH first_time_users AS (
    SELECT
    DISTINCT from_address AS Users,
    MIN(block_timestamp) AS first_date
    FROM thorchain.defi.fact_swaps
    WHERE block_timestamp >= '2022-01-01'
    GROUP BY 1
    ),
    new_users AS (
    SELECT
    date_trunc('day', first_date) AS date,
    COUNT(DISTINCT Users) AS new_users
    FROM first_time_users
    GROUP BY 1
    ),
    returning_users AS (
    SELECT
    date_trunc('day', t.block_timestamp) AS date,
    COUNT(DISTINCT t.from_address) AS returning_users
    FROM thorchain.defi.fact_swaps t
    JOIN first_time_users f ON t.from_address = f.Users
    WHERE t.block_timestamp >= '2022-01-01'
    AND t.block_timestamp > f.first_date
    GROUP BY 1
    ),
    usersb AS (
    SELECT
    date_trunc('day', block_timestamp) AS date,
    COUNT(DISTINCT from_address) AS current_users
    FROM thorchain.defi.fact_swaps
    WHERE block_timestamp >= '2022-01-01'
    GROUP BY 1
    ),
    churn AS (
    Auto-refreshes every 12 hours
    QueryRunArchived: QueryRun has been archived