select

tx_from as "User",

min(block_timestamp::date) as "Activity Start Date",

datediff('day', "Activity Start Date", current_date) as "Duration of Activity"

from osmosis.core.fact_transactions

where tx_status = 'SUCCEEDED'

group by "User"

having "Activity Start Date" < current_date - 7

),

user_day_of_transactions as (

select

tx_from as "User",

count(distinct block_timestamp::date) as "Number of Active Days"

from osmosis.core.fact_transactions

where tx_status = 'SUCCEEDED'

and block_timestamp::date >= current_date - 90

and block_timestamp::date <= current_date - 1

group by "User"

),

users_and_activity_status as (

select

user_activity.*,

user_transactions."Number of Active Days",

case

when (user_transactions."Number of Active Days" / user_activity."Duration of Activity") * 100 >= 85 or (user_transactions."Number of Active Days" / 90) * 100 >= 85 then 1

else 0

end as "Active Status"

from user_start_activity user_activity

join user_day_of_transactions user_transactions using("User")

),

active_user_list as (

select

"User",

"Duration of Activity"

from users_and_activity_status