[SQL 코드카타] 175 ~ 177번(HackerRank)

175번 contest-leaderboard

> ?? with 절을 사용하는데 계속 오류가 나서 사용하지 못함

select
    hacker_id, name, sum(score) as sum_score
from
    (select
        sm.hacker_id, hk.name, sm.challenge_id, max(sm.score) as score
    from 
        submissions sm
        join hackers hk
        on sm.hacker_id = hk.hacker_id
    group by
        1,2,3) abc
group by
    1,2
having
    sum(score) > 0
order by
    3 desc, 1

 

176번 sql-projects 

> 이해 x.. 해답 참고

WITH source AS(
    SELECT 
    	 Start_Date, 
    	 End_Date,
         DATEDIFF(day, ROW_NUMBER() OVER (ORDER BY End_Date), End_Date) AS grp
         -- end_day에서 번호순서대로 빼면 순차적인 day들은 값이 같아지는 것을 활용
    FROM Projects
    ),
    group_pj AS(
    SELECT MIN(Start_Date) AS Start_Date,
            MAX(End_Date) AS End_Date
    FROM source
    GROUP BY grp
    -- 그 grp를 그룹화하여 start_data와 end_date를 구한 뒤
    )
    -- 조회
SELECT Start_Date, End_Date
FROM group_pj
ORDER BY DATEDIFF(day, Start_Date, End_Date) ASC , Start_Date ASC;

＞ 

SET @prev_date = NULL, @grp = 0;

SELECT MIN(Start_Date) as Start_Date, MAX(End_Date) as End_Date
FROM (
    SELECT Start_Date, End_Date,
           @grp := IF(@prev_date = Start_Date - INTERVAL 1 DAY, @grp, @grp + 1) as grp,
           @prev_date := Start_Date
    FROM Projects
    ORDER BY Start_Date
) t
GROUP BY grp
ORDER BY COUNT(*) ASC, Start_Date ASC;

 

177번 placements

select
    st.name
from
    friends fr
    join packages pk1
    on fr.friend_id = pk1.id
    join packages pk2
    on fr.id = pk2.id
    join students st
    on fr.id = st.id
where
    pk1.salary > pk2.salary
order by
    pk1.salary