题解 | #统计活跃间隔对用户分级结果#
统计活跃间隔对用户分级结果
https://www.nowcoder.com/practice/6765b4a4f260455bae513a60b6eed0af
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
