1c1 - 个人主页动态 - 牛客网

发布(34) 评论刷题收藏

2024-10-14 23:22

数据分析师

SQL170 某店铺的各商品毛利率及店铺整体毛利率

SELECT product_id, CONCAT(ROUND(profit_rate*100,1),'%') profit_rate FROM( SELECT tb_product_info.product_id, (1-SUM(in_price)/SUM(price)) profit_rate FROM tb_order_overall LEFT JOIN tb_order_detail ON tb_order_overall.order_id=tb_order_detail.order_id LEFT JOIN tb_product_info ON tb_order_detail.pro...

0 点赞评论收藏

分享

2024-03-24 12:33

数据分析师

题解 | #未完成率较高的50%用户近三个月答卷情况#

#需求：统计SQL试卷上未完成率较高的50%用户中，6级和7级用户在有试卷作答记录的近三个月中，每个月的答卷数目和完成数目 #输出：uid、start_month(月份)、total_cnt(作答数)、complete_cnt(完成数) #要求：1.对用户的筛选：按未完成率高到低排序的前50%中，level in (6,7) 的用户 # 2.时间范围：有作答记录的近三个月 # 3.指标输出：按月划分，按uid，month升序 with w1 as( select uid from( select uid,percent_rank()over(order by count(submit_time...

0 点赞评论收藏

分享

2024-03-23 19:24

数据分析师

SQL184 | 店铺连续2天及以上购物的用户及其对应的天数

#需求：统计连续2天及以上在该店铺购物的用户及其对应的次数 #输出：用户编号(user_id)、次数(days_count) #要求：输出按user_id升序；用户范围为有连续两天以上购物记录(>1) select user_id,count(*) days_count from( select distinct sales_date,user_id, dense_rank()over(partition by user_id order by sales_date) ck1 #需求只按天考虑用户行为，这里对日期进行去重 #建立窗口函数排序日期，得到日期的排序序号，用于后续date_su...

0 点赞评论收藏

分享

2024-03-23 18:53

数据分析师

SQL184 | 店铺连续2天及以上购物的用户及其对应的天数

#需求：统计连续2天及以上在该店铺购物的用户及其对应的次数 #输出：用户编号(user_id)、次数(days_count) #要求：输出按user_id升序；用户范围为有连续两天以上购物记录(>1) select user_id,count(*) days_count from(select distinct sales_date,user_id from sales_tb) w1 group by 1 having count(*)>1 order by 1 #总结：可以这么写是因为给的数据源以及提的需求非常简单，涉及的指标很少，实际应用会复杂很多

0 点赞评论收藏

分享

2024-03-24 09:39

已编辑

数据分析师

开窗SQL139|#近三个月未完成试卷数为0的用户完成情况#

#需求：找到每个人近三个有试卷作答记录的月份中,没有试卷是未完成状态的用户,的试卷作答完成数 #输出：uid、exam_complete_cnt(试卷完成作答数) #拆分：对uid的筛选：有作答记录的近三个月中，没有试卷存在未完成记录。以dense_rank开窗，对作答时间的月份排名，筛选取排名<=3的 #各人的试卷作答完成数：count(submit_time) #输出按exam_complete_cnt、uid降序：order by select uid,count(submit_time) from( select uid,submit_time, dense_rank()over...

0 点赞评论收藏

分享

2024-03-17 22:20

已编辑

数据分析师

开窗SQL138 | #连续两次作答试卷的最大时间窗#

#需求：计算在2021年至少有两天作答过试卷的人中，连续两次作答试卷的最大时间窗days_window，根据该年的历史规律他在days_window天里平均会做多少套试卷 #输出：uid、days_window(最长的一次时间间隔)、avg_exam_cnt(平均每天答卷数) #要求：人员选区为2021年中至少有两天作答过的人；days_window要2021年中连续两次作答间隔最大的一次；avg_exam_cnt为在该时间窗中日均答卷数；输出按days_window和avg_exam_cnt倒序，avg_exam_cnt保留两位小数 #材料解释部分：用户1006在2021年共作答3次；第一次...

0 点赞评论收藏

分享

2024-03-11 17:49

数据分析师

开窗sql137 第二快/慢用时之差大于试卷时长一半的试卷

#需求：找到第二快和第二慢用时之差大于试卷时长的一半的试卷信息 #输出：exam_id、duration、release_time #要求：按exam_id降序；时差为， #拆解：用时之差：每个试卷ID下timestampdiff(second,start_time,submit_time)第二快和第二慢的；求第二快/慢，做两个开窗排名，对时差order by asc/desc+where ck=2 #大于试卷时长的一半：以second为单位计算，试卷时长的一半=duration*30；对作答时差赋值，耗时长排名为2的为正值，耗时短排名为2的为负值，二者求和>duration*30的即为...

0 点赞评论收藏

分享

2024-03-11 16:49

数据分析师

开窗SQL136 | #每类试卷得分前3名#

#需求：找到每类试卷得分的前3名 #输出：tid(试卷类别)、uid(用户)、ranking(类别排名) #要求：如果两人最大分数相同，选择最小分数大者，如果还相同，选择uid大者；输出按ranking排序 #要用到的字段：tag、uid、score #思路：一是表连接，二是开窗得到每类试卷的分数排名；疑点在于，如果直接limit 3,会只筛选所有结果的前三，怎么做到输出每类试卷的前三； #解疑：开窗得到排名1,2,3..，where限制输出范围 select tid,uid,ranking from( select tag tid,uid, row_number()over(partitio...

0 点赞评论收藏

分享

2024-02-25 17:26

数据分析师

sql174 | 2021年国庆在北京接单3次以上的司机数据

#需求：统计2021年国庆7天期间在北京市接单至少3次的司机的平均接单数和平均兼职收入 #输出：city、avg_order_num、avg_income #要求：时间区间为2021-10-01 and 2021-10-07；city="北京"；目标为count(order_time)>=3的司机；输出指标均保留三位小数；材料中提到取消的不算，finish_time is not null #涉及数据：city、driver_id、order_time、fare select "北京" city,round(avg(e1),3) avg_order...

0 点赞评论收藏

分享

2024-02-25 16:35

数据分析师

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

#需求：计算店铺901在2021年国庆头3天的7日动销率和滞销率 #输出：dt、sale_rate、unsale_rate #要求：输出时间为1号-3号，取值区间为9月25号-10月3号；shop_id=901；输出指标保留三位小数，按日期升序 #动销率=有销量的商品/已上架总商品数，滞销率=没有销量的商品/已上架总商品数，涉及三个指标 #需要用到的数据：shop_id、product_id、event_time #拆分：动销率与滞销率：假设动销率=x/y，则滞销率=1-x/y，即只需要求得有销量的商品和已上架总商品数两个参数即可 #时间：有两次对时间的筛选，一次是计算指标时对参数时间区间的过...

0 点赞评论收藏

分享

2024-02-25 15:20

数据分析师

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

#需求：计算2021年10月商城里所有新用户的首单平均交易金额（客单价）和平均获客成本 #输出：avg_amount(首单的平均单价)、avg_cost(平均获客成本) #要求：时间区间为2021年10月，对象是新用户，输出均保留一位小数； #均单价=订单金额/用户数，平均获客成本=优惠金额/用户数，优惠金额=(price*cnt)-total_amount #涉及数据：event_time、uid、total_amount、price、cnt，不涉及表tb_product_info，只用二表和三表链接 #拆解：时间：where date_format(event_time,'%Y%m')=2...

0 点赞评论收藏

分享

2024-01-26 16:39

数据分析师

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

#需求：统计零食类商品中复购率top3高的商品 #输出：商品ID、复购率 #要求：tag为零食；复购率保留3为小数，倒序，商品ID升序输出；只输出复购率前三的商品信息 #复购率=近90天内有至少两次购买的人数/有购买的总人数;近90天指包含当天在内的90天 #拆分问题： #1、要用到的数据：product_id、tag、uid、event_time，表链接 #2、近90天内：event_time>=date_sub(max(event_time),interval 89 day) #3、是否为复购：if(count(event_time)>1,1,0) 加上聚合过商品和用户，得到的...

0 点赞评论收藏

分享

2024-01-23 17:27

数据分析师

题解 | #某店铺的各商品毛利率及店铺整体毛利率#

#需求：计算2021年10月以来店铺901中商品毛利率大于24.9%的商品信息及店铺整体毛利率#输出：商品ID、毛利率，店铺汇总行#要求：先输出店铺毛利率，再输出各商品毛利率，按商品ID升序，保留1位小数#特殊点：1、输出信息中要有一行店铺整体的毛利率信息；2、原表信息中没有平均单件售价#思路：问题分解#1、商品毛利率和店铺毛利率计算公式实际是同一个，只是一个用的总价，一个用的单价#2、要用到的数据：product_id、price、in_price、event_time，涉及三个表，做表链接#3、2021年10月以来店铺901：where shop_id=901 and date(event...

0 点赞评论收藏

分享

2024-01-22 17:26

数据分析师

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

#需求：计算每个用户2021年7月以来每月获得的金币数 #输出：uid、month、coin(金币数) #要求：活动时间范围为'2021-07-07'-'2021-10-31';artical_id为0时sign_in值才有效;按月份、ID升序输出;如果in_time和out_time跨天，只记in_time对应的日期签到 #用户每天签到领1金币，并累积签到天数，连续签到的第3、7天分别可额外领2、6金币，每连续7天后重新累积签到天数 select uid,date_format(dt,'%Y%m') 'month',sum(w2) coin from( select*,date_sub(dt...

0 点赞评论收藏

分享

2024-01-22 16:00

数据分析师

题解 | #每天的日活数及新用户占比#

#需求：统计每天的日活数及新用户占比 #输出：日期、日活数、新用户占比 #要求：uv_new_ratio保留两位小数，dt升序输出；如果进入时间和离开时间跨天，则两天里都记为该用户活跃 #新用户占比=当天的新用户数/当天活跃用户数 #思路：先拆分出日期格式(union)，求用户的首次记录时间（新用户）、当日活跃数直接count，当日所有记录行即是活跃数 select dt,count(*) dau,round(sum(if(dt=ck1,1,0))/count(*),2) uv_new_ratio #对于新用户数的解释，如果dt日期=ck1得到的日期，那么该日期就是一个用户的首次登录时间，计为...

0 点赞评论收藏

分享

创作者周榜

更多

关注他的用户也关注了：

牛客网
牛客网在线编程
牛客网题解
牛客企业服务