2024-02-06

SQL 코드카타

 

Top Competitors(SQL)(여러번의 left join, from 서브쿼리, group by, having, count) (이해 잘 못해서 오래 푼 문제)

Top Competitors | HackerRank

만점으로 점수 받은 challenge가 두개이상인 hacker의 id와 이름을 출력하는데 만점을 획득한 챌린지 수로 내림차순하고 id를 기준으로 오름차순하여 출력하는 문제이다.

SELECT 
	s.hacker_id, COUNT(DISTINCT s.challenge_id), COUNT(s.challenge_id)
FROM
	(SELECT
		s1.hacker_id, s1.challenge_id, s1.score 
	FROM
		submissions s1) AS s
GROUP BY s.hacker_id
HAVING COUNT(DISTINCT s.challenge_id) != COUNT(s.challenge_id)

SELECT 
	s.hacker_id , s.challenge_id, c.difficulty_level
FROM 
	submissions s
LEFT JOIN 
	challenges c ON s.challenge_id = c.challenge_id

challenge는 챌린지id와 그 챌린지를 처음 만든 사람의 id와 어려움 레벨로 구성된 테이블이라

join할때 조건으로 hacker_id로 하면 안된다(만들지 않은 사람들도 풀었기 때문에 그리고 혹시 만든 사람도 풀었나 싶어서 확인해보니 전부는 아니지만 일부 풀었는 사람도 있는 듯하였다)

아래와 같은 식으로 컬럼이 되어있는데

SELECT 
	s.hacker_id, s.challenge_id, s.submission_id
FROM
	submission s
GROUP BY 
	s.hacker_id, s.challenge_id 
ORDER BY 
	s.hacker_id, s.challenge_id;

# SQL Error [1055] [42000]: Expression #3 of SELECT list is not in GROUP BY 
#clause and contains nonaggregated column 'codetest.s.submission_id' 
#which is not functionally dependent on columns in GROUP BY clause; 
#this is incompatible with sql_mode=only_full_group_by

위와 같이 group by해줄려면 에러가 난다
전엔 컬럼 순서대로 해주지 않아도 되는 경우가 있었던 것 같은데 또 한동안 겪을 일 없었다고 헷깔린다..ㅠ

SELECT tot.hacker_id, tot.name
FROM
    (SELECT 
        s.hacker_id, h.name, s.challenge_id
    FROM 
        submissions s
    LEFT JOIN 
        challenges c ON s.challenge_id = c.challenge_id 
    LEFT JOIN 
        difficulty d ON c.difficulty_level = d.difficulty_level
    LEFT JOIN 
        hackers h ON s.hacker_id = h.hacker_id 
    WHERE
        s.score = d.score) tot
GROUP BY 
    tot.hacker_id, tot.name
HAVING
	COUNT(tot.challenge_id) > 1
ORDER BY 
    COUNT(tot.challenge_id) DESC, tot.hacker_id

위의 이유로 서브쿼리로 하지 않고 바로 group by를 해면 에러가 나서 순서바꿔서 서브쿼리를 통해 테이블 만들어주고 거기서 group by를 해주었다

쿼리에 대해 간단히 설명하면 먼저 각 연결되는 컬럼들로 join해주었고, 그 뒤 제출한 점수가 만점을 받았는지 조건을 걸기 위하여 difficulty의 점수와 같을 때로 조건을 걸어주었다 그뒤 hacker_id와 이름, challenge_id를 출력하도록한 쿼리를 서브쿼리로 써서 hacker_id와 이름으로 그룹핑해준 뒤 각 그룹에 대해 (만점을 달성한)challenge의 수가 2개 이상인 hacker에 대해서만 id와 이름에 대해 출력해주는데 challenge의 갯수대로 내림차순하고 hacke_id로 오름차순해주었다

오늘은 프로젝트 관련해서 한다고 시간을 거의 못썼느데 컨디션도 안좋은 상태로 이해가 잘안되는 문제를 만나 해결한다고 너무 오랜 시간을 쓰다가 체력과 시간을 모두 써버려 오늘은 한문제 밖에 풀지 못하였다

못푼 2문제는 추가로 시간 여유될 때 더 풀도록 하겠다