HackerRank/Oracle interview

[HackerRank/Oracle] interview

๐Ÿ“Œ๋ฌธ์ œ๋งํฌ


join ๋ฌธ์ œ๋‹ค.

view_stats์—๋งŒ ๋ฐ์ดํ„ฐ๊ฐ€ ์žˆ๊ฑฐ๋‚˜ submission_stats์—๋งŒ ๋ฐ์ดํ„ฐ๊ฐ€ ์žˆ๋Š”๊ฒฝ์šฐ๋ฅผ ์œ„ํ•ด outer join ์‹คํ–‰


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
select con.contest_id,
        con.hacker_id, 
        con.name, 
        sum(total_submissions), 
        sum(total_accepted_submissions), 
        sum(total_views), sum(total_unique_views)
from contests con 
inner join colleges col on con.contest_id = col.contest_id 
inner join challenges cha on  col.college_id = cha.college_id 
left outer join (select challenge_id, 
           sum(total_views) as total_views, 
           sum(total_unique_views) as total_unique_views
           from view_stats 
           group by challenge_id) vs on cha.challenge_id = vs.challenge_id 
left outer join (select challenge_id, 
           sum(total_submissions) as total_submissions, 
           sum(total_accepted_submissions) as total_accepted_submissions 
           from submission_stats 
           group by challenge_id) ss on cha.challenge_id = ss.challenge_id
group by con.contest_id, con.hacker_id, con.name
having sum(total_submissions) + sum(total_accepted_submissions) + sum(total_views) + sum(total_unique_views) >0
order by contest_id;