[SQL 코드카타] 178 ~ 180 (HackerRank)

178번 symmetric-pairs

with test as
(select
    x, y, row_number() over(order by x) as nr
from 
    Functions) -- 1) 구분하고자 row_number 생성
select 
    t1.X, t2.X
from 
    test as t1, test as t2
where 
    t1.nr < t2.nr -- 2) rn을 활용해 비교
    and t1.X = t2.Y 
    and t1.Y = t2.X

 

179번 interviews

> 갑자기 with 절 안 되는게 너무...불편하다ㅠㅠ

select 
    ct.contest_id,
    ct.hacker_id, 
    ct.name, -- 3) contest 기준으로 합계
    sum(total_submissions), 
    sum(total_accepted_submissions), 
    sum(total_views),
    sum(total_unique_views)
from 
    contests ct
    join colleges cl on ct.contest_id = cl.contest_id 
    join challenges ch on  cl.college_id = ch.college_id 
    left join
        (select -- 1) 각 challege별로 sum
            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 ch.challenge_id = vs.challenge_id 
    left join(select 
                challenge_id, -- 2) 각 challenge별로 sum
                sum(total_submissions) as total_submissions,
                sum(total_accepted_submissions) as total_accepted_submissions
              from 
                submission_stats 
                group by challenge_id) sm on ch.challenge_id = sm.challenge_id
group by ct.contest_id, ct.hacker_id, ct.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;

 

180번 15-days-of-learning-sql

- 나만 이해가 안 가나 싶었는데 다른 분들도 마찬가지였다. 어려워서 다른 분의 해답을 참고..

SELECT
    table1.submission_date,
    table1.total_hardworking_hackers,
    table2.hacker_id,
    hackers.name
FROM (
    SELECT 
        sub2.submission_date, 
        COUNT(sub2.hacker_id) AS total_hardworking_hackers
    FROM (
        SELECT 
            s3.submission_date,
            s3.hacker_id,
            COUNT(DISTINCT s4.submission_date)
        FROM submissions s3
        JOIN submissions s4
            ON s4.hacker_id = s3.hacker_id
            AND s4.submission_date <= s3.submission_date
        GROUP BY s3.submission_date, s3.hacker_id
        HAVING COUNT(DISTINCT s4.submission_date) = DATEDIFF(s3.submission_date, '2016-03-01') + 1
    ) AS sub2
    GROUP BY sub2.submission_date
) AS table1
JOIN (
    SELECT 
        sub1.submission_date, 
        MIN(sub1.hacker_id) AS hacker_id
    FROM (
        SELECT
            s1.submission_date,
            s1.hacker_id,
            COUNT(s1.submission_id)
        FROM submissions s1
        GROUP BY s1.submission_date, s1.hacker_id
        HAVING COUNT(s1.submission_id) = (
            SELECT COUNT(s2.submission_id)
            FROM submissions s2
            WHERE s2.submission_date = s1.submission_date
            GROUP BY s2.hacker_id
            ORDER BY COUNT(s2.submission_id) DESC
            LIMIT 1
        )
    ) AS sub1
    GROUP BY sub1.submission_date
) AS table2
ON table1.submission_date = table2.submission_date
JOIN hackers
ON hackers.hacker_id = table2.hacker_id
ORDER BY table1.submission_date