-- 简单记录一下，思路非常直观：分别计算出报名人数和出勤人数表，连接之后计算出勤率 WITH t5 AS (SELECT * FROM -- 根据if_sign计算每个课程的报名人数 (SELECT course_id, COUNT(DISTINCT user_id) AS sign_cnt FROM behavior_tb WHERE if_sign = 1 GROUP BY course_id) AS t1 LEFT JOIN (SELECT course_id, course_name, COUNT(DISTINCT user_id) AS attend_cnt FROM (SELEC...

-- 非常多陷阱的一道题... -- (1）注意时间：只收集2021年；（2）同一个人当天多次登录算活跃天数为1天；（3）不同人在同一个时间点活动活跃天数算两天 WITH t1 AS (SELECT DATE_FORMAT(start_time, '%Y%m') AS month, uid, COUNT(DISTINCT DATE_FORMAT(start_time, '%Y%m%d')) AS cnt FROM exam_record WHERE submit_time IS NOT NULL AND DATE_FORMAT(submit_time, '%Y')=2021 GROUP BY ...

import sys for line in sys.stdin: a = line.split() print(int(a[0]) + int(a[1])) import pandas as pd df = pd.read_csv('nowcoder.csv') df['date'] = pd.to_datetime(df['date'], format='%Y/%m/%d %H:%M') df['date'] = df['date'].dt.date # 只提取出user_id&date来进行识别 df1 = df[['user_id', 'date']] df2 = df1.co...

-- 最重要需要判定是否是工作日 WITH t1 AS (SELECT r.event_time, r.end_time, o.order_time, o.start_time, CASE WHEN 7 <= HOUR(event_time) AND HOUR(event_time) < 9 THEN '早高峰' WHEN 9 <= HOUR(event_time) AND HOUR(event_time) < 17 THEN '工作时间' WHEN 17 <= HOUR(event_time) AND HOUR(event_time) < 20 THEN ...

-- 使用窗口函数解决连续求解问题 SELECT user_id FROM (SELECT *, -- 若两行记录登录时间与序号相减是是相同值，则证明这两行时连续登录 DATE_SUB(DATE(log_time), INTERVAL ranking DAY) AS dt FROM (SELECT *, -- 首先根据用户分组，对用户登录时间进行排序 ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY log_time) AS ranking FROM login_tb JOIN register_tb USING(user_id)) AS t1) ...

-- 一直都不是很会计算这类题目,这次参考大佬的解答写一个回复简单记录下 WITH t1 AS -- 第一张子表：只保留我们需要的记录,满足时间和签到要求 (SELECT DISTINCT uid, DATE(in_time) AS dt, -- 这里使用窗口函数可以帮我们找到用户的连续签到开始日期 ROW_NUMBER() OVER(PARTITION BY uid ORDER BY DATE(in_time)) AS ranking FROM tb_user_log WHERE (DATE_FORMAT(in_time, '%Y%m%d') BETWEEN '20210707' AND '...

SELECT dt, -- 可能会出现同一天某个用户多次登录的场景,所以需要去重 COUNT(DISTINCT uid) AS dau, ROUND(COUNT(DISTINCT uid_1) / COUNT(DISTINCT uid), 2) AS uv_new_ratio FROM -- 第一张子表：统计每天的登记情况 (SELECT uid, DATE(in_time) AS dt FROM tb_user_log UNION ALL SELECT uid, DATE(out_time) AS dt FROM tb_user_log) AS t1 LEFT JOIN -- 第二张子表：统...

SELECT * FROM (SELECT user_grade, -- 因为这里有用GROUPBY子句,所以这里一定要对users聚合,但因为users的值是一样的,MAX/AVG/MIN都一样 ROUND(COUNT(user_grade) / MAX(users), 2) AS ratio FROM (SELECT uid, users, -- 若最近的时间间隔都已超过30天，则是流失用户，若最近的时间间隔大于7天，小于30天，则是沉睡用户； -- 若最远的时间间隔在7天之内,则是新进用户,忠实用户的情况最难判断,因此直接用ELSE进行判断 CASE WHEN interval_s &g...

-- 分别统计国庆前三天的情况再拼接在一起 SELECT * FROM (SELECT t1.tag, -- 添加时间 '2021-10-01' AS dt, SUM(t1.like_cnt) AS sum_like_cnt, MAX(t1.retweet_cnt) AS max_retweet_cnt_7d FROM (SELECT v.tag, SUM(u.if_like) AS like_cnt, SUM(u.if_retweet) AS retweet_cnt FROM tb_user_video_log u JOIN tb_video_info v USING(video_id) -...

SELECT t1.tag, CONCAT(FORMAT(AVG(t1.play_progress)*100 , 2), '%') AS avg_play_progress FROM (SELECT tag, IF((TIMESTAMPDIFF(SECOND, start_time, end_time)) / duration > 1, 1, (TIMESTAMPDIFF(SECOND, start_time, end_time)) / duration) AS play_progress FROM tb_user_video_log JOIN tb_video_info USING(v...

SELECT t3.video_id, ROUND(t3.ranking / t3.count, 3) AS avg_comp_play_rate FROM -- 第三章子表，填充NULL为0 (SELECT video_id, MAX(count) AS count, CASE WHEN MAX(ranking) IS NULL THEN 0 ELSE MAX(ranking) END AS ranking FROM -- 第二章子表，计算播放视频总数量 (SELECT video_id, COUNT(id) AS count FROM tb_user_video_log WHERE YEA...

SELECT user_id, MAX(连续登录天数) AS max_consec_days FROM -- 第二张子表：根据初始起始天数进行分组,分别计算该月内的连续登录时间 (SELECT user_id, MAX(日期排序) - MIN(日期排序) + 1 AS 连续登录天数 FROM -- 第一张子表：若两行登陆日期-序号相同则证明这两行是连续的两天,并且能得到两行的相同的起始天数 (SELECT user_id, fdate, ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY fdate) AS 日期排序, DATE_SUB(fdate,...

SELECT t1.device_id, t1.university, COUNT(t1.question_id) AS question_cnt, SUM(result_1) AS right_question_cnt FROM (SELECT -- 有重复字段名id,这里应该避免全选,否则后面会出现duplicate_columns错误 u.device_id, u.university, d.question_id, d.result, d.date, -- 把答错的记为0,答对的记为1,便于后面进行right_question_cnt的计算 CASE WHEN result = 'wr...

SELECT -- 计算分子 (SELECT COUNT(*) FROM -- 将原表中的日期向前推一天生成新表,若新表能和原表连接上,则证明该用户在该时间点进行了连续刷题操作,需要注意的是这里的是每个用户可能在同一天进行多次刷题,为避免重复值影响,需要使用DISTINCT (SELECT DISTINCT q.device_id, q.date FROM question_practice_detail q INNER JOIN -- 任何新生成的表都需要给一个别名 (SELECT DISTINCT device_id, DATE_ADD(date, INTERVAL 1 DAY) AS d...