力扣数据库进阶50--No.1501题解

官方难度中等，体感中等较繁琐，值得耐心思考逐步优化。

先用union all处理通话表，再逐步处理人员表的字符串，并联表获取人员对应国家。

最后嵌套子查询，来找到高于全球平均值的国家记录。

with tmp1 as
((select caller_id as call_id,duration
from Calls)
union all
(select callee_id as call_id,duration
from Calls))

,tmp2 as
(select id,Country.name as name
from Person join Country
on left(Person.phone_number,3) = Country.country_code)

,tmp3 as
(select call_id,duration,name
from tmp1 left join tmp2
on tmp1.call_id = tmp2.id)

select name as country
from tmp3
group by name
having avg(duration) > (select avg(duration) from tmp3)