select round(count(d2)/count(d1),4) as avg_ret from( select distinct q1.device_id as d1, q1.date, q2.device_id as d2 from question_practice_detail as q1 left join question_practice_detail as q2 on q1.device_id = q2.device_id and datediff(q2.date,q1.date)=1 ) t 计算次日留存率：当日登陆数据左连接次日登陆数据。存在用户一天多日登陆的情况...

with temp1 as( # 1. 2021年10月商城里所有新用户的首单实际付款 select order_id, uid, total_amount from( select order_id, uid, event_time, min(event_time) over(partition by uid) as first_time, total_amount from tb_order_overall where status = 1 )t where event_time = first_time and year(first_time)=20...

# 零食类商品订单明细,订单明细表tb_order_detail为底表 with temp as ( select uid, order_id, t.product_id, dt from ( select uid, tb_order_detail.order_id, product_id, date_format(event_time,'%Y-%m-%d') as dt from tb_order_detail left join tb_order_overall on tb_order_overall...

# 店铺901在2021年10月以来的所有明细 with temp as ( select t.order_id, product_id, price, cnt, in_price from( select order_id, tb_order_detail.product_id, price, cnt, in_price from tb_order_detail left join tb_product_info on tb_product_info.product_id = tb_order_detail.produ...

select t.product_id, round(sum(if_click)/count(if_click),3) as ctr, case when sum(if_click)=0 then 0 else round(sum(if_cart)/sum(if_click),3) end as cart_rate, case when sum(if_cart)=0 then 0 else round(sum(if_payment)/sum(if_cart),3) end as payment_rate, case when sum(if_payment)=0 then 0 else roun...

# 对同一个uid的连续日期进行切片，然后对每一个切片内进行排序并对7取余，按照余数分配金币 # 3. 统计连续签到领取金币 select uid, dt as month, sum(coinper) as coin from( # 2. 计算每个用户每天的金币 # 2.2 对同一个uid的连续日期进行切片:对同一个用户，相同group_id的数据按dt排序并对7取余 select uid, dt, case row_number() over(partition by uid,group_id order by dt)%7 when 3 then 3 ...

# 4. 计算 select dt, count(distinct uid1), round(count(distinct uid2)/count(distinct uid1),2) as uv_new_ratio from( # 3. join select t1.uid as uid1, t2.uid as uid2, dt, first_time from ( # 2. 用户在线表 (select uid, date_format(in_time,"%Y-%m-%d") as dt from tb_user_log) union (se...

with t1 as( # 1. 新晋用户(近7天新增)，用户注册表 select uid, min(date_format(in_time,"%Y-%m-%d")) as login from tb_user_log group by uid having datediff((select max(date_format(out_time,"%Y-%m-%d")) from tb_user_log),login) < 7 ), t2 as( # 2. 忠实用户(近7天活跃过且非新晋用户)，用户在线表，剔除t1 select * from( (se...

# 4. 计算2021年11月每天新用户的次日留存率 select first as dt, round(count(uid2)/count(uid1),2) as uv_left_rate from( # 3. join，选择用户在注册第二天在线的记录 select t1.uid as uid1, t2.uid as uid2, first from( # 1. 底表：用户注册表 select uid, min(date_format(in_time,"%Y-%m-%d")) as first from tb_user_log group by u...

select artical_id, max(num) as max_uv from ( select artical_id, dt, sum(num) over(partition by artical_id order by dt, num desc) as num from( ( select artical_id, in_time as dt, 1 as num from tb_user_log where artical_id != 0) union all ( select artical_id, out_time as dt, -1 as num fr...

# 注意这里不能用avg(viiew_len_sec)，因为可能一个人在每天有多次浏览 # 因此，人均浏览时长 = 总浏览时长/去重用户数 select dt, round(sum(viiew_len_sec)/count(distinct uid),1) as avg_viiew_len_sec from ( select uid, date_format(in_time,"%Y-%m-%d") as dt, timestampdiff(second,in_time,out_time) as viiew_len_sec from tb_user_log where art...

# 2. 求和 group by video_id select video_id, round((100*sum(complete)/count(complete) + 5*sum(if_like) + 3*sum(comment_id)+ 2*sum(if_retweet))*(1/(datediff((select max(date_format(end_time,"%Y-%m-%d")) from tb_user_video_log), max(dt))+1)),0) as hot_index from( # 1. join，计算每个视频每天的完播数，点赞数，评论数...

# 3. 窗口函数后，如果需要设置日期condition，必须进行全选后再加上日期和排序条件，否则会对窗口函数的结果有影响。 select * from ( # 2. 计算近一周总点赞量和一周内最大单天转发量 select tag, dt, sum(if_like) over(partition by tag order by dt rows 6 preceding) as sum_like_cnt_7d, max(if_retweet) over(partition by tag order by dt rows 6 preceding) as max_retweet_cnt_7d fro...

with t as ( select author, tb_video_info.video_id, date_format(start_time,"%Y-%m") as month, case when if_follow = 2 then -1 else if_follow end as if_follow from tb_video_info left join tb_user_video_log on tb_user_video_log.video_id = tb_video_info.video_id where year(start_time)=2021 ) ...