iT邦幫忙

0

HackerRank題目(sql)解法疑問

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

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

1 個回答

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;

https://ithelp.ithome.com.tw/upload/images/20200227/20050647UzHXKP3itP.png

這個站的題目很爛,沒有靈氣,欄位名字囉哩八唆的,又不清楚.一整個很無聊.
我是站在幫助新人的立場,寫這個很無趣又很冗長.

st474ddr iT邦新手 5 級 ‧ 2020-02-27 15:21:03 檢舉

感謝大大的建議及回答
但我快解完他題目了哈哈
我也是跟大大有相同的感受題目不清
(我leetcode沒有付費QQ 所以SQL題目比較少)
  
大大寫的解答
我了解您的作法
不過大大我想知道我的作法為何不行
因為我覺得我的想法應該也沒錯才對@@

你自己再慢慢追蹤啦. 自己體會出來,會比較好.

Leetcode 這些站的題目,在實戰來說,太淺了.
而且有夠無聊.哪個白爛欄位取這樣名稱,要負責掃公共區域
一星期.

我要發表回答

立即登入回答