题解 | #统计活跃间隔对用户分级结果#

select

user_grade,

round(count(user_grade)/(select count(*) from (select 

uid,

case when dt is not null and first_id is not null and dt != first_id then '忠实用户' 

when dt = first_id then '新晋用户'

when dt is null and datediff((select max(date(in_time)) from tb_user_log),first_id) >=7 

and datediff((select max(date(in_time)) from tb_user_log),first_id) <30 then '沉睡用户'

else '流失用户' end 'user_grade'

from

(select distinct 

b.uid,

dt,

first_id

from 

(select uid,date(in_time) dt from tb_user_log

where datediff((select max(date(in_time)) from tb_user_log),date(in_time)) <=6

order by dt) a

right join

(select uid,min(date(in_time)) first_id from tb_user_log group by uid) b

on a.uid = b.uid) c) e),2) ratio

from

(select 

uid,

case when dt is not null and first_id is not null and dt != first_id then '忠实用户' 

when dt = first_id then '新晋用户'

when dt is null and datediff((select max(date(in_time)) from tb_user_log),first_id) >=7 

and datediff((select max(date(in_time)) from tb_user_log),first_id) <30 then '沉睡用户'

else '流失用户' end 'user_grade'

from

(select distinct

b.uid,

dt,

first_id

from 

(select uid,date(in_time) dt from tb_user_log

where datediff((select max(date(in_time)) from tb_user_log),date(in_time)) <=6

order by dt) a

right join

(select uid,min(date(in_time)) first_id from tb_user_log group by uid) b

on a.uid = b.uid) c) d

group by user_grade

order by ratio desc;