哔哩哔哩2020届秋招数据分析师一面 —— SQL整理(2019.8.8)

本文深入解析了哔哩哔哩2020届秋招数据分析师面试中的SQL操作题,包括统计每天新增会员数量和输出各节目分类下播放量前3的节目。通过详细步骤和代码示例,帮助读者掌握窗口函数和子查询等高级SQL技巧。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

补上「窗口函数」的知识空缺,整理「哔哩哔哩一面」的SQL操作。

数据是自己生成的,代码是自己码的。如有错误,请评论指正,感谢!

❤️「完整真题」

哔哩哔哩2020届秋招数据分析师面试第一轮(2019.8.8)


一、第一题

在这里插入图片描述

已知日志表log,有3个字段:Date、user_ID(ID都是唯一的)、Flag(会员与否,0/1变量)。

问:统计每天新增会员数量。

  • 「思路」
  1. 先过滤,只考虑是会员的记录;
  2. 每日新增会员,是指当日的该会员不在以往出现过。所以,用NOT IN排除以往的user_id。
  • SQL实现

step1:

CREATE VIEW flag1 AS
SELECT * FROM log
WHERE flag = 1;

在这里插入图片描述
step2 :

SELECT t1.date, COUNT(user_id)
FROM flag1 AS t1 
WHERE t1.user_id NOT IN
	(SELECT t2.user_id FROM flag1 AS t2
		WHERE t2.date < t1.date)
GROUP BY t1.date;

在这里插入图片描述

  • 完整SQL代码
CREATE VIEW flag1 AS
SELECT * FROM log
WHERE flag = 1;

SELECT t1.date, COUNT(user_id)
FROM flag1 AS t1 
WHERE t1.user_id NOT IN
	(SELECT t2.user_id FROM flag1 AS t2
		WHERE t2.date < t1.date)
GROUP BY t1.date;

发散思维
  • 如果要每日新增会员表呢?
SELECT t1.date, user_id
FROM flag1 AS t1 
WHERE t1.user_id NOT IN
	(SELECT t2.user_id FROM flag1 AS t2
		WHERE t2.date < t1.date);

在这里插入图片描述

  • 如果要把每日新增会员名字和数量合并在一张表呢?
SELECT t1.date, user_id, 
	COUNT(user_id) OVER (PARTITION BY t1.date) AS cum_count
FROM flag1 AS t1 
WHERE t1.user_id NOT IN
	(SELECT t2.user_id FROM flag1 AS t2
		WHERE t2.date < t1.date);

在这里插入图片描述


二、第二题

已知节目表,3个字段:节目id,节目分类,播放量。

输出:每个分类的播放量前3的节目。

在这里插入图片描述

  • SQL 实现
SELECT category, show_id, video_view FROM 
(SELECT *, 
	rank() OVER (PARTITION BY category ORDER BY video_view DESC) AS t
	FROM show_table) new_table
WHERE new_table.t <= 3;

在这里插入图片描述

评论 14
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值