-- SQL Template for New Users learned at Covalent Alchemist Data BootCamp

with user_cohorts as (

SELECT

from_address as address,

min(date_trunc('day',block_timestamp)) as cohortDate

FROM thorchain.core.fact_swaps

GROUP BY 1

),

new_users as (

SELECT cohortDate as date, count (distinct address) as new_users_count

FROM user_cohorts uc

Group by Date

),

all_users as (

Select date_trunc('day',block_timestamp) as date,

count (distinct from_address) as total_users

FROM thorchain.core.fact_swaps

Group by 1

SELECT au.date as "Date",

nu.new_users_count as "New Users",

au.total_users - nu.new_users_count AS "Existing Users",

(nu.new_users_count/au.total_users)*100 as "New User Percentage"

FROM all_users au

LEFT JOIN new_users nu

ON au.date = nu.date

where au.date >= '2023-03-01'