We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
with cte1 as (
select a.contest_id s, a.hacker_id t, a.name u,
sum(d.total_submissions) w,
sum(d.total_accepted_submissions) x from contests a
left join colleges b on a.contest_id = b.contest_id
left join challenges c on b.college_id = c.college_id
left join Submission_Stats d on c.challenge_id = d.challenge_id
group by a.contest_id, a.hacker_id, a.name),
cte2 as (
select e.contest_id l, e.hacker_id m, e.name n,
sum(h.total_views) y,
sum(h.total_unique_views) z from contests e
left join colleges f on e.contest_id = f.contest_id
left join challenges g on f.college_id = g.college_id
left join view_stats h on g.challenge_id = h.challenge_id
group by e.contest_id, e.hacker_id, e.name
)
select cte1.s, cte1.t, cte1.u, cte1.w, cte1.x, cte2.y, cte2.z from
cte1, cte2
where cte1.s = cte2.l
and cte1.t = cte2.m
and cte1.u = cte2.n
and cte1.w+cte1.x+cte2.y+cte2.z>0
order by cte1.s asc
Cookie support is required to access HackerRank
Seems like cookies are disabled on this browser, please enable them to open this website
Interviews
You are viewing a single comment's thread. Return to all comments →
Simple and faster query as below
with cte1 as ( select a.contest_id s, a.hacker_id t, a.name u,
sum(d.total_submissions) w, sum(d.total_accepted_submissions) x from contests a left join colleges b on a.contest_id = b.contest_id left join challenges c on b.college_id = c.college_id left join Submission_Stats d on c.challenge_id = d.challenge_id
group by a.contest_id, a.hacker_id, a.name), cte2 as ( select e.contest_id l, e.hacker_id m, e.name n,
sum(h.total_views) y, sum(h.total_unique_views) z from contests e left join colleges f on e.contest_id = f.contest_id left join challenges g on f.college_id = g.college_id left join view_stats h on g.challenge_id = h.challenge_id
group by e.contest_id, e.hacker_id, e.name )
select cte1.s, cte1.t, cte1.u, cte1.w, cte1.x, cte2.y, cte2.z from cte1, cte2 where cte1.s = cte2.l and cte1.t = cte2.m and cte1.u = cte2.n and cte1.w+cte1.x+cte2.y+cte2.z>0 order by cte1.s asc