网易互娱 数分笔试 SQL题
A了两题，还有一个没写
第一题
select
count(a.role_id),
round(avg(match_cnt), 4),
count(case when b.role_id is not null then b.role_id end),
round(avg(case when b.role_id is not null then b.battle end), 4)
from
(
select
role_id,
count(1) as match_cnt
from
success
group by
role_id
) a
left join
(
select
role_id,
count(1) as battle_cnt
from
battle
group by
role_id
) b
on a.role_id = b.role_id
第二题
select
a.new_rank,
count(distinct a.role_id)
from
(
select
role_id,
new_rank,
t_when
) a
left join
(
select
role_id,
max(t_when) as tw
from
battle
group by
role_id
) b
on a.role_id = b.role_id
and a.t_when = b.tw
where
b.role_id is not null
group by
a.new_rank
投递中国邮政储蓄银行等公司7个岗位 >
TP-LINK二面40人在聊