x as (

SELECT DISTINCT

contract,count(DISTINCT date_trunc('week', TIMESTAMP::date)) as active_weeks

from

external.tokenflow_starknet.decoded_transactions

where

CHAIN_ID = 'mainnet'

group by

1

)

count(DISTINCT contract) as wallets,

case

when active_weeks = 1 or active_weeks = 0 then '1 week'

when active_weeks > 2

and active_weeks < 6 then '2-5 weeks'

when active_weeks > 5

and active_weeks < 11 then '6-10 weeks'

when active_weeks > 10

and active_weeks < 51 then '11-50 weeks'

when active_weeks > 50 then '>50 weeks'

end as "weeks rate"

x

GROUP by

2