# mau、新增用户数、 最大新增用户数、 累计用户数 # 新增用户数标记 with table1 as( select *,date_format(start_time,'%Y%m') as start_month, if(start_time = MIN(start_time)over(partition by uid),1,0) as if_new from exam_record ) select start_month, count(distinct uid) as mau, sum(if_new) as month_add_uv, max(sum(if_ne...

# 商城里所有新用户的首单平均交易金额（客单价）和平均获客成本（平均优惠价）。 # 新用户的定义？之前（10月份前）没有出现过的uid # 字段：客单价 / 平均获客成本 # 表一：聚合订单明细表 with table1 as ( select order_id,sum(price*cnt) as pre_cost from tb_order_detail group by order_id ), # 表二：连接 订单总表 & 新用户筛选 table2 AS( select event_time,order_id,uid,total_amount,...

# 第二快 和 第二慢 用时之差大于试卷时长的一半的试卷信息 # 字段：exam_id、duration、release_time # 表一：链接 with table1 as ( select exam_id,duration,release_time, timestampdiff(minute,start_time,submit_time) as finish_time from exam_record left join examination_info using(exam_id) # where submit_time is not null ),...

# 统计在有用户互动的最近一个月 # 表中最新时间作为标准时间？ # 字段：类别tag / 转发量 / 播放量 # 筛选条件：时间要求在 最近时间且过往30天 select tag,sum(if_retweet) as retweet_cut, round(sum(if_retweet)/count(if_retweet),3) as retweet_rate from tb_user_video_log left join tb_video_info using(video_id) where date(start_time) > (select date_sub(max(date...

# 计算该年连续两次作答试卷的最大时间窗days_window # 字段：uid / 最大时间窗 / 最大时间窗内平均做卷数 # 表一：筛选最大时间以及最小时间(时间差) & 做的卷子数量 with table1 as ( select uid,min(date_format(start_time,'%Y%m%d')) as time1, max(date_format(start_time,'%Y%m%d')) as time2, count(exam_id) as exam_cnt, count(exam_id) / (timestampd...

# 2021年10月每个有展示记录的退货率不大于0.5的商品各项指标 # 字段:商品id/ctr/加购率/支付率/退款率 = 退款/支付 # 表一：获取退款率不大于0.5的名单 10月份 select product_id, round(sum(if_click) / count(if_click),3) as ctr, round(sum(if_cart) / sum(if_click),3) as cart_rate, # 加购率 round(sum(if_payment)/ sum(if_cart),3) as payment_rate,# 支付率 round(sum(if_refu...

# 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) bet...

# 计算2021年里有播放记录的每个视频的完播率(结果保留三位小数)，并按完播率降序排序 # 🤔️：完播率=完成播放次数/总播放次数。 结束观看时间-开始播放时间>=视频时长视为完播 # 表一：链接&构造是否完播字段 with table1 as( select uid,video_id, (case when timestampdiff(second,start_time,end_time) >= duration then 1 else 0 end) as if_wb from tb_user_video_log left join ...

# 构造临时表1:链接exam_id with table1 as( select uid,tag,score from exam_record left join examination_info using(exam_id) where score is not null ), # 构造表2:按照tag进行分组，组内按照分数倒序 # 注意点：比较最大分，再比较最小分，最后比较uid,直接在order中实现 table2 as( select tag,uid,max(score),min(score), row_number()over(partiti...

# 连续签到 构造 核心：排序编号 与 签到日期的差值是相等的 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' and in_time <"2021-11_01" and artical_id = 0 and ...

# 各类试卷的做完次数 上半年 同期的增长率 # rank with table1 as( select tag, date_format(start_time,"%Y") as start_year, date_format(submit_time,"%Y%m") as submit_time from exam_record left join examination_info using(exam_id) where month(submit_time) <= 6 ), table2 as ( ...

# 基础表 with table1 as( select uid , level, tag, date_format(start_time,"%Y%m") as start_month, date_format(submit_time,"%Y%m") as submit_month from exam_record left join examination_info using(exam_id) left join user_info using(uid) ), # 统计SQL试卷上未完成率 # 6级和7级用户...

with table1 as ( select uid, date_format(start_time,"%Y%m%d") as time from exam_record where year(start_time) = 2021 ), table2 as( select * , # 窗口函数数据迁移 lead(time) over(partition by uid order by time) as next_time from table1 ), table3 as( select * ,(datedi...

with table1 as ( select uid , date_format(start_time,'%Y%m') as time, submit_time, dense_rank() over(partition by uid order by date_format(start_time,'%Y%m') desc) as time_rank from exam_record ), table2 as ( select uid , sum(if(time is not null , 1 , 0)) as exam_all_cnt...