0

## HackerRank題目(sql)解法疑問

https://www.hackerrank.com/challenges/interviews/problem

``````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;
``````

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

(我leetcode沒有付費QQ 所以SQL題目比較少)

Leetcode 這些站的題目,在實戰來說,太淺了.