#order_tb: order_id #visit_tb: user_id+visit_time #需求 访问-下单 -- 这种登录问题，一定要去重，某个用户一天内多次访问丨登录的情况 select date(visit_time) as date ,concat(round(count(distinct o.user_id,date(order_time)) /count(distinct v.user_id,date(visit_time))*100,1),'%') as cr from visit_tb v left join order_tb o on v.user_id = o.u...

#梳理表格&需求颗粒度 #guestroom_tb room_id #checkin_tb room_id+user_id+checkin_time #需求 user_id+room_id select c.user_id ,c.room_id ,g.room_type ,abs(datediff(checkin_time,checkout_time)) as days from checkin_tb c left join guestroom_tb g on c.room_id = g.room_id where date(checkin_time)>='2022-06-12'...

# customers customer_id # products product_id # orders order_id #1.product_id #2.product_id #3.product_id #4.product_id #5.product_id #6.product_id+month #7.product_id+age_group with t1 as( select p.product_id ,unit_price ,sum(quantity) as total_quantity ,sum(quantity)*unit_price as total_sales ,sum...

#user_info uid #exam_record uid+exam_id+start_time #任意一个0级用户未完成试卷数大于2 #输出每个0级用户的试卷未完成数和未完成率（保留3位小数） #若不存在这样的用户，则输出所有有作答记录的用户的这两个指标 with t1 as ( select a.uid ,level ,count(start_time)-count(submit_time) as incomplete_cnt ,round((count(start_time)-count(submit_time)) /count(*),3) as incomplete_rate ,c...

#user_info uid #examination_info exam_id #exam_record uid+exam_id+start_time #practice_record uid+question_id+submit_time select t.uid -- 全部用户 ,count(distinct a.dt) as act_month_total -- 总活跃月份数 ,count(distinct b.dt) as act_days_2021 -- 2021年活跃天数 ,count(distinct c.exam_dt) as act_days_2021_exam -- 20...

#用户-视频互动表 uid+video_id+start_time #短视频信息表 video_id #热度 #top3 desc limit 3 #近一个月发布 max(end_time) release_time #热度= (a*视频完播率+b*点赞数+c*评论数+d*转发数)*新鲜度 #视频完播率 = 完整播放视频数/视频播放总数= （结束时间-开始时间）vs 视频时长 #点赞数 = sum(if_like) #评论数 = sum(comment_id) #转发数 = sum(if_retweet) #新鲜度=1/(最近无播放天数+1)=max(end_time)+vider id se...

#follow user_id+follower_id #follower_id user_id+music_id #music id #user_id #用户已经喜欢的音乐 #用户的关注人喜欢的音乐 select music_name from (select distinct id ,music_name from (select music_id #用户的关注人喜欢的音乐且自己未喜欢的音乐id from music_likes where user_id in (select follower_id from follow where user_id = 1) #用户的关注人 and m...

#断点分组 select user_id ,max(cnt_consec_days) as max_consec_days from (select user_id ,fdate ,count(consec_days)over(partition by user_id,consec_days) as cnt_consec_days from (select user_id ,fdate ,pd ,sum(pd)over(partition by user_id order by fdate) as consec_days from (select user_id ,fdate ,lag_fda...

#员工信息表 emp_id #考核试卷信息表 exam_id #试卷作答记录表 emp_id+exan_id+start_time #emp_id+emp_level+exam_tag+score+score_avg+timestampdiff(start.submit) #员工等级小于7 2021 select emp_id ,emp_level ,tag from (select emp_id ,exam_id ,time ,avg(time)over(partition by exam_id) as avg_time ,score ,avg(score)over(partition by...

#建立临时表查询SQL试卷上未完成率较高的50%用户 with t1 as #用户表 (select a.uid from (select uid ,percent_rank()over(order by(sum(if(score is null,1,0))/count(er.exam_id))) as pk #percent_rank窗口函数 from exam_record er left join examination_info ei on er.exam_id=ei.exam_id where tag='SQL' #sql条件 group by 1)a left join user_...

select dt ,round(finish_num_7d,2) as finish_num_7d ,round(cancel_num_7d,2) as cancel_num_7d from (select dt ,avg(s1)over(order by dt rows 6 preceding) as finish_num_7d ,avg(s2)over(order by dt rows 6 preceding) as cancel_num_7d from(select date(order_time) as dt ,sum(if(start_time is not null,1,0)) ...

select video_id ,round(((100*视频完播率+5*点赞数+3*评论数+2*转发数)*新鲜度),0) as hot_index from( select a.video_id ,avg(if(timestampdiff(second,start_time,end_time)>=duration,1,0)) as 视频完播率 ,sum(if_like) as 点赞数 ,sum(if_retweet) as 转发数 ,count(comment_id) as 评论数 ,1/(datediff((select date(max(end_time)) from tb_use...

select b.id ,b.name ,c.总积分 from (select user_id ,总积分 ,dense_rank()over(order by 总积分 desc) as de_rk from (select user_id ,sum(if(type='add',+grade_num,-grade_num)) as 总积分 from grade_info group by 1) a )c join user b on c.user_id=b.id where de_rk=1 group by 1,2 order by b.id;

select grade ,sum(number)over(order by grade) as t_rank from class_grade