base as

(select

date_trunc(month,block_timestamp)::date as month ,

TX_SENDER,TX_ID

from

terra.core.fact_transactions

where block_timestamp ilike '2022%' )

,raw as (select

TX_SENDER , count(distinct month) as active_months

from base

group by 1)

select

case

when active_months = 1 then '1 month'

when active_months = 2 then '2 months'

when active_months = 3 then '3 months'

when active_months = 4 then '4 months'

when active_months = 5 then '5 months'

when active_months = 6 then '6 months'

when active_months = 7 then '7 months'

when active_months = 8 then '8 months'

when active_months = 9 then '9 months'

when active_months = 10 then '10 months'

when active_months = 11 then '11 months'

when active_months = 12 then '12 months'

end as tier ,

count (distinct TX_SENDER) as users

from raw group by 1