datediff('day', "Activity Start Date", current_date)as"Duration of Activity"
fromosmosis.core.fact_transactions
wheretx_status='SUCCEEDED'
groupby"User"
having"Activity Start Date"<current_date-7
),
user_day_of_transactionsas(
select
tx_fromas"User",
count(distinctblock_timestamp::date)as"Number of Active Days"
fromosmosis.core.fact_transactions
wheretx_status='SUCCEEDED'
andblock_timestamp::date>=current_date-90
andblock_timestamp::date<=current_date-1
groupby"User"
),
users_and_activity_statusas(
select
user_activity.*,
user_transactions."Number of Active Days",
case
when(user_transactions."Number of Active Days"/user_activity."Duration of Activity")*100>=85or(user_transactions."Number of Active Days"/90)*100>=85then1