Leetcode|MySQL|数据库刷题记录（1045~1098）

目录

1045. 买下所有产品的客户

1050. 合作过至少三次的演员和导演

 1068. 产品销售分析 I

 1069. 产品销售分析 II

 1070. 产品销售分析 III

 1075. 项目员工 I

 1076. 项目员工II

 1077. 项目员工 III

 1082. 销售分析 I 

 1083. 销售分析 II

 1084. 销售分析III

1097. 游戏玩法分析 V

 1098. 小众书籍

---

1045. 买下所有产品的客户

https://leetcode-cn.com/problems/customers-who-bought-all-products/

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 。

思路：其实有多种写法，这是最简洁的一种。

由于统计Product中产品数量的语句比较简单，所以可以直接在筛选语句中嵌套使用。对Customer表按customer_id进行分组，然后用HAVING语句筛选每个用户购买的产品数量和产品总数的用户。 

ps:记得在COUNT的时候要DISTINCT一下product_key，不然会不通过

SELECT customer_id
FROM Customer
GROUP BY customer_id
HAVING COUNT(DISTINCT product_key)=(SELECT COUNT(DISTINCT product_key) FROM Product)

1050. 合作过至少三次的演员和导演

https://leetcode-cn.com/problems/actors-and-directors-who-cooperated-at-least-three-times/

ActorDirector 表：

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| actor_id    | int     |
| director_id | int     |
| timestamp   | int     |
+-------------+---------+
timestamp 是这张表的主键.
 

写一条SQL查询语句获取合作过至少三次的演员和导演的 id 对 (actor_id, director_id)

示例：

ActorDirector 表：
+-------------+-------------+-------------+
| actor_id    | director_id | timestamp   |
+-------------+-------------+-------------+
| 1           | 1           | 0           |
| 1           | 1           | 1           |
| 1           | 1           | 2           |
| 1           | 2           | 3           |
| 1           | 2           | 4           |
| 2           | 1           | 5           |
| 2           | 1           | 6           |
+-------------+-------------+-------------+

Result 表：
+-------------+-------------+
| actor_id    | director_id |
+-------------+-------------+
| 1           | 1           |
+-------------+-------------+
唯一的 id 对是 (1, 1)，他们恰好合作了 3 次。

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/actors-and-directors-who-cooperated-at-least-three-times
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

思路：根据actor_id 和director_id进行分组，count具有相同的actor_id和director_id的行数，取行数大于等于3的actor_id和director_id即可

SELECT actor_id, director_id
FROM ActorDirector
GROUP BY actor_id, director_id
HAVING COUNT(*)>=3

 1068. 产品销售分析 I

销售表 Sales：

+-------------+-------+
| Column Name | Type  |
+-------------+-------+
| sale_id     | int   |
| product_id  | int   |
| year        | int   |
| quantity    | int   |
| price       | int   |
+-------------+-------+
(sale_id, year) 是销售表 Sales 的主键.
product_id 是关联到产品表 Product 的外键.
注意: price 表示每单位价格
产品表 Product：

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| product_id   | int     |
| product_name | varchar |
+--------------+---------+
product_id 是表的主键.
 

写一条SQL 查询语句获取 Sales 表中所有产品对应的 产品名称 product_name 以及该产品的所有 售卖年份 year 和 价格 price 。

 

示例：

Sales 表：
+---------+------------+------+----------+-------+
| sale_id | product_id | year | quantity | price |
+---------+------------+------+----------+-------+ 
| 1       | 100        | 2008 | 10       | 5000  |
| 2       | 100        | 2009 | 12       | 5000  |
| 7       | 200        | 2011 | 15       | 9000  |
+---------+------------+------+----------+-------+

Product 表：
+------------+--------------+
| product_id | product_name |
+------------+--------------+
| 100        | Nokia        |
| 200        | Apple        |
| 300        | Samsung      |
+------------+--------------+

Result 表：
+--------------+-------+-------+
| product_name | year  | price |
+--------------+-------+-------+
| Nokia        | 2008  | 5000  |
| Nokia        | 2009  | 5000  |
| Apple        | 2011  | 9000  |
+--------------+-------+-------+

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/product-sales-analysis-i
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

思路：根据product_id INNER JOIN两个表， 然后取题目要求的字段就可以了

SELECT b.product_name, a.year, a.price
FROM Sales a
INNER JOIN Product b
ON a.product_id=b.product_id

 1069. 产品销售分析 II

销售表：Sales

+-------------+-------+
| Column Name | Type  |
+-------------+-------+
| sale_id     | int   |
| product_id  | int   |
| year        | int   |
| quantity    | int   |
| price       | int   |
+-------------+-------+
sale_id 是这个表的主键。
product_id 是 Product 表的外键。
请注意价格是每单位的。
产品表：Product

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| product_id   | int     |
| product_name | varchar |
+--------------+---------+
product_id 是这个表的主键。
 

编写一个 SQL 查询，按产品 id product_id 来统计每个产品的销售总量。

 

查询结果格式如下面例子所示:

Sales 表：
+---------+------------+------+----------+-------+
| sale_id | product_id | year | quantity | price |
+---------+------------+------+----------+-------+ 
| 1       | 100        | 2008 | 10       | 5000  |
| 2       | 100        | 2009 | 12       | 5000  |
| 7       | 200        | 2011 | 15       | 9000  |
+---------+------------+------+----------+-------+

Product 表：
+------------+--------------+
| product_id | product_name |
+------------+--------------+
| 100        | Nokia        |
| 200        | Apple        |
| 300        | Samsung      |
+------------+--------------+

Result 表：
+--------------+----------------+
| product_id   | total_quantity |
+--------------+----------------+
| 100          | 22             |
| 200          | 15             |
+--------------+----------------+

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/product-sales-analysis-ii
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

思路：建立子表，子表中用SUM(quantity) OVER(PARTITION BY product_id)统计每个product_id的total_quantity， 然后从子表中取出product_id和对应的total_quantity

SELECT t.product_id, t.total_quantity
FROM 
(
    SELECT DISTINCT product_id, SUM(quantity) OVER(PARTITION BY product_id) as total_quantity FROM Sales
) t

 1070. 产品销售分析 III

销售表 Sales：

+-------------+-------+
| Column Name | Type  |
+-------------+-------+
| sale_id     | int   |
| product_id  | int   |
| year        | int   |
| quantity    | int   |
| price       | int   |
+-------------+-------+
sale_id 是此表的主键。
product_id 是产品表的外键。
请注意，价格是按每单位计的。
产品表 Product：

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| product_id   | int     |
| product_name | varchar |
+--------------+---------+
product_id 是此表的主键。
 

编写一个 SQL 查询，选出每个销售产品的 第一年 的 产品 id、年份、数量 和 价格。

查询结果格式如下：

Sales table:
+---------+------------+------+----------+-------+
| sale_id | product_id | year | quantity | price |
+---------+------------+------+----------+-------+ 
| 1       | 100        | 2008 | 10       | 5000  |
| 2       | 100        | 2009 | 12       | 5000  |
| 7       | 200        | 2011 | 15       | 9000  |
+---------+------------+------+----------+-------+

Product table:
+------------+--------------+
| product_id | product_name |
+------------+--------------+
| 100        | Nokia        |
| 200        | Apple        |
| 300        | Samsung      |
+------------+--------------+

Result table:
+------------+------------+----------+-------+
| product_id | first_year | quantity | price |
+------------+------------+----------+-------+ 
| 100        | 2008       | 10       | 5000  |
| 200        | 2011       | 15       | 9000  |
+------------+------------+----------+-------+

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/product-sales-analysis-iii
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

思路：这道题有一个坑。。。Sales中的数据可能一年内同一个产品有不同的定价和数量，在检索的时候要把最小年的产品记录都取出来 

SELECT product_id, year as first_year, quantity, price
FROM Sales
WHERE (product_id, year) IN
(
    SELECT product_id, MIN(year)
    FROM Sales 
    GROUP BY product_id
)

 1075. 项目员工 I

项目表 Project： 

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| project_id  | int     |
| employee_id | int     |
+-------------+---------+
主键为 (project_id, employee_id)。
employee_id 是员工表 Employee 表的外键。
员工表 Employee：

+------------------+---------+
| Column Name      | Type    |
+------------------+---------+
| employee_id      | int     |
| name             | varchar |
| experience_years | int     |
+------------------+---------+
主键是 employee_id。
 

请写一个 SQL 语句，查询每一个项目中员工的 平均 工作年限，精确到小数点后两位。

查询结果的格式如下：

Project 表：
+-------------+-------------+
| project_id  | employee_id |
+-------------+-------------+
| 1           | 1           |
| 1           | 2           |
| 1           | 3           |
| 2           | 1           |
| 2           | 4           |
+-------------+-------------+

Employee 表：
+-------------+--------+------------------+
| employee_id | name   | experience_years |
+-------------+--------+------------------+
| 1           | Khaled | 3                |
| 2           | Ali    | 2                |
| 3           | John   | 1                |
| 4           | Doe    | 2                |
+-------------+--------+------------------+

Result 表：
+-------------+---------------+
| project_id  | average_years |
+-------------+---------------+
| 1           | 2.00          |
| 2           | 2.50          |
+-------------+---------------+
第一个项目中，员工的平均工作年限是 (3 + 2 + 1) / 3 = 2.00；第二个项目中，员工的平均工作年限是 (3 + 2) / 2 = 2.50

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/project-employees-i
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

思路：将两个表按employee_id JOIN起来，根据project_id进行分组，然后计算每个project分组内员工工作年限的平均值 

SELECT a.project_id, ROUND(AVG(b.experience_years), 2) as average_years
FROM Project a
JOIN Employee b
ON a.employee_id=b.employee_id
GROUP BY project_id

 1076. 项目员工II

Table: Project

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| project_id  | int     |
| employee_id | int     |
+-------------+---------+
主键为 (project_id, employee_id)。
employee_id 是员工表 Employee 表的外键。
Table: Employee

+------------------+---------+
| Column Name      | Type    |
+------------------+---------+
| employee_id      | int     |
| name             | varchar |
| experience_years | int     |
+------------------+---------+
主键是 employee_id。
 

编写一个SQL查询，报告所有雇员最多的项目。

查询结果格式如下所示：

Project table:
+-------------+-------------+
| project_id  | employee_id |
+-------------+-------------+
| 1           | 1           |
| 1           | 2           |
| 1           | 3           |
| 2           | 1           |
| 2           | 4           |
+-------------+-------------+

Employee table:
+-------------+--------+------------------+
| employee_id | name   | experience_years |
+-------------+--------+------------------+
| 1           | Khaled | 3                |
| 2           | Ali    | 2                |
| 3           | John   | 1                |
| 4           | Doe    | 2                |
+-------------+--------+------------------+

Result table:
+-------------+
| project_id  |
+-------------+
| 1           |
+-------------+
第一个项目有3名员工，第二个项目有2名员工。

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/project-employees-ii
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

思路：先计算在project的employee的数量，然后根据employee的数量进行排序，可能会有多个project的employee数量一样，所以要用rank排序，记得要降序排序，然后取rank=1的project_id即可

SELECT b.project_id
FROM(
    SELECT t.project_id, RANK() OVER(ORDER BY t.cnt DESC) as rk
    FROM 
    (
        SELECT project_id, COUNT(DISTINCT employee_id) as cnt
        FROM Project
        GROUP BY project_id
    ) t
)b
WHERE b.rk=1

 1077. 项目员工 III

项目表 Project：

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| project_id  | int     |
| employee_id | int     |
+-------------+---------+
(project_id, employee_id) 是这个表的主键
employee_id 是员工表 Employee 的外键
员工表 Employee：

+------------------+---------+
| Column Name      | Type    |
+------------------+---------+
| employee_id      | int     |
| name             | varchar |
| experience_years | int     |
+------------------+---------+
employee_id 是这个表的主键
 

写 一个 SQL 查询语句，报告在每一个项目中经验最丰富的雇员是谁。如果出现经验年数相同的情况，请报告所有具有最大经验年数的员工。

查询结果格式在以下示例中：

Project 表：
+-------------+-------------+
| project_id  | employee_id |
+-------------+-------------+
| 1           | 1           |
| 1           | 2           |
| 1           | 3           |
| 2           | 1           |
| 2           | 4           |
+-------------+-------------+

Employee 表：
+-------------+--------+------------------+
| employee_id | name   | experience_years |
+-------------+--------+------------------+
| 1           | Khaled | 3                |
| 2           | Ali    | 2                |
| 3           | John   | 3                |
| 4           | Doe    | 2                |
+-------------+--------+------------------+

Result 表：
+-------------+---------------+
| project_id  | employee_id   |
+-------------+---------------+
| 1           | 1             |
| 1           | 3             |
| 2           | 1             |
+-------------+---------------+
employee_id 为 1 和 3 的员工在 project_id 为 1 的项目中拥有最丰富的经验。在 project_id 为 2 的项目中，employee_id 为 1 的员工拥有最丰富的经验。

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/project-employees-iii
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

思路：创建子表，在子表中按employee_id连接两个表，然后分别对各project的雇员工作年限降序排序， 在外层查询中取出rank=1的雇员即可

SELECT t.project_id, t.employee_id
FROM(
    SELECT a.project_id, a.employee_id, RANK() OVER(PARTITION BY a.project_id ORDER BY b.experience_years DESC) as rk
    FROM Project a
    JOIN Employee b 
    ON a.employee_id=b.employee_id
)t
WHERE rk=1

 1082. 销售分析 I 

产品表：Product

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| product_id   | int     |
| product_name | varchar |
| unit_price   | int     |
+--------------+---------+
product_id 是这个表的主键.
销售表：Sales

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| seller_id   | int     |
| product_id  | int     |
| buyer_id    | int     |
| sale_date   | date    |
| quantity    | int     |
| price       | int     |
+------ ------+---------+
这个表没有主键，它可以有重复的行.
product_id 是 Product 表的外键.
 

编写一个 SQL 查询，查询总销售额最高的销售者，如果有并列的，就都展示出来。

查询结果格式如下所示：

Product 表：
+------------+--------------+------------+
| product_id | product_name | unit_price |
+------------+--------------+------------+
| 1          | S8           | 1000       |
| 2          | G4           | 800        |
| 3          | iPhone       | 1400       |
+------------+--------------+------------+

Sales 表：
+-----------+------------+----------+------------+----------+-------+
| seller_id | product_id | buyer_id | sale_date  | quantity | price |
+-----------+------------+----------+------------+----------+-------+
| 1         | 1          | 1        | 2019-01-21 | 2        | 2000  |
| 1         | 2          | 2        | 2019-02-17 | 1        | 800   |
| 2         | 2          | 3        | 2019-06-02 | 1        | 800   |
| 3         | 3          | 4        | 2019-05-13 | 2        | 2800  |
+-----------+------------+----------+------------+----------+-------+

Result 表：
+-------------+
| seller_id   |
+-------------+
| 1           |
| 3           |
+-------------+
Id 为 1 和 3 的销售者，销售总金额都为最高的 2800。

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/sales-analysis-i
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

思路：先对每个seller计算price的总值，然后根据总值进行降序排序，在最外层查询中取出总值最大的seller_id（可能会有多个，所以排序时使用rank）

SELECT b.seller_id
FROM(
    SELECT t.seller_id, RANK() OVER(ORDER BY t.total_price DESC) as rk
    FROM(
        SELECT seller_id, SUM(price) as total_price
        FROM Sales
        GROUP BY seller_id
    )t
)b
WHERE b.rk=1

 1083. 销售分析 II

Table: Product

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| product_id   | int     |
| product_name | varchar |
| unit_price   | int     |
+--------------+---------+
product_id 是这张表的主键
Table: Sales

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| seller_id   | int     |
| product_id  | int     |
| buyer_id    | int     |
| sale_date   | date    |
| quantity    | int     |
| price       | int     |
+------ ------+---------+
这个表没有主键，它可以有重复的行.
product_id 是 Product 表的外键.
编写一个 SQL 查询，查询购买了 S8 手机却没有购买 iPhone 的买家。注意这里 S8 和 iPhone 是 Product 表中的产品。

查询结果格式如下图表示：

Product table:
+------------+--------------+------------+
| product_id | product_name | unit_price |
+------------+--------------+------------+
| 1          | S8           | 1000       |
| 2          | G4           | 800        |
| 3          | iPhone       | 1400       |
+------------+--------------+------------+

Sales table:
+-----------+------------+----------+------------+----------+-------+
| seller_id | product_id | buyer_id | sale_date  | quantity | price |
+-----------+------------+----------+------------+----------+-------+
| 1         | 1          | 1        | 2019-01-21 | 2        | 2000  |
| 1         | 2          | 2        | 2019-02-17 | 1        | 800   |
| 2         | 1          | 3        | 2019-06-02 | 1        | 800   |
| 3         | 3          | 3        | 2019-05-13 | 2        | 2800  |
+-----------+------------+----------+------------+----------+-------+

Result table:
+-------------+
| buyer_id    |
+-------------+
| 1           |
+-------------+
id 为 1 的买家购买了一部 S8，但是却没有购买 iPhone，而 id 为 3 的买家却同时购买了这 2 部手机。

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/sales-analysis-ii
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

 思路：连接两张表，然后筛选出product_name为S8或iPhone的记录，然后对这些记录按照买家id进行分组，只取买了1种产品并且这个产品是S8的买家就可以了

SELECT b.buyer_id
FROM
(
    SELECT t.buyer_id, t.product_name
    FROM(
        SELECT a.product_name, b.product_id, b.buyer_id
        FROM Product a
        RIGHT JOIN Sales b
        ON a.product_id=b.product_id
        WHERE a.product_name IN ('S8', 'iPhone')
    )t
    GROUP BY t.buyer_id
    HAVING COUNT(DISTINCT t.product_id)=1
)b 
WHERE b.product_name='S8'

 1084. 销售分析III

Table: Product

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| product_id   | int     |
| product_name | varchar |
| unit_price   | int     |
+--------------+---------+
product_id 是这个表的主键
Table: Sales

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| seller_id   | int     |
| product_id  | int     |
| buyer_id    | int     |
| sale_date   | date    |
| quantity    | int     |
| price       | int     |
+------ ------+---------+
这个表没有主键，它可以有重复的行.
product_id 是 Product 表的外键.
 

编写一个SQL查询，报告2019年春季才售出的产品。即仅在2019-01-01至2019-03-31（含）之间出售的商品。

查询结果格式如下所示：

Product table:
+------------+--------------+------------+
| product_id | product_name | unit_price |
+------------+--------------+------------+
| 1          | S8           | 1000       |
| 2          | G4           | 800        |
| 3          | iPhone       | 1400       |
+------------+--------------+------------+

Sales table:
+-----------+------------+----------+------------+----------+-------+
| seller_id | product_id | buyer_id | sale_date  | quantity | price |
+-----------+------------+----------+------------+----------+-------+
| 1         | 1          | 1        | 2019-01-21 | 2        | 2000  |
| 1         | 2          | 2        | 2019-02-17 | 1        | 800   |
| 2         | 2          | 3        | 2019-06-02 | 1        | 800   |
| 3         | 3          | 4        | 2019-05-13 | 2        | 2800  |
+-----------+------------+----------+------------+----------+-------+

Result table:
+-------------+--------------+
| product_id  | product_name |
+-------------+--------------+
| 1           | S8           |
+-------------+--------------+
id为1的产品仅在2019年春季销售，其他两个产品在之后销售。

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/sales-analysis-iii
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

思路：先从Sales表中筛选出sale_date不在2019-01-01至2019-03-31（含）之间的product_id，然后在Product表中取出不在之前筛选出来的product_id的product_id

SELECT product_id, product_name
FROM Product
WHERE product_id NOT IN
(
    SELECT DISTINCT product_id
    FROM Sales
    WHERE sale_date NOT BETWEEN '2019-01-01' AND '2019-03-31'
)

1097. 游戏玩法分析 V
 

Activity 活动记录表

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| player_id    | int     |
| device_id    | int     |
| event_date   | date    |
| games_played | int     |
+--------------+---------+
（player_id，event_date）是此表的主键
这张表显示了某些游戏的玩家的活动情况
每一行表示一个玩家的记录，在某一天使用某个设备注销之前，登录并玩了很多游戏（可能是 0）
 

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

玩家的 第一天留存率 定义为：假定安装日期为 X 的玩家的数量为 N ，其中在 X 之后的某一天重新登录的玩家数量为 M ，M/N 就是第一天留存率，四舍五入到小数点后两位。

编写一个 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

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/game-play-analysis-v
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

思路：这道题还是比较复杂的。。。一开始我的思路和题解的其实差不多，代码也通过了，但是计算效率很低，看了题解才知道有更简便的做法。整个题目的思路是先求每个安装日期的安装人数，再算每个安装日期的留存人数，再把前者除以后者就可以得到安装日的留存率了。安装日期的安装人数是比较好算的，核心在获取每个用户的安装日期即最小登陆日，得到每个用户的安装日后，然后再统计各个安装日的安装用户数就可以了。难点在于计算每个安装日的留存人数，这也是我想复杂了的一步，最后还是看题解才知道有更简便的写法。 把刚才获得每个用户的最小登陆日的子表左连接上原表，匹配条件是子表的安装日 - 原表的登陆日=-1，且匹配用户id，这个条件的意思其实是对于每个用户，我们只关注他安装日的后一天有没有继续使用该游戏，因此只需要匹配安装日的后一天记录，如果不是NULL就知道他有没有登录了。在连接后的大表中按安装日进行分组，统计每个安装日的安装用户数，且统计每个安装日的后一天登录用户数，两者相除记得到安装日留存率。

SELECT a.install_dt, COUNT(a.player_id) AS installs, ROUND(COUNT(b.player_id)/COUNT(a.player_id),2) AS Day1_retention 
FROM
(
    SELECT player_id, MIN(event_date) as install_dt
    FROM Activity
    GROUP BY player_id
)a
LEFT JOIN Activity b
ON DATEDIFF(a.install_dt, b.event_date)=-1 AND a.player_id=b.player_id
GROUP BY a.install_dt

 1098. 小众书籍

书籍表 Books：

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| book_id        | int     |
| name           | varchar |
| available_from | date    |
+----------------+---------+
book_id 是这个表的主键。
订单表 Orders：

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| order_id       | int     |
| book_id        | int     |
| quantity       | int     |
| dispatch_date  | date    |
+----------------+---------+
order_id 是这个表的主键。
book_id  是 Books 表的外键。
 

你需要写一段 SQL 命令，筛选出过去一年中订单总量 少于10本 的 书籍 。

注意：不考虑 上架（available from）距今 不满一个月 的书籍。并且 假设今天是 2019-06-23 。

 

下面是样例输出结果：

Books 表：
+---------+--------------------+----------------+
| book_id | name               | available_from |
+---------+--------------------+----------------+
| 1       | "Kalila And Demna" | 2010-01-01     |
| 2       | "28 Letters"       | 2012-05-12     |
| 3       | "The Hobbit"       | 2019-06-10     |
| 4       | "13 Reasons Why"   | 2019-06-01     |
| 5       | "The Hunger Games" | 2008-09-21     |
+---------+--------------------+----------------+

Orders 表：
+----------+---------+----------+---------------+
| order_id | book_id | quantity | dispatch_date |
+----------+---------+----------+---------------+
| 1        | 1       | 2        | 2018-07-26    |
| 2        | 1       | 1        | 2018-11-05    |
| 3        | 3       | 8        | 2019-06-11    |
| 4        | 4       | 6        | 2019-06-05    |
| 5        | 4       | 5        | 2019-06-20    |
| 6        | 5       | 9        | 2009-02-02    |
| 7        | 5       | 8        | 2010-04-13    |
+----------+---------+----------+---------------+

Result 表：
+-----------+--------------------+
| book_id   | name               |
+-----------+--------------------+
| 1         | "Kalila And Demna" |
| 2         | "28 Letters"       |
| 5         | "The Hunger Games" |
+-----------+--------------------+

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/unpopular-books
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

 思路：这道题不难，先从Books表里面筛选available_from <= '2019-05-23'的记录，再从Orders表中筛选 dispatch_date>='2018-06-23'的记录，并按照book_id分组统计quantity的加和（这里一定要注意是加和，我就是没注意用了count一直没通过），然后将两个子表左连接起来，去quantity总数<10或者总数为null的书就可以了。

SELECT a.book_id, a.name
FROM 
(
    SELECT book_id, name
    FROM Books
    WHERE available_from <= '2019-05-23'
)a 
LEFT JOIN
(
    SELECT book_id, SUM(quantity) as qt
    FROM Orders
    WHERE dispatch_date>='2018-06-23'
    GROUP BY book_id
)b 
ON a.book_id=b.book_id
WHERE b.qt<10 OR b.qt is null