题解 | #每天的日活数及新用户占比#

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

https://www.nowcoder.com/practice/dbbc9b03794a48f6b34f1131b1a903eb

# 计算日活的时候也要算out_time,有可能是在午夜12点左右都刷了,用UNION自动去重!!!

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
),
t3 AS(
SELECT
dt,
COUNT(DISTINCT uid) AS dau
FROM t1
GROUP BY dt
),
t2 AS
(SELECT 
uid,
MIN(DATE(in_time)) AS dt
FROM tb_user_log
GROUP BY uid)

SELECT
t3.dt,
t3.dau,
ROUND(COUNT(t2.uid)/t3.dau,2) AS uv_new_ratio
FROM t3
LEFT JOIN t2 ON t3.dt =t2.dt
GROUP BY t3.dt,t3.dau
ORDER BY t3.dt;

全部评论

相关推荐

05-07 19:10
已编辑
中国科学技术大学 C++
silly01:现在先去 momenta,8-9月去鹅找日常实习,八股文算法背好了你这随便进。不过建议补充一下后端知识,MySQL、Redis看下八股,再补个6824,加点go后台的技术栈,9月随便进大厂。CPP后端只能来WXG
点赞 评论 收藏
分享
评论
点赞
收藏
分享

创作者周榜

更多
牛客网
牛客网在线编程
牛客网题解
牛客企业服务