各城市最大同时等车人数 明确题意： 统计各个城市在2021年10月期间，单天里最大的同时等车人数。结果按最大等车人数升序排序，相同时按城市升序排序。 等车指从开始打车起，直到取消打车或取消等待或上车止的这段时间里用户的状态。如果同一时刻有人停止等车，有人开始等车，等车人数记作先增加后减少。 问题分解： 计算截止当前时刻最大在等车人数（生成子表t_city_cur_max）： 计算每个时刻人数变化量（生成子表t_uv_at_time）： 记录等车人数增加（有人开始打车）：event_time as at_time, 1 as wait_uv 记录等车人数减少（上车了、打到车取消、没打到车...

工作日各时段叫车量&等待接单时间&调度时间 明确题意： 统计周一到周五各时段的叫车量、平均等待接单时间和平均调度时间。 全部以开始打车时间为时段划分依据，平均等待接单时间和平均调度时间均保留1位小数，平均调度时间仅计算完成了的订单，结果按叫车量升序排序。 问题分解： 计算每次叫车的等待时间和调度时间（生成子表t_wait_dispatch_time）： 关联打车记录和订单表：tb_get_car_record JOIN tb_get_car_order USING(order_id) 筛选工作日的记录（周一到周五）：WHERE DAYOFWEEK(event_time) ...

国庆期间近7日日均取消订单量 明确题意： 统计国庆头3天里，每天的近7日日均订单完成量和日均订单取消量，按日期升序排序。 问题分解： 计算每天的近7日订单的日均完成量和日均取消量： 计算每天的订单完成量和取消量（生成子表t_finish_cancel_daily）： 统计每个订单是否完成（生成子表t_order_status）： 筛选必要的时间窗（题目只要国庆头3天的近7日）：WHERE DATE(order_time) BETWEEN '2021-09-25' and '2021-10-03' 生成日期：DATE(order_time) as dt 生成完成状态：IF(start_...

每个城市中评分最高的司机日均接单量和里程数 明确题意： 统计每个城市中评分最高的司机平均评分、日均接单量和日均行驶里程数。有多个司机评分并列最高时，都输出。 平均评分和日均接单量保留1位小数，日均行驶里程数保留3位小数，按日均接单数升序排序。 问题分解： 计算每个司机的评分、日均接单量、日均里程和城市内评分排名（生成子表t_driver_rk）： 计算每个司机的评分等信息（生成子表t_driver_info）： 关联打车记录表和订单表：tb_get_car_record JOIN tb_get_car_order USING(order_id) 按司机和城市分组：GROUP BY dr...

有取消订单记录的司机平均评分 明确题意： 找到2021年10月有过取消订单记录的司机，计算他们每人全部已完成的有评分订单的平均评分及总体平均评分，保留1位小数。 问题分解： 找出2021年10月有取消订单的司机：WHERE DATE_FORMAT(order_time, "%Y-%m")='2021-10' AND ISNULL(fare) 筛选他们的已完成订单的评分：WHERE driver_id in (...) AND NOT ISNULL(grade) 按司机分组：GROUP BY driver_id 追加汇总信息：WITH ROLLUP 输出每个司机的平均评分： 司机ID或总体...

2021年国庆在北京接单3次及以上的司机统计信息 明确题意： 统计2021年国庆7天期间在北京市接单至少3次的司机的平均接单数和平均兼职收入（暂不考虑平台佣金，直接计算完成的订单费用总额），结果保留3位小数。 问题分解： 计算2021国庆在北京接单至少3次的司机信息： 关联接单表和打车记录表：tb_get_car_order JOIN tb_get_car_record USING(order_id) 筛选北京国庆期间的记录：WHERE city = "北京" and DATE_FORMAT(order_time,"%Y%m%d") BETWEEN '20211001' AND '202...

店铺901国庆期间的7日动销率和滞销率 明确题意： 计算店铺901在2021年国庆头3天的7日动销率和滞销率。结果保留3位小数，按日期升序排序。只要当天有任何商品的销量就输出该天的结果，即使店铺901当天的动销率为0。 动销率定义为店铺中一段时间内有销量的商品占当前已上架总商品数的比例，同理滞销率就是没有销量的商品比例。 问题分解： 计算每天的动销率（有销量产品的比例）： 计算国庆期间店铺901截止每天的近7天有销量的商品数（生成子表t_dt_901_pid_cnt）： 统计每个有销量的日期（生成子表t_dates）： 筛选目标时间窗：WHERE DATE(event_time) B...

10月的新户客单价和获客成本 明确题意： 计算2021年10月商城里所有新用户的首单平均交易金额（客单价）和平均获客成本（保留一位小数）。 某个订单的优惠金额可通过订单明细里的 {该订单各商品单价×数量之和-订单总金额} （订单总金额在订单总表中）得到。 问题分解： 统计用户首单信息（生成子表t_first_order）： 定义按用户分区按行为时间排序的窗口：WINDOW wd_uid_first as (partition by uid order by event_time) 获取窗口内第一个行为时间：FIRST_VALUE(event_time) over(wd_uid_first...

零食类商品中复购率top3高的商品 明确题意： 统计零食类商品中复购率top3高的商品。复购率指用户在一段时间内对某商品的重复购买比例，复购率越大，则反映出消费者对品牌的忠诚度就越高，也叫回头率。 此处我们定义：某商品复购率 = 近90天内购买它至少两次的人数 ÷ 购买它的总人数。近90天指包含最大日期（记为当天）在内的近90天。 结果中复购率保留3位小数，并按复购率倒序、商品ID升序排序。 问题分解： 计算每个用户对每个商品是否复购（生成子表t_uid_product_info）： 内连接多表：tb_order_detail JOIN tb_order_overall USING(or...

某店铺的各商品毛利率及店铺整体毛利率 明确题意： 计算2021年10月以来店铺901中商品毛利率大于24.9%的商品信息及店铺整体毛利率。 此处毛利率定义如下：商品毛利率=(1-进价/平均单件售价)*100%；店铺毛利率=(1-总进价成本/总销售收入)*100%。 结果先输出店铺毛利率，再按商品ID升序输出各商品毛利率，均保留1位小数。 问题分解： 统计每个被售出的商品的售价进价（生成子表t_product_in_each_order）： 订单明细表内连接商品信息表：tb_order_detail JOIN tb_product_info USING(product_id) 继续内连接订...

统计2021年10月每个退货率不大于0.5的商品的各项指标 明确题意： 统计2021年10月每个有展示记录的退货率不大于0.5商品的各项指标： 商品点展比=点击数÷展示数；加购率=加购数÷点击数；成单率=付款数÷加购数；退货率=退款数÷付款数，当分母为0时整体结果记为0。 结果中各项指标保留3位小数，并按商品ID升序排序。 问题分解： 计算各个维度的计数（生成子表t_product_index_cnt） 筛选时间窗内的记录：where DATE_FORMAT(event_time, '%Y%m') = '202110' 按商品ID分组：group by product_id 统计各种计数...

计算商城中2021年每月的GMV 明确题意： 统计GMV大于10w的每月GMV 问题分解： 筛选满足条件的记录： 退款的金额不算（付款的记录还在，已算过一次）：where status != 2 2021年的记录：and YEAR(event_time) = 2021 按月份分组：group by DATE_FORMAT(event_time, "%Y-%m") 计算GMV：(sum(total_amount) as GMV 保留整数：ROUND(x, 0) 筛选GMV大于10w的分组：having GMV > 100000 细节问题： 表头重命名：as 按GMV排序：o...

连续签到领金币 明确题意： 计算每个用户2021年7月以来每月获得的金币数（该活动到10月底结束，11月1日开始的签到不再获得金币），结果按月份、ID升序排序。 问题分解： 计算每个人连续签到的天次（生成子表t_sign_idx）： 计算每次签到的序号和连续签到起始日期（生成子表t_sign_base）： 计算每次签到的序号（生成子表t_sign_date_rn）： 过滤出活动期内的有效签到记录： 有效签到：artical_id = 0 and sign_in = 1 活动期：DATE(in_time) >= "2021-07-07" and DATE(in_time) &l...

每天的日活数及新用户占比 明确题意： 统计每天的日活数及新用户占比，新用户占比=当天的新用户数÷当天活跃用户数（日活数）。 如果in_time和out_time跨天了，在两天里都记为该用户活跃过。 新用户占比保留2位小数，结果按日期升序排序 问题分解： 计算每天的日活数（生成子表t_dau）： 计算每条记录活跃信息（生成子表t_active_dt）： 分别将记录中的进入和离开时间作为该用户的活跃日期 进入日期：DATE(in_time) as dt 离开日期：DATE(out_time) as dt 合并结果：UNION 按日期分组：GROUP BY dt 统计用户数：COUNT(...