# 二刷：通用方法 22:25 ~ 22:48 23min with tb0 as ( select distinct uid,date(in_time) as in_date from tb_user_log union select distinct uid,date(out_time) as in_date from tb_user_log ), # 1.自连接 tb1 as( select a.uid,a.in_date as time1,b.in_date as time2, # 标记新用户 if(min(a.in_date)o...

# 作答时间：20240417 14:52 ~ # 字段： course_id、course_name、max_num # 问题类型：最大在线人数 # 方法：①union all in_time和out_time;②sum(label)over()按照课程分组、time(升)和label(降)排序;③max筛选最大在线人数 # tb1：union all with tb1 as ( select user_id,course_id,course_name,in_datetime as inout_time,1 as label from attend_tb left joi...

# 4/17 10:43 ~ # 字段：course_id、course_name、attend_rate(%) # 出勤率 = 出勤人数(在线时长10分钟以上) / 报名人数 # tb1：出勤人数, 按照user_id、course_id分组，统计出勤时间 with tb1 as ( select user_id,course_id,course_name,sum(timestampdiff(second,in_datetime,out_datetime)) as on_time,if_sign from attend_tb left join course_tb usi...

# 字段：course_name、avg_Len # tb1：计算时间差 select course_name, round(avg(timestampdiff(second,in_datetime,out_datetime)/60),2) as avg_Len from attend_tb left join course_tb using(course_id) group by course_name order by round(avg(timestampdiff(second,in_datetime,out_datetime)/60),2) desc

# 时间：4/16 11:00 ~ # 明确问题：统计的是19:00 时刻的在线人数！ # 方法：maybe sum()over()进行累计统计 (no) 只需要筛选保留符合条件时间段的数据 # 不涉及 # 字段：course_id、course_name、online_num # tb1：筛选时间段包含了19:00的数据\ with tb1 as( select distinct user_id , course_id ,course_name,course_datetime from attend_tb left join course_tb using(cour...

# 时间：4/16 11:00 ~ # 明确问题：统计的是19:00 时刻的在线人数！ # 方法：maybe sum()over()进行累计统计 (no) 只需要筛选保留符合条件时间段的数据 # 字段：course_id、course_name、online_num # tb1：筛选时间段包含了19:00的数据\ with tb1 as( select distinct user_id , course_id ,course_name,course_datetime from attend_tb left join course_tb using(course_id)...

# 第二次练习 4/3 9：47~ # 字段：dt 、 uv_left_rate # 统计每天新用户的次日留存率 # 注意：跨天的也算为活跃，代表out_time也作为活跃记录日 # tb0：将in_time 和 out_time 组合 with tb0 as ( select distinct uid,date(in_time) as in_time from tb_user_log union select distinct uid,date(out_time) as in_time from tb_user_log ), # 注意2：合并i...

# 3.22 9:28~ 9:44 16min # 字段：author_id，author_level，days_cnt # 核心：连续问题的核心是 排序编号 和 日期 之差为定值则实为连续状态 # tb1：标记连续编号 with tb1 as( select answer_date,author_id,author_level, dense_rank()over(partition by author_id order by answer_date) as rk from answer_tb left join author_tb using(author_i...

# 3.22 9:28~ 9:44 16min # 字段：author_id，author_level，days_cnt # 核心：连续问题的核心是 排序编号 和 日期 之差为定值则实为连续状态 # tb1：标记连续编号 with tb1 as( select answer_date,author_id,author_level, dense_rank()over(partition by author_id order by answer_date) as rk from answer_tb left join author_tb using(author_i...

# 3.22 9:17 ~ 25 8min # 字段：num # tb1：回答过教育类 且 回答过职场类 的用户数量 with tb1 as ( select distinct author_id,issue_type from answer_tb left join issue_tb using(issue_id) where issue_type = 'Education' or issue_type = 'Career' order by author_id ), # 结果查询 tb2 as( select author_id,coun...

# 3.22 9:11 ~ 9:14 3min # 字段：answer_date，author_id，answer_cnt # 时间：21年11月 每日 # tb1： select answer_date,author_id,count(issue_id) as answer_cnt from answer_tb group by answer_date,author_id having count(issue_id) >= 3 order by answer_date,author_id

# 3/22 8:56 ~ 9:08 12min # 字段：level_cut，num # 暂且认为高质量回答可以重复 # # tb1：标记等级区间 with tb1 as( select author_id, case when author_level between 5 and 6 then '5-6级' when author_level between 3 and 4 then '3-4级' when author_level between 1 and 2 then '1-2级' els...

# 3/21 11：06 ~ # 字段：style_id、pin_rate、sell-through_rate # pin_rate动销率 = 有销售的SKU数量 / 在售SKU数量 # 其中在售sku数量 = 库存 - 已经出售的sku数量， 需要注意的一点是sku数量，不是sku种类噢！~ # sell-through_rate售罄率 = GMV / 备货值# 备货值 = 吊牌价*库存数 # tb1：计算在售sku数量（库存量-出售的sku数量） 【先求出售的sku数量，再左连接库存量】 with tb1 as( select style_id, sum(sales_num)...

# 3/21 11：06 ~ # 字段：style_id、pin_rate、sell-through_rate # pin_rate动销率 = 有销售的SKU数量 / 在售SKU数量 # 其中在售sku数量 = 库存 - 已经出售的sku数量， 需要注意的一点是sku数量，不是sku种类噢！~ # sell-through_rate售罄率 = GMV / 备货值# 备货值 = 吊牌价*库存数 # tb1：计算在售sku数量（库存量-出售的sku数量） 【先求出售的sku数量，再左连接库存量】 with tb1 as( select style_id, sum(sales_num)...