首页 > 试题广场 >

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

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

热度指数：62035 时间限制：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

Dawn0614

select dt,count(dt) as dau,round(count(if(rk=1,1,null))/count(dt),2) as uv_new_ratio from

(select uid,dt,rank() over (partition by uid order by dt) as rk 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 t) as a

group by dt

order by dt

发表于 2025-06-23 11:09:17 回复(0)

Mysql

halosun

WITH t AS (
    SELECT 
        uid
        ,DATE(in_time) AS dt
        ,in_time = FIRST_VALUE(in_time) OVER (PARTITION BY uid ORDER BY in_time) AS is_newuid
    FROM 
        tb_user_log
    UNION 
    SELECT 
        uid
        ,DATE(out_time) AS dt
        ,in_time = FIRST_VALUE(in_time) OVER (PARTITION BY uid ORDER BY in_time) AS is_newuid
    FROM 
        tb_user_log
)
SELECT 
    dt
    ,COUNT(*) AS dau
    ,ROUND(AVG(is_newuid),2) AS uv_new_ratio
FROM
    t
GROUP BY
    dt 
;

发表于 2025-04-21 16:36:53 回复(0)

Mysql

牛客775456359号

#--构建用户活跃表,记录用户首次登陆日期
with t1 as(
select uid,date(in_time) dt,min(date(in_time))over(partition by uid) first_date
from tb_user_log
union 
select uid,date(out_time) dt,min(date(in_time))over(partition by uid) first_date
from tb_user_log
)
#--计算当天活跃人数，以及新用户数
,t2 as(
select dt,
count(uid) dav,
count(if(dt=first_date,uid,null)) new_uid_cnt
from t1
group by dt
)
#--计算新用户占比
select dt,dav,
round(new_uid_cnt/dav,2) uv_new_ratio
from t2
order by 1;

发表于 2025-03-20 10:19:05 回复(0)

Mysql

牛客931265050号

select dt,count(distinct t1.uid),

round(sum(if(newuser_time=dt,1,0))/count(distinct t1.uid),2) as dau

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 min(date(in_time)) as newuser_time,uid from tb_user_log group by uid) t2 on

t1.uid = t2.uid

group by dt

;

发表于 2025-02-15 10:05:43 回复(0)

Mysql

风也温柔_

select
    t2.dt,
    count(*) as dau,
   round( count(t1.uid)/count(t2.uid),2) as uv_new_ratio
from (
select
    uid,
    MIN(DATE(in_time)) as dt
from tb_user_log 
group by uid
) t1
right join (
    select 
        uid,DATE(in_time) as dt
    from tb_user_log
    union 
    select 
        uid,DATE(out_time) 
    from tb_user_log
) t2 on t1.uid=t2.uid and t1.dt=t2.dt
group by t2.dt
order by t2.dt

发表于 2025-01-06 10:19:28 回复(0)

Mysql

yuciaaa

with a as(
    select
    uid,
    date_format(in_time, '%Y-%m-%d') as dt
    from tb_user_log
    union 
    select
    uid,
    date_format(out_time, '%Y-%m-%d') as dt
    from tb_user_log
), b as(
select 
min(dt) as min_dt,
uid
from a
group by uid
)
select 
ttt.min_dt,
cnt_all,
case when cnt is null then 0.00
else round(cnt / cnt_all , 2)
end as rate
from(
    select 
    count(distinct uid) as cnt_all,
    dt as min_dt
    from a
    group by dt
) ttt
left join
(
    select
    count(uid) as cnt,
    min_dt
    from b
    group by min_dt
) ccc
on ttt.min_dt = ccc.min_dt
order by ttt.min_dt asc

发表于 2024-09-22 20:41:06 回复(0)

Mysql

牛客712695039号

select t as dt,count(uid) as dau,round(sum(新增)/count(uid),2) as uv_new_ratio
from	(select uid,t,if(datediff(t,min(t)over(partition by uid))=0,1,0) as 新增
		from   (select uid,date(in_time) as t
				from tb_user_log
				union
				select uid,date(out_time) as t
				from tb_user_log) A) B
group by t
order by dt

发表于 2024-08-28 20:45:45 回复(0)

Mysql

牛客596634077号

select 
    e.dt,
    dau,
    round(new_in/dau,2) vu_new_ratio
from(
    select 
    dt,
    count(uid) dau
from (
    select 
     distinct(uid) uid,date_format(dt,'%Y-%m-%d') dt
from (
    select uid,in_time dt from tb_user_log
    union
    select uid,out_time dt from tb_user_log
) a
order by dt
) b
group by dt
) c
join(
    select 
    dt,count(uid) new_in
from (
    select uid,date_format(min(in_time),'%Y-%m-%d') dt
    from tb_user_log 
    group by uid
) d
group by dt
) e
on c.dt=e.dtna
难得自己写出来一次

发表于 2024-08-01 01:25:57 回复(0)

Mysql

starry_sky

# 统计每天的日活数及新用户占比
# # 每天日活：当日登入人数
# # 新用户占比：判断当日是否新用户--登入日期是否为首次登录日期

select
    in_day dt,
    count(distinct t1.uid) dau,
    round(sum(if(in_day=first_day,1,0))/count(distinct t1.uid),2) uv_new_ratio
from ( -- 当日登录(处理跨天情况)
    select
        uid,
        date(in_time) in_day
    from tb_user_log

    union 
    
        select
        uid,
        date(out_time) in_day
    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
    in_day
order by    
    in_day asc

发表于 2024-07-25 17:39:36 回复(0)

Mysql

魔王史莱姆

select dt,count(1),round(sum(new_user)/count(1),2) uv_new_ratio
from(
select uid,dt,if(dt=first_dt,1,0) new_user
from(
select uid,dt,first_dt from
(
select uid,date(in_time) dt from tb_user_log
union 
select uid,date(out_time) dt from tb_user_log)t
left join
(
select uid,date(min(in_time)) first_dt from tb_user_log
group by uid )t1
using(uid)
)t2)t3
group by dt 
order by dt

发表于 2024-04-16 13:21:52 回复(0)

Mysql

祖拜尔实习生

select
    t1.active_date,
    count(distinct t1.uid),
    round(
        count(
            distinct case
                when t2.register_date is not null then t1.uid
                else null
            end
        ) / count(distinct t1.uid),
        2
    )
from
    (
        select distinct
            uid,
            date (in_time) active_date
        from
            tb_user_log
        union
        select distinct
            uid,
            date (out_time)
        from
            tb_user_log
    ) t1
    left join (
        select
            uid,
            min(date (in_time)) register_date
        from
            tb_user_log
        group by
            uid
    ) t2 on t1.uid = t2.uid
    and t1.active_date = t2.register_date
group by
    t1.active_date
order by
    t1.active_date

编辑于 2024-04-07 09:50:25 回复(0)

Mysql

牛客268263674号

直接窗口函数，不需要表链接的方法，大家参考下：

with t1 as

(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)

,t2 as

(select uid,dt,

min(dt)over(partition by uid) min_dt,

if(datediff(dt,min(dt)over(partition by uid))= 0,1,0) type

from t1)

select dt,count(uid) dau,round(sum(type)/count(uid),2) uv_new_ratio

from t2 group by dt

order by dt;

发表于 2024-02-22 15:15:34 回复(0)

Mysql

牛客605111887号

with 
t as ( # 总表，把intime，outtime合成一列
select uid,  date(in_time) as t from tb_user_log
union select uid, date(out_time) as t from tb_user_log
),
t1 as ( # 日活数目
select t, count(distinct uid) as dau from t group by t
),
t2 as ( # 新用户数目
select day, count(uid) as new
from
    (select uid, min(t) as day from t group by uid)tt 
    # 这个子查询的逻辑是每个用户都有一个最早登陆日期，因此当天他是新用户，只要算出多少用户共享最早同一天就行了
group by day)

select t, dau, 
    case
    when new is null then 0.00
    else round(new/dau, 2) end as uv_new_ratio 
from t1 
    left join t2 on t=day
order by t;

编辑于 2024-02-13 23:31:23 回复(0)

Mysql

崔睿哲

select e.dt,e.dau,ifnull(round(e.sn/e.dau,2),0) uv_new_ratio
from
(select t1.dt dt,t2.sumnew sn,t1.dau dau from
(select  a.dt dt,count(a.uid)  dau
from (select distinct date_format(in_time,'%Y-%m-%d') dt ,uid
from tb_user_log
union 
select distinct date_format(out_time,'%Y-%m-%d') dt ,uid
from tb_user_log) a
group by a.dt) t1 
left join 
(select b.dt dt ,count(b.uid) sumnew
from (select uid,date_format(min(in_time),'%Y-%m-%d') dt
from tb_user_log
group by uid) b
group by b.dt) t2
on t2.dt=t1.dt) e
order by e.dt

发表于 2024-01-21 01:28:48 回复(0)