from terra.core.fact_transactions

group by TX_SENDER) ,

transactions_days_table as (select count(distinct block_timestamp::date) as transactions_diffenrence_days , TX_SENDER from terra.core.fact_transactions

group by TX_SENDER) ,

active_users_table as (

select t.tx_sender as users , register_days , transactions_diffenrence_days from register_days_table as r inner join transactions_days_table as t

on r.tx_sender=t.tx_sender

where transactions_diffenrence_days >= 0.5 * register_days and register_days > 10),

percent_table as (select count(distinct users) , case

when register_days=transactions_diffenrence_days then 'Every days have transactions'

when transactions_diffenrence_days >= 0.9 * register_days then 'more than 90% days have transactions'

when transactions_diffenrence_days >= 0.8 * register_days then 'between 80% to 90% days have transactions'

when transactions_diffenrence_days >= 0.7 * register_days then 'between 70% to 80% days have transactions'

when transactions_diffenrence_days >= 0.6 * register_days then 'between 60% to 70% days have transactions'

when transactions_diffenrence_days >= 0.5 * register_days then 'between 50% to 60% days have transactions' end as percent

from active_users_table

group by percent)

select count(*) , TX_SENDER from terra.core.fact_transactions

where TX_SENDER in (select users from active_users_table where register_days=transactions_diffenrence_days) and TX_SUCCEEDED='TRUE'

group by TX_SENDER