面试题：微信运动

有两个表，朋友关系表（friend）和用户步数表（sport）
1、用户在好友中的排名
2、占据多少个好友的封面

表如图所示：

friend： image.png
sport： image.png

此题的关键点是将自己与自己关联，此处用到union关联friend和sport。
1、用户在好友中的排名

select distinct t3.friend,t3.ranking
from
(
select t2.friend,t2.ranking
from
(
select t1.*,row_number() over(partition by name order by steps desc) ranking
from
(
select a.name,a.friend,b.steps
from friend a join sport b on a.friend = b.name
union
select b.name,b.name,b.steps
from sport b) t1) t2
where t2.friend = t2.name) t3
join friend t4 on t3.friend = t4.name

2、占据多少个好友的封面

select t2.friend,count(distinct name) as frequency
from
(
select t1.*,max(steps) over(partition by name) as max_steps
from
(
select a.name,a.friend,b.steps
from friend a join sport b on a.friend = b.name
union
select b.name,b.name,b.steps
from sport b) t1) t2
where t2.name != t2.friend and t2.steps = t2.max_steps
group by t2.friend