# 找出近一个月发布的视频中热度最高的top3视频 # 字段：video_id、hot_index # 热度hot_index = (100*完播率+5*点赞数+3*评论数+2*转发数)*新鲜度 # 新鲜度 = 1/(最近无播放天数 + 1) # tb1： # 完播率 =(end_time - start_time) >= duration 则完播 with tb1 as( select video_id,date(end_time) as play_date, timestampdiff(second,start_time,end_time) as play_duration, dur...

# 字段：product_id , profit_rate # 时间：2021-10至今 # 店铺：901 # 商品毛利率 = (1-进价/平均单件售价)*100% # 店铺毛利率 = (1-总进价成本/总销售收入)*100%、 # ?? 需要添加status = 1? # tb1：连接 with tb1 as( select order_id,product_id,shop_id,in_price,price,cnt from tb_order_detail left join tb_product_info using(product_id) left join tb_order_over...

# 字段：author、month、每月涨粉率fans_growth_rate、total_fans # # tb1：连接 with tb1 as( select uid,author,date_format(start_time,'%Y-%m') as start_date,if_follow, # 增加关注字段 if(if_follow = 2,-1,if_follow) as follow # 播放量 from tb_user_video_log left join tb_video_info using(video_id) where year(start_time) = 2021 )...

# 字段：author、month、每月涨粉率fans_growth_rate、total_fans # # tb1：连接 with tb1 as( select uid,author,date_format(start_time,'%Y-%m') as start_date,if_follow, # 增加关注字段 if(if_follow = 2,-1,if_follow) as follow # 播放量 from tb_user_video_log left join tb_video_info using(video_id) where year(start_time) = 2021 )...

# 字段：user_grade 和 ratio # 忠实用户：近7天活跃过且非新晋用户、新晋用户(近7天新增)、 # 沉睡用户(近7天未活跃但更早前活跃过)、流失用户(近30天未活跃但更早前活跃过) # tb1：筛选最近的时间点/uid with tb1 as( select uid, max(date(in_time)) as day1, min(date(in_time)) as day2 from tb_user_log group by uid ), # tb2：选出最大时间 tb2 as( select max(day1) as max_day from tb1 ), # tb3：时...

# tb1：找到有取消过订单的司机id列表 select ifnull(driver_id,'总体') as driver_id,round(avg(grade),1) as avg_grade from tb_get_car_order where driver_id in (select distinct driver_id from tb_get_car_order where start_time is null AND year(order_time) = 2021 AND month(order_time) = 10) #有取消记录的司机 AND grade is not null...

-- 1.存在0级用户未完成试卷数大于2，输出每个0级别用户的 试卷未完成数和未完成率 -- 2.若不存在，则输出所有作答记录的用户的这两个指标。 -- 字段：uid、incomplete_cnt、incomplete_rate -- tb1：链接表 with tb1 as( select uid,level,start_time,submit_time,score from exam_record right join user_info using(uid) ), -- tb2：求取总量用户 tb2 as( select uid,round(count(if(submit_time is ...

# 筛选artical_id = 0 ， # 时间筛选：20210707 - 20211031 # 字段：uid、month、coin # 连续签到 构造 核心：排序编号 与 签到日期的差值是相等的 # V1 # with table1 as( # select uid,date_format(in_time,'%Y%m%d') as in_date, # dense_rank()over(partition by uid order by in_time) as rank_number # from tb_user_log # where in_time >='2021-07-07' ...

# 字段：author_id，author_level,days_cnt # tb1：过程表，链接 with tb1 as( select answer_date,author_id,author_level from answer_tb left join author_tb using(author_id) ), # tb2：排序 tb2 as( select author_id,author_level,dense_rank()over(partition by author_id order by answer_date) as cnt from tb1 ) # tb3：结果表筛选天数...

# 统计连续2天消费用户 # 字段：user_id，days_count # 核心：解决连续签到问题核心是排序编号和签到日期的差是相等的。。 # tb1：过程表 with tb1 as( select distinct user_id,dense_rank()over(partition by user_id order by sales_date) as rk from sales_tb ) select user_id,max(rk) as days_count from tb1 where rk >=2 group by user_id # 这连续问题还比较简单，只需要一个dens...

# V1 # # 2021年上半年各类试卷的做完次数 以及 同比 # # 以及做完次数排名变化，按照增长率📈 和 21年排名降序输出 # # 字段要求：tag\20年做完次数\21年做完次数\同比\20排名\21排名\排名增长数 # # 表一：链接 # with table1 as ( # select tag,uid,exam_id,submit_time # from exam_record left join examination_info using(exam_id) # where submit_time is not null # And year(submit_time) b...

# 国庆前3天，每类视频每天近一周总点赞量和一周内最大每天转发量 # 字段要求：tag、dt、近一周总点赞量(包括当日)、近一周最大单日转发量 # tb1：宽表连接tag with tb1 as ( select video_id,start_time,end_time,tag,if_follow,if_like,if_retweet,comment_id from tb_user_video_log left join tb_video_info using(video_id) ), # tb2：过程表，维度：tag、dt，指标：求和点赞量 & 最大值转发量 tb2 as( sele...

# 计实际总销售额、客单价（总收入/总用户数） select sum(sales_price) as sales_total, round(sum(sales_price)/count(distinct user_id),2) as per_trans from sales_tb

# dt、近七日日均订单完成量、日均订单取消量 # 构造 p-date 订单完成量、订单取消量(根据start_time是否为空判断完成or取消) # select date(start_time) as dt, # count(start_time) as finish_num, # sum(if(start_time is null,1,0)) as cancel_num # from tb_get_car_order # # where date(start_time) is not null # 筛选不为空的 # group by date(start_time) # 标记订单状态(完...