# 求每个课的转换率 # 转换率 = 报名人数/浏览人数 SELECT course_id, course_name, ROUND(SUM(if_sign) / SUM(if_vw) * 100, 2) 'sign_rate(%)' FROM course_tb JOIN behavior_tb USING(course_id) GROUP BY course_id, course_name ORDER BY course_id 联结表，分组求值即可

# 求连续2天及以上购物的用户和对应天数 # 根据用户分组，按照日期升序排列，再移动日期一个单位新增一个字段，同位置相减，天数为1则是有1+1天是连续的，因为下一条记录出来的结果是null # 直接分组是不可能的，因为只要连续两天及以上，所以一旦有一天和下一天是连续的，那是n=1，就记为n+1=2天；扩展开来，就是有n天直接是连续的，那就是n+1天 # 临时表0，根据用户和日期升序排列，使用窗口函数lead以用户分页，将日期字段往上拉一个单位，使得同记录增加一个下一天日期的字段 WITH temp_0 AS( SELECT user_id, sales_date, LEAD(sal...

# 求每款的动销率、售罄率 # 动销率：有销售的SKU数量/在售的数量 # 售罄率：GMV/(总共价值) # 联结两张表，有销售的SKU数量也就是 sales_num 的求和，在售的数量也就是 inventory - 对应商品的售出数量 的求和 # GMV也就是 总商品交易额，是 sales_price 的求和，备货值是 tag_price * inventory- 对应商品的售出数量 的求和 # 错误理解 # 分别从两张表提取所需的字段 # 临时表0，联结两张表，因为是计算各个商品的细节，所以直接对标价、库存取最大值降维，对售出数和金额取总和，这样就知道一个商品的标价、初始库存、售出...

# 求单日最大同时等车人数，同一时间有增有减，先增后减 # 前面有一道题也是这样 # 令开始打车为1，取消打车或上车时为-1。同时筛选符合日期的记录 # 要注意的是开始打车是指event_time，而取消打车是finish_time存在而start_time不存在，上车是start_time存在。采用条件选择语句新增一个字段 # 再使用union all组合 # 分城市求，在窗口函数分页排序时要先把1排到前面，也就是根据这个新增的字段正序(想说的意思是先1后-1，应该是倒序，正序的话就是先减后增了） # #临时表0，查询城市、开始时间、结束时间 # 结束时间存在两种情况，一种是取消，一种...

# 求工作日各时段（四个时段）的叫车量、平均等待接单时间和平均调度时间 # 等待接单时间：从开始打车到司机接单 # 调度时间：从司机接单到上车 # 联立两张表，筛选出工作日的记录（使用DAYOFWEEK函数） # 再根据不同时段的定义分组计算：叫车量（有记录就算叫了一次车），等待接单时间（event_time和end_time的时间间隔），调度时间（order_time和start_time的间隔，这个只计算完成了的订单，要独立出来，也就是说要新增一个字段是完成了的订单数） # 临时表0，筛选出工作日的记录。其实可以和表1合并 WITH temp_0 AS( SELECT even...

# 感觉和上一道困难题如出一辙。这道明显要简单许多 # 求近7日日均订单完成量和日均订单取消量 # 限制是国庆前三天每天的 # 尝试使用滑动窗口函数。 # 先统计最远的，2021-9-25到10-3每天的订单完成和取消量 # 再滑动统计日均完成和取消量 # 临时表，对日期分组，计算每天的成功订单数、未完成订单数 WITH temp_0 AS( SELECT DATE(order_time) dt, COUNT(fare) over_cnt, SUM(IF(fare, 0, 1)) fail_cnt FROM tb_get_car_order # 不要忘了限定时间范围 ...

# 求每个城市中评分最高的司机的三个指标 # 评分是指平均分 # 按城市分组求司机的评分均值，取最大值对应的司机 # 针对这些司机求平均评分、日均接单量和日均行驶里程数 # 未完成的接单也算作接单数； # 只有接单成功的次数，才用于计算平均评分 # 临时表，用于查询每个城市的每个司机的聚合信息 WITH temp_0 AS( # 查询城市、司机id、接单成功的总订单数、平均评分、开窗函数求每个城市的最大平均评分、跑单的天数（要去重）、订单总数（不管接没接成）、里程总数 SELECT city, driver_id, COUNT(fare) order_grade, ROUND(S...

# 求每人全部已完成的有评分订单的平均分和总体平均分 # 限制：司机是2021年10月有过取消记录的 # 先找到时间段内有取消记录的司机 # 再作为子查询找这些人的所有完成的订单 # 分别求每人的平均分，和总体平均分 SELECT # 使用条件语句对汇总字段和普通字段的值进行分辨 CASE WHEN GROUPING(driver_id) = 1 THEN '总体' ELSE CAST(driver_id AS CHAR) END driver_id, ROUND(AVG(grade), 1) avg_grade FROM tb_get_car_order # 子查...

# 日期：2021.10.1-7；城市：北京；接单次数：>=3； # 求平均接单数和兼职收入 # 联结两个表，筛选出符合条件的记录，接单数应该是成功跑完单的 # 以司机id为依据分组求记录数，继续筛选出符合要求的司机id # 最后对接单数和收入求平均 # 临时表0 查询城市、司机id、收入、每个司机的跑单数（题意应该是有记录就算跑单，而不是必须完成接单，也就是fare字段有值才算） WITH temp_0 AS( SELECT city, driver_id, fare, COUNT(*) OVER(PARTITION BY driver_id) order_num ...

# 订单优惠金额：理论订单总金额 - 实际订单总金额 # 求2021.10的所有新用户的的首单平均金额和平均获客成本 # 新用户的首单，也就是每个用户第一次出现的记录的订单 # 先对订单总表筛选符合时间要求和新用户首单要求的记录，在和明细表联结 # 计算 实际订单总金额 和 理论订单总金额 # 求均值，计算优惠也求均值 # 临时表，查询订单id，用户id，订单实际总金额，商品id，单价，卖出数量 WITH temp_0 AS( SELECT order_id, uid, total_amount, product_id, price, cnt # 是由两个子查询得到了的，只需要...

# 求商品，限制是零食类商品复购率前三的 # 复购率：近90天购买它大于等于2次的人数；购买它的总人数 # 先联结三个表，查询商品id，uid；限制付款的订单，还有近90天，还有商品类型为零食 # 今天的定义是最大日期，在临时表0的where语句里面没有办法使用聚合函数，所以在下一个表里面筛选日期 # 查询必要的 商品id，uid，记录的日期， 聚合开窗函数求记录的最大日期作为一个新字段 WITH temp_0 AS( SELECT product_id, uid, DATE(event_time) event_date, MAX(DATE(event_time)) OVER() t...

# 求商品和店铺整体的毛利率 # 限制：时间大于等于2021.10；店铺id为901 # 商品毛利率：进价、平均单件售价 # 店铺毛利率：总进价、总销售收入 # 订单明细表联结其他两个表，筛选出符合时间和店铺、付款状态要求的记录，作为一个临时表 # 根据 店铺ID 分组聚合出店铺的总进价和总销售收入，得到毛利率，作为一个临时表 # ps: 这个总进价是 进价*卖出数量，总销售收入是 售出价格*卖出收入 （得通过示例说明发现） # 在第一个临时表的基础上 商品ID 分组，分别计算各个商品的总进价和总收入，得到毛利率（方法和店铺一样） # UNION 组合两个查询，组合的时候为了按商品id排序...

# 求商品各项指标，具体包括点击率、加购率、成单率、退货率 # 要求在2021.10月退货率小于等于0.5 # 先计算时间范围内的各项指标，再筛选出退货率满足要求的 SELECT product_id, ROUND(SUM(if_click)/COUNT(*), 3), # 点击率 ROUND(SUM(if_cart)/SUM(if_click), 3), # 加购率 ROUND(SUM(if_payment)/SUM(if_cart), 3), # 成单率 ROUND(SUM(if_refund)/SUM(if_payme...

# 连续签到，根据注的内容，应该只记进入时间，忽略离开时间， # lead开窗函数，用户分页，新增一列是对应日期的下一个记录的日期 # 两列相减，如果是1，那就算连续；不是1，那就是不连续 # 从第一个1开始到第三个1，就是2， 到第七个就是6 # 日期：7.1 到 10.31 # 错了，是从7.7 # 原来直接date就能提取出日期 #以上是本来的想法 WITH temp_0 AS( SELECT DISTINCT uid, DATE(in_time) date_now, DENSE_RANK() OVER(PARTITION BY uid...