Hacker Rank 문제풀이 #3

 

 

○ Weather Observation Station 5

 

Query the two cities in STATION with the shortest and longest CITY names, as well as their respective lengths (i.e.: number of characters in the name). If there is more than one smallest or largest city, choose the one that comes first when ordered alphabetically.
The STATION table is described as follows:

where LAT_N is the northern latitude and LONG_W is the western longitude.

Sample Input

For example, CITY has four entries: DEF, ABC, PQRS and WXY.

 

Sample Output

 

ABC 3
PQRS 4

 

 

 

[ 나의 풀이 ]

select  CITY, len(CITY)
from STATION
where CITY = (select CITY
from STATION
where len(CITY) =  (select Max(len(CITY)) as max from STATION) )
or CITY = (select top 1CITY
from STATION
where len(CITY) =  (select Min(len(CITY)) as min from STATION) 
order by CITY)

 

 

○ Challenges

ulia asked her students to create some coding challenges. Write a query to print the hacker_id, name, and the total number of challenges created by each student. Sort your results by the total number of challenges in descending order. If more than one student created the same number of challenges, then sort the result by hacker_id. If more than one student created the same number of challenges and the count is less than the maximum number of challenges created, then exclude those students from the result.

Input Format

The following tables contain challenge data:

• Hackers: The hacker_id is the id of the hacker, and name is the name of the hacker. 
• Challenges: The challenge_id is the id of the challenge, and hacker_id is the id of the student who created the challenge. 

 

[ 나의 풀이 ]

with cte as (
select C.hacker_id, name , count(C.hacker_id) as cnt
    from Challenges as C join Hackers as H on C.hacker_id = H.hacker_id
    group by C.hacker_id, H.name)

select cte.hacker_id , Hackers.name , cnt 
from cte left join Hackers on cte.hacker_id = Hackers.hacker_id
where cnt in (  select cnt 
                        from cte
                        group by cnt
                        having count(hacker_id) < 2)
        or cnt = (select max(cnt) from cte)
order by cnt DESC , cte.hacker_id

CTE를 사용하여 각 hacker_id 별 challenge 개수를 구하고
여기에 챌린지 개수 기준으로 그룹화 하여 동일한 챌린지 개수를 가지고 있는 hacker_id의 개수를 구한 후 그 값이

2보다 작은 경우( 생각해보니 그냥 1인 경우로 했어도 됐을 것 같다 ) 의 챌린지 개수를 구했다.

또한 챌린지 개수가 가장 많은 경우를 구해서 cte 테이블의 cnt와 비교하여 동일한 경우의 hacker_id , cnt를 구했다

그리고 name을 출력하기 위해 Hackers 테이블과 left join 조건을 걸어주었다

 

 

 

○ Contest Leaderboard

 

You did such a great job helping Julia with her last coding contest challenge that she wants you to work on this one, too!

The total score of a hacker is the sum of their maximum scores for all of the challenges. Write a query to print the hacker_id, name, and total score of the hackers ordered by the descending score. If more than one hacker achieved the same total score, then sort the result by ascending hacker_id. Exclude all hackers with a total score of  from your result.

Input Format

The following tables contain contest data:

• Hackers: The hacker_id is the id of the hacker, and name is the name of the hacker. 
• Submissions: The submission_id is the id of the submission, hacker_id is the id of the hacker who made the submission, challenge_id is the id of the challenge for which the submission belongs to, and score is the score of the submission. 

 

[ 나의 풀이 ]

with cte as(
select hacker_id, challenge_id , max(score) as m_score
from Submissions
group by hacker_id , challenge_id )

select cte.hacker_id , name , sum(m_score) as total
from cte join Hackers on cte.hacker_id = Hackers.hacker_id
where  m_score > 0
group by cte.hacker_id , name
order by total DESC , cte.hacker_id

 

cte로 가장 큰 점수를 포함한 가상 테이블을 만들고

그 점수가 0이 아닌 경우에만 hacker_id와 name으로 그룹화하여( name 을 그룹화하지 않으면 오류가 발생함)

각 hacker의 최대점수의 합을 구하고 hacker의 name을 조회하기 위한 Hackers 테이블을 join연결해주면 된다.

 

 

 

 

○ SQL Project Planning

You are given a table, Projects, containing three columns: Task_ID, Start_Date and End_Date. It is guaranteed that the difference between the End_Date and the Start_Date is equal to 1 day for each row in the table.

If the End_Date of the tasks are consecutive, then they are part of the same project. Samantha is interested in finding the total number of different projects completed.

Write a query to output the start and end dates of projects listed by the number of days it took to complete the project in ascending order. If there is more than one project that have the same number of completion days, then order by the start date of the project.

Sample Input

Sample Output

2015-10-28 2015-10-29
2015-10-30 2015-10-31
2015-10-13 2015-10-15
2015-10-01 2015-10-04

 

[ 나의 풀이 ]

with cte as(select Task_ID, Start_Date , End_Date,
            case when 
            datediff(day, LAG(End_Date) over(order by End_Date) , End_Date) = 1
            then 0 
            else 1
            end as flag
            from projects)
, cte2 as (select * , sum(flag) over(order by End_Date) as cnt
                from cte)
select min(Start_Date) , max(End_Date)
from cte2
group by cnt
order by count(*) , min(Start_Date)

 

◆SQL Server에서 순차적 누적합계 구하기

SUM(컬럼명) OVER(ORDER BY 정렬기준 컬럼) AS 컬럼명 지정

 

++ 그룹별로 순차적 누적 합계 구하기

 

SUM(컬럼명) OVER(PARTITION BY 그룹 기준 컬럼 ORDER BY 정렬 기준 컬럼) AS 컬럼명 지정

 

◆SQL Server에서 앞/뒤 의 행 값 접근하기

LAG(컬럼명)  OVER(ORDER BY 컬럼명) 

LEAD(컬럼명)  OVER(ORDER BY 컬럼명) 

 

 

따라서 cte로 앞 행의 End date와 비교했을때 하루 차이가 나는 경우에는 0을 나머지는 1을 반환하는 flag 컬럼을 추가한 테이블을 구성하고 >> 앞의 행과 이어지는 경우에는 0 , 이어지는 날짜가 아닌 경우에는 1 반환함

 

다음으로 cte2를 통해 sum over를 사용하여 End date 기준으로 정렬했을때의 flag 값의 합을 구하도록 하였다.

누적 합계로 이어지는 경우에는 0으로 같은 값을 , 이어지지 않는 경우에는 1 씩 추가된 값을 갖게되어

이어지는 Task 간의 구별이 가능한 번호를 생성할 수 있다.

 

이를 이용하여 마지막으로  구분 번호로 그룹화 하고 가장 작은 Start Date와 가장 큰 End Date를 출력하며

count를 통해 구한 각각의 Task의 일수 합계를 기준으로 정렬하고 그 다음으로 Start Date 기준으로 정렬하면 된다.

 

 

◆ OVER() 함수 사용

 

집계함수 뒤에 붙여 사용 할 수 있음

AVG , SUM 과 같은 집계함수는 단일 값을 반환하기 때문에 Group by를 하지 않으면 오류가 발생하며 사용할 수 없음

이러한 문제를 OVER를 사용하면 해결됨

 

ex) Select name , AVG(score) OVER() 

      From class_score

>> 단일 값인 score의 평균이 행 개수 만큼 출력됨

 

OVER () 함수 내에 PARTITION BY 또는 ORDER BY를 추가하여 그룹화 또는 정렬할 수 있다