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

select user_grade, round(num/sum(num) over(partition by pa order by num desc rows between unbounded preceding and unbounded following) ,2)
from (
select user_grade,count(*) as num , 'ab' as pa
from (
SELECT '忠实用户' as user_grade,a.uid
FROM (
	SELECT *
	FROM tb_user_log
	WHERE DATE (in_time) BETWEEN date_sub('2021-11-04', interval 6 day)
			AND '2021-11-04'
	) a
JOIN (
	SELECT *
	FROM tb_user_log
	WHERE DATE (in_time) < date_sub('2021-11-04', interval 6 day)
    ) b 
    ON a.uid = b.uid
    group by a.uid
union all
SELECT '新晋用户' as user_grade, a.uid
FROM (
	SELECT *
	FROM tb_user_log
	WHERE DATE (in_time) BETWEEN date_sub('2021-11-04', interval 6 day)
			AND '2021-11-04'
	) a
left JOIN (
	SELECT *
	FROM tb_user_log
	WHERE DATE (in_time) < date_sub('2021-11-04', interval 6 day)
    ) b 
    ON a.uid = b.uid
    where b.uid is null
    group by a.uid
union all
SELECT '沉睡用户' as user_grade, b.uid
FROM (
	SELECT *
	FROM tb_user_log
	WHERE DATE (in_time) BETWEEN date_sub('2021-11-04', interval 6 day)
			AND '2021-11-04'
			and artical_id != 0
	) a
right JOIN (
	SELECT *
	FROM tb_user_log
	WHERE DATE (in_time) < date_sub('2021-11-04', interval 6 day)
	and artical_id != 0
    ) b 
    ON a.uid = b.uid
    where a.uid is null 
    group by b.uid
union all
SELECT '流失用户' as user_grade, b.uid
FROM (
	SELECT *
	FROM tb_user_log
	WHERE DATE (in_time) BETWEEN date_sub('2021-11-04', interval 29 day)
			AND '2021-11-04'
	) a
right JOIN (
	SELECT *
	FROM tb_user_log
	WHERE DATE (in_time) < date_sub('2021-11-04', interval 29 day)
    ) b 
    ON a.uid = b.uid
    where a.uid is null 
    group by b.uid
)h
group by user_grade
) lg