select

tx_sender as "User",

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

max(block_timestamp::date) as "Activity End Date",

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

from terra.core.fact_transactions

where tx_succeeded = true

group by "User"

having "Activity Start Date" < current_date - 4 and "Duration of Activity" >= 3

),

user_day_of_transactions as (

select

tx_sender as "User",

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

from terra.core.fact_transactions

where tx_succeeded = true

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 >= 40 then 1

else 0

end as "Active Status"

from user_start_activity user_activity

join user_day_of_transactions user_transactions using("User")

),

final_result as (

select

*

from users_and_activity_status

where "Active Status" = 1