以下是牛客大厂真题篇：包括百度、滴滴、抖音、京东、牛客、淘宝、知乎等
百度
2021年11月每天的人均浏览文章时长
2021年11月每天的人均浏览文章时长牛客题霸牛客网 (nowcoder.com)
问题：
统计2021年11月每天的人均浏览文章时长（秒数），结果保留1位小数，并按时长由短到长排序。
题目分析：
2021年11月开始：date_format(in_time,"%Y-%m") = '2021-11'
每天的人均浏览文章时长
文章ID = 0表示用户在浏览非文章内容，要过滤掉
GROUP BY dt：同一天的记录聚合在一起
一个用户一天内可能浏览几个文章内容（留下几行数据，但不对应成多人），故要过滤distinct uid
保留一位小数，ROUND(,1)
ORDER BY avg_viiew_len_sec：按时长升序
SELECT date(in_time) dt,
ROUND(sum(
TIMESTAMPDIFF(SECOND,in_time,out_time)) /
count(distinct uid)
,1
) avg_viiew_len_sec
FROM tb_user_log
WHERE date_format(in_time,"%Y-%m") = '2021-11' and artical_id != 0
GROUP BY dt
ORDER BY avg_viiew_len_sec每篇文章同一时刻最大的在看人数
每篇文章同一时刻最大在看人数牛客题霸牛客网 (nowcoder.com)
详细解答：利用SUM窗口函数找到同一时刻内的最大计数_牛客博客 (nowcoder.net)
SELECT
artical_id,
MAX(instant_viewer_cnt) max_uv
FROM (SELECT
artical_id,
SUM(diff) OVER(PARTITION BY artical_id ORDER BY dt,diff DESC) instant_viewer_cnt
FROM(SELECT
artical_id,in_time dt,1 diff
FROM tb_user_log
WHERE artical_id != 0
UNION ALL
SELECT
artical_id,out_time dt,-1 diff
FROM tb_user_log
WHERE artical_id != 0) t1
) t2
GROUP BY artical_id
ORDER BY max_uv DESC2021年11月每天新用户的次日留存率
2021年11月每天新用户的次日留存率牛客题霸牛客网 (nowcoder.com)
解析：题解 | #2021年11月每天新用户的次日留存率#_牛客博客 (nowcoder.net)
WITH t1 AS(
SELECT DISTINCT uid,DATE(in_time) AS dt FROM tb_user_log
UNION
SELECT DISTINCT uid,DATE(out_time) AS dt FROM tb_user_log
) # 用户活跃表
SELECT dt,
ROUND(SUM(
CASE WHEN
(uid,dt) IN (SELECT uid,date_add(dt,INTERVAL -1 DAY) FROM t1) AND
(uid,dt) IN (SELECT uid,MIN(dt) FROM t1 GROUP BY uid)
THEN 1 ELSE 0 END
) /
SUM(
CASE WHEN
(uid,dt) IN (SELECT uid,MIN(dt) FROM t1 GROUP BY uid)
THEN 1 ELSE 0 END
)
,2) AS uv_left_rate
FROM t1
WHERE DATE_FORMAT(dt,'%Y-%m') = '2021-11'
GROUP BY dt
HAVING uv_left_rate is not null
ORDER BY dt统计活跃间隔对用户分级结果
统计活跃间隔对用户分级结果牛客题霸牛客网 (nowcoder.com)
SELECT user_grade,ROUND(COUNT(uid) / MAX(user_cnt),2) as ratio
FROM(
SELECT uid,user_cnt,
CASE
WHEN last_dt_diff >= 30 THEN "流失用户"
WHEN last_dt_diff >= 7 THEN "沉睡用户"
WHEN first_dt_diff < 7 THEN "新晋用户"
ELSE "忠实用户"
END as user_grade
FROM(
SELECT uid,user_cnt,
TIMESTAMPDIFF(DAY,first_dt,cur_dt) as first_dt_diff,
TIMESTAMPDIFF(DAY,last_dt,cur_dt) as last_dt_diff
FROM (
SELECT uid,
MIN(DATE(in_time)) as first_dt,
MAX(DATE(out_time)) as last_dt
FROM tb_user_log
GROUP BY uid
) as t_uid_first_last
LEFT JOIN(
SELECT MAX(DATE(out_time)) as cur_dt,
COUNT(DISTINCT uid) as user_cnt
FROM tb_user_log
) as t_overall_info ON 1
) as t_user_info
) as t_user_grade
GROUP BY user_grade
ORDER BY ratio DESC;每天的日活数及新用户占比
每天的日活数及新用户占比牛客题霸牛客网 (nowcoder.com)
解析：题解 | #每天的日活数及新用户占比#_牛客博客 (nowcoder.net)
WITH t1 AS(
SELECT DISTINCT uid,DATE(in_time) dt,MIN(DATE(in_time))OVER(PARTITION BY uid) new_dt FROM tb_user_log
UNION
SELECT DISTINCT uid,DATE(out_time) dt,MIN(DATE(in_time))OVER(PARTITION BY uid) new_dt FROM tb_user_log
)
SELECT dt,COUNT(1) dau,ROUND(SUM(IF(dt=new_dt,1,0))/COUNT(1),2) uv_new_ratio
FROM t1 GROUP BY dt ORDER BY dt ASC;连续签到领金币
解析：题解 | #连续签到领金币#_牛客博客 (nowcoder.net)
select t2.uid,DATE_FORMAT(t2.dt,'%Y%m') month,sum(t2.coin) coin
FROM(select *,
row_number() over(PARTITION BY t1.uid,t1.dt2 order by t1.dt) ranking2,
case
when row_number() over(PARTITION BY t1.uid,t1.dt2 order by t1.dt)%7=3 then 3
when row_number() over(PARTITION BY t1.uid,t1.dt2 order by t1.dt)%7=0 then 7
else 1 end coin
FROM(SELECT DISTINCT uid,date(in_time) dt,
row_number() over (partition by uid order by date(in_time)) ranking,
date(in_time)-(row_number() over (partition by uid order by date(in_time))) dt2
FROM tb_user_log
where date(in_time) between '2021-07-07' and '2021-10-31'
and artical_id=0 and sign_in=1)t1
) t2
group by t2.uid,DATE_FORMAT(t2.dt,'%Y%m')
order by t2.uid,DATE_FORMAT(t2.dt,'%Y%m')
滴滴
2021年国企在北京接单3次及以上的司机统计信息
2021年国庆在北京接单3次及以上的司机统计信息牛客题霸牛客网 (nowcoder.com)
解题分析：题解 | #SQL 19.2021年国庆在北京接单3次及以上的司机统计信息#_牛客博客 (nowcoder.net)
SELECT "北京" city,
ROUND(AVG(order_num),3) avg_order_num,
ROUND(AVG(income),3) avg_income
FROM (
SELECT driver_id,
COUNT(order_id) order_num,
SUM(fare) income
FROM tb_get_car_order
JOIN tb_get_car_record USING(order_id)
WHERE city = "北京" and DATE(order_time) BETWEEN '20211001' AND '20211007'
GROUP BY driver_id
HAVING COUNT(order_id) >= 3
) t_driver_info有取消订单记录的司机平均分
有取消订单记录的司机平均评分牛客题霸牛客网 (nowcoder.com)
解题分析：题解 | #SQL 20.有取消订单记录的司机平均评分#_牛客博客 (nowcoder.net)
SELECT IFNULL(driver_id,"总体") driver_id,
ROUND(AVG(grade),1) avg_grade
FROM tb_get_car_order
WHERE driver_id in (
SELECT driver_id
FROM tb_get_car_order
WHERE DATE_FORMAT(order_time,"%Y-%m") = '2021-10' AND ISNULL(fare)
) AND NOT ISNULL(grade)
GROUP BY driver_id
WITH ROLLUP每个城市中评分最高的司机信息
每个城市中评分最高的司机信息牛客题霸牛客网 (nowcoder.com)
解题分析：题解 | #SQL 21.每个城市中评分最高的司机信息#_牛客博客 (nowcoder.net)
SELECT city,driver_id,
avg_grade,avg_order_num,avg_mileage
FROM(SELECT city,driver_id,
ROUND(avg_grade,1) avg_grade,
ROUND(order_num / work_days,1) avg_order_num,
ROUND(total_mileage / work_days,3) avg_mileage,
RANK() OVER(PARTITION BY city ORDER BY avg_grade DESC) as rk
FROM(SELECT driver_id,city,
AVG(grade) avg_grade,
COUNT(DISTINCT DATE(order_time)) work_days,
COUNT(order_time) order_num,
SUM(mileage) total_mileage
FROM tb_get_car_record
JOIN tb_get_car_order USING(order_id)
GROUP BY driver_id,city
) t_driver_info
) t_driver_rk
WHERE rk = 1
ORDER BY avg_order_num国企期间近7日日均取消订单量
国庆期间近7日日均取消订单量牛客题霸牛客网 (nowcoder.com)
解题分析：题解 | #国庆期间近7日日均取消订单量#_牛客博客 (nowcoder.net)
SELECT *
FROM(SELECT dt,
ROUND(
SUM(daily_complete) OVER(ORDER BY dt rows 6 preceding)/7
,2) finish_num_7d,
ROUND(
SUM(daily_cancel) OVER(ORDER BY dt rows 6 preceding)/7
,2) cancel_num_7d
FROM(SELECT date(event_time) dt,
SUM(
CASE WHEN ISNULL(fare) = FALSE THEN 1 ELSE 0 END
) daily_complete,
SUM(
CASE WHEN ISNULL(fare) = TRUE THEN 1 ELSE 0 END
) daily_cancel
FROM tb_get_car_record
INNER JOIN tb_get_car_order USING(order_id)
WHERE date(event_time) BETWEEN '20210925' AND '20211003'
GROUP BY dt
) temp1
) temp2
WHERE dt BETWEEN '2021-10-01' AND '2021-10-03'抖音
各个视频的平均完播率
各个视频的平均完播率牛客题霸牛客网 (nowcoder.com)
SELECT
a.video_id,
ROUND(
AVG(IF(TIMESTAMPDIFF(second,start_time,end_time) >= duration,1,0)),
3
) as avg_comp_play_rate
FROM
tb_user_video_log a
LEFT JOIN tb_video_info b on a.video_id = b.video_id
WHERE
year (start_time) = 2021
GROUP BY
a.video_id
ORDER BY
avg_comp_play_rate DESC;平均播放进度大于60%的视频类别
平均播放进度大于60%的视频类别牛客题霸牛客网 (nowcoder.com)
SELECT tag, CONCAT(avg_play_progress, "%") as avg_play_progress
FROM (
SELECT tag,
ROUND(AVG(
IF(TIMESTAMPDIFF(SECOND, start_time, end_time) > duration, 1,
TIMESTAMPDIFF(SECOND, start_time, end_time) / duration)
) * 100, 2) as avg_play_progress
FROM tb_user_video_log a
Left JOIN tb_video_info b
ON a.video_id = b.video_id
GROUP BY tag
HAVING avg_play_progress > 60
ORDER BY avg_play_progress DESC
) as t_progress;每类视频近一个月的转发量
SELECT
b.tag,
SUM(if_retweet) retweet_cnt,
ROUND(SUM(if_retweet) / COUNT(*), 3) retweet_rate
FROM
tb_user_video_log a
LEFT JOIN tb_video_info b ON a.video_id = b.video_id
WHERE
DATEDIFF (
DATE (
(
select
max(start_time)
FROM
tb_user_video_log
)
),
DATE (a.start_time)
) <= 29
GROUP BY
b.tag
ORDER BY
retweet_rate desc每个创作者每月的涨粉率及截至当前的总粉丝量
每个创作者每月的涨粉率及截止当前的总粉丝量牛客题霸牛客网 (nowcoder.com)
SELECT
author,
date_format(start_time,'%Y-%m') month,
round(
sum(case when if_follow=1 then 1
when if_follow=2 then -1
else 0 end) / count(author)
,3) fans_growth_rate,
sum(
sum(
case when if_follow = 1 then 1
when if_follow = 2 then -1
else 0 end
)
) over(partition by author order by date_format(start_time,'%Y-%m')) total_fans
FROM tb_user_video_log a
LEFT JOIN tb_video_info b
ON a.video_id = b.video_id
WHERE year(start_time) = 2021
group by author,month
order by author,total_fans国庆期间每类视频点赞量和转发量
国庆期间每类视频点赞量和转发量牛客题霸牛客网 (nowcoder.com)
WITH
t1 AS (
SELECT
tag,
DATE_FORMAT (start_time, '%Y-%m-%d') dt,
SUM(SUM(if_like)) OVER (
PARTITION BY tag
ORDER BY DATE_FORMAT (start_time, '%Y-%m-%d') rows 6 preceding
) AS sum_like_cnt_7d,
MAX(SUM(if_retweet)) OVER (
PARTITION BY tag
ORDER BY DATE_FORMAT (start_time, '%Y-%m-%d') rows 6 preceding
) AS max_retweet_cnt_7d
FROM
tb_user_video_log
JOIN tb_video_info USING (video_id)
WHERE
DATEDIFF (
'2021-10-03',DATE_FORMAT (start_time, '%Y-%m-%d')
) < 9
GROUP BY dt,tag
)
SELECT *
FROM t1
WHERE dt BETWEEN '2021-10-01' AND '2021-10-03'
ORDER BY
tag DESC,
dt ASC;近一个月发布的视频中热度最高的Top3视频
近一个月发布的视频中热度最高的top3视频牛客题霸牛客网 (nowcoder.com)
SELECT video_id,
ROUND((100 * comp_play_rate + 5 * like_cnt + 3 * comment_cnt + 2 * retweet_cnt)
/ (TIMESTAMPDIFF(DAY, recently_end_date, cur_date) + 1), 0) as hot_index
FROM (
SELECT video_id,
AVG(IF(
TIMESTAMPDIFF(SECOND, start_time, end_time)>=duration, 1, 0
)) as comp_play_rate,
SUM(if_like) as like_cnt,
COUNT(comment_id) as comment_cnt,
SUM(if_retweet) as retweet_cnt,
MAX(DATE(end_time)) as recently_end_date, -- 最近被播放日期
MAX(DATE(release_time)) as release_date, -- 发布日期
MAX(cur_date) as cur_date -- 非分组列，加MAX避免语法错误
FROM tb_user_video_log
JOIN tb_video_info USING(video_id)
LEFT JOIN (
SELECT MAX(DATE(end_time)) as cur_date
FROM tb_user_video_log
) as t_max_date ON 1
GROUP BY video_id
HAVING TIMESTAMPDIFF(DAY, release_date, cur_date) < 30
) as t_video_info
ORDER BY hot_index DESC
LIMIT 3;
京东
计算商城中2021年每月的GMV
计算商城中2021年每月的GMV牛客题霸牛客网 (nowcoder.com)
SELECT date_format(event_time,"%Y-%m") month,
ROUND(SUM(total_amount),0) GMV
FROM tb_order_overall
WHERE year(event_time) = 2021 AND status != 2
GROUP BY month
HAVING GMV > 100000
ORDER BY GMV统计2021年10月每个退货率不大于0.5的商品各项指标
统计2021年10月每个退货率不大于0.5的商品各项指标牛客题霸牛客网 (nowcoder.com)
select product_id, round(click_cnt/show_cnt, 3) as ctr,
round(IF(click_cnt>0, cart_cnt/click_cnt, 0), 3) as cart_rate,
round(IF(cart_cnt>0, payment_cnt/cart_cnt, 0), 3) as payment_rate,
round(IF(payment_cnt>0, refund_cnt/payment_cnt, 0), 3) as refund_rate
from (
select product_id, COUNT(1) as show_cnt,
sum(if_click) as click_cnt,
sum(if_cart) as cart_cnt,
sum(if_payment) as payment_cnt,
sum(if_refund) as refund_cnt
from tb_user_event
where DATE_FORMAT(event_time, '%Y%m') = '202110'
group by product_id
) as t_product_index_cnt
where payment_cnt = 0 or refund_cnt/payment_cnt <= 0.5
order by product_id;某店铺的各商品毛利率及店铺整体毛利率
某店铺的各商品毛利率及店铺整体毛利率牛客题霸牛客网 (nowcoder.com)
解题分析：题解 | #某店铺的各商品毛利率及店铺整体毛利率#_牛客博客 (nowcoder.net)
with t as(
select p.product_id, p.in_price,d.price,d.cnt
from tb_order_overall o
left join tb_order_detail d
on o.order_id = d.order_id
left join tb_product_info p
on p.product_id = d.product_id
where o.status = 1 and date(o.event_time)>='2021-10-01' and p.shop_id = 901
)
select '店铺汇总' as product_id,
concat(round((1-sum(in_price*cnt)/sum(price*cnt))*100,1),'%') as profit_rate
from t
union
select *
from(
select product_id,
concat(round((1-sum(in_price*cnt)/sum(price*cnt))*100,1),'%') as profit_rate
from t
group by product_id
having profit_rate>24.9
order by product_id
) t2
;零食类商品中复购率top3高的商品
SELECT product_id,
ROUND(SUM(repurchase) / COUNT(repurchase), 3) as repurchase_rate
FROM (
SELECT uid, product_id, IF(COUNT(event_time)>1, 1, 0) as repurchase
FROM tb_order_detail
JOIN tb_order_overall USING(order_id)
JOIN tb_product_info USING(product_id)
WHERE tag="零食" AND event_time >= (
SELECT DATE_SUB(MAX(event_time), INTERVAL 89 DAY)
FROM tb_order_overall
)
GROUP BY uid, product_id
) as t_uid_product_info
GROUP BY product_id
ORDER BY repurchase_rate DESC, product_id
LIMIT 3;牛客
直播转换率
SELECT c.course_id,course_name,
ROUND(SUM(if_sign) * 100 / SUM(if_vw), 2) 'sign_rate(%)'
FROM course_tb c
JOIN behavior_tb b USING(course_id)
GROUP BY c.course_id,course_name
ORDER BY c.course_id直播开始时各直播间在线人数
牛客直播开始时各直播间在线人数牛客题霸牛客网 (nowcoder.com)
SELECT course_id,course_name,
COUNT(DISTINCT user_id) online_num
FROM course_tb
JOIN attend_tb USING(course_id)
WHERE '19:00' BETWEEN DATE_FORMAT(in_datetime,'%H:%i') and DATE_FORMAT(out_datetime,'%H:%i')
GROUP BY course_id,course_name
ORDER BY course_id直播各科目平均观看时长
牛客直播各科目平均观看时长牛客题霸牛客网 (nowcoder.com)
-- 小明在A课程总共看了50分钟；在B课程总共看了60分钟，在C课程总共看了55分钟
-- 小红在A课程总共看了45分钟；在B课程总共看了40分钟
-- A课程的平均观看时长为：(50 + 45) / 2 = 47.5;
-- B课程的平均观看时长为：(60 + 40) / 2 = 50;
-- C课程的平均观看时长为：55
WITH t1 as (
SELECT user_id,
course_name,
SUM(TIMESTAMPDIFF(Minute,in_datetime,out_datetime)) len
FROM course_tb
JOIN attend_tb USING(course_id)
GROUP BY user_id,course_name
)
SELECT course_name,
ROUND(
SUM (len) * 100 / COUNT(user_id)
,2) avg_Len
FROM t1
GROUP BY course_name
ORDER BY avg_Len DESC
----------------------------这题意实在出的有很大问题----------------------------------
SELECT
course_name,
ROUND(
AVG(TIMESTAMPDIFF (MINUTE, in_datetime, out_datetime)),
2
) avg_Len
FROM
attend_tb
JOIN course_tb USING (course_id)
GROUP BY
course_name
ORDER BY
avg_Len DESC;
直播各科目出勤率
牛客直播各科目出勤率牛客题霸牛客网 (nowcoder.com)
题目分析：题解 | #牛客直播各科目出勤率#_牛客博客 (nowcoder.net)
SELECT
course_id,
course_name,
ROUND(attend_cnt * 100 / application_cnt, 2) 'attend_rate(%)'
FROM
(
SELECT course_id,
COUNT(DISTINCT user_id) application_cnt
FROM behavior_tb
WHERE if_sign = 1
GROUP BY course_id
) AS t1
JOIN (
SELECT course_id,
COUNT(DISTINCT user_id) attend_cnt
FROM attend_tb
WHERE TIMESTAMPDIFF (minute, in_datetime, out_datetime) >= 10
GROUP BY course_id
) AS t2 USING (course_id)
JOIN (course_tb) USING (course_id)
ORDER BY course_id;直播各科目同时在线人数
牛客直播各科目同时在线人数牛客题霸牛客网 (nowcoder.com)
题目分析：题解 | #牛客直播各科目同时在线人数#_牛客博客 (nowcoder.net)
SELECT course_id,course_name,
MAX(uv_cnt) max_num
FROM(SELECT course_id,course_name,
SUM(uv) OVER(PARTITION BY course_id ORDER BY dt,uv DESC) uv_cnt
FROM(SELECT course_id,user_id,
in_datetime dt,1 AS uv
FROM attend_tb
UNION ALL
SELECT course_id,user_id,
out_datetime dt,-1 AS uv
FROM attend_tb
) uv_tb
JOIN course_tb USING (course_id)
) t1
GROUP BY course_id,course_name
ORDER BY course_id淘宝
店铺的SPU数量
某宝店铺的SPU数量牛客题霸牛客网 (nowcoder.com)
SELECT style_id,count(1) SPU_num
FROM product_tb
GROUP BY style_id
ORDER BY SPU_num DESC店铺的实际销售额与客单价
某宝店铺的实际销售额与客单价牛客题霸牛客网 (nowcoder.com)
SELECT SUM(sales_price) sales_total,
ROUND(
SUM(sales_price) / count(distinct user_id)
,2) per_trans
FROM sales_tb店铺折扣率
SELECT
round(
(SUM(sales_price)/sum(sales_num*tag_price)) * 100
,2)
as discount_rate
FROM sales_tb s
LEFT JOIN product_tb p USING(item_id)店铺动销率与售罄率
题目分析：题解 | #某宝店铺动销率与售罄率#_牛客博客 (nowcoder.net)
SELECT t1.style_id,
ROUND(sales_num_cnt * 100 / (inventory_cnt - sales_num_cnt),2) 'pin_rate(%)',
ROUND(gmv * 100 / total_gmv, 2) ' sell-through_rate(%)'
FROM
(SELECT style_id,
SUM(inventory) inventory_cnt,
SUM(tag_price * inventory) total_gmv
FROM product_tb
GROUP BY style_id
) t1
LEFT JOIN (SELECT style_id,
SUM(sales_num) sales_num_cnt,
SUM(sales_price) gmv
FROM sales_tb
JOIN product_tb USING (item_id)
GROUP BY style_id
) t2 USING (style_id)
ORDER BY style_id;店铺连续2天及以上购物的用户及其对应的天数（重要）
某宝店铺连续2天及以上购物的用户及其对应的天数牛客题霸牛客网 (nowcoder.com)
题目分析：题解 | #某宝店铺连续2天及以上购物的用户及其对应的天数#_牛客博客 (nowcoder.net)
SELECT user_id,
COUNT(1) days_count
FROM (SELECT DISTINCT sales_date,user_id,
DENSE_RANK() OVER(PARTITION BY user_id ORDER BY sales_date)rk FROM sales_tb) t1
GROUP BY user_id,
DATE_SUB(sales_date,INTERVAL rk DAY)
HAVING COUNT(1)>=2;知乎
问答11月份日人均回答量
SELECT answer_date,
ROUND(
count(issue_id) / count(distinct author_id)
,2) per_num
FROM answer_tb
GROUP BY answer_date问答质量的回答总用户属于各级别的数量
SELECT
case
when b.author_level in (1,2) then '1-2级'
when b.author_level in (3,4) then '3-4级'
when b.author_level in (5,6) then '5-6级'
else '' end as level_cut,
count(issue_id) num
from answer_tb a
left join author_tb b USING(author_id)
where char_len >= 100
group by level_cut
order by num desc问答日回答问题数大于等于3个的所有用户
SELECT answer_date,author_id,
count(1) as answer_cnt
FROM answer_tb
GROUP BY answer_date,author_id
HAVING answer_cnt >= 3
ORDER BY answer_date,author_id回答过教育类问题的用户同时回答过职场类的用户
SELECT count(distinct author_id) num
FROM issue_tb
JOIN answer_tb USING(issue_id)
WHERE issue_type = 'Education' and author_id in (
SELECT author_id
FROM issue_tb
JOIN answer_tb USING(issue_id)
WHERE issue_type = 'Career'
)最大连续回答问题天数大于3天
题目分析：题解 | #某乎问答最大连续回答问题天数大于等于3天的用户及其对应等级#_牛客博客 (nowcoder.net)
SELECT author_id,author_level,
COUNT(1) days_cnt
FROM
(SELECT DISTINCT author_id,
answer_date,author_level,
DENSE_RANK() OVER (PARTITION BY author_id ORDER BY answer_date) rk
FROM answer_tb
JOIN author_tb USING (author_id)
) t1
GROUP BY author_id,author_level,
DATE_SUB(answer_date, INTERVAL rk DAY)
HAVING COUNT(1) >= 3
ORDER BY author_id;