【SQL高频练习带刷】day19：补充习题

题目一：可以放心投资的国家

题目要求：

一家电信公司想要投资新的国家。该公司想要投资的国家是:  该国的平均通话时长要严格地大于全球平均通话时长。

写一个解决方案,  找到所有该公司可以投资的国家。

返回的结果表 无顺序要求。

1501. 可以放心投资的国家 - 力扣（LeetCode）

表结构：

运行结果示例：

思路：

        本题首先查找出每一次通话的通话人、时长和国家码，其中国家码就是通话人电话号码的前三位，因此我们还需要将通话表和人员表进行连接，查询到相关数据。查询出结果后，我们使用国家码字段将结果集与国家表进行连接，找到按照国家分类的每个国家的平均通话时长大于总通话时长的国家名。

运行代码示例：

with temp as (    
    select caller_id as callee,duration ,left(phone_number,3) as phone
    from Calls c 
    join Person p
    on c.caller_id = p.id
    union all 
    select callee_id as callee ,duration,left(phone_number,3) as phone
    from Calls c 
    join Person p 
    on c.callee_id = p.id
)

select c.name as country 
from temp t 
join Country c 
on t.phone = c.country_code
group by country_code
having sum(duration) / count(*)  > ( select avg(duration) from Calls)

题目二：树节点

题目要求：

树中的每个节点可以是以下三种类型之一：

• "Leaf"：节点是叶子节点。
• "Root"：节点是树的根节点。
• "lnner"：节点既不是叶子节点也不是根节点。

编写一个解决方案来报告树中每个节点的类型。

以 任意顺序 返回结果表。

608. 树节点 - 力扣（LeetCode）

表结构：

运行结果示例：

思路：

        本题我们可以用条件判断的思想，分情况讨论三种可能的情况，无论是直接使用case when或者if判断还是使用where筛选再用union合并结果集都可以。需要注意的是如果使用union合并结果集的方法，那么每一个查询的where条件里都要有p_id is not null的条件，因为每一次查询是独立的，需要我们手动去完成。其次还要注意对于leaf叶子节点的判断条件，我们不能直接使用not in进行判断，因为not in的就是查询集合的补集，当我们的集合中存在null值时，补集为全集，where id not in (select p_id from Tree)这句话会导致判断结果均为false。因此判断叶子节点时，我们在上述语句的基础上，还要加上不为空的限定，去除集合中的null值数据，即select p_id from Tree where p_id is not null。

运行代码示例：

select id, 'Root' as type
from Tree
where p_id is null
union
select id, 'Leaf'as type
from Tree
where id not in (select p_id from Tree where p_id is not null) and p_id is not null
union 
select id, 'Inner'as type
from Tree
where id in (select p_id from Tree) and p_id is not null

题目三：游戏玩法分析III

题目要求：

编写一个解决方案，同时报告每组玩家和日期，以及玩家到 目前为止 玩了多少游戏。也就是说，玩家在该日期之前所玩的游戏总数。详细情况请查看示例。

以 任意顺序 返回结果表。

534. 游戏玩法分析 III - 力扣（LeetCode）

表结构：

运行结果示例：

思路：

        使用窗口函数计算游戏总数之和即可。

运行代码示例：

select player_id,event_date,  
sum(games_played) over(partition by player_id order by event_date) as games_played_so_far
from Activity

题目四：大满贯数量

题目要求：

编写解决方案，找出每一个球员赢得大满贯比赛的次数。结果不包含没有赢得比赛的球员的ID 。

结果集 无顺序要求 。

1783. 大满贯数量 - 力扣（LeetCode）

表结构：

运行结果示例：

思路：

        我们使用“行转列”的思想，将比赛奖项表进行转换，得到不同比赛的得奖记录，再连接两表计算每组球员的得奖总数即可。

运行代码示例：

with temp as (
    select Wimbledon as flag from Championships
    union all
    select Fr_open as flag from Championships
    union all
    select US_open as flag from Championships
    union all
    select Au_open as flag from Championships
)

select player_id ,player_name,count(*) grand_slams_count 
from Players p 
join temp t
on p.player_id = t.flag
group by player_id,player_name

题目五：应该被禁止的Leetflex帐户

题目要求：

编写解决方案，查找那些应该被禁止的Leetflex帐户编号 account_id 。 如果某个帐户在某一时刻从两个不同的网络地址登录了，则这个帐户应该被禁止。

可以以 任何顺序 返回结果。

表结构：

运行结果示例：

思路：

        自连接找到相同账户数，不同登录账号的数据，需要注意的是对于时间的判断。我们要找的是当一个账号未退出登录时，另一个账号已经登陆了，我们假设两个时间段不相交，也就是start1>end2 || start2>end1，那么我们取反即可得到相交的情况，也就是 start1<=end2 && start2<=end1。

运行代码示例：

select distinct t1.account_id
from LogInfo t1
join LogInfo t2 
on t1.account_id = t2.account_id 
and t1.login <= t2.logout 
and t1.logout >= t2.login 
and t1.ip_address != t2.ip_address