【力扣 | SQL题 | 每日3题】力扣1107，1112， 1077

今天三道mid题都可以用窗口函数轻松秒杀。

1. 力扣1107：每日新用户统计

1.1 题目：

Traffic 表：

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| user_id       | int     |
| activity      | enum    |
| activity_date | date    |
+---------------+---------+
该表可能有重复的行。
activity 列是 ENUM 类型，可能取 ('login', 'logout', 'jobs', 'groups', 'homepage') 几个值之一。

编写解决方案，找出从今天起最多 90 天内，每个日期该日期首次登录的用户数。假设今天是 2019-06-30 。

以 任意顺序 返回结果表。

结果格式如下所示。

示例 1：

输入：
Traffic 表：
+---------+----------+---------------+
| user_id | activity | activity_date |
+---------+----------+---------------+
| 1       | login    | 2019-05-01    |
| 1       | homepage | 2019-05-01    |
| 1       | logout   | 2019-05-01    |
| 2       | login    | 2019-06-21    |
| 2       | logout   | 2019-06-21    |
| 3       | login    | 2019-01-01    |
| 3       | jobs     | 2019-01-01    |
| 3       | logout   | 2019-01-01    |
| 4       | login    | 2019-06-21    |
| 4       | groups   | 2019-06-21    |
| 4       | logout   | 2019-06-21    |
| 5       | login    | 2019-03-01    |
| 5       | logout   | 2019-03-01    |
| 5       | login    | 2019-06-21    |
| 5       | logout   | 2019-06-21    |
+---------+----------+---------------+
输出：
+------------+-------------+
| login_date | user_count  |
+------------+-------------+
| 2019-05-01 | 1           |
| 2019-06-21 | 2           |
+------------+-------------+
解释：
请注意，我们只关心用户数非零的日期.
ID 为 5 的用户第一次登陆于 2019-03-01，因此他不算在 2019-06-21 的的统计内。

1.2 思路：

首次登录=>排名第一=>窗口函数

row_number函数。

1.3 题解：

-- 首次登录=>排名第一=>窗口函数
-- 先得到activity全是login的记录
with tep as (
    select user_id, activity , activity_date, row_number() over (partition by user_id order by activity_date) ranks
    from Traffic
    where activity = 'login'
)
-- 然后将用户首次登录（排名第一）的过滤出来
-- 再activity_date分组查询即可
select activity_date login_date, count(*) user_count
from tep
where ranks = 1
group by activity_date
having activity_date >= '2019-04-01'

2. 力扣1112：每位学生的最高成绩

2.1 题目;

表：Enrollments

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| student_id    | int     |
| course_id     | int     |
| grade         | int     |
+---------------+---------+
(student_id, course_id) 是该表的主键（具有唯一值的列的组合）。
grade 不会为 NULL。

编写解决方案，找出每位学生获得的最高成绩和它所对应的科目，若科目成绩并列，取 course_id 最小的一门。查询结果需按 student_id 增序进行排序。

查询结果格式如下所示。

示例 1：

输入：
Enrollments 表：
+------------+-------------------+
| student_id | course_id | grade |
+------------+-----------+-------+
| 2          | 2         | 95    |
| 2          | 3         | 95    |
| 1          | 1         | 90    |
| 1          | 2         | 99    |
| 3          | 1         | 80    |
| 3          | 2         | 75    |
| 3          | 3         | 82    |
+------------+-----------+-------+
输出：
+------------+-------------------+
| student_id | course_id | grade |
+------------+-----------+-------+
| 1          | 2         | 99    |
| 2          | 2         | 95    |
| 3          | 3         | 82    |
+------------+-----------+-------+

2.2 思路：

题目看到最高=>排名第一=>窗口函数

where ranks = 1过滤得到第一名。

2.3 题解：

-- 题目看到最高=>排名第一=>窗口函数
with tep as (
    select student_id, course_id, grade, rank() over (partition by student_id order by grade desc, course_id, student_id) ranks
    from Enrollments
)

select student_id, course_id, grade
from tep
where ranks = 1

3. 力扣1077：项目员工3

3.1 题目：

项目表 Project：

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| project_id  | int     |
| employee_id | int     |
+-------------+---------+
(project_id, employee_id) 是这个表的主键（具有唯一值的列的组合）
employee_id 是员工表 Employee 的外键（reference 列）
该表的每一行都表明具有 employee_id 的雇员正在处理具有 project_id 的项目。

员工表 Employee：

+------------------+---------+
| Column Name      | Type    |
+------------------+---------+
| employee_id      | int     |
| name             | varchar |
| experience_years | int     |
+------------------+---------+
employee_id 是这个表的主键（具有唯一值的列）
该表的每一行都包含一名雇员的信息。

编写解决方案，报告在每一个项目中 经验最丰富 的雇员是谁。如果出现经验年数相同的情况，请报告所有具有最大经验年数的员工。

返回结果表 无顺序要求 。

结果格式如下示例所示。

示例 1：

输入：
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                |
+-------------+--------+------------------+
输出：
+-------------+---------------+
| project_id  | employee_id   |
+-------------+---------------+
| 1           | 1             |
| 1           | 3             |
| 2           | 1             |
+-------------+---------------+
解释：employee_id 为 1 和 3 的员工在 project_id 为 1 的项目中拥有最丰富的经验。在 project_id 为 2 的项目中，employee_id 为 1 的员工拥有最丰富的经验。

3.2 思路：

-- 经验最丰富=>经验第一=>一眼窗口函数

where ranks = 1筛选出第一名。

3.3 题解：

-- 经验最丰富=>经验第一=>一眼窗口函数

with tep as (
    select project_id, p.employee_id employee_id, dense_rank() over (partition by project_id order by experience_years desc) ranks
    from Project p
    join Employee e 
    on p.employee_id  = e.employee_id 
)

select project_id, employee_id
from tep
where ranks = 1