HackerRank-My SQL

Prepare > SQL > Advanced Join > 15 Days of Learning SQL

stem_sw 2023. 6. 19. 22:51

https://www.hackerrank.com/challenges/15-days-of-learning-sql/problem?isFullScreen=true

 

15 Days of Learning SQL | HackerRank

find users who submitted a query every day.

www.hackerrank.com

 

문제

Julia conducted a 15days of learning SQL contest. The start date of the contest was March 01, 2016 and the end date was March 15, 2016.

Write a query to print total number of unique hackers who made at least 1 submission each day (starting on the first day of the contest), and find the hacker_id and name of the hacker who made maximum number of submissions each day. If more than one such hacker has a maximum number of submissions, print the lowest hacker_id. The query should print this information for each day of the contest, sorted by the date.

 

=> 15일간 SQL콘테스트가 있었다. 16.03.01 시작했다. 콘테스트의 첫 날 부터 매일 1개 이상 제출한 해커의 수, 각각의 날에 최다 제출자의 ID와 NAME을 출력하는 쿼리문을 써라. 최다 제출수가 같으면 ID로 뽑고, 날짜로 정렬해라.

 

 

 

 

코드

SELECT
    submission_date,
    (
    SELECT COUNT(distinct hacker_id)  
    FROM SUBMISSIONS AS S2  
    WHERE S2.submission_date = S1.submission_date 
        AND DATEDIFF(S1.submission_date , '2016-03-01') = (
            SELECT COUNT(distinct S3.submission_date)  
             FROM Submissions AS S3 
             WHERE S3.hacker_id = S2.hacker_id 
                AND S3.submission_date < S1.submission_date) 
    ),  
    (
    SELECT hacker_id
    FROM submissions AS S2 
    WHERE S2.submission_date = S1.submission_date 
    GROUP BY hacker_id 
    ORDER BY COUNT(submission_id) DESC , hacker_id limit 1
    ) AS shit,
    (
    SELECT name FROM hackers WHERE hacker_id = shit
    )

FROM (SELECT DISTINCT(submission_date) from SUBMISSIONS) AS S1

GROUP BY submission_date;
더보기

OUTPUT

2016-03-01 112 81314 Denise 
2016-03-02 59 39091 Ruby 
2016-03-03 51 18105 Roy 
2016-03-04 49 533 Patrick 
2016-03-05 49 7891 Stephanie 
2016-03-06 49 84307 Evelyn 
2016-03-07 35 80682 Deborah 
2016-03-08 35 10985 Timothy 
2016-03-09 35 31221 Susan 
2016-03-10 35 43192 Bobby 
2016-03-11 35 3178 Melissa 
2016-03-12 35 54967 Kenneth 
2016-03-13 35 30061 Julia 
2016-03-14 35 32353 Rose 
2016-03-15 35 27789 Helen

 

 

 

 

노트

1. 정답의 전체적인 구조부터 파악

SELECT
[고유한 제출일],
[성실한 인원 수]에 대한 쿼리,
[제출일 별 최다 제출자 ID]에 대한 쿼리,
[제출일 별 최다 제출자 NAME]에 대한 쿼리
FROM SUBMISSINOS
  • 서브쿼리를 SELECT 에 넣어서 조회할 대상을 다양하게 설정할 수 있음

 

 

2. 고유한 제출일

SELECT
    submission_date

FROM (SELECT DISTINCT(submission_date) from SUBMISSIONS) AS S1

GROUP BY submission_date;
  • 내가 쓴 답이 아니라 왜 이렇게 S1을 저렇게 설정했는지는 모름
  • S1은 고유한 제출일을 조회하는 전체 컬럼으로서 기억해두자
더보기

OUTPUT

2016-03-01 
2016-03-02 
2016-03-03 
2016-03-04 
2016-03-05 
2016-03-06 
2016-03-07 
2016-03-08 
2016-03-09 
2016-03-10 
2016-03-11 
2016-03-12 
2016-03-13 
2016-03-14 
2016-03-15

 

 

3. [성실한 인원 수]에 대한 쿼리

SELECT COUNT(distinct hacker_id)  # 고유한 해커ID
    FROM SUBMISSIONS AS S2  
    WHERE S2.submission_date = S1.submission_date  # 앞서 조회한 날짜에 해당하는 결과값을 조회해야함
        AND
        DATEDIFF(S1.submission_date , '2016-03-01') = (       # 제출일 - 시작일
            SELECT COUNT(distinct S3.submission_date)  # 이때까지 제출한 날짜의 수
             FROM Submissions AS S3 
             WHERE S3.hacker_id = S2.hacker_id    # 조회하려는 해커ID(위의 고유한 해커ID)에 대해 일치시킴
                AND S3.submission_date < S1.submission_date) # 6.10일에서 보는 중인데 6.12일 데이터가 끼어들지 못하게끔
  • 조회하려는 [해커ID(위의 고유한 해커ID)가 이때까지 제출한 날짜의 수] 와 [제출일 - 시작일] 의 값이 같으면 되는거 아니야??
더보기

INPUT

SELECT
    submission_date,
    (
    SELECT COUNT(distinct hacker_id)  
    FROM SUBMISSIONS AS S2  
    WHERE S2.submission_date = S1.submission_date 
        AND DATEDIFF(S1.submission_date , '2016-03-01') = (
            SELECT COUNT(distinct S3.submission_date)  
             FROM Submissions AS S3 
             WHERE S3.hacker_id = S2.hacker_id 
                AND S3.submission_date < S1.submission_date) 
    )


FROM (SELECT DISTINCT(submission_date) from SUBMISSIONS) AS S1

GROUP BY submission_date;

OUTPUT

2016-03-01 112 
2016-03-02 59 
2016-03-03 51 
2016-03-04 49 
2016-03-05 49 
2016-03-06 49 
2016-03-07 35 
2016-03-08 35 
2016-03-09 35 
2016-03-10 35 
2016-03-11 35 
2016-03-12 35 
2016-03-13 35 
2016-03-14 35 
2016-03-15 35

 

 

4. [제출일 별 최다 제출자 ID]에 대한 쿼리

SELECT hacker_id
FROM submissions AS S2 
WHERE S2.submission_date = S1.submission_dat # 앞서 조회한 날짜에 해당하는 결과값을 조회해야함
GROUP BY hacker_id 
ORDER BY COUNT(submission_id) DESC , hacker_id limit 1
  • 이제 이정도는 뚝딱 해석 가능
더보기

INPUT

SELECT
    submission_date,
    (
    SELECT COUNT(distinct hacker_id)  
    FROM SUBMISSIONS AS S2  
    WHERE S2.submission_date = S1.submission_date 
        AND DATEDIFF(S1.submission_date , '2016-03-01') = (
            SELECT COUNT(distinct S3.submission_date)  
             FROM Submissions AS S3 
             WHERE S3.hacker_id = S2.hacker_id 
                AND S3.submission_date < S1.submission_date) 
    ),  
    (
    SELECT hacker_id
    FROM submissions AS S2 
    WHERE S2.submission_date = S1.submission_date 
    GROUP BY hacker_id 
    ORDER BY COUNT(submission_id) DESC , hacker_id limit 1
    )
    
FROM (SELECT DISTINCT(submission_date) from SUBMISSIONS) AS S1

GROUP BY submission_date;

 OUTPUT

2016-03-01 112 81314 
2016-03-02 59 39091 
2016-03-03 51 18105 
2016-03-04 49 533 
2016-03-05 49 7891 
2016-03-06 49 84307 
2016-03-07 35 80682 
2016-03-08 35 10985 
2016-03-09 35 31221 
2016-03-10 35 43192 
2016-03-11 35 3178 
2016-03-12 35 54967 
2016-03-13 35 30061 
2016-03-14 35 32353 
2016-03-15 35 27789

 

 

5. [제출일 별 최다 제출자 NAME]에 대한 쿼리

  • 앞서 조회한 "제출일 별 최다 제출자ID"를 보고 이름만 가져오면 됨
 (
    SELECT hacker_id
    FROM submissions AS S2 
    WHERE S2.submission_date = S1.submission_date 
    GROUP BY hacker_id 
    ORDER BY COUNT(submission_id) DESC , hacker_id limit 1
    ) AS shit, # 별칭 붙여주기
  • 4번 쿼리에 별칭을 붙여준다
SELECT name FROM hackers WHERE hacker_id = shit
  • 이름 가져옴

 

 

6. 종합

SELECT
    submission_date,
    (
    SELECT COUNT(distinct hacker_id)  
    FROM SUBMISSIONS AS S2  
    WHERE S2.submission_date = S1.submission_date 
        AND DATEDIFF(S1.submission_date , '2016-03-01') = (
            SELECT COUNT(distinct S3.submission_date)  
             FROM Submissions AS S3 
             WHERE S3.hacker_id = S2.hacker_id 
                AND S3.submission_date < S1.submission_date) 
    ),  
    (
    SELECT hacker_id
    FROM submissions AS S2 
    WHERE S2.submission_date = S1.submission_date 
    GROUP BY hacker_id 
    ORDER BY COUNT(submission_id) DESC , hacker_id limit 1
    )
    

FROM (SELECT DISTINCT(submission_date) from SUBMISSIONS) AS S1

GROUP BY submission_date;

 

 

 

 

참조

 

15 days of learning SQL

www.hackerrank.com/challenges/15-days-of-learning-sql/problem 15 Days of Learning SQL | HackerRank find users who submitted a query every day. www.hackerrank.com 해결 방법 : 서브 쿼리 JOIN 현재까지 매일 submission하고 있는 사람을 구

profailure.tistory.com

 

 

 

 

 

 

 

 

 

 

더보기

여기는 내가 고생했던 흔적들....ㅋ 그냥 남겨보고 싶었어

제출일               =   그냥 가능
매일제출한인원 수       =  ...?
일일 최다 제출 해커    =   일자별 해커별 count(submission_id)

SELECT 
SB.submission_date,
SB.hacker_id,
COUNT(SB.submission_id) AS k1

FROM SUBMISSIONS AS SB
    LEFT JOIN HACKERS AS H ON SB.hacker_id = H.hacker_id
    
GROUP BY SB.submission_date, SB.hacker_id

ORDER BY SB.submission_date, SB.hacker_id
;
SELECT 
SB.submission_date,
SB.hacker_id,
COUNT(SB.submission_id) AS k1

FROM SUBMISSIONS AS SB
    LEFT JOIN HACKERS AS H ON SB.hacker_id = H.hacker_id
    
GROUP BY SB.submission_date, SB.hacker_id

ORDER BY SB.submission_date, SB.hacker_id
;

매일 제출한 인원 파악

인원별 count(submission_date) : 띄엄띄엄 낸건 어쩔건데

case

when 날짜별 제출 인원 리스트에 포함 , 1?? 

 

submission_date - start_date =  인원별 count(submission_date) from submission as s1 where s1.submission_date <= s2.submissiond_date

 

SELECT 
S1.hacker_id,
DATEDIFF(S1.submission_date, '2016.03.01')

FROM SUBMISSIONS AS S1
WHERE 
    DATEDIFF(S1.submission_date, '2016.03.01') =  # 제출일-시작일 값이 이때까지의 제출일 수 와 같으면?
        (SELECT COUNT(DISTINCT(S2.submission_date)) FROM SUBMISSIONS AS S2
        WHERE S2.submission_date <= S1.submission_date AND S1.hacker_id = S2.hacker_id
        GROUP BY hacker_id)
SELECT DATEDIFF(S2.submission_date, '2016-03-01')
FROM SUBMISSIONS AS S2
WHERE DATEDIFF(S2.submission_date, '2016-03-01') =
    (SELECT COUNT(DISTINCT(S3.submission_id)) FROM SUBMISSIONS AS S3
    WHERE S3.submission_date <= S2.submission_date AND S3.hacker_id = S2.hacker_id
    GROUP BY S3.hacker_id)
SELECT
S1.submission_date,
SELECT K1 FROM (
    SELECT DATEDIFF(S1.submission_date, '2016-03-01') AS K1 FROM SUBMISSIONS AS S2
        WHERE DATEDIFF(S1.submission_date, '2016-03-01') <=
        (SELECT COUNT(DISTINCT(S3.submission_id)) FROM SUBMISSIONS AS S3
            WHERE S1.
    
    )
FROM SUBMISSIONS AS S1
    LEFT JOIN HACKERS AS H ON S1.hacker_id  = H.hacker_id
GROUP BY S1.submission_date
ORDER BY S1.submission_date
SELECT
S.submission_date AS submission_date,
S.hacker_id AS hacker_id,
H.name AS name,
S.submission_id AS submission_id,
S.score AS Score

FROM SUBMISSIONS AS S
    LEFT JOIN HACKERS AS H ON S.hacker_id  = H.hacker_id
ORDER BY S.submission_date