# 注意： # MySQL中不允许先select一些数据,然后再delete # 所以要在from之后直接新建一个表 delete from exam_record WHERE start_time in (select * from (select e1.start_time from exam_record as e1 where e1.submit_time is NULL or TIMESTAMPDIFF(minute, e1.start_time, e1.submit_time) < 5 order by e1.start_time limit 3) t1 );

select e2.month_date,ifnull(t2.mau,0),ifnull(t3.month_add_uv,0), max(t3.month_add_uv) over(order by e2.month_date) , sum(t3.month_add_uv) over (order by e2.month_date) from ( select distinct date_format(e1.start_time,'%Y%m') as month_date from exam_record as e1 )e2 left join ( -- 计算新增用户数:时间-每月新增用户数 ...

select t1.exam_id,t1.month_date,t1.cnt,sum(t1.cnt) over(partition by t1.exam_id order by t1.month_date asc) as cum_exam_cnt from ( select distinct exam_id,date_format(start_time,'%Y%m') as month_date, count(start_time) over(partition by exam_id,date_format(start_time,'%Y%m')) as cnt from exam_record...

select t2.uid,t2.exam_id , round(avg(new_score),0) as avg_new_score from ( select t1.uid,t1.exam_id, if(max_score=min_score,t1.score,(t1.score-min_score)*100/(max_score-min_score)) as new_score from ( select e1.uid,e1.exam_id,e1.score, min(e1.score) over(partition by e1.exam_id ) as min_score, max(e...

select t2.uid,count(t2.month_submit) as exam_complete_cnt from (SELECT t1.uid,t1.month_submit from( select uid,date_format(start_time,'%Y-%m') as month_start, dense_rank() over(partition by uid order by date_format(start_time,'%Y-%m') desc) as rank_date, DATE_FORMAT(submit_time,'%Y-%m') as month_sub...

# 思路： # 查询完成高难度SQL得分平均值大于80分且7级的用户 # 统计该用户2021年试卷完成次数和题目总练习次数 # 由于有些用户完成试卷，但是没有练习；有些用户练习了但是没有完成试卷，因此用左右连接后union拼起来 select * from ( select t5.uid,ifnull(t5.exam_cnt,0) as exam_cnt ,ifnull(t5.question_cnt,0) as question_cnt from (select t1.*,t2.question_cnt from ( select e3.uid,count(e3.submit_time) a...

# 思路： # 1、生成每个tag每个uid的最大分数、最小分数 # 2、利用row_number按照tag分组，按照最大分数、最小分数、uid排序生成排名 # 3、查询排名小于等于3的信息 select t2.tag,t2.uid,t2.rank_score from ( select t1.tag,t1.uid, ROW_NUMBER() over(partition by t1.tag order by t1.max_score desc,t1.min_score desc,t1.uid desc) as rank_score from ( select e1.tag,e2.uid,ma...

# 获取活动1的用户 select t2.uid,'activity1' as activity from exam_record as t2 where year(t2.submit_time) = 2021 group by t2.uid having min(t2.score) >=85 union # 获取活动2的用户 select t1.uid,'activity2' as activity from ( select e.uid, score from exam_record as e left join examination_info as e2 on e.exam_id...

select * from ( select exam_id as tid,count(distinct uid) as uv, count(start_time) as pv from exam_record group by exam_id order by uv desc ,pv desc) t1 UNION select * from ( select question_id,count(distinct uid) as uv , count(submit_time) as pv from practice_record group by question_id order by uv...

select t1.submit_time,count(*) as month_q_cnt, round(count(*)/t1.cnt_day,3) as avg_day_q_cnt from (select uid,question_id,date_format(submit_time,'%Y%m') as submit_time, (case when month(submit_time) in (1,3,5,7,8,10,12) then 31 when month(submit_time)=2 then 28 else 30 end) as cnt_day from practice...

# 对原表进行处理，因为涉及到跨天活跃 with a as (select uid,in_time from ( SELECT uid,date(in_time) as in_time from tb_user_log UNION ALL SELECT uid,date(out_time) as in_time from tb_user_log)t group by uid,in_time) select t3.in_time,round(ifnull(t4.cnt_next/t3.cnt_first,0.00),2) as uv_left_rate from ( # 计算每个日期的新增用户数...

# 暴力解题思路： # 1、连接两表，保留tag start_time if_kile if_retweet字段 # 2、提取2021-10-01近七天的日期，用tag分组，计算sum(if_like) max(if_retweet) # 3、将2021-10-01 2021-10-02 2021-10-03用union连接起来 # 4、按照视频类别降序、日期升序 select t2.tag,t2.dt,t2.sum_like_cnt_7d,t2.max_retweet_cnt_7d from ( # 2021-10-01查询结果 select t1.tag,'2021-10-01' as d...

# 思路： # 1、利用video_id将两表拼起来，保留author,month(start_time),if_follow # 2、设置新字段cum_cnt，if_follow=1 为1，if_follow=2 为-1,else 为0 用于后续计算当前粉丝 # 3、设置新字段max_date max(start_time) over(partition by month(start_time)) 选取每个月的最大日期，每个月最大日期对应的max_date_sum是截至当月的累计粉丝数 # 4、利用author month(start_time)分组，sum(cum_cnt)计算当月累计粉丝...

注意事项 首先要明确某商品复购率 = 近90天内购买它至少两次的人数 ÷ 购买它的总人数。 近90天指包含最大日期（记为当天）在内的近90天。最大日期就是tb_order_overall表里的最大日期，用Max即可求出。近90天用datediff函数 select p1.product_id,ifnull(round(buy_2/buy,3),0) as repurchase_rate from tb_product_info as p1 left join ( # 计算每个产品购买的人数 select o1.product_id,count(distinct o2.uid) as buy f...