首页 > 试题广场 >

每天的日活数及新用户占比

[编程题]每天的日活数及新用户占比

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

用户行为日志表tb_user_log

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

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

问题：统计每天的日活数及新用户占比

注：

新用户占比=当天的新用户数÷当天活跃用户数（日活数）。
如果in_time-进入时间和out_time-离开时间跨天了，在两天里都记为该用户活跃过。
新用户占比保留2位小数，结果按日期升序排序。

输出示例：

示例数据的输出结果如下

dt	dau	uv_new_ratio
2021-10-30	2	1.00
2021-11-01	3	0.33
2021-11-02	3	0.67
2021-11-03	5	0.40

解释：

2021年10月31日有2个用户活跃，都为新用户，新用户占比1.00；

2021年11月1日有3个用户活跃，其中1个新用户，新用户占比0.33；

示例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
  (101, 9001, '2021-10-31 10:00:00', '2021-10-31 10:00:09', 0),
  (102, 9001, '2021-10-31 10:00:00', '2021-10-31 10:00:09', 0),
  (101, 0, '2021-11-01 10:00:00', '2021-11-01 10:00:42', 1),
  (102, 9001, '2021-11-01 10:00:00', '2021-11-01 10:00:09', 0),
  (108, 9001, '2021-11-01 10:00:01', '2021-11-01 10:01:50', 0),
  (108, 9001, '2021-11-02 10:00:01', '2021-11-02 10:01:50', 0),
  (104, 9001, '2021-11-02 10:00:28', '2021-11-02 10:00:50', 0),
  (106, 9001, '2021-11-02 10:00:28', '2021-11-02 10:00:50', 0),
  (108, 9001, '2021-11-03 10:00:01', '2021-11-03 10:01:50', 0),
  (109, 9002, '2021-11-03 11:00:55', '2021-11-03 11:00:59', 0),
  (104, 9003, '2021-11-03 11:00:45', '2021-11-03 11:00:55', 0),
  (105, 9003, '2021-11-03 11:00:53', '2021-11-03 11:00:59', 0),
  (106, 9003, '2021-11-03 11:00:45', '2021-11-03 11:00:55', 0);

输出

2021-10-31|2|1.00
2021-11-01|3|0.33
2021-11-02|3|0.67
2021-11-03|5|0.40

算法知识视频讲解

Mysql

牛客415117036号

select dt,count(uid) dau,round(avg(if_new),2) uv_new_ratio
from
(
select uid,dt,time_first,
if(dt=time_first,1,0) as if_new
from
(
select uid,date(in_time) dt
from tb_user_log
union
select uid,date(out_time) dt
from tb_user_log
) t1
join
(
select uid,min(date(in_time)) time_first
from tb_user_log
group by uid
) t2
using(uid)
) t3
group by dt
order by dt

发表于 2022-04-28 01:34:44 回复(0)

Mysql

牛客297556698号

思路：1.创造ID所有的活动日期（union in_time 和out_time）

2.获取每个ID的首次登陆日期。

3.按照登陆日期进行计算

发表于 2022-03-25 12:05:22 回复(1)

Mysql

夏朗

思路：

1. 计算登录次数
2. group by dt
3. count(distinct uid);新用户数/日活数

select dt
,count(distinct uid) dau
,round(sum(if(t_count=1,1,0))/count(distinct uid),2) uv_new_ratio
from(
    select uid,dt,row_number()over(partition by uid order by dt) t_count
    from(
        select uid,date(in_time) dt
        from tb_user_log
        union 
        select uid, date(out_time) dt
        from tb_user_log
    ) t1
) t2
group by dt
order by dt;

发表于 2021-12-29 11:49:03 回复(1)

Mysql

shiry8214

select x.dt,dau,
    ifnull(round(new_num/dau,2),0) as uv_new_ratio
from
    (SELECT dt,COUNT(uid) dau
    FROM
        (select uid,DATE(in_time) dt from tb_user_log
        union
        select uid,DATE(out_time) dt from tb_user_log) a
    GROUP BY dt) x
left join
    (select dt,COUNT(uid) new_num
    from
        (SELECT uid,min(DATE(in_time)) dt 
        from tb_user_log
        GROUP BY uid) b
    GROUP BY dt) y
on x.dt = y.dt

发表于 2021-12-03 17:34:02 回复(5)

Mysql

清水卡布

#问题拆分做，会比较清晰点
#先求日活数
select dt,count(distinct uid) as dau
from
     (select uid,date(in_time) as dt
         from tb_user_log
     union   #用union连接in_time和out_time  union会自动去重，如果登陆登出没有跨天，则不会多出一条记录
     select uid,date(out_time) as dt
         from tb_user_log) as a
group by dt

#求新用户数
select dt,sum(new) as new_user
from (
         select uid,
                date(in_time) as dt,
                if(row_number() over (partition by uid order by in_time) = 1, 1, 0) as new
            #做标记，如果是新用户，则当天记为1，否则记为0。
         from tb_user_log
     ) as b
group by dt

#left join得出最终答案
select dt,dau,round(new_user/dau,2)as uv_new_ratio
from(
select dt,count(distinct uid) as dau,sum(new) as new_user
from
     (select uid,date(in_time) as dt
         from tb_user_log
     union
     select uid,date(out_time) as dt
         from tb_user_log) as a
left join
    (
         select uid as uid2,
                date(in_time) as dt2,
                if(row_number() over (partition by uid order by in_time) = 1, 1, 0) as new
         from tb_user_log
     ) as b
on a.dt=b.dt2 and a.uid=b.uid2  #dt和uid都要相互对应
group by dt
    ) as c

发表于 2022-01-03 15:55:24 回复(2)

Mysql

不想睡觉zzz

select c.dt,count(distinct c.uid) dau,
round(count(distinct b.uid)/(count(distinct c.uid)),2)
from
(select uid,date_format(in_time,'%Y-%m-%d') dt from tb_user_log
union all 
select uid,date_format(out_time,'%Y-%m-%d') dt from tb_user_log
where date_format(out_time,'%Y-%m-%d') != date_format(in_time,'%Y-%m-%d')) as c
left join
(select uid,min(dt) dt from 
(select uid,date_format(in_time,'%Y-%m-%d') dt from tb_user_log
union all 
select uid,date_format(out_time,'%Y-%m-%d') dt from tb_user_log
where date_format(out_time,'%Y-%m-%d') != date_format(in_time,'%Y-%m-%d')) as a
group by uid) as b on c.dt=b.dt
group by c.dt

测试用例通过，但是提交以后就不通过，将inner join改成left join就可以了。

所以反思了一下，确实是不能用inner join ，因为一旦某一天没有新增的用户，inner join就漏掉了有活跃用户的这一天！但直接过出错

发表于 2021-12-05 09:57:07 回复(0)

Mysql

一个好懒的人

使用子查询和窗口函数

由于跨天都记为该用户活跃过，所以使用union建立一个用户活跃日期表

select
uid,date(in_time) as dt
from tb_user_log
union 
select
uid,date(out_time) as dt
from tb_user_log

union 可以自动去重，此时我们得到了一个包括跨天的用户活跃日期表

然后使用窗口函数，算出每个用户的首登日期

select 
uid,
dt,
min(dt) over (partition by uid) as first_in
from 
(
select
uid,date(in_time) as dt
from tb_user_log
union 
select
uid,date(out_time) as dt
from tb_user_log
) t1

由此我们便得到了一个有用户活跃日期dt和用户首登日期first_in的表

将此表命名为temp。接下来使用此表查询

根据日期分组，计算每日的所有有记录的用户作为每日活跃用户

而如果用户活跃日期dt和用户首登日期first_in相等则记为新用户

使用sum(if累加

select
dt,
count(distinct uid) as dau,
round(sum(if(dt=first_in,1,0))/count(distinct uid),2) as uv_new_ratio
from temp
group by dt
order by dt

完整代码如下

with temp as
(
select 
uid,
dt,
min(dt) over (partition by uid) as first_in
from 
(
select
uid,date(in_time) as dt
from tb_user_log
union 
select
uid,date(out_time) as dt
from tb_user_log
) t1
)

select
dt,
count(distinct uid) as dau,
round(sum(if(dt=first_in,1,0))/count(distinct uid),2) as uv_new_ratio
from temp
group by dt
order by dt

如有问题欢迎批评指正

发表于 2022-05-16 16:02:21 回复(0)

Tiffany_tang

select b.dt, b.dau, 
       ifnull(round(d.sum_new/b.dau,2),0) uv_new_ratio 
from (select dt, count(distinct uid) dau
      from 
      (select date(in_time) dt, uid
      from tb_user_log 
      union 
      select date(out_time) dt, uid
      from tb_user_log) a
      group by dt) b
left join 
      (select date(in_time) dt,
       count(distinct uid) sum_new
       from (select *,
            RANK()over(partition by uid order by in_time) rk
            from tb_user_log) c
       where c.rk=1
      group by date(in_time)) d
on b.dt=d.dt

发表于 2021-12-03 17:17:52 回复(1)

海底的星星fly

select dt,
count(uid) as dau,
round(count(if(ranking=1,uid,null))/count(uid),2) as uv_new_ratio
from
(
select uid,dt,
row_number() over(partition by uid order by dt) as ranking
from
(
select uid,
date(in_time) as dt
from tb_user_log
union #用union会自动去掉同一天多次活跃的用户
select uid,
date(out_time) as dt
from tb_user_log
where datediff(out_time,in_time)=1
) as tu #第一步：合并当天用户活跃和跨天用户活跃的情况表，这样才能将跨天活跃的情况，计算为两天都活跃过
) as tu1 #第二步：用row_number排序找到新用户
group by dt #第三步：计算日活和新用户占比
order by dt;

发表于 2023-02-20 13:30:56 回复(0)

酸菜鱼土豆大侠

【场景】：统计新用户

【分类】：聚合分组函数、min(date) group by uid

分析思路

难点：

1.题目说求每天的日活，所以有日期就要计算，即使新用户为0的时候也要计算进去，所以使用left join

2.记得去重

（1）统计用户活跃记录

如果in_time-进入时间和out_time-离开时间跨天了，在两天里都记为该用户活跃过

[使用]：union

（2）统计新用户第一次活跃记录

[使用]：min()确定用户第一次活跃时间，max()确定用户最后一次活跃时间

（3）统计每天的日活数及新用户占比

[使用]：group by 分组条件 [日期]；order by 对查询结果排序 [日期升序];

最终结果

select 查询结果 [日期;日活数;新用户占比]
from 从哪张表中查询数据[多表]
group by 分组条件 [日期]
order by 对查询结果排序 [日期升序];

求解代码

方法一：

with子句 + 一步步拆解

with
    main as(
        #统计用户活跃记录
        select distinct
            uid,
            date(in_time) as dt
        from tb_user_log
        union 
        select
            uid,
            date(out_time) as dt
        from tb_user_log
    )
    ,attr as(
        #统计新用户第一次活跃记录
        select
            uid,
            min(dt) as dt
        from main
        group by uid
    )
#统计每天的日活数及新用户占比
select
    dt,
    count(distinct a.uid) as dau,
    round(count(distinct b.uid)/count(distinct a.uid),2) as uv_new_ratio
from main a
left join attr b using(dt)
group by dt
order by dt

方法二：

不使用with子句

#统计每天的日活数及新用户占比
select
    dt,
    count(distinct main.uid) as dau,
    round(count(distinct attr.uid)/count(distinct main.uid),2) as uv_new_ratio
from(
    #统计用户活跃记录
    select
        uid,
        date(in_time) as dt
    from tb_user_log
    union 
    select
        uid,
        date(out_time) as dt
    from tb_user_log
) main
left join(
    #统计新用户第一次活跃记录
    select
        uid,
        min(dt) as dt
    from(
        #统计用户活跃记录
        select
            uid,
            date(in_time) as dt
        from tb_user_log
        union 
        select
            uid,
            date(out_time) as dt
        from tb_user_log
    ) main
    group by uid
) attr using(dt)
group by dt
order by dt

发表于 2022-11-21 11:29:14 回复(0)

Mysql

牛客504513895号

SELECT
dt,
COUNT(DISTINCT uid) AS dau,
IFNULL(ROUND(SUM(rnk = 1)/COUNT(DISTINCT uid),2),0) AS uv_new_ratio
FROM
    (SELECT *,ROW_NUMBER() OVER (PARTITION BY uid ORDER BY dt) AS rnk
    FROM
        (SELECT uid,DATE(in_time) AS dt
        FROM tb_user_log
        UNION
        SELECT uid,DATE(out_time) AS dt
        FROM tb_user_log) t1
    ) t2
GROUP BY dt
ORDER BY dt

发表于 2022-07-22 10:49:57 回复(0)

牛客203974129号

select dt,count(distinct uid) as daylive_cnt ,round(sum(if(rk=1,1,0))/count(distinct uid),2) new_rat
from
(select uid,dt,row_number() over(partition by uid order by dt) rk
from
(select uid,in_time as dt from tb_user_log
union
select uid,out_time as dt from tb_user_log)
c
) d group by dt order by dt

发表于 2022-06-06 19:54:51 回复(0)

Mysql

zzw11

with tmp as(
select uid,date(in_time) dt
from tb_user_log
union 
select uid,date(out_time) dt
from tb_user_log
order by dt
)
select dt,count(uid) as dau,round(sum(if(new_dt=dt,1,0))/count(uid),2) as uv_new_ratio
from(
    select uid,dt
    from tmp
    )t1
left join(
    select uid,min(dt) as new_dt
    from tmp
    group by uid
    )t2
using(uid)
group by dt
order by dt

发表于 2022-05-28 11:22:27 回复(0)

Mysql

红烧牛客

一.用户活跃表

with t1 as(select uid,date(in_time) as dt from tb_user_log
union
select uid,date(out_time) as dt from tb_user_log)

通过t1，算出日活数dau

select dt,count(distinct uid) as dau from t1 group by dt -----后期命名为t3

二.新增用户表

with t2 as(select uid,date(min(in_time)) as first_dt from tb_user_log group by uid)

通过t2，算出每天新增用户数user_new_cnt

select first_dt,count(distinct uid) as user_new_cnt from t2 group by first_dt ----后期命名为t4

三.将t3和t4左联结

with t3 as(select dt,count(distinct uid) as dau

from(select uid,date(in_time) as dt from tb_user_log
union
select uid,date(out_time) as dt from tb_user_log) as t1
group by dt),

t4 as(select first_dt,count(distinct uid) as user_new_cnt
from(select uid,date(min(in_time)) as first_dt from tb_user_log group by uid) as t2
group by first_dt)
并算出dau和uv_new_ratio

select t3.dt,dau,round(ifnull(user_new_cnt/dau,0),2) as uv_new_ratio
from t3 left outer join t4 on t3.dt=t4.first_dt
order by t3.dt;

要点：1. 一定是左联结，因为题目要求的是每天的日活数，所以要显示用户活跃表的所有日期

2. 一定要加上ifnull进行判断，牛客后台才能通过

发表于 2022-05-03 16:21:45 回复(1)

Mysql

ZH数据分析

with t1 as 
(select dt, count(uid) new
from (select uid, date(min(in_time)) dt
      from tb_user_log
      group by uid) a
group by dt)
/*表t1 每天新用户数*/
,t2 as
(select dt, count(uid) dau
from(select uid, date(in_time) dt 
     from tb_user_log
     union
     select uid, date(out_time) dt 
     from tb_user_log) b
group by dt)
/*表t2 每天活跃用户数*/
select t2.dt, dau, ifnull(round(new/dau,2),0) uv_new_ratio
from t2
left join t1
on t2.dt =t1.dt
order by t2.dt

发表于 2022-02-11 10:52:49 回复(0)

Mysql

牛客375945908号

select a.dt,count(distinct a.uid),
round(count(distinct b.uid)/count(distinct a.uid),2)
from
(select uid,date_format(in_time,"%Y-%m-%d") dt
from tb_user_log
union
select uid,date_format(out_time,"%Y-%m-%d") dt
from tb_user_log) a 
left JOIN
(select uid,date_format(min(in_time),"%Y-%m-%d") ct 
 from tb_user_log
 group by uid) b 
 on a.uid = b.uid 
 and a.dt = b.ct
group by a.dt
order by a.dt asc

可以和上上道题做一下对比，一个是用户首次活跃表作为左表和整体的表以特定的时间（date_add ）和 uid做链接。这里是以用户整体活跃表为左表，首次活跃表为右表连接

发表于 2022-01-06 17:55:10 回复(1)

牛客563312411号

count(distinct uid) dau 中可以去掉distinct，因为在b表中union 已经合并去重了

select 
dt 
,count(distinct uid) dau
,round(count(if(dt=f_dt,uid,null))/count(distinct uid),2) cv_new_ratio
from(
select uid,dt,min(dt) over(partition by uid order by dt) f_dt
from(
select
uid
,date(in_time) dt
from tb_user_log
union 
select 
uid
,date(out_time) dt
from tb_user_log
)b) a 
group by 1
order by 1

发表于 2024-03-02 19:30:48 回复(0)

在笔试的杰克很勤劳

with zb2 as (with zb as (select uid,in_time dt

from tb_user_log

union /**/

select uid,out_time

from tb_user_log)

select uid,date_format(dt,"%Y-%m-%d") dt,row_number() over(partition by uid order by dt) new_uid /*排序,1为新用户首次登录时间*/

from zb)

select dt,count(distinct uid) dau,round(sum(if(new_uid=1,1,0))/count(distinct uid),2) uv_new_ratio /*1为新用户其他为0*/

from zb2

group by dt

order by dt

编辑于 2023-12-19 22:03:00 回复(0)

Mysql

War张

-- 解法1：
with t1 as (
    select uid,date(in_time) dt from tb_user_log
    union
    select uid,date(out_time) dt from tb_user_log),
t2 as (select *,row_number() over(partition by uid order by dt) ranking from t1)
select
    dt,
    count(uid) dau,
    round(avg(if(ranking = 1,1,0)),2) uv_new_ratio
from t2
group by dt
order by dt;

-- 解法2：
select
    dt,
    count(t1.uid) dau,
    round(avg(if(dt = first_day,1,0)),2) uv_new_ratio
from (select uid,date(in_time) dt from tb_user_log
      union
      select uid,date(out_time) dt from tb_user_log) t1  -- 用户活跃表
left join
     (select uid,min(date(in_time)) first_day from tb_user_log group by uid) t2 -- 每日新用户表
on t1.uid = t2.uid
group by dt
order by dt;

-- 解法3：
with t as (
    select uid,date(in_time) dt,min(date(in_time)) over(partition by uid) first_dt from tb_user_log
    union
    select uid,date(out_time) dt,min(date(in_time)) over(partition by uid) first_dt from tb_user_log)
select dt,count(uid) dau,round(avg(if(dt = first_dt,1,0)),2) uv_new_ratio
from t group by dt order by dt;

发表于 2023-07-22 18:35:42 回复(0)

心远#12

select dt,

count(*) as dau,

round(sum(new)/count(*),2) as uv_new_ratio

from (

select dt,a.uid,

(case when datediff(a.dt,b.min_login)=0 then 1

else 0 end

) as new

from

(

select date(in_time) as dt,uid

from tb_user_log

union

select date(out_time) as dt,uid

from tb_user_log

) a

inner join

(

select uid,

min(date(in_time)) as min_login

from tb_user_log

group by uid

) b

on a.uid = b.uid

) t

group by dt

order by dt

发表于 2023-05-15 20:43:13 回复(0)

提交观点

问题信息

难度：

267条回答 597收藏 1804浏览

通过挑战的用户

查看代码

Zuuu

2022-09-16 18:21:20
弃置

2022-09-16 16:32:04
数据搬砖

2022-09-16 15:30:01
牛客68522...

2022-09-16 15:08:48
冰菓_

2022-09-16 10:53:39

每天的日活数及新用户占比

输入

输出

分析思路

求解代码

问题信息

热门推荐

通过挑战的用户

相关试题