2023-10-04 21:29 门头沟学院 数据分析师
关注
题解 | #每天的日活数及新用户占比#
WITH t1 AS(
SELECT uid,MIN(DATE(in_time)) AS dt
FROM tb_user_log
GROUP BY uid
), # 新用户表
t2 AS (
SELECT uid, DATE(in_time) as active_time
FROM tb_user_log
UNION
SELECT uid, DATE(out_time) as active_time
FROM tb_user_log
UNION
SELECT uid, (DATE(in_time)+ INTERVAL 1 DAY) as active_time # 跨天活跃
FROM tb_user_log
WHERE TIMESTAMPDIFF(DAY,in_time,out_time)>=1
) # 用户活跃日期表
SELECT t2.active_time, COUNT(t2.uid) AS dau,
ROUND(COUNT(t1.uid)/COUNT(t2.uid),2) AS uv_new_ratio
FROM t2
LEFT JOIN t1 ON t1.uid = t2.uid and t1.dt = t2.active_time
GROUP BY active_time
ORDER BY active_time
SELECT uid,MIN(DATE(in_time)) AS dt
FROM tb_user_log
GROUP BY uid
), # 新用户表
t2 AS (
SELECT uid, DATE(in_time) as active_time
FROM tb_user_log
UNION
SELECT uid, DATE(out_time) as active_time
FROM tb_user_log
UNION
SELECT uid, (DATE(in_time)+ INTERVAL 1 DAY) as active_time # 跨天活跃
FROM tb_user_log
WHERE TIMESTAMPDIFF(DAY,in_time,out_time)>=1
) # 用户活跃日期表
SELECT t2.active_time, COUNT(t2.uid) AS dau,
ROUND(COUNT(t1.uid)/COUNT(t2.uid),2) AS uv_new_ratio
FROM t2
LEFT JOIN t1 ON t1.uid = t2.uid and t1.dt = t2.active_time
GROUP BY active_time
ORDER BY active_time
全部评论
相关推荐
球球与墩墩：这不是前端常考的对象扁平化吗，面试官像是前端出来的
const flattern = (obj) => {
const res = {};
const dfs = (curr, path) => {
if(typeof curr === 'object' && curr !== null) {
const isArray = Array.isArray(curr);
for(let key in curr) {
const newPath = path ? isArray ? `${path}[${key}]` : `${path}.${key}` : key;
dfs(curr[key], newPath);
}
} else {
res[path] = curr
}
}
dfs(obj);
return res;
}
查看3道真题和解析 点赞 评论 收藏
分享
牛客96609213...：疯狂背刺，之前还明确设置截止日期，还有笔试，现在一帮人卡在复筛，他反而一边开启扩招，还给扩招的免笔试，真服了，你好歹先把复筛中的给处理了再说
投递大疆等公司10个岗位 点赞 评论 收藏
分享
