-- 思路：-- 1.构造用户活跃表：跨天数据，算作两条活跃记录-- 在拿到用户活跃日期和最早活跃日期即可区分新旧用户-- 2. 按日期分组统计dau和新用户占比-- 新用户判断逻辑：if(dt=first_dt,1, 0) -- 思路： -- 1.构造用户活跃表：跨天数据，算作两条活跃记录 -- 在拿到用户活跃日期和最早活跃日期即可区分新旧用户 -- 2. 按日期分组统计dau和新用户占比 -- 新用户判断逻辑：if(dt=first_dt,1, 0) with t_active...

-- 思路：-- 1.构建用户活跃表-- 2.计算出每个用户的最早和最近登录时间-- 3.构建用户等级字段,并统计出整体用户数-- 4.分组计算比例，排序补充：聚合函数count()与窗口函数count() over()的区别？窗口函数和聚合函数在使用上的区别主要体现在作用域、用途、参数以及与GROUP BY的关系上。作用域：聚合函数操作整个结果集，计算并返回一个单一的值；而窗口函数在查询的每一行上执行计算，生成一个结果集，这个结果集中的每一行对应原始结果集中的一行。用途：聚合函数用于对数据进行汇总和统计，例如计算总和、平均值或计数等；而窗口函数常用于执...

-- 思路：-- 1.统计出每个用户的登录时间和最早登录时间-- 2.然后统计2021年11月每天新用户的次日留存率（保留2位小数）-- 本题次日留存用户定义：用户登录时间与最早登录时间的差值为1，则是次日留存用户，计算逻辑如下：-- if(datediff(uv_dt, min_in_dt)=1, 1,0) -- 思路： -- 1.统计出每个用户的登录时间和最早登录时间 -- 2.然后统计2021年11月每天新用户的次日留存率（保留2位小数） -- 本题次日留存用户定义：用户登录时间与最早登录时间的差值为1，则是次日留存用户，计算逻辑如下： ...

-- 思路：-- 1.对in_time与out_time编码：in观看人数+1，out观看人数-1,然后union all 结果为临时表temp-- 2.如果同一时刻有进入也有离开时，先记录用户数增加再记录减少-- 3.结果按最大人数降序 -- 思路： -- 1.对in_time与out_time编码：in观看人数+1，out观看人数-1,然后union all 结果为临时表temp -- 2.如果同一时刻有进入也有离开时，先记录用户数增加再记录减少 -- 3.结果按最大人数降序 with t as ( ...

-- 思路：-- 1.统计出2021年11月浏览文章的记录-- 2.按照日期分组统计：人均浏览文章时长（秒数），结果保留1位小数，并按时长由短到长排序 -- 思路： -- 1.统计出2021年11月浏览文章的记录 -- 2.按照日期分组统计：人均浏览文章时长（秒数），结果保留1位小数，并按时长由短到长排序 select date_format (in_time, '%Y-%m-%d') as dt, round( sum(timestampdiff (SECOND, in_time, out_time)) / count(...

-- 思路： -- 1.筛选出10月份的新用户 -- 2.按指标计算：所有新用户的首单平均交易金额（客单价）和平均获客成本并（保留一位小数) -- 思路： -- 1.筛选出10月份的新用户 -- 2.按指标计算：所有新用户的首单平均交易金额（客单价）和平均获客成本并（保留一位小数) -- 1.筛选出10月份的新用户 with new_user as( select uid,min(event_time) from tb_order_overall -- 10月的所有新用户：首次event_time在10月之内 group by uid having min(date(ev...

-- 思路：-- 1.统计出近90天零食类商品的购买信息[每个用户对每件商品的购买次数]-- 2.按照复购率公式计算每种商品的复购率，并按复购率倒序、商品ID升序排序，取top3即可. -- 思路： -- 1.统计出近90天零食类商品的购买信息[每个用户对每件商品的购买次数] -- 2.按照复购率公式计算每种商品的复购率，并按复购率倒序、商品ID升序排序，取top3即可. -- 1.统计出近90天零食类商品的购买信息[每个用户对每件商品的购买次数] with t as( select o.uid, d.product_id, c...

-- 思路：-- 1.先统计出2021年10月以来店铺901已付款的商品信息-- 2.分别统计商品的毛利率和店铺汇总的毛利率，然后再union all汇总-- 指标定义：-- 商品毛利率=(1-进价/平均单件售价)*100%-- 店铺毛利率=(1-总进价成本/总销售收入)*100% -- 思路： -- 1.先统计出2021年10月以来店铺901已付款的商品信息 -- 2.分别统计商品的毛利率和店铺汇总的毛利率，然后再union all汇总 -- 指标定义： -- 商品毛利率=(1-进价/平均单件售价)*100% -- ...

-- 思路：-- 1.先统计出每件商品的点击数/展示数/加购数/付款数/退款数-- 2.按照各指标计算公式加工指标，当分母为0时整体结果记为0，结果中各项指标保留3位小数-- 各指标计算公式：-- 商品点击率=点击数/展示数-- 加购率=加购数/点击数-- 成单率=付款数/加购数-- 退货率=退款数/付款数 -- 思路： -- 1.先统计出每件商品的点击数/展示数/加购数/付款数/退款数 -- 2.按照各指标计算公式加工指标，当分母为0时整体结果记为0，结果中各项指标保留3位小数 -- 各指标计算公式： -- ...

-- GMV:已经付款订单金额和未付款订单金额之和(不包含已退款订单) select date_format (event_time, '%Y-%m') as month, sum(total_amount) as GMV from tb_order_overall where status <> 2 -- 该场景下：year(event_time)，left(event_time, 4)，substring(event_time, 1, 4)效果等价 -- and year(event_time)= '2021' -- an...

-- 思路：-- 1.对event_time,start_time,finish_time编码：event_time等车人数+1，start_time等车人数-1，finish_time and start_time is null等车人数-1-- 同时关联tb_get_car_record表拿到用户所在城市city,然后union all结果为临时表temp-- 2.对表temp使用开窗函数sum() 按照city维度分组，dt升序，diff降序统计等车人数变化情况-- 3.按照city分组，取每个城市同时等待的最大人数即可 -- 思路： -- ...

-- 思路：-- 1.筛选出工作日的所有打车记录，对时间进行格式化处理取出对应时间-- 2.构建period时间范围字段，按照period分组统计平均等待时间和平均调度时间核心函数：TIMESTAMPDIFF()与 DAYOFWEEK() -- 思路： -- 1.筛选出工作日的所有打车记录，对时间进行格式化处理取出对应时间 -- 2.构建period时间范围字段，按照period分组统计平均等待时间和平均调度时间 with t as ( select time(date_format (event_time...

-- 思路：-- 1.筛选出'2021-09-25' and '2021-10-03' 之间的订单记录-- 2.按日期分组统计每日的订单完成量和订单取消量-- 3.然后使用窗口函数sum()统计每天的近7日日均订单完成量和日均订单取消量,核心用法：-- rows between 6 preceding and current row表示窗口大小为7，-- 范围从当前行往前6行，包括当前行-- 4.最后取国庆前三天记录按照dt排序即可 -- 思路： -- 1.筛选出'2021-09-25' and '2021...

-- 思路：-- 1.先按城市，司机分组统计每个司机的avg_grade,avg_order_num,avg_mileage-- 2.然后使用dense_rank函数按照城市分组，按平均评分降序排序-- 3.取排序第一的记录，按照日均接单数升序 -- 思路： -- 1.先按城市，司机分组统计每个司机的avg_grade,avg_order_num,avg_mileage -- 2.然后使用dense_rank函数按照城市分组，按平均评分降序排序 -- 3.取排序第一的记录，按照日均接单数升序 with t1 as ( ...