- 博客(50)
- 收藏
- 关注
RSS订阅原创 大数据SQL题50 员工在职人数问题
统计2020年每个月实际在职员工数量(只统计2020-03-31之前)，如果1个月在职天数只有1天，数量计算方式：1/当月天数。如果一个月只有一天的话，只算30分之1个人。现有用户表（emp）如下。
2024-06-06 13:24:55
201
原创 大数据SQL题49 视频热度问题
最近播放日期以 end_time-结束观看时间 为准，假设为T，则最近一个月按 [T-29, T] 闭区间统计。看评论普遍反映答题系统有问题，新鲜度根本就没有用到，那就大概凑一下答案吧。当前配置的参数a,b,c,d分别为100、5、3、2。现有用户视频表（user_video_log）如下。找出近一个月发布的视频中热度最高的top3视频。当天日期使用视频中最大的end_time。结果中热度保留为整数，并按热度降序排序。新鲜度=1/(最近无播放天数+1)；
2024-06-06 13:21:36
391
原创 大数据SQL题47 打车问题
注：不同时段定义：早高峰 [07:00:00 , 09:00:00)、工作时间 [09:00:00 , 17:00:00）、晚高峰 [17:00:00 ,20:00:00）、休息时间 [20:00:00 , 07:00:00） 时间区间左闭右开（即7:00:00算作早高峰，而9:00:00不算做早高峰）全部以event_time-开始打车时间为时段划分依据，平均等待接单时间和平均调度时间均保留2位小数，平均调度时间仅计算完成了的订单，结果按叫车量升序排序。司机订单信息表（get_car_order）如下。
2024-06-06 13:15:19
862
原创 大数据SQL题46 出勤率问题
注：出勤率指用户看直播时间超过40分钟，求出每个课程的出勤率（结果保留两位小数）。现有用户出勤表（user_login）如下。课程报名表（course_apply）如下。
2024-06-06 13:13:45
191
原创 大数据SQL题45 复购率问题
近90天指包含最大日期（以订单详情表(order_detail)中最后的日期）在内的近90天。结果中复购率保留2位小数，并按复购率倒序、商品ID升序排序。注：复购率指用户在一段时间内对某商品的重复购买比例，复购率越大，则反映出消费者对品牌的忠诚度就越高，也叫回头率。此处我们定义：某商品复购率 = 近90天内购买它至少两次的人数 ÷ 购买它的总人数。现有电商订单表（order_detail）如下。
2024-06-06 13:13:12
296
原创 大数据SQL题44 日期交叉问题
现有各品牌优惠周期表（promotion_info）如下，其记录了每个品牌的每个优惠活动的周期，其中同一品牌的不同优惠活动的周期可能会有交叉。现要求统计每个品牌的优惠总天数，若某个品牌在同一天有多个优惠活动，则只按一天计算。
2024-06-06 12:07:24
245
原创 大数据SQL题43 间断连续登录用户问题
现要求统计各用户最长的连续登录天数，间断一天也算作连续，例如：一个用户在1,3,5,6登录，则视为连续6天登录。现有各用户的登录记录表（login_events）如下，表中每行数据表达的信息是一个用户何时登录了平台。
2024-06-06 11:15:11
320
原创 大数据SQL题42 会话划分问题
使用lag()开窗取出每条记录的前一次访问时间，若间隔超过60秒，则标记is_first_page=1（新会话的开始）。使用sum(is_first_page) over()从第一条到当前条进行累加，即可以为每一条记录赋上数字顺序，随后拼接成session_id即可。规定若同一用户的相邻两次访问记录时间间隔小于60s，则认为两次浏览记录属于同一会话。现有页面浏览记录表（page_view_events）如下，表中有每个用户的每次页面访问记录。
2024-06-06 10:59:13
431
原创 大数据SQL题41 同时在线人数问题
现有各直播间的用户访问记录表（live_events）如下，表中每行数据表达的信息为，一个用户何时进入了一个直播间，又在何时离开了该直播间。与40题做法类似，只是增加了直播间live_id作为分组依据。
2024-06-06 10:40:33
463
原创 大数据SQL题40 同时在线最多的人数
用户登录，相当于在线人数加1，用户登出，相当于在线人数-1。因此可以把用户登录明细转化为用户操作明细，操作时间为登录/登出时间，操作带来的在线人数变化量为1/-1。最后使用开窗函数的累积计算功能，计算每条操作发生时刻的在线人数，取最大值即为同时在线最多的人数。根据用户登录明细表（user_login_detail），求出平台同时在线最多的人数。
2024-06-05 11:49:29
241
原创 大数据SQL题39 国庆期间的7日动销率和滞销率
从订单明细表（order_detail）和商品信息表（sku_info）表中求出国庆7天（指2021-10-01-2021-10-07）每天每个品类的商品的动销率和滞销率。滞销率定义为品类商品中一段时间内没有销量的商品占当前已上架总商品数的比例。（没有销量的商品 / 已上架总商品数）。动销率定义为品类商品中一段时间内有销量的商品占当前已上架总商品数的比例（有销量的商品/已上架总商品数）。只要当天任一店铺有任何商品的销量就输出该天的结果。
2024-06-05 11:48:44
386
原创 大数据SQL题38 连续签到领金币数
首先同样找出每个用户的所有连续登录区间，随后对区间内的每一天，求出当天可以获得的金币数量，最后求和。用户每天签到可以领1金币，并可以累计签到天数，连续签到的第3、7天分别可以额外领2和6金币。从用户登录明细表中求出每个用户金币总数，并按照金币总数倒序排序。每连续签到7天重新累积签到天数。
2024-06-05 11:45:23
386
原创 大数据SQL题37 统计活跃间隔对用户分级结果
假设今天是数据中所有日期的最大值，从用户登录明细表中的用户登录时间给各用户分级，求出各等级用户的人数。流失用户：近30天未活跃但是在30天前活跃。沉睡用户：近7天未活跃但是在7天前活跃。忠实用户：近7天活跃且非新用户。新晋用户：近7天新增。
2024-06-05 11:34:23
183
原创 大数据SQL题36 国庆期间每个品类的商品的收藏量和购买量
从订单明细表（order_detail）和收藏信息表（favor_info）统计2021国庆期间，每个商品总收藏量和购买量期望结果如下：需要用到的表：订单明细表：order_detail收藏信息表：favor_info
2024-06-05 11:27:14
353
原创 大数据SQL题35 同期商品售卖分析表
求出同一个商品在2021年和2022年中同一个月的售卖情况对比。从订单明细表（order_detail）中。
2024-06-05 11:26:03
161
原创 大数据SQL题34 销售订单首购和次购分析
通过商品信息表（sku_info）订单信息表（order_info）订单明细表（order_detail）分析如果有一个用户成功下单两个及两个以上的购买成功的手机订单（购买商品为xiaomi 10，apple 12，小米13）那么输出这个用户的id及第一次成功购买手机的日期和第二次成功购买手机的日期，以及购买手机成功的次数。因此像下面这样写可以AC。
2024-06-05 11:24:26
680
原创 大数据SQL题33 查看每件商品的售价涨幅情况
从商品价格变更明细表（sku_price_modify_detail），得到最近一次价格的涨幅情况，并按照涨幅升序排序。
2024-06-05 11:20:25
210
原创 大数据SQL题32 某周内每件商品每天销售情况
从订单详情表（order_detail）中查询2021年9月27号-2021年10月3号这一周所有商品每天销售情况。
2024-06-05 11:17:06
309
原创 大数据SQL题30 登录次数及交易次数统计
分别从登陆明细表（user_login_detail）和配送信息表中用户登录时间和下单时间统计登陆次数和交易次数。
2024-06-05 11:14:05
242
原创 大数据SQL题29 求出商品连续售卖的时间区间
从订单详情表（order_detail）中，求出商品连续售卖的时间区间。获取某日期在商品所有售卖日期中的排序值。相同），分组内日期则为连续登录日期。本题与第15题类似，首先通过。进行分组（若为连续日期，则。
2024-06-05 11:09:53
355
原创 大数据SQL题28 查询有新注册用户的当天的新用户数量、新用户的第一天留存率
通过row_number()函数可以得到每个用户每个登录日期的次序号，以此为偏移量对登录日期进行处理，得到一个基准日期flag，若存在连续日期的情况，则基准日期会相同，可以通过flag分组内记录条数判断是否存在连续登录行为。本题与第05题类似，由于该题需要统计的是每天新用户数量、新用户的第一天留存率，因此可以简单的把每个用户的首次登录日期查询出来，随后把所有登录日期与首单日期进行作差对比，差值=1则说明存在1日留存。除常规思路外，本题实质上是连续区间/留存的问题，因此可以使用3、4两种连续区间的处理方法。
2024-06-05 11:09:22
1119
原创 大数据SQL题26 各品类中商品价格的中位数
从商品信息表（sku_info）求出各分类商品价格的中位数，如果一个分类下的商品个数为偶数则输出中间两个值的平均值，如果是奇数，则输出中间数即可。
2024-04-26 21:26:23
883
原创 大数据SQL题25 各品类销量前三的所有商品
从订单详情表中（order_detail）和商品（sku_info）中查询各个品类销售数量前三的商品。如果该品类小于三个商品，则输出所有的商品销量。
2024-04-26 21:25:39
281
原创 大数据SQL题24 根据商品销售情况进行商品分类
从订单详情表中（order_detail）对销售件数对商品进行分类，0-5000为冷门商品，5001-19999位一般商品，20000往上为热门商品，并求出不同类别商品的数量。
2024-04-26 21:25:04
324
2
原创 大数据SQL题23 销售额完成任务指标的商品
请写出SQL从订单详情表中（order_detail）查询连续两个月销售总额大于等于任务总额的商品。假设1号商品销售总额大于21000，2号商品销售总额大于10000，其余商品没有要求。商家要求每个商品每个月需要售卖出一定的销售总额。
2024-04-23 10:29:18
206
2
原创 大数据SQL题22 查询相同时刻多地登陆的用户
从登录明细表（user_login_detail）中查询在相同时刻，多地登陆（ip_address不同）的用户。
2024-04-23 10:27:18
308
原创 大数据SQL题21 查询每个用户登录日期的最大空档期
从登录明细表（user_login_detail）中查询每个用户两个登录日期（以login_ts为准）之间的最大的空档期。统计最大空档期时，用户最后一次登录至今的空档也要考虑在内，假设今天为2021-10-10。
2024-04-23 10:26:34
319
2
原创 大数据SQL题19 统计每日商品1和商品2销量的差值
从订单明细表（order_detail）中统计每天商品1和商品2销量（件数）的差值（商品1销量-商品2销量）
2024-04-19 16:26:03
224
原创 大数据SQL题18 购买过商品1和商品2但是没有购买商品3的顾客
从订单明细表(order_detail)中查询出所有购买过商品1和商品2，但是没有购买过商品3的用户，
2024-04-19 16:25:32
444
原创 大数据SQL题16 男性和女性每日的购物总金额统计
从订单信息表（order_info）和用户信息表（user_info）中，分别统计每天男性和女性用户的订单总金额，如果当天男性或者女性没有购物，则统计结果为0。
2024-04-16 11:05:18
339
原创 大数据SQL题15 查询所有用户的连续登录两天及以上的日期区间
从登录明细表（user_login_detail）中查询出，所有用户的连续登录两天及以上的日期区间，以登录时间（login_ts）为准。
2024-04-16 11:04:22
400
原创 大数据SQL题14 向用户推荐朋友收藏的商品
现需要请向所有用户推荐其朋友收藏但是用户自己未收藏的商品，请从好友关系表（friendship_info）和收藏表（favor_info）中查询出应向哪位用户推荐哪些商品。期望结果如下：需要用到的表：好友关系表：friendship_info收藏表：favor_info
2024-04-16 11:01:02
321
原创 大数据SQL题13 即时订单比例
请从配送信息表（delivery_info）中求出每个用户的首单（用户的第一个订单）中即时订单的比例，保留两位小数，以小数形式显示。订单配送中，如果期望配送日期和下单日期相同，称为即时订单，如果期望配送日期和下单日期不同，称为计划订单。使用row_number()找出所有首单记录，随后对比下单日期和期望配送日期即可。
2024-04-15 20:48:57
139
原创 大数据SQL题12 查询指定日期的全部商品价格
查询所有商品（sku_info表）截至到2021年10月01号的最新商品价格（需要结合价格修改表进行分析）
2024-04-15 20:48:23
272
原创 大数据SQL题11 用户注册、登录、下单综合统计
从用户登录明细表（user_login_detail）和订单信息表（order_info）中查询每个用户的注册日期（首次登录日期）、总登录次数以及其在2021年的登录次数、订单数和订单总额。
2024-04-15 20:47:51
315
空空如也
空空如也
TA创建的收藏夹 TA关注的收藏夹
TA关注的人