kitlunaLayer-1 Users
    Updated 2022-07-18
    SELECT
    d.Blockchain,
    d.retained_users,
    d.all_users,
    d.all_users - d.retained_users AS unretained_user,
    d.retained_users/ d.all_users * 100 AS percentage_of_retained_users
    FROM (
    WITH retained_user AS (
    SELECT
    'Ethereum' AS Blockchain,
    COUNT (DISTINCT a.wallet) AS retained_users
    FROM (
    SELECT
    FROM_ADDRESS AS wallet,
    block_timestamp,
    DATEDIFF(hour, LAG(date_trunc('hour', block_timestamp)) OVER (PARTITION BY FROM_ADDRESS ORDER BY date_trunc('hour', block_timestamp)), date_trunc('hour', block_timestamp)) AS "interval"
    FROM ethereum.core.fact_transactions
    WHERE block_timestamp > '2022-04-20'
    ) a
    WHERE a."interval" > 0
    ),

    All_user AS (
    SELECT
    'Ethereum' AS Blockchain,
    COUNT (DISTINCT FROM_ADDRESS) AS all_users
    FROM ethereum.core.fact_transactions
    WHERE block_timestamp > '2022-04-20'
    )

    SELECT
    b.Blockchain,
    b.retained_users,
    c.all_users

    FROM retained_user b
    Run a query to Download Data