# 请统计各个城市在2021年10月期间，单日中最大的同时等车人数 # 筛选条件2021年10月 # 分组条件：city # 等车指从开始打车起，直到取消打车、取消等待或上车前的这段时间里用户的状态 # 等车时间段：event_time to finish_time/start_time # 计算每天的同时等车人数，再求每天最大的同时等车人数 # 再求2021年10月的单日中最大的同时等车人数，再按城市分组 # 如何计算每天同时等车的人数？ # 如果同一时刻有人停止等车，有人开始等车，等车人数记作先增加后减 # 开始等车：event_time 结束等车: case when mileage i...

# 统计周一到周五各时段的叫车量、平均等待接单时间和平均调度时间。 # 周一到周五 # WHERE DAYOFWEEK(event_time) BETWEEN 2 AND 6; # case when time(event_time) >= '07:00:00' and time(event_time) < '09:00:00' then '早高峰' # when time(event_time) >= '09:00:00' and time(event_time) < '17:00:00' then '工作时间' # when time(event_time) &gt...

# 国庆头3天里，每天的近7日日均订单完成量和日均订单取消量，按日期升序排序。结果保留2位小数 # 日均订单完成量和日均订单取消量 近7日 国庆头3天里 # 订单完成：mileage is not null # 订单取消：mileage is nulll # 先计算每天的订单完成量和每天的订单取消量 # 再用窗口函数计算每天的近七日日均订单完成量和日均订单取消量 select * from( select date(co.order_time) as dt , round(avg(count(co.mileage)) over (order by date(co.order_time) ran...

select round(avg(total_amount), 1) as avg_amount , round(avg(cost), 1) as avg_cost from( select oo.order_id , oo.uid , oo.total_amount , sum(od.price * od.cnt) - oo.total_amount as cost from tb_order_overall as oo inner join tb_order_detail as od on od.order_id = oo.order_id where (oo.uid, oo.order_...

select distinct t1.product_id , round(count(distinct case when rn >= 2 then t1.uid end) / count(distinct t1.uid), 3) as repurchase_rate from( select pi.product_id , oo.uid , row_number() over (partition by pi.product_id, oo.uid order by oo.event_time) as rn from tb_product_info as pi left join tb...

# 给每个用户分级 # 计算每类用户占比 # 忠实用户：[T-6, T] 至少活跃1次，且曾经也活跃过一次 # 新晋用户：[T-6, T] 至少活跃一次， 且曾经没有活跃过 # 沉睡用户：[T-6, T] 未活跃，但之前至少活跃一次 # 流失用户：[T-29, T] 未活跃，但曾经活跃过 # 只需要计算用户的最早活跃时间和最近活跃时间 select t3.user_grade , round(count(*) / (select count(distinct uid) from tb_user_log), 2) as ratio from( select t2.uid , ( case whe...

select lef.dt_in as dt , round(avg(lef.flag), 2) as uv_left_rate from( select dt1.uid # 所有首次活跃用户 , dt1.dt_in # 首次活跃日期 , (case when datediff(dt1.dt_out, dt1.dt_in) = 1 then 1 when datediff(dt2.dt_in, dt1.dt_in) = 1 then 1 else 0 end) as flag from( select * from( select # 查找每个用户首次活跃日期和首次离开日期 ug.uid , ...

select distinct * from( select day_cnt.tag , dt , sum(like_cnt) over (partition by tag order by dt range interval 6 day preceding) as sum_like_cnt_7d , max(retweet_cnt) over (partition by tag order by dt range interval 6 day preceding) as max_retweet_cnt_7d from( select vi.tag , date(uv.start_time) ...

# 涨粉率 = (加粉量 - 掉粉量) / 播放量。结果按创作者ID、总粉丝量升序排序。 # 2021 每月的涨粉， 截至当月的总粉丝量 # sum(case uv.if_follow when 1 then 1 when 2 then -1 end) over (partition by vi.author order by month(uv.start_time)) as fans_change # 以人为窗口，按照月份排序， # 构造字段，粉丝变化数 # 每个作者每月的涨粉， 截至当月的总粉丝数 # 每人每月 partition by vi.author order by date_fo...

# 涨粉率 = (加粉量 - 掉粉量) / 播放量。结果按创作者ID、总粉丝量升序排序。 # 2021 每月的涨粉， 截至当月的总粉丝量 # sum(case uv.if_follow when 1 then 1 when 2 then -1 end) over (partition by vi.author order by month(uv.start_time)) as fans_change # 以人为窗口，按照月份排序， # 构造字段，粉丝变化数 # 每个作者每月的涨粉， 截至当月的总粉丝数 # 每人每月 partition by vi.author order by date_fo...

# 找出有用户互动的最近一个月 # 先找具体日期 date(uv_log.start_time) # 每类视频的转发量和转发率（保留3位小数）。 select vi.tag , sum(uv_log.if_retweet) as retweet_cut , round(avg(uv_log.if_retweet), 3) as retweet_rate from tb_user_video_log as uv_log right join tb_video_info as vi on uv_log.video_id = vi.video_id where date(uv_log.start_t...

# 统计所有用户2021年的试卷完成次数和题目练习次数 # 只要2021年有试卷完成记录的用户 select t_re.uid , count(distinct case when year(comp_time) = 2021 and t_re.tag = "exam" then comp_time end, t_re.paper_id) as exam_cnt , count(distinct case when year(comp_time) = 2021 and t_re.tag = "question" then comp_time end, t...

select ui.uid , act_month.act_month_total , act_day.act_day_num as act_days_2021 , count(distinct case when year(er.start_time) = 2021 then date(er.start_time) else null end) as act_days_2021_exam , count(distinct case when year(pr.submit_time) = 2021 then date(pr.submit_time) else null end) as act_...

SELECT avg(if(datediff(t1.date, t1.date_lag) = 1, 1, 0)) AS avg_ret FROM( SELECT pd.device_id , pd.`date` , lag(pd.`date`) OVER (PARTITION BY pd.device_id ORDER BY pd.`date`) AS date_lag FROM question_practice_detail AS pd GROUP BY pd.device_id , pd.`date` ) as t1