首页 > 试题广场 >

统计活跃间隔对用户分级结果

[编程题]统计活跃间隔对用户分级结果

热度指数：57376 时间限制：C/C++ 1秒，其他语言2秒空间限制：C/C++ 256M，其他语言512M
算法知识视频讲解

用户行为日志表tb_user_log

id	uid	artical_id	in_time	out_time	sign_cin
1	109	9001	2021-08-31 10:00:00	2021-08-31 10:00:09	0
2	109	9002	2021-11-04 11:00:55	2021-11-04 11:00:59	0
3	108	9001	2021-09-01 10:00:01	2021-09-01 10:01:50	0
4	108	9001	2021-11-03 10:00:01	2021-11-03 10:01:50	0
5	104	9001	2021-11-02 10:00:28	2021-11-02 10:00:50	0
6	104	9003	2021-09-03 11:00:45	2021-09-03 11:00:55	0
7	105	9003	2021-11-03 11:00:53	2021-11-03 11:00:59	0
8	102	9001	2021-10-30 10:00:00	2021-10-30 10:00:09	0
9	103	9001	2021-10-21 10:00:00	2021-10-21 10:00:09	0
10	101	0	2021-10-01 10:00:00	2021-10-01 10:00:42	1

（uid-用户ID, artical_id-文章ID, in_time-进入时间, out_time-离开时间, sign_in-是否签到）

问题：统计活跃间隔对用户分级后，各活跃等级用户占比，结果保留两位小数，且按占比降序排序。

注：

用户等级标准简化为：忠实用户(近7天活跃过且非新晋用户)、新晋用户(近7天新增)、沉睡用户(近7天未活跃但更早前活跃过)、流失用户(近30天未活跃但更早前活跃过)。
假设今天就是数据中所有日期的最大值。
近7天表示包含当天T的近7天，即闭区间[T-6, T]。

输出示例：

示例数据的输出结果如下

user_grade	ratio
忠实用户	0.43
新晋用户	0.29
沉睡用户	0.14
流失用户	0.14

解释：

今天日期为2021.11.04，根据用户分级标准，用户行为日志表tb_user_log中忠实用户有：109、108、104；新晋用户有105、102；沉睡用户有103；流失用户有101；共7个用户，因此他们的比例分别为0.43、0.29、0.14、0.14。

示例1

输入

DROP TABLE IF EXISTS tb_user_log;
CREATE TABLE tb_user_log (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid INT NOT NULL COMMENT '用户ID',
    artical_id INT NOT NULL COMMENT '视频ID',
    in_time datetime COMMENT '进入时间',
    out_time datetime COMMENT '离开时间',
    sign_in TINYINT DEFAULT 0 COMMENT '是否签到'
) CHARACTER SET utf8 COLLATE utf8_bin;

INSERT INTO tb_user_log(uid, artical_id, in_time, out_time, sign_in) VALUES
  (109, 9001, '2021-08-31 10:00:00', '2021-08-31 10:00:09', 0),
  (109, 9002, '2021-11-04 11:00:55', '2021-11-04 11:00:59', 0),
  (108, 9001, '2021-09-01 10:00:01', '2021-09-01 10:01:50', 0),
  (108, 9001, '2021-11-03 10:00:01', '2021-11-03 10:01:50', 0),
  (104, 9001, '2021-11-02 10:00:28', '2021-11-02 10:00:50', 0),
  (104, 9003, '2021-09-03 11:00:45', '2021-09-03 11:00:55', 0),
  (105, 9003, '2021-11-03 11:00:53', '2021-11-03 11:00:59', 0),
  (102, 9001, '2021-10-30 10:00:00', '2021-10-30 10:00:09', 0),
  (103, 9001, '2021-10-21 10:00:00', '2021-10-21 10:00:09', 0),
  (101, 0, '2021-10-01 10:00:00', '2021-10-01 10:00:42', 1);

输出

忠实用户|0.43
新晋用户|0.29
沉睡用户|0.14
流失用户|0.14

算法知识视频讲解

Mysql

祖拜尔实习生

with to_day as(
select 
    uid,
    in_time,
    out_time,
    max(out_time) over() today,
    min(in_time) over(partition by uid) register_date,
    max(out_time) over(partition by uid) last_date
from 
    tb_user_log)

select user_grade,
    round(cnt/sum(cnt) over(),2) ratio
from(
select
    case when datediff(today,register_date)<7 then '新晋用户'
         when datediff(today,register_date) >=7 and datediff(today,last_date) < 7 then '忠实用户'
         when datediff(today,last_date) >=7 and datediff(today,last_date) < 30 then '沉睡用户'
         when datediff(today,last_date) >=30 then '流失用户' end user_grade,
    round(count(distinct uid)) cnt
from to_day
group by case when datediff(today,register_date)<7 then '新晋用户'
         when datediff(today,register_date) >=7 and datediff(today,last_date) < 7 then '忠实用户'
         when datediff(today,last_date) >=7 and datediff(today,last_date) < 30 then '沉睡用户'
         when datediff(today,last_date) >=30 then '流失用户' end) t1
order by ratio desc

发表于 2024-04-06 12:08:22 回复(0)

Mysql

klihann

先抓用户信息，记为表x：uid, 加入时间, 最后活跃时间

SELECT 
    uid,
    MIN(DATE(in_time)) AS join_time,
    MAX(DATE(out_time)) AS latest_active_time
FROM tb_user_log
GROUP BY uid

用CASE WHEN 新增列，根据当前时间 (SELECT MAX(in_time) FROM tb_user_log) 判断用户等级，抓取uid和用户等级user_grade记为表y

在 SQL 中，CASE 表达式是顺序匹配的，一旦找到匹配的条件，就会停止匹配并返回结果。这意味着你可以按照优先级顺序编写条件，并且只有在前面的条件不满足时才会考虑后面的条件。

SELECT 
    uid,
    CASE 
        WHEN DATEDIFF((SELECT MAX(in_time) FROM tb_user_log), join_time) <= 6 THEN '新晋用户'
        WHEN DATEDIFF((SELECT MAX(in_time) FROM tb_user_log), latest_active_time) <= 6 THEN '忠实用户'
        WHEN DATEDIFF((SELECT MAX(in_time) FROM tb_user_log), latest_active_time) <= 29 THEN '沉睡用户'
        ELSE '流失用户'
    END AS user_grade
FROM (
    SELECT 
        uid,
        MIN(DATE(in_time)) AS join_time,
        MAX(DATE(out_time)) AS latest_active_time
    FROM tb_user_log
    GROUP BY uid
)x

最后根据用户等级user_grade分组计算比率，全部代码为：

SELECT
    user_grade,
    ROUND(COUNT(DISTINCT(y.uid))/(SELECT COUNT(DISTINCT(uid)) FROM tb_user_log),2) AS ratio
FROM(
    SELECT 
        uid,
        CASE 
            WHEN DATEDIFF((SELECT MAX(in_time) FROM tb_user_log), join_time) <= 6 THEN '新晋用户'
            WHEN DATEDIFF((SELECT MAX(in_time) FROM tb_user_log), latest_active_time) <= 6 THEN '忠实用户'
            WHEN DATEDIFF((SELECT MAX(in_time) FROM tb_user_log), latest_active_time) <= 29 THEN '沉睡用户'
            ELSE '流失用户'
        END AS user_grade
    FROM (
        SELECT 
            uid,
            MIN(DATE(in_time)) AS join_time,
            MAX(DATE(out_time)) AS latest_active_time
        FROM tb_user_log
        GROUP BY uid
    )x
)y
GROUP BY user_grade
ORDER BY ratio DESC;

编辑于 2024-03-23 01:15:43 回复(0)

Mysql

要好好学习啊

with t1 as (select         # 先找出首次登录，最后一次登录以及现在的日期，现在的日期用max（）窗口函数查找
uid,
max(out_time) as last_dt,
min(in_time) as first_dt,
max(max(out_time))over() as now_dt
from tb_user_log
group by uid),
t2 as(              # 定义用户等级
    select
    uid,
    case when datediff(now_dt,first_dt) <=6 then'新晋用户'
    when datediff(now_dt,first_dt) >6 and datediff(now_dt,last_dt) <=6 then '忠实用户'
    when datediff(now_dt,last_dt) between 7 and 29 then '沉睡用户'
    else '流失用户' end as user_grade
    from t1
)
select        # 求各等级比率按等级分组之后求各等级人数，用窗口函数求总人数
user_grade,
round(count(user_grade)/sum(count(user_grade))over(),2) as ratio
from t2
group by user_grade
order by ratio desc

发表于 2024-03-01 13:21:08 回复(0)

Mysql

牛客605111887号

with
max_time as (  # 最新的一个时间和总用户数量
select max(in_time) as mt, count(distinct uid) as aid from tb_user_log
),
latest_time as (  # 每个用户的最近活跃时间和最新时间的差值
select uid, min(timestampdiff(day, in_time, mt)) as daydiff
from tb_user_log, (select mt from max_time)t
group by uid
),
old_user as (  # 老用户id
select uid, max(timestampdiff(day, in_time, mt)) as daydiff
from tb_user_log, (select mt from max_time)t
group by uid
having daydiff>=7
)

select 
    case
        when daydiff>=7 and daydiff<30 then '沉睡用户'
        when daydiff>=30 then '流失用户'
        when daydiff<7 and uid in (select uid from old_user) then '忠实用户'
    else '新晋用户'
    end as user_grade
    , round(count(distinct uid)/avg(aid), 2) as ratio
from latest_time, max_time
group by user_grade

编辑于 2024-02-13 22:11:29 回复(0)

Mysql

llaaoo

select(
case when date_format(in_time,'%Y-%m-%d') between date_sub((select max(date_format(in_time,'%Y-%m-%d')) from tb_user_log),interval 6 day) and (select max(date_format(in_time,'%Y-%m-%d')) from tb_user_log) and not exists (select in_time from tb_user_log t2 where in_time<date_sub((select max(date_format(in_time,'%Y-%m-%d')) from tb_user_log),interval 6 day) and t2.uid=t.uid) then '新晋用户'
when exists (select in_time from tb_user_log t2 where in_time<date_sub((select max(date_format(in_time,'%Y-%m-%d')) from tb_user_log),interval 6 day) and t2.uid=t.uid) and (date_format(in_time,'%Y-%m-%d') between date_sub((select max(date_format(in_time,'%Y-%m-%d')) from tb_user_log),interval 6 day) and (select max(date_format(in_time,'%Y-%m-%d')) from tb_user_log)) then '忠实用户'
when date_format(in_time,'%Y-%m-%d')<date_sub((select max(date_format(in_time,'%Y-%m-%d')) from tb_user_log),interval 29 day) and not exists (select in_time from tb_user_log t2 where in_time>=date_sub((select max(date_format(in_time,'%Y-%m-%d')) from tb_user_log),interval 29 day) and t2.uid=t.uid) then '流失用户'
when date_format(in_time,'%Y-%m-%d')<date_sub((select max(date_format(in_time,'%Y-%m-%d')) from tb_user_log),interval 6 day) and not exists (select in_time from tb_user_log t2 where in_time>=date_sub((select max(date_format(in_time,'%Y-%m-%d')) from tb_user_log),interval 6 day) and t2.uid=t.uid) then '沉睡用户' end) as user_grade,
round(count(uid)/(select count(distinct uid) from tb_user_log),2) ratio
from tb_user_log t
group by user_grade
order by ratio desc

有没有大佬知道为啥我的结果是这样啊

None	0.43
忠实用户	0.43
新晋用户	0.29
沉睡用户	0.14
流失用户	0.14

编辑于 2023-12-03 15:29:19 回复(1)

Mysql

War张

with t as (
    select
        uid,
        date(min(in_time)) first_day,
        date(max(out_time)) last_day,
        max(date(max(out_time))) over() today
    from tb_user_log
    group by uid
)
select
    case 
        when datediff(today,first_day) <= 6 then '新晋用户'
        when datediff(today,last_day) <= 6 then '忠实用户'
        when datediff(today,last_day) > 29 then '流失用户'
        else '沉睡用户'
    end user_grade,
    round(count(uid) / sum(count(uid)) over(),2) ratio
from t
group by user_grade
order by ratio desc;

发表于 2023-11-03 12:29:52 回复(0)

Mysql

周六六不喝粥

select
    user_grade,
    count(user_grade)/all_user as ratio
from
    (
        select
            uid,
            case
                when datediff (first_time, '2021-11-04') >= 7
                and datediff (last_time, '2021-11-04') <= 6 then '忠实用户'
                when datediff (first_time, '2021-11-04') < 7
                and datediff (last_time, '2021-11-04') <= 6 then '新晋用户'
                when datediff (last_time, '2021-11-04') <= 30 then '沉睡用户'
                when datediff (last_time, '2021-11-04') > 30 then '流失用户'
            end as user_grade,
            count(distinct uid) all_user
        from
            (
                select
                    min(date_format (in_time, '%y-%M-%d')) as first_time,
                    max(date_format (out_time, '%y-%M-%d')) as last_time,
                    uid
                from
                    tb_user_log
                group by
                    uid
            ) t1
        group by uid
    ) t2
group by
    user_grade
order by ratio desc;

为什么这样写会一直报错

发表于 2023-09-18 15:32:18 回复(0)

Mysql

牛客913150013号

SELECT user_grade,ROUND(COUNT(uid)/SUM(COUNT(uid)) over(),2) AS ratio
FROM(
SELECT uid,
		CASE WHEN(DATEDIFF(max_time,recent_time)<7 AND DATEDIFF(max_time,register_time)>=7) THEN "忠实用户"
			   WHEN(DATEDIFF(max_time,recent_time)<7 AND DATEDIFF(max_time,register_time)<7) THEN "新晋用户"
			   WHEN(DATEDIFF(max_time,recent_time)<30) THEN "沉睡用户"
			   WHEN(DATEDIFF(max_time,recent_time)>=30) THEN "流失用户"
		END AS user_grade
FROM(
SELECT DISTINCT uid,
MAX(DATE(in_time)) over(PARTITION BY uid) AS recent_time, #最近一次活跃时间
MIN(DATE(in_time)) over(PARTITION BY uid) AS register_time, #注册时间
MAX(DATE(out_time)) over() AS max_time #今天日期
FROM tb_user_log)a
)b
GROUP BY user_grade
ORDER BY ratio DESC,user_grade ASC

写了好久。。。不知道为啥还要按照user_grade排序才给我通过

发表于 2023-08-27 23:49:53 回复(0)

Mysql

我要成为鹅孝子

TIMSTAPDIFF 是计算实际时间差，即

TIMESTAMPDIFF(DAY,2021-10-01 10:00:00 , '2021-11-04 23:59:59')

<30

发表于 2023-08-17 20:44:35 回复(0)

Mysql

牛客784286447号

绕来绕去终于想到比较清晰的方法了

核心思路：用每个用户的最小日期、最大日期来形成区间，即表b，来框定用户等级。

难点：

1、对于题中用户等级的理解：用户等级标准简化为：忠实用户(近7天活跃过且非新晋用户)、新晋用户(近7天新增)、沉睡用户(近7天未活跃但更早前活跃过)、流失用户(近30天未活跃但更早前活跃过)。

其实就是把时间分区间，共分成3个区间：今日——第7天——第30天——大于30天

max_dt<——>min_dt

2、uid、min_dt、max_dt、今日：select max(dt) from a

新晋用户(近7天新增)：即用户的最小日期 min_dt 在7天内，即 datediff((select max(dt) from a),min_dt) between 0 and 6

忠实用户(近7天活跃过且非新晋用户)：1、非新晋用户：用户最小日期不在7天内，即大于7天

即 datediff((select max(dt) from a),min_dt)>6；2、近7天活跃过：只要用户的最大日期在7天内，那他一定在7内活跃过，即 datediff((select max(dt) from a),max_dt) between 0 and 6

沉睡用户(近7天未活跃但更早前活跃过)：1、7天未活跃：最大日期max_dt 不在7天内即datediff((select max(dt) from a),max_dt)>6 2、更早前活跃：最大日期max_dt 要在30天以内即datediff((select max(dt) from a),max_dt)<=29

流失用户(近30天未活跃但更早前活跃过):最大日期max_dt 大于30天，即datediff((select max(dt) from a),max_dt)>29

with a as (select uid,date(in_time) as dt from tb_user_log
union all 
select uid,date(out_time) as dt from tb_user_log),
b as (
select
uid,min(dt) as min_dt,max(dt) as max_dt
from a 
group by uid
),
c as (
select 
uid,
case when datediff((select max(dt) from a),min_dt) between 0 and 6 then '新晋用户'
    when datediff((select max(dt) from a),max_dt) between 0 and 6 and datediff((select max(dt) from a),min_dt)>6 then '忠实用户'
    when datediff((select max(dt) from a),max_dt)>6 and datediff((select max(dt) from a),max_dt)<=29 then '沉睡用户'
    when datediff((select max(dt) from a),max_dt)>29 then '流失用户' end as flag
from b)


select 
flag,
round(count(distinct uid)/(select count(*) from b),2) as ratio
from c
group by flag
order by ratio desc

发表于 2023-07-31 13:28:45 回复(0)

Mysql

阿圆96

select user_grade, round(count(user_grade)/(select count(distinct uid) from tb_user_log),2)ratio
from(
select uid, case 
when max(in_time) <= DATE_SUB((select max(date(in_time)) from tb_user_log),interval 29 day)
	then '流失用户'
when min(in_time) >= DATE_SUB((select max(in_time) from tb_user_log),interval 6 day) 
	then '新晋用户'
when max(in_time) <= DATE_SUB((select max(in_time) from tb_user_log),interval 6 day)
	then '沉睡用户'
else '忠实用户'
end as user_grade
from tb_user_log group by uid)a
group by user_grade
order by ratio desc

发表于 2023-07-24 12:54:38 回复(0)

Mysql

想在家办公的六边形战士拿到了ssp

/*1、获取用户新增日期、最近登录日期、当前天数最大时间*/

with tmp as (

select uid,first_loaddt,last_loaddt,nowdata

from

(select uid,date(min(in_time)) first_loaddt,date(max(out_time)) last_loaddt

from tb_user_log group by uid) log

join (select date(max(out_time)) nowdata from tb_user_log) maxlog),

countuser as (select count(distinct uid) alluser from tb_user_log)

/*3、统计各级用户占比情况*/

select user_grade ,round(count(user_grade)/(select alluser from countuser),2) ratio

from (

/*2、用户分级*/

select uid,

case

when datediff(nowdata,last_loaddt)>=30 then '流失用户'

when datediff(nowdata,last_loaddt) between 7 and 29 then '沉睡用户'

when datediff(nowdata,last_loaddt)<=6 and datediff(nowdata,first_loaddt)>=7 then '忠实用户'

when datediff(nowdata,last_loaddt)<=6 and datediff(nowdata,first_loaddt)<=6 then '新晋用户'

end user_grade

from tmp ) aa

group by user_grade

order by user_grade,ratio desc

发表于 2023-06-11 08:30:47 回复(0)

Mysql

完美的牛油果在吃瓜

有无大佬帮忙看看，哪儿有问题，老是提示unknown column ‘user_time_info in ‘group statement’

select distinct user_grade,round(count(uid)/cnt,2) as ratio 
from (
    select uid,cnt,
        (case when timestampdiff(day,m_day,last_time) >=30  then '流失用户'
            when  timestampdiff(day,m_day,last_time) >= 7  then '沉睡用户'
            when  timestampdiff(day,m_day,first_time) < 7 then '新晋用户'
            else '活跃用户' end) as user_grade 
            from (
                select uid,cnt,m_day,last_time,first_time from
                (select distinct uid,min(date(in_time)) first_time,max(date(out_time)) last_time from tb_user_log group by uid) a
                left join 
                (select count(distinct uid) cnt, max(date(out_time)) as m_day from tb_user_log ) as user_overall_info on 1 group  
                by uid ) b
) user_grade_info 
group by user_time_info
order by ratio desc;

发表于 2023-06-07 21:08:10 回复(0)

Mysql

我叫谢小盟，叫我查尔斯好了

select 
user_grade, 
round(count(user_grade)/(select count(distinct uid) from tb_user_log),2) as ratio

from 

(select uid, 
case when diff > 6 and 7days_login is not null then "忠实用户" 
# 首次登录时间在6天之前，且最近7天内有登录记录 (非空) 
when diff <= 6 and 7days_login is not null then "新晋用户" 
# 首次登录时间在6天之内，且最近7天内有登录记录 (非空) 
when diff > 6 and diff < 30 and 7days_login is null then "沉睡用户"
# 首次登录时间在6天之外，30天之内 (不含30)，且最近7天内无登录记录 (空) 
when diff >= 30 and 7days_login is null then "流失用户"
# 首次登录时间在30天之外 (含30)，且最近7天内无登录记录 (空) 
end as user_grade

from

(select t1.uid, 7days_login, 
timestampdiff(day,first_login,date((select date(max(out_time)) from tb_user_log))) as diff #所有用户第一次登录的时间，与现在的时间的时间差

from

(select
uid, date(min(out_time)) as first_login
from tb_user_log
group by uid) as t1 #首次登录时间

left join

(select
uid, 
date(min(in_time)) as 7days_login #所有用户在7天内的首次登录时间
from tb_user_log
where timestampdiff(day, date(in_time), date((select date(max(out_time)) from tb_user_log))) <7
group by uid) as t2

on t1.uid = t2.uid 
#两个表join一下，为什么要left join，因为所有用户都有一个第一次登录时间，但不是所有用户都在最近7天内登录过
) as t3) as t4


group by user_grade
order by ratio desc

发表于 2023-05-31 17:59:42 回复(0)