with first_date as (

select eth_to_address as address,

min(block_timestamp::date) as first_date

from avalanche.core.ez_avax_transfers

group by 1

having first_date < CURRENT_DATE

, base as (

select from_address as user_address,

count(tx_hash) as tx_count,

first_date,

tx_count/DATEDIFF(day, first_date, CURRENT_DATE) as daily_count

from avalanche.core.fact_transactions

join first_date on from_address = address

-- where block_timestamp::date >= '2021-08-01' and block_timestamp::date < CURRENT_DATE

group by user_address, first_date )

,base2 as (

select count(user_address) as user_count,

case when daily_count <= 1/DATEDIFF(day, first_date, CURRENT_DATE) then 'a.Active Only Once'

when daily_count < 0.5 then 'b.< 0.5 TX/day'

when daily_count < 1 then 'c.< 1 TX/day'

when daily_count < 2 then 'd.< 2 TX/day'

when daily_count < 3 then 'e.< 3 TX/day'

when daily_count >= 3 then 'f.>= 3 TX/day' end as type

from base

group by type

select * from base2