【SQL】进阶学习与题目补充

1. 学习框架

2. leetcode日常练习

Q1 1097. 游戏玩法分析 V

Q1.1 题目：

我们将玩家的安装日期定义为该玩家的第一个登录日。

我们还将某个日期 X 的第 1 天留存时间定义为安装日期为 X 的玩家的数量，他们在 X 之后的一天重新登录，除以安装日期为 X 的玩家的数量，四舍五入到小数点后两位。

编写一个 SQL 查询，报告每个安装日期、当天安装游戏的玩家数量和第一天的留存时间。

查询结果格式如下所示：

Activity 表：

player_id	device_id	event_date	games_played
1	2	2016-03-01	5
1	2	2016-03-02	6
2	3	2017-06-25	1
3	1	2016-03-01	0
3	4	2016-07-03	5

Result 表：

install_dt	installs	Day1_retention
2016-03-01	2	0.50
2017-06-25	1	0.00

玩家 1 和 3 在 2016-03-01 安装了游戏，但只有玩家 1 在 2016-03-02 重新登录，所以 2016-03-01 的第一天留存时间是 1/2=0.50；
玩家 2 在 2017-06-25 安装了游戏，但在 2017-06-26 没有重新登录，因此 2017-06-25 的第一天留存时间为 0/1=0.00

Q1.2 代码：

select a1.install_dt,
       count(*) installs,
       round(count(a2.event_date) /count(*),2) Day1_retention
from 
(
    select player_id,min(event_date) install_dt
from Activity
group by Activity.player_id
) a1
left join Activity a2
on a1.player_id=a2.player_id
and datediff(event_date,install_dt)=1
group by install_dt

Q1.3 注意：

连接后的表，player_id为主键，使用左连接，第三列显示1天之后有登陆记录的信息

player_id	install_dt	event_date_Day1_retention
1	2016-03-01	2016-03-02

Q2 571. 给定数字的频率查询中位数

Q2.1 题目：

Numbers 表保存数字的值及其频率。

Number	Frequency
0	7
1	1
2	3
3	1

在此表中，数字为 0, 0, 0, 0, 0, 0, 0, 1, 2, 2, 2, 3，所以中位数是 (0 + 0) / 2 = 0。

median
0.0000

请编写一个查询来查找所有数字的中位数并将结果命名为 median 。

Q2.2 代码：

select avg(a1.Number) median
from 
(select Number,
    sum(Frequency) over(order by Number asc) asc_accumula,
    sum(Frequency) over(order by Number desc) desc_accumula
from Numbers) a1
where asc_accumula>=(select sum(frequency) from Numbers)/2
and  desc_accumula>=(select sum(frequency) from Numbers)/2

Q2.3 注意：

sum(Frequency) over(order by Number desc) desc_accumula, order by 的是Number而不是Frequency
当某一数字的 正序和逆序累计 均大于 整个序列的数字个数的一半 时即为中位数， 将最后选定的一个或两个中位数进行求均值即可。

Q3 569. 员工薪水中位数

Q3.1 题目：

Employee 表包含所有员工。Employee 表有三列：员工Id，公司名和薪水。

Id	Company	Salary
1	A	2341
2	A	341
3	A	15
4	A	15314
5	A	451
6	A	513
7	B	15
8	B	13
9	B	1154
10	B	1345
11	B	1221
12	B	234
13	C	2345
14	C	2645
15	C	2645
16	C	2652
17	C	65

请编写SQL查询来查找每个公司的薪水中位数。挑战点：你是否可以在不使用任何内置的SQL函数的情况下解决此问题。

Id	Company	Salary
5	A	451
6	A	513
12	B	234
9	B	1154
14	C	2645

Q3.2 代码：

# Write your MySQL query statement below

select a1.Id,a1.Company,Salary
from
(
select Id,Company,Salary,
row_number() over (partition by Company order by Salary  asc) salary_rnk,
count(*) over (partition by Company ) emply_count
from Employee
) a1
where salary_rnk=emply_count/2 
or salary_rnk=emply_count/2 + 1
or salary_rnk=emply_count/2 + 0.5

Q3.3 注意：

where salary_rnk=emply_count/2 or salary_rnk=emply_count/2 + 1 or salary_rnk=emply_count/2 + 0.5
count 和 sum可以同时用

Q4 1045. 买下所有产品的客户

Q4.1 题目：

Customer 表：

Column Name	Type
customer_id	int
product_key	int

product_key 是 Customer 表的外键。
Product 表：

Column Name	Type
product_key	int

product_key 是这张表的主键。

写一条 SQL 查询语句，从 Customer 表中查询购买了 Product 表中所有产品的客户的 id。

示例：

Customer 表：

customer_id	product_key
1	5
2	6
3	5
3	6
1	6

Product 表：

product_key
5
6

Result 表：

customer_id
1
3

购买了所有产品（5 和 6）的客户的 id 是 1 和 3 。

Q4.2 代码：

select customer_id
from Customer
group by customer_id
having count(distinct product_key)=(select count(distinct product_key) from Product)

SELECT
    A.customer_id
FROM
    Customer AS A
    RIGHT OUTER JOIN Product AS B
ON
    A.product_key=B.product_key
GROUP BY
    A.customer_id
HAVING
    COUNT(DISTINCT A.product_key)=(SELECT COUNT(*) FROM Product);

Q4.3 注意：

如果是要找买下指定几样商品的客户呢？直接用count相等的第一种方法不对

Q5 578. 查询回答率最高的问题

Q5.1 题目：

从 survey_log 表中获得回答率最高的问题，survey_log 表包含这些列：id, action, question_id, answer_id, q_num, timestamp。

id 表示用户 id；action 有以下几种值：“show”，“answer”，“skip”；当 action 值为 “answer” 时 answer_id 非空，而 action 值为 “show” 或者 “skip” 时 answer_id 为空；q_num 表示当前会话中问题的编号。

请编写 SQL 查询来找到具有最高回答率的问题。

示例：

输入：

id	action	question_id	answer_id	q_num	timestamp
5	show	285	null	1	123
5	answer	285	124124	1	124
5	show	369	null	2	125
5	skip	369	null	2	126

输出：

survey_log
285

解释：
问题 285 的回答率为 1/1，而问题 369 回答率为 0/1，因此输出 285 。

提示：回答率最高的含义是：同一问题编号中回答数占显示数的比例最高。

Q5.2 代码：

select question_id as survey_log
from
(select question_id,
sum(case when action='show' then 1 else 0 end ) show_num,
sum(case when action='answer' then 1 else 0 end ) answer_num
from survey_log
group by question_id) t1
order by (answer_num/show_num) desc
limit 1

Q5.3 注意：

group by question_id) t1 select * from（）后面要给临时表加个名字

Q6

Q6.1 题目：

Q6.2 代码：

Q6.3 注意：

Q3

Q3.1 题目：

Q3.2 代码：

Q3.3 注意：

Q3

Q3.1 题目：

Q3.2 代码：

Q3.3 注意：

2.4

2.41 题目：

2.42 代码：

2.43 注意：

3. zhihu进阶题目