with accs as (SELECT DISTINCT CONCAT('0x', LTRIM(CONTRACT, '0x')) as addr, CONTRACT as addr0

from external.tokenflow_starknet.decoded_traces

where

CHAIN_ID = 'mainnet'

FUNCTION = 'constructor'

and

CLASS_HASH = '0x03131fa018d520a037686ce3efddeab8f28895662f019ca3ca18a626650f7d1e'

CLASS_HASH = '0x025ec026985a3bf9d0cc1fe17326b245dfdc3ff89b8fde106542a3ea56c5a918'),

tb1 as (SELECT DISTINCT CONTRACT, count(DISTINCT TIMESTAMP::date) as day, count(DISTINCT date_trunc('month',TIMESTAMP::date) ) as month, count(DISTINCT tx_hash) as txs

from external.tokenflow_starknet.decoded_transactions

where

CHAIN_ID = 'mainnet'

and CONTRACT in (SELECT DISTINCT addr0 from accs )

group by 1)

select

case when month>=2 and txs>=10 and day >=3 then 'min'

else 'trash'

end as loyalty_level,

count(distinct CONTRACT) as users

from tb1

group by 1