with t1 as(select usr_id, substring(login_time,1,10) as day
from user_login_log
where datediff(now(),login_time)<=180
 group by usr_id, substring(login_time,1,10))
, t2 as( select 
 	usr_id,count(*) as cnt
 from t1
 group by usr_id)
, t3 as( select 
 	usr_id,
cnt,
case when cnt>=1 and cnt <=5 then 1 else 0 end as days_1_to_5,
case when cnt>5 and cnt<11 then 1 else 0 end as days_6_to_10,
case when cnt>10 and cnt<21 then 1 else 0 end as days_11_to_20,
case when cnt>20 then 1 else 0 end as days_over_20,
1 as flag
 from t2)
 , t4 as(
 	select
 		flag ,
 		sum(days_1_to_5) as days_1_to_5,
 		sum(days_6_to_10) as days_6_to_10,
 		sum(days_11_to_20) as days_11_to_20,
 		sum(days_over_20) as days_over_20
 from t3 
 group by flag
 )
 select days_1_to_5,days_6_to_10,days_11_to_20,days_over_20 from t4