各位大大好
小弟在HackerRank上做題目練習
而我是用mysql
題目如下:
https://www.hackerrank.com/challenges/interviews/problem
小弟的想法是這樣:
先合併所有相同challenge的total_unique_views,total_views,total_submissions,total_accepted_submissions
再合併所有相同college的challenge
最後合併所有相同contest的college
而code如下:
select contest_id,hacker_id,name,all_s,all_as,all_v
from Contests con
join Colleges using (contest_id)
join(
select college_id,sum(tot_s) as all_s,sum(tot_as) as all_as,sum(tot_v) as all_v,sum(tot_uv) as all_uv
from Challenges cha
left join (
select challenge_id,sum(total_views) as tot_v,sum(total_unique_views)as tot_uv
from View_Stats
group by challenge_id
) v
on (cha.challenge_id = v.challenge_id)
left join (
select challenge_id,sum(total_submissions) as tot_s,sum(total_accepted_submissions)as tot_as
from Submission_Stats
group by challenge_id
) s
on (cha.challenge_id = s.challenge_id)
group by college_id
) g
group by con.contest_id, con.hacker_id, con.name
having sum(total_submissions)!=0 or
sum(total_accepted_submissions)!=0 or
sum(total_views)!=0 or
sum(total_unique_views)!=0
order by contest_id;
可是答案都是有誤差
不知道是哪一步思考有問題
上來向各位大大請教
select contest_id, hacker_id, name
, ifnull(sum(tsub),0) as total_submissions
, ifnull(sum(tasu),0) as total_accepted_submissions
, ifnull(sum(tvie),0) as total_views
, ifnull(sum(tuvi),0) as total_unique_views
from Contests cn
join Colleges cl
using (contest_id)
join Challenges ch
using (college_id)
left join (select challenge_id
, sum(total_views) as tvie
, sum(total_unique_views) as tuvi
from View_Stats
group by challenge_id) vs
on (ch.challenge_id = vs.challenge_id)
left join (select challenge_id
, sum(total_submissions) as tsub
, sum(total_accepted_submissions) as tasu
from Submission_Stats
group by challenge_id) ss
on (ch.challenge_id = ss.challenge_id)
group by contest_id, hacker_id, name
order by contest_id;
這個站的題目很爛,沒有靈氣,欄位名字囉哩八唆的,又不清楚.一整個很無聊.
我是站在幫助新人的立場,寫這個很無趣又很冗長.