LC 1501. 可以放心投资的国家 (left的运用)

link

select name as country from 
(
select name,sum(duration)/count(duration) as avg from 
(
select t.name,t.id, c1.caller_id, c1.callee_id, c1.duration  from 
(
select c.name, country_code, id from country c join person p on c.country_code=left(p.phone_number,3) 
)t join calls c1 on t.id=c1.caller_id or t.id=c1.callee_id order by t.name
)t1 group by name
)t3 where avg>
(
select sum(duration)/count(duration) as lavg from 
(
select t.name,t.id, c1.caller_id, c1.callee_id, c1.duration  from 
(
select c.name, country_code, id from country c join person p on c.country_code=left(p.phone_number,3) 
)t join calls c1 on t.id=c1.caller_id or t.id=c1.callee_id order by t.name
)t2
)