HackerRank題目(sql)解法疑問

sql mysql hackerrank

st474ddr 2020-02-27 13:46:17 ‧ 3433 瀏覽

分享至

各位大大好
小弟在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;

可是答案都是有誤差
不知道是哪一步思考有問題
上來向各位大大請教

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

1 個回答

一級屠豬士

iT邦大師 1 級 ‧ 2020-02-27 15:16:56

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;