Hoveii - 个人主页动态

2022-06-13 23:36

题目分析  限定：条件最近一个月发布 维度：视频 指标：热度最高的 top3 统计口径：热度 = （a * 视频完播率 + b * 点赞数 + c * 评论数 + d * 转发数） * 新鲜度 视频完播率 = 播放时长 / 视频时长 【用户维度】 新鲜度 = 1 / (最近无播放天数 + 1) 【视频维度】 最近无播放天数 = 整体最近播放【用户维度】 - 视频最近一次播放的日期 【视频维度】   返回：video_id, hot_index  题解思路  一张用户播放记录，一张视频信息表，显然统计的内容是要依赖用户播放记录信息，因此比较适合的方式是将tb_user_video_log作为主表...

0 点赞评论收藏

2022-06-05 20:51

西安财经大学数据分析师

题解 | #【模板】队列#

两个栈来实现队列 核心思路是：栈（先进后出）弹出并压入另一个栈再弹出，完成先进先出  初始化两个栈，一个为输入栈s1，一个为输出栈s2，均使用list()实现 执行push，直接s1.append(x) 执行pop时： 先检查输出栈s2是否为空，不为空直接pop 再检查输入栈s1是否为空，为空则报错error s1不为空则逐项pop到s2中，最后对s2执行pop   执行front时，和pop类似 先检查输出栈s2是否为空，不为空直接返回栈顶s2[-1] 再检查输入栈s1是否为空，为空则报错error s1不为空则直接访问首个元素，即s1[0]    class Quene(object): ...

0 点赞评论收藏

2022-06-05 13:38

西安财经大学数据分析师

题解 | #【模板】栈#

注意输入是一行一行input  class stack(object): def __init__(self): self.stack = list() self.p = 0 def push(self, num): self.stack.append(num) self.p += 1 def top(self): if self.p > 0: print(self.stack[-1]) return print('error') def pop(self): if self.p > 0: self.p -= 1 res = self.stack.pop() pri...

0 点赞评论收藏

2022-05-25 22:06

西安财经大学数据分析师

题解 | #筛选昵称规则和试卷规则的作答记录#

正则表达式写法（感觉写复杂了...）select t1.uid, t1.exam_id, round(avg(t1.score), 0) as avg_score from exam_record as t1 left join examination_info as t2 on t1.exam_id = t2.exam_id left join user_info as t3 on t1.uid = t3.uid where t1.score is not null and (t3.nick_name rlike '^牛客[0-9]{1,}号$' or t3.nick_nam...

0 点赞评论收藏

2022-05-24 23:32

西安财经大学数据分析师

题解 | #筛选限定昵称成就值活跃日期的用户#

rlike（正则表达式）的写法：^牛客.*号$ like的写法：牛客%号  select t1.uid, t2.nick_name, t2.achievement from ( select uid from ( select uid, date_format(start_time, '%Y%m') as dt from exam_record union all select uid, date_format(submit_time, '%Y%m') as dt from practice_record ) as t group by uid having m...

0 点赞评论收藏

2022-05-01 11:24

西安财经大学数据分析师

题解 | #每类试卷得分前3名#

比较顺序  最大分数前三，按最大分数降序 若最大分数相同，继续按对应最小分数降序 若最小分数仍相同，按uid降序（uid不存在相同）  思路  先找到每个用户在每类试卷的最大得分与最小得分 对每类试卷进行分组，按最大分数降序、最小分数降序、uid降序得到唯一排序数 筛选出排序数 <= 3的记录  代码（写法一）  -- 每类试卷得分的前3名 select tag as tid, uid, ranking from ( select uid, tag, row_number() over (partition by tag order by max_s desc, min_s desc, ...

0 点赞评论收藏

2022-05-01 01:35

西安财经大学数据分析师

题解 | #每个6/7级用户活跃情况#

问题梳理   主问题：每个6/7级用户活跃情况   1. 筛选条件：6/7级用户，即：user_info.level >= 6   2. 指标：       （1）总活跃月份数：做过试卷或做过题目的月份数（无年份限定）       （2）2021年活跃天数：2021年（限定年份），做过试卷或做过题目的天数       （3）2021年试卷作答活跃天数：2021年（限定年份），做过试卷的天数       （4）2021年答题活跃天数：2021年（限定年份），做过题目的天数   3. 返回：u...

0 点赞评论收藏

2022-03-10 01:33

已编辑

西安财经大学数据分析师

题解 | #店铺901国庆期间的7日动销率和滞销率#

注意点  时间限定：国庆节头3天 计算指标：近7日动销率（1-滞销率）  因此，实际关注的时间范围是：国庆前6天+国庆头3天   核心字段：product_id, order_id, event_time, shop_id, shop_id 难点：近七日在售商品识别，已上架商品识别 坑点：当天任一店铺有任何商品的销量就输出该天的结果，意味着先有所有商店结果再筛选901店铺  思路  合并数据，记录国庆前7天到国庆头3天时段中每件商品的店铺名、上架时间、下单时间  这里以商品信息表为主表，采用左连接另外两张表，保证上架商品数计算不漏   合并后的信息表记为tb，自联结生成字段dt存储"2021-...

0 点赞评论收藏

2022-03-08 00:06

西安财经大学数据分析师

题解 | #10月的新户客单价和获客成本#

注意点  时间限定：2021年10月 对象限定：新用户，首单 求值目标：月客单价为总支付金额除以用户数，月获客成本为优惠金额除以用户数 识别新用户，首次购买日期在2021年10月（先找再筛） 识别首单，每个用户的最小订单号（这默认订单号按下单先后顺序生成）  思路  对订单总表查询每个用户的最早下单日期和最早订单id 上述表内连接订单明细表，筛选日期、新用户、首单  注意：这里返回的是每个新用户的首单产品销售单价、下订数量及支付总价   对上述连接表按用户汇总销售总价（售单价*下订数）、订单支付总价 最后对上述汇总表计算客单价和获客成本  SQL代码 select round(sum(tota...

0 点赞评论收藏

2022-02-28 00:00

西安财经大学数据分析师

题解 | #零食类商品中复购率top3高的商品#

思路：   从商品信息表tpi中筛选出tag="零食"的记录   tpi连接tod(on=product_id)，再连接too(on=order_id)，均进行内连接   使用窗口函数从too中计算出最近日期，注意日期格式转换   计算每个产品每个用户在近90天内的购买次数   对每个产品分组计算购买次数大于1的用户数与总用户数，从而计算出复购率   复购率保留3位，按复购率倒序、按product_id升序，保留前三（注意SQL执行顺序）   代码：  select product_id, round(sum(if(purchase_cnt > 1, 1, 0)) / count(uid...

0 点赞评论收藏

2022-02-13 02:39

西安财经大学数据分析师

题解 | #连续签到领金币#

难点：连续n次的筛选判定  解决思路：可以参考计算连续出现3次的所有数字。 构建自增序列，与原序列相减得到序列差。 序列差相同的意味着序列连续，序列差不同则意味着序列不是连续    select uid, month, sum(coin) as coin from ( select uid, month, min_date_diff - date_id as id_diff, count(*) div 7 * 15 + if(count(*) mod 7 > 2, count(*) mod 7 + 2, count(*) mod 7) as coin from ( select uid,...

0 点赞评论收藏

2022-02-08 01:43

西安财经大学数据分析师

题解 | #统计活跃间隔对用户分级结果#

主要思路：  参考前面几道题的思路，先建立用户活跃表（临时表，使用with...as (...)语句），记录每个用户的活跃时间。 分别计算出每个用户的最早登陆时间、最近登陆时间以及总数据中的最近登陆时间。 等级划分顺序，使用case when： (1) 先筛选出流失用户（与最近登陆时间间隔大于29天）； (2) 再继续筛选出沉睡用户（与最近登陆时间间隔大于6天）；(3) 进而筛选出新晋用户（与最早登陆间隔不小于7天）； (4) 最后筛选出忠实用户。 分组汇总计算出各用户等级的数量以及总用户数量，再计算出比例，保留小数，排序。    with active_user_tb as ( select...

0 点赞评论收藏

2022-02-08 00:20

已编辑

西安财经大学数据分析师

题解 | #2021年11月每天新用户的次日留存率#

注意点：  判断用户是否为新用户（首次登陆日期，注意口径） 判断是否为次日留存（是否与首次登陆日期相差1天） 特殊情况：新用户退出时间跨天属于次日留存   主要步骤：  按用户id、进入时间、退出时间进行去重； 查询出每个用户的首次登陆时间与下次登陆时间，这里注意首次是要统计11月以前，即有些用户在11月1日的时候是老用户，因此这里不能直接去掉11月以前的数据； 筛选出需要的数据（限定年月、当天为新用户），判断新用户是否为次日留存。当首次登陆日期与次日登陆日期相差1天时，或首次登陆日期与退出观看日期相差1天时均记为次日留存； 分别统计新用户次日留存数与新用户数，按日期分组汇总出次日留存率，保留...

0 点赞评论收藏

2022-02-03 02:46

西安财经大学数据分析师

题解 | #每篇文章同一时刻最大在看人数#

难点：瞬时计算 思路：  找出所有用户观看的时刻（包含进入与离开，注意去重）作为时间轴 计算每个时间点在观看的人数，即进入时刻不大于该时刻且离开时刻不小于该时刻 对时间点和文章id分组汇总，计算出每个时刻点每篇文章的观看人数 进一步对文章id分组汇总，计算出每篇文章在所有时刻中最大的观看人数    with tmp as ((select in_time as dt from tb_user_log group by in_time order by in_time ) union (select out_time as dt from tb_user_log group by out_tim...

0 点赞评论收藏

2022-01-19 16:25

西安财经大学数据分析师

题解 | #近一个月发布的视频中热度最高的top3视频#

视频热度计算  简要思路：  合并表，使用inner join即可。目标是提取需要的字段，并加工出部分需求字段，如：if_finish表示是否完播，if_comment表示是否评论，recent_play表示最近一天（用于筛选近一个月的数据） 筛选出近一个月，并按video_id分组汇总，计算子指标。如：播放量、点赞量、转发量、评论数、近期未观看天数 计算最终指标hot_index并按其大小倒序选出top3   难点：  对最近一个月的理解，这里修饰的是发布，而非热度的，因此筛选出最近一个月的数据即可，同时这里的最近日期来自于所有数据的max(end_time) 对最近无播放天数的理解，这里指...

0 点赞评论收藏

创作者周榜

关注他的用户也关注了：