case

when unique_days = 1 then '1天'

when unique_days between 2 and 3 then '2至3天'

when unique_days between 4 and 7 then '4至7天'

when unique_days between 8 and 15 then '8至15天'

when unique_days between 16 and 30 then '16至30天'

when unique_days > 30 then '30天以上'

else '无交易数据' end as "独立交易天数区间",

count(user) as "用户数量",

round(count(user) * 100.0 / (select count(DISTINCT FROM_ADDRESS)

from monad.testnet.fact_transactions

where TX_SUCCEEDED=true), 2) as "% 百分比"

from

(select FROM_ADDRESS as user,

count(DISTINCT to_char(convert_timezone('UTC', 'Asia/Shanghai', inserted_timestamp), 'YYYY-MM-DD')) as unique_days

from monad.testnet.fact_transactions

where TX_SUCCEEDED=true

group by 1) as tx_counts

group by 1

order by 2 desc;