游戏玩法分析 IV

Table: Activity

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

编写一个 SQL 查询，报告在首次登录的第二天再次登录的玩家的比率，四舍五入到小数点后两位。换句话说，您需要计算从首次登录日期开始至少连续两天登录的玩家的数量，然后除以玩家总数。

查询结果格式如下所示：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Activity table:
+-----------+-----------+------------+--------------+
| 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-02 | 0            |
| 3         | 4         | 2018-07-03 | 5            |
+-----------+-----------+------------+--------------+

Result table:
+-----------+
| fraction  |
+-----------+
| 0.33      |
+-----------+
只有 ID 为 1 的玩家在第一天登录后才重新登录，所以答案是 1/3 = 0.33

CodeDemo

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 别人想的解决方式, 判断本次登录时间和上一次的登录时间是否差1, 但是为了加强代码的健壮性, 还需要给出排名(根据日期排名, 当前时间必须排名第二)
select
    round(
        (select
            count(*)
        from (
            select
                player_id,
                event_date,
                lag(event_date) over(partition by player_id order by event_date) as next_date,
                rank() over(partition by player_id order by event_date) as rk
            from Activity) as tmp where datediff(event_date, next_date) = 1 and rk = 2 )
            /
            (select count(distinct player_id) from Activity), 2
    ) as fraction

至少有5名直接下属的经理

表: Employee

1
2
3
4
5
6
7
8
9
10
11
12
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| name        | varchar |
| department  | varchar |
| managerId   | int     |
+-------------+---------+
在 SQL 中，id 是该表的主键列。
该表的每一行都表示雇员的名字、他们的部门和他们的经理的id。
如果managerId为空，则该员工没有经理。
没有员工会成为自己的管理者。

查询至少有5名直接下属的经理 。

以 任意顺序 返回结果表。

查询结果格式如下所示。

示例 1:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
输入: 
Employee 表:
+-----+-------+------------+-----------+
| id  | name  | department | managerId |
+-----+-------+------------+-----------+
| 101 | John  | A          | None      |
| 102 | Dan   | A          | 101       |
| 103 | James | A          | 101       |
| 104 | Amy   | A          | 101       |
| 105 | Anne  | A          | 101       |
| 106 | Ron   | B          | 101       |
+-----+-------+------------+-----------+
输出: 
+------+
| name |
+------+
| John |
+------+

CodeDemo

1
2
3
4
5
6
7
8
9
# e1为员工表, e2为经理表
select
    name 
from (
    select
    e2.name as name,
    count(*) as cnt
from Employee e1, Employee e2 where e1.managerId = e2.id group by e2.id
) as tmp where cnt >= 5

判断三角形

首先知道三角形成立的两个条件,要不然没法做题

  • 两边之和大于第三边
  • 两边之差小于第三边

表: Triangle

1
2
3
4
5
6
7
8
9
+-------------+------+
| Column Name | Type |
+-------------+------+
| x           | int  |
| y           | int  |
| z           | int  |
+-------------+------+
(x, y, z)是该表的主键列。
该表的每一行包含三个线段的长度。

写一个SQL查询，每三个线段报告它们是否可以形成一个三角形。

以 任意顺序 返回结果表。

查询结果格式如下所示。

示例 1:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
输入: 
Triangle 表:
+----+----+----+
| x  | y  | z  |
+----+----+----+
| 13 | 15 | 30 |
| 10 | 20 | 15 |
+----+----+----+
输出: 
+----+----+----+----------+
| x  | y  | z  | triangle |
+----+----+----+----------+
| 13 | 15 | 30 | No       |
| 10 | 20 | 15 | Yes      |
+----+----+----+----------+

CodeDemo

1
2
3
4
5
6
7
8
# 首先想到的是if嵌套来判断, 然后开窗
# 三角形的成立条件是: 1, 两边之和大于第三边, 2, 两边之差小于第三边
select
    x,
    y,
    z,
    if(x+y>z and abs(x-y)<z ,'Yes',if(x+z>y and abs(x-z)<y,'Yes',if(y+z>x and abs(y-z)<x,'Yes', 'No'))) as triangle
from Triangle