力扣SQL题训练（二）

511. 游戏玩法分析 I

活动表 Activity：表的主键是 (player_id, event_date)。这张表展示了一些游戏玩家在游戏平台上的行为活动。每行数据记录了一名玩家在退出平台之前，当天使用同一台设备登录平台后打开的游戏的数目（可能是 0 个）。写一条 SQL 查询语句获取每位玩家 第一次登陆平台的日期。
查询结果的格式如下所示：
Activity 表：
+-----------+-----------+------------+--------------+
| player_id | device_id | event_date | games_played |
+-----------+-----------+------------+--------------+
| 1 | 2 | 2016-03-01 | 5 |
| 1 | 2 | 2016-05-02 | 6 |
| 2 | 3 | 2017-06-25 | 1 |
| 3 | 1 | 2016-03-02 | 0 |
| 3 | 4 | 2018-07-03 | 5 |
+-----------+-----------+------------+--------------+
Result 表：
+-----------+-------------+
| player_id | first_login |
+-----------+-------------+
| 1 | 2016-03-01 |
| 2 | 2017-06-25 |
| 3 | 2016-03-02 |
+-----------+-------------+

select player_id,min(event_date) as 'first_login' from Activity 
group by player_id;

1141. 查询近30天活跃用户数

活动记录表：Activity，该表是用户在社交网站的活动记录。该表没有主键，可能包含重复数据。activity_type 字段为以下四种值 ('open_session', 'end_session', 'scroll_down','send_message')。每个 session_id 只属于一个用户。请写SQL查询出截至 2019-07-27（包含2019-07-27），近 30天的每日活跃用户数（当天只要有一条活动记录，即为活跃用户）。查询结果示例如下：
Activity table:
+---------+------------+---------------+---------------+
| user_id | session_id | activity_date | activity_type |
+---------+------------+---------------+---------------+
| 1 | 1 | 2019-07-20 | open_session |
| 1 | 1 | 2019-07-20 | scroll_down |
| 1 | 1 | 2019-07-20 | end_session |
| 2 | 4 | 2019-07-20 | open_session |
| 2 | 4 | 2019-07-21 | send_message |
| 2 | 4 | 2019-07-21 | end_session |
| 3 | 2 | 2019-07-21 | open_session |
| 3 | 2 | 2019-07-21 | send_message |
| 3 | 2 | 2019-07-21 | end_session |
| 4 | 3 | 2019-06-25 | open_session |
| 4 | 3 | 2019-06-25 | end_session |
+---------+------------+---------------+---------------+
Result table:
+------------+--------------+
| day | active_users |
+------------+--------------+
| 2019-07-20 | 2 |
| 2019-07-21 | 2 |
+------------+--------------+
非活跃用户的记录不需要展示。

-- 1
select activity_date as day,count(distinct user_id) as active_users from Activity
where date(activity_date) between '2019-06-28' and '2019-07-28'
group by activity_date ;
-- 2 ok
select activity_date as day,count(distinct user_id) as active_users from Activity
where datediff('2019-07-27', activity_date) < 30
group by activity_date ;

1777. 每家商店的产品价格

表：Products
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| product_id | int |
| store | enum |
| price | int |
+-------------+---------+
(product_id,store) 是这个表的主键。store 字段是枚举类型，它的取值为以下三种 ('store1', 'store2', 'store3') 。price 是该商品在这家商店中的价格。写出一个 SQL 查询语句，查找每种产品在各个商店中的价格。
可以以 任何顺序 输出结果。查询结果格式如下例所示：
Products 表：
+-------------+--------+-------+
| product_id | store | price |
+-------------+--------+-------+
| 0 | store1 | 95 |
| 0 | store3 | 105 |
| 0 | store2 | 100 |
| 1 | store1 | 70 |
| 1 | store3 | 80 |
+-------------+--------+-------+
Result 表：
+-------------+--------+--------+--------+
| product_id | store1 | store2 | store3 |
+-------------+--------+--------+--------+
| 0 | 95 | 100 | 105 |
| 1 | 70 | null | 80 |
+-------------+--------+--------+--------+
产品 0 的价格在商店 1 为 95 ，商店 2 为 100 ，商店 3 为 105 。产品 1 的价格在商店 1 为 70 ，商店 3 的产品 1 价格为 80 ，但在商店 2 中没有销售。

select 
    product_id,
    sum(case store when 'store1' then price else null end) as 'store1',
    sum(case store when 'store2' then price else null end) as 'store2',
    sum(case store when 'store3' then price else null end) as 'store3'
from Products group by product_id;
-- faster
select
    product_id,
    sum(if(store = 'store1', price, null)) store1,
    sum(if(store = 'store2', price, null)) store2,
    sum(if(store = 'store3', price, null)) store3
from  Products group by 1;

1350. 院系无效的学生

院系表: Departments
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| name | varchar |
+---------------+---------+
id 是该表的主键，该表包含一所大学每个院系的 id 信息，学生表: Students
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| name | varchar |
| department_id | int |
+---------------+---------+
id 是该表的主键，该表包含一所大学每个学生的 id 和他/她就读的院系信息，写一条 SQL 语句以查询那些所在院系不存在的学生的 id 和姓名，可以以任何顺序返回结果，下面是返回结果格式的例子
Departments 表:
+------+--------------------------+
| id | name |
+------+--------------------------+
| 1 | Electrical Engineering |
| 7 | Computer Engineering |
| 13 | Bussiness Administration |
+------+--------------------------+
Students 表:
+------+----------+---------------+
| id | name | department_id |
+------+----------+---------------+
| 23 | Alice | 1 |
| 1 | Bob | 7 |
| 5 | Jennifer | 13 |
| 2 | John | 14 |
| 4 | Jasmine | 77 |
| 3 | Steve | 74 |
| 6 | Luis | 1 |
| 8 | Jonathan | 7 |
| 7 | Daiana | 33 |
| 11 | Madelynn | 1 |
+------+----------+---------------+
结果表:
+------+----------+
| id | name |
+------+----------+
| 2 | John |
| 7 | Daiana |
| 4 | Jasmine |
| 3 | Steve |
+------+----------+
John, Daiana, Steve 和 Jasmine 所在的院系分别是 14, 33, 74 和 77， 其中 14, 33, 74 和 77 并不存在于院系表

select id, name from Students where department_id not in(select id from Departments);
-- faster
select s.id, s.name from Students s left join Departments d
on s.department_id = d.id where d.id is null;