우리는 데이터로 무엇을 하려고 하는가? 데이터 분석에는 항상 정답이 있을까?

내가 데이터 분석 관련 프로젝트를 진행할 때마다 여러 번 스스로 던졌던 질문이다. 최근에 데이터의 중요성이 대두되면서 데이터 사이언스 커리큘럼을 제공하는 학원들이 우후죽순 등장하고, 나 또한 그런 학원에서 데이터 사이언스 강의를 수강하게 되었다. 그러한 학원에서는 통계 지식이나 머신러닝 이론 등 데이터 관련 지식을 중점적으로 가르치기에, 분석가로서 데이터를 가지고 무엇을 하려고 하는지, 어떻게 데이터를 활용해야 할지 등에 대해서는 스스로 고민하며 찾아 나가야 한다. 그래서 나는 책을 읽으며 스스로 해답을 찾아보기로 하였다.

데이터분석가로 처음 업무를 하면서 처음으로 읽게 된 책이 '데이터 문해력'이다. 이 책은 데이터 분석 기술에 관한 서적이라기보다는, 본질적이고 가치 있는 데이터 활용 방식에 대해 기술한다. 책 내용은 총 8개의 챕터로 구성되어 있고 어렵지도 길지도 않아서 쉽게 술술 읽혔지만, 책을 읽으며 그동안 분석가로서 고민하던 것들에 대해 생각하느라 두 번을 더 읽어서 시간이 오래 걸렸다. 책을 읽으며 내가 분석 업무를 하면서 고민하던 것들이 조금은 해결된 느낌이었다.

오늘은 데이터 분석을 시작하는 분들께 추천하고 싶은 서적 "데이터 문해력"을 읽은 후 느꼈던 점을 질문과 대답 형식으로 적어보려고 한다.

1. 우리는 왜 데이터 분석을 하는가?

데이터 분석이 왜 필요한 것인지, 나는 왜 데이터 분석을 하는지 다시 생각해보게 되었다. 우리는 비즈니스에서 다양한 문제를 마주하고 해결해야 하는 상황을 자주 겪는다. 문제를 해결하기 위한 도구로 데이터 분석이 필요한 것이고, 이런 의미에서 데이터 분석은 목적을 달성하기 위한 도구에 지나지 않는다는 것을 항상 인지해야 한다.

우리의 최종 목표는 행동과 판단이라는 것을 항상 인지하고 있어야 한다. 결국, 우리가 하고자 하는 것은 데이터를 통해 문제 해결 방안을 수립하거나 행동 계획을 세우는 것이다. 분석 자체가 목적이 되어서는 안 되고, 어떠한 목적을 달성하기 위한 도구로써 활용해야 한다.

2. 데이터 분석을 잘한다는 것은 무엇일까?

데이터 분석가로서 데이터 분석을 잘한다는 것은 어떻게 정의할 수 있을까? 단순히 데이터를 분석하기 위한 통계 분석 방법론을 잘 알고 있는 사람일까? 아니면 데이터 툴을 잘 익히고 데이터 시각화를 잘하는 사람일까? 아니다. 데이터 분석을 잘하는 사람은 자신의 목적과 문제를 정의하고, 데이터를 적절히 활용해서 가치 있는 결과물을 낼 수 있도록 하는 사고방식과 기술을 가진 사람을 말한다. 데이터 분석 기술보다 문제 및 목적을 정의하고 가설을 구축하고 분석을 위한 기술을 활용해 분석 결과를 해석하고 스토리를 구축하는 일련의 데이터 활용 프로세스에 맞춰 데이터를 활용하는 사람을 데이터 분석을 잘한다고 말할 수 있다.

반대로 데이터 분석을 잘 못하는 사람, 즉 데이터 활용을 못하는 사람은 생각하고→작업하고→생각하는 과정에서 생각하는 과정을 간과하고 분석을 진행하는 사람이라고 말할 수 있다. 분석 전 문제 및 목적을 정의하고 가설을 구축하는 첫 번째 생각하는 단계, 그리고 데이터 분석 결과에 따른 해석을 추가해 결론을 내리게 되는 세 번째 생각하는 단계를 간과하면 데이터 활용을 못하는 사람이 될 수 있다. 우리는 이 생각하는 단계에 집중해야 한다.

3. 데이터 분석을 잘하기 위해서는 어떻게 해야 할까?

데이터 문해력에 뛰어난 사람이 되려면 목적을 올바르게 설정하고 그 목적에 따라 데이터를 활용해 결과를 결론으로까지 끌어낸다는 흐름을 익히고 잘 밟아가야 한다. 우리가 가장 먼저 추구해야 하는 것은 목적 중심이고, 내가 알고 싶은 것에 따라 데이터를 수집하고 그 결과를 검증하고 결론으로 연결하는 것이다. 목적 중심으로, 목적 및 문제 정의 -> 지표 결정- > 현재 상태 파악- > 평가 -> 요인 분석 -> 해결 방안 모색 이 데이터 활용 프로세스를 인지하고 이대로 따라야 한다.

가장 먼저 해야 하는 것은 목적과 문제에 대한 정의이다. 하지만 우리는 주어진 데이터로 현재 상태를 파악하는 것에서부터 시작하는 경우가 많다. 나는 무엇을 알고 싶은지, 무엇을 해결하고자 하는지 이 두 가지를 명확히 하는 것에서부터 시작해야 한다. 이 단계가 확실히 되어야 문제를 분석하기 위해 어떤 지표를 사용하면 좋을지 고민하고 지표를 결정하는 단계로 나아갈 수 있다.

4. 논리적인 사고란 무엇이고 논리적인 사고력을 어떻게 배양할 수 있을까?

데이터 분석가 채용 공고를 읽다 보면, 자격 요건에 논리적인 사고력이 기재되어 있는 것을 흔히 볼 수 있다. 하지만 논리적인 사고력이라는 것이 정확히 무엇일까? 나는 가끔 논리적인 사고력이라는 단어가 너무 모호하다고 생각했다. 하지만 이 책을 읽으면서 이 뜻이 조금은 명확해졌고, 논리적인 사고력을 어떻게 배양할 수 있을지 진지하게 고민해보게 되었다.

논리적인 사고란, 스토리를 탄탄히 구성하고 결론의 설득력을 높이기 위한 사고력과 구성력을 말한다. 우리는 이 논리적 사고와 문제 해결 능력을 배양하기 위해서는, 문제->원인->해결 방안이라는 세 단계로 구성된 프로세스를 염두에 두어야 한다. 많은 사람들이 문제 해결 프로세스에서 현재 상태를 파악하고 곧바로 해결 방안에 달려드는 실수를 범하는데, 이는 데이터 기반이라고 볼 수 없다. 따라서 해결 방안으로 뛰어들기 전에 원인을 분석하는 과정을 꼭 거쳐야 한다. 무엇을 해결하고자 하는지, 무엇이 결정적 요인인지를 확실히 한 이후 무엇을 해야 하는가라는 순서로 사고를 진행해야 한다.

또, 문제에 대한 범위나 분석 영역을 누락 없이 판별하기 위해서 논리적 사고가 필요하다. 여기서 말하는 논리적 사고란 문제를 구조화하고 정리하면서 생각하는 것을 말한다. 구조화해 정리하게 되면, 왜 이 데이터를 사용해서 분석했는가에 대한 질문에 대해 논리적인 설명이 가능하다.

5. 결과와 결론은 어떻게 다를까?

우리는 결과와 결론의 차이를 명확히 알아야 한다. 계산과 분석을 통해 나온 결과물은 결과이고, 그 결과가 목적에 대해 어떤 의미가 있는지 설명하는 것이 결론이다. 결론이란 결과를 기반으로 도출하기에 결과와 결론에 이르는 과정은 본질적으로 같지만 표현 방식은 다르다. 이 차이를 이해하는 것이 데이터 문해력에 있어서 중요한 요소이다. 결과로부터 결론을 도출할 때 자신의 정보 집약 능력에 더해 상상력이 필요하지만, 필요 이상으로 자신의 해석을 덧붙이지 않도록 주의해야 한다. 원칙적으로 데이터를 통해 알 수 있는 사실의 범위 내에서 생각해야 한다.

6. 내가 되돌아보게 되었던 것들

나는 이때까지 데이터 중심 사고를 하고 있었는지 아니면 목적 중심 사고를 하고 있었는지 많이 되돌아보게 되었다. 데이터 분석 전에 목적과 문제를 먼저 정의하는 것을 간과하고, 데이터를 보면 무언가 답이 있겠지라고 생각했던 적이 있었다. 또, 데이터 분석 업무를 할 때 데이터 정리로 끝낸 적도 있다. 내가 꼭 기억해야 하는 것은 데이터는 그 자체로는 의미가 없다는 것이다. 적절한 사고 과정을 밟으며 데이터 분석을 통해 어떠한 실행이 되어야 그것이 의미 있는 데이터 분석이라고 말할 수 있다. 내가 현재 배양해야 할 능력은 데이터 분석 기술이나 지식을 키우는 일이 아니라 적절한 사고 과정을 밟는 것이다.

책에서도 나오듯이, 업무를 할 때 우리의 사고 과정을 방해하는 것들이 있다. 대표적인 것이 시간의 부족이다. 목표에 도달하는 데 필요한 프로세스를 숙지하고 있더라도 이를 실현하려면 시간이 필요한데, 시간을 확보할 수 없는 환경 때문에 우리의 사고 과정이 방해를 받는다. 하지만 시간에 쫓겨 데이터 중심 사고를 하는 것보다는 시간을 더 들여서라도 목적 중심 사고를 기반으로 데이터를 사용하는 목적이 무엇이며, 데이터를 분석해 실현하고 싶은 것이 무엇이며, 어떤 행동으로 이어지게 하고 싶은가? 를 고민하는 과정을 거쳐야 하며, 명확하고 구체적인 목적을 협업하는 팀원들에게 공유하는 문화를 지향해야 한다.

원래는 프로그래머스 코딩테스트 고득점 Kit로 공부를 시작하려 했는데, 그냥 프로그래머스에 있는 모든 문제에서 레벨 1, 2의 문제부터 공부를 시작했다.

오늘은 작년부터 위 문제들 중 레벨 1의 문제들을 풀며 노션에 정리해두었던 것들을 블로그에 정리해보려고 한다.

level 1

• 약수의 합

  문제 설명: 정수 n을 입력받아 n의 약수를 모두 더한 값을 리턴하는 함수, solution을 완성해주세요.

• 입출력 예*: n이 12면, 28을 return
• 입출력 예 설명*: 12의 약수는 1, 2, 3, 4, 6, 12입니다. 이를 모두 더하면 28입니다.

    def solution(n):
        list = [i for i in range(1, n+1)] #n까지의 숫자 출력해 리스트에 넣음
        answer = []
        for i in range(len(list)): # 리스트의 요소 반복문
            if n % list[i] == 0: #만약 n을 요소로 나눴을 때 나머지가 0이라면(약수라면)
                answer.append(list[i]) #answer 리스트에 추가
        return sum(set(answer)) # answer 리스트의 합

    def solution(n):
        return sum([i for i in range(1, n+1) if n%i==0]) #n까지의 숫자 반복문 돌리고, n을 특정 숫자로 나눴을 때 나머지가 0이라면(약수라면) 합을 구함

• 짝수와 홀수

  문제 설명: 정수 num이 짝수일 경우 "Even"을 반환하고 홀수인 경우 "Odd"를 반환하는 함수, solution을 완성해주세요.

• 입출력 예*: num이 3이면, "Odd"를 return

    def solution(num):
        return "Even" if num%2==0 else "Odd" #만약 num이 2로 나누어 떨어지면 "Even" 출력, 아니면 "Odd" 출력

• 평균 구하기

  문제 설명: 정수를 담고 있는 배열 arr의 평균값을 return하는 함수, solution을 완성해보세요.

• 입출력 예*: arr가 [1,2,3,4]이면, 2.5 return

    def solution(arr):
        return sum(arr)/len(arr) #arr 합을 arr 배열 개수로 나눔

• 자릿수 더하기

  문제 설명:자연수 N이 주어지면, N의 각 자릿수의 합을 구해서 return 하는 solution 함수를 만들어 주세요.예를들어

• 입출력 예*: N이 123이면, answer은 6
• 입출력 예 설명*: N = 123이면 1 + 2 + 3 = 6을 return 하면 됩니다.

    def solution(n):
        return sum([int(i) for i in str(n)]) #N을 문자열로 변환 후, 반복문을 돌려 각 자리를 정수로 변환해 합을 구함 

• 나머지가 1이 되는 수 찾기

  문제 설명: 자연수 n이 매개변수로 주어집니다. n을 x로 나눈 나머지가 1이 되도록 하는 가장 작은 자연수 x를 return 하도록 solution 함수를 완성해주세요. 답이 항상 존재함은 증명될 수 있습니다.

• 입출력 예*: (1) n이 10일 때, result는 3 (2) n이 12일 때, result는 11
• 입출력 예 설명*: (입출력 예 #1) 10을 3으로 나눈 나머지가 1이고, 3보다 작은 자연수 중에서 문제의 조건을 만족하는 수가 없으므로, 3을 return 해야 합니다. (입출력 예 #2) 12를 11로 나눈 나머지가 1이고, 11보다 작은 자연수 중에서 문제의 조건을 만족하는 수가 없으므로, 11을 return 해야 합니다.

    def solution(n):
        list = [i for i in range(1, n)] #1부터 n-1까지 리스트에 담음
        for i in list: #리스트 요소 반복문
            if n % i == 1: #n이 i로 나눠서 나머지가 1이면 i를 리턴
                return i
        else: # 아닐 경우 n-1 반환
            return n-1

    def solution(n):
        return [i for i in range(1, n) if n%i==1][0] #1부터 n-1까지 수를 반복문 돌려 n을 수(i)로 나눴을 때 나머지가 1인 것만 리스트에 담음 -> [0]을 이용해 리스트 중 맨 앞 수만 출력 

• x만큼 간격이 있는 n개의 숫자

  문제 설명: 함수 solution은 정수 x와 자연수 n을 입력 받아, x부터 시작해 x씩 증가하는 숫자를 n개 지니는 리스트를 리턴해야 합니다.

• 입출력 예*: (1) x=4, n=3면, answer은 [4,8,12] (2) x=-4, n=2면, answer은 [-4,-8]

     def solution(x, n):
         answer = [x*i for i in range(1, n+1)] #1부터 n까지 반복문 돌려 x에 i를 곱해 리스트 출력
         return answer

• 문자열 내 p와 y 개수

  문제 설명: 대문자와 소문자가 섞여있는 문자열 s가 주어집니다. s에 'p'의 개수와 'y'의 개수를 비교해 같으면 True, 다르면 False를 return 하는 solution를 완성하세요. 'p', 'y' 모두 하나도 없는 경우는 항상 True를 리턴합니다. 단, 개수를 비교할 때 대문자와 소문자는 구별하지 않습니다.예를 들어 s가 "pPoooyY"면 true를 return하고 "Pyy"라면 false를 return합니다.

• 입출력 예*: (1) s가 "pPoooyY"이면, answer은 true (2) s가 "Pyy"이면, answer은 false

• 입출력 예 설명*: (입출력 예 #1)'p'의 개수 2개, 'y'의 개수 2개로 같으므로 true를 return 합니다. (입출력 예 #2)'p'의 개수 1개, 'y'의 개수 2개로 다르므로 false를 return 합니다.

    def solution(s):
    s = s.lower() #s를 소문자로 변환 
    if s.count('p') == s.count('y'): #만약 s에서 p의 개수와 y의 개수가 동일하면 True 반환
        return True
    else: #아니라면 False 반환
        return False

    def solution(s):
        return s.lower().count('p') == s.lower().count('y') #s를 소문자로 변환한 것에서 p의 개수와 s의 개수가 동일하다 -> 맞다면 True 반환, 아니면 False 반환

• 정수 제곱근 판별

  문제 설명: 임의의 양의 정수 n에 대해, n이 어떤 양의 정수 x의 제곱인지 아닌지 판단하려 합니다. n이 양의 정수 x의 제곱이라면 x+1의 제곱을 리턴하고, n이 양의 정수 x의 제곱이 아니라면 -1을 리턴하는 함수를 완성하세요.

• 입출력 예*: (1) n이 121이면, 144 return (2)n이 3이면, -1 return
• 입출력 예 설명*: (입출력 예#1) 121은 양의 정수 11의 제곱이므로, (11+1)를 제곱한 144를 리턴합니다. (입출력 예#2) 3은 양의 정수의 제곱이 아니므로, -1을 리턴합니다.

    def solution(n):
        import math 
        return (math.sqrt(n)+1)**2 if math.sqrt(n) % 1 == 0 else -1 #n의 제곱근을 1으로 나눴을 때 나머지가 0이라면 (n의 제곱근+1)의 제곱 반환, 아니라면 -1 반환

    def solution(n):
        sqrt = n**(1/2) #n의 제곱근을 sqrt라는 변수로 저장
        if sqrt % 1 == 0: #n의 제곱근을 1으로 나눴을 때 나머지가 0이라면
            return (sqrt+1)**2 #sqrt(n의 제곱근)+1의 제곱을 반환
        return -1 #아니면 -1 반환

• 자연수 뒤집어 배열로 만들기

  문제 설명: 자연수 n을 뒤집어 각 자리 숫자를 원소로 가지는 배열 형태로 리턴해주세요.

• 입출력 예*: n이 12345면, [5,4,3,2,1]을 return

    def solution(n):
        list = [int(i) for i in str(n)[::-1]] #n을 문자열로 변환해 순서 뒤집음, 그리고 반복문을 돌려 각 자리를 정수로 변환해 리스트에 넣음
        return list

• 정수 내림차순으로 배치하기

  문제 설명: 함수 solution은 정수 n을 매개변수로 입력받습니다. n의 각 자릿수를 큰것부터 작은 순으로 정렬한 새로운 정수를 리턴해주세요. 예를들어 n이 118372면 873211을 리턴하면 됩니다.

• 입출력 예*: n이 118372이면, 873211 return

    def solution(n):
        list = [i for i in str(n)] #n을 문자열로 변환해 각 문자를 리스트에 넣기
        list.sort(reverse=True) #요소를 내림차순으로 정렬
        return int("".join(list)) #join 활용해 리스트 안의 요소 결합 후 정수 변환

• 하샤드 수

  문제 설명: 양의 정수 x가 하샤드 수이려면 x의 자릿수의 합으로 x가 나누어져야 합니다. 예를 들어 18의 자릿수 합은 1+8=9이고, 18은 9로 나누어 떨어지므로 18은 하샤드 수입니다. 자연수 x를 입력받아 x가 하샤드 수인지 아닌지 검사하는 함수, solution을 완성해주세요.

• 입출력 예*: (1) arr가 10이면, true를 반환 (2) arr가 11이면, false를 반환
• 입출력 예 설명*: (입출력 예 #1) 10의 모든 자릿수의 합은 1입니다. 10은 1로 나누어 떨어지므로 10은 하샤드 수입니다. (입출력 예 #2) 11의 모든 자릿수의 합은 2입니다. 11은 2로 나누어 떨어지지 않으므로 11는 하샤드 수가 아닙니다.

    def solution(x):
        return x % sum([int(i) for i in str(x)]) == 0 #x를 문자열로 변환해 반복문 돌려 각 숫자를 리스트에 담아 합을 구함(18이면 1+8=9), 이후 x가 이 합으로 나눴을 때 나머지가 0이면 true 반환, 아니면 false 반환

• 두 정수 사이의 합

  문제 설명: 두 정수 a, b가 주어졌을 때 a와 b 사이에 속한 모든 정수의 합을 리턴하는 함수, solution을 완성하세요.예를 들어 a = 3, b = 5인 경우, 3 + 4 + 5 = 12이므로 12를 리턴합니다.

• 입출력 예*: (1)a=3, b=5이면 12 반환 (2)a=3, b=3이면 3 반환 (3)a=5, b=3이면 12 반환

    def solution(a, b):
        return sum(range(a, b+1)) if a<=b else sum(range(b, a+1)) #a가 b보다 작거나 같을 때 a부터 b까지의 합을 구하고, a가 b보다 클 때 b부터 a까지의 합 구함

    def adder(a, b):
        if a > b: #a가 b보다 크면 
            a, b = b, a #a는 b, b는 a
        return sum(range(a, b+1)) #a부터 b까지의 합 구하기

• 콜라츠 추측

  문제 설명: 1937년 Collatz란 사람에 의해 제기된 이 추측은, 주어진 수가 1이 될 때까지 다음 작업을 반복하면, 모든 수를 1로 만들 수 있다는 추측입니다. 작업은 다음과 같습니다. (1-1) 입력된 수가 짝수라면 2로 나눕니다. (1-2) 입력된 수가 홀수라면 3을 곱하고 1을 더합니다. (2) 결과로 나온 수에 같은 작업을 1이 될 때까지 반복합니다. 예를 들어, 주어진 수가 6이라면 6 → 3 → 10 → 5 → 16 → 8 → 4 → 2 → 1 이 되어 총 8번 만에 1이 됩니다. 위 작업을 몇 번이나 반복해야 하는지 반환하는 함수, solution을 완성해 주세요. 단, 주어진 수가 1인 경우에는 0을, 작업을 500번 반복할 때까지 1이 되지 않는다면 –1을 반환해 주세요.

• 입출력 예*: (1) n=6이면, 8 반환 (2) n=16이면, 4 반환 (3) n=626331이면, -1 반환
• 입출력 예 설명*: (입출력 예 #1) 문제의 설명과 같습니다. (입출력 예 #2) 16 → 8 → 4 → 2 → 1 이 되어 총 4번 만에 1이 됩니다. (입출력 예 #3) 626331은 500번을 시도해도 1이 되지 못하므로 -1을 리턴해야 합니다.

    def solution(num):
        answer = 0
        while True: #무한 반복
            if num != 1: #num이 1이 아니면, 
                if num % 2 ==0: #num을 2로 나누었을 때 나머지가 0이면(짝수) 
                    num = num / 2 #num은 num을 2로 나눈 값으로 저장
                    answer += 1 #answer 1 증가
                elif num % 2 != 0: #num을 2로 나누었을 때 나머지가 0이 아니면(홀수)
                    num = num*3 + 1 #num은 num에 3을 곱한 값에 1을 더한 값으로 저장
                    answer += 1 #answer 1 증가
            else: #num이 1이면 반복문 그만
                break
        return answer if answer < 500 else -1 #만약 answer이 500미만이면 answer 반환, 아니면 -1 반환

• 서울에서 김서방 찾기

  문제 설명: String형 배열 seoul의 element중 "Kim"의 위치 x를 찾아, "김서방은 x에 있다"는 String을 반환하는 함수, solution을 완성하세요. seoul에 "Kim"은 오직 한 번만 나타나며 잘못된 값이 입력되는 경우는 없습니다.

• 입출력 예*: seoul= ["Jane", "Kim"]이면, "김서방은 1에 있다"를 반환

    def solution(seoul):
        return '김서방은 ' + str(seoul.index('Kim')) +'에 있다' #seoul에서 'Kim'이 들어있는 index 찾아 문자열 조합

    def findKim(seoul):
        return "김서방은 {}에 있다".format(seoul.index('Kim')) #format 함수를 이용해 {}안에 seoul에서 'Kim'이 들어있는 index 넣어줌

    def solution(seoul):
        answer = ''
        return ('김서방은 %d에 있다' %seoul.index('Kim')) #%서식 기호 활용해 d에 seoul에서 'Kim'이 들어있는 index 넣어줌

• 나누어 떨어지는 숫자 배열

  문제 설명: array의 각 element 중 divisor로 나누어 떨어지는 값을 오름차순으로 정렬한 배열을 반환하는 함수, solution을 작성해주세요. divisor로 나누어 떨어지는 element가 하나도 없다면 배열에 -1을 담아 반환하세요.

• 입출력 예*: (1)arr=[5, 9, 7, 10], divisor=5이면, [5,10] 반환 (2)arr=[3,2,6], divisor=10이면, [-1] 반환
• 입출력 예 설명*: (입출력 예#1) arr의 원소 중 5로 나누어 떨어지는 원소는 5와 10입니다. 따라서 [5, 10]을 리턴합니다. (입출력 예#2) 3, 2, 6은 10으로 나누어 떨어지지 않습니다. 나누어 떨어지는 원소가 없으므로 [-1]을 리턴합니다.

    def solution(arr, divisor): 
        return sorted([n for n in arr if n%divisor == 0]) or [-1] #arr에서 각 요소을 divisor로 나눴을 때 나머지가 0인 요소만 리스트에 담음, 없을 경우 [-1] 반환

• 핸드폰 번호 가리기

  문제 설명: 프로그래머스 모바일은 개인정보 보호를 위해 고지서를 보낼 때 고객들의 전화번호의 일부를 가립니다. 전화번호가 문자열 phone_number로 주어졌을 때, 전화번호의 뒷 4자리를 제외한 나머지 숫자를 전부 기호 '별'로 가린 문자열을 리턴하는 함수, solution을 완성해주세요.

• 입출력 예**: phone_number="01033334444"면, " * * * * * * 4 4 4 4" 반환

    def solution(phone_number):
        return '*'*len(phone_number[:-4]) + phone_number[-4:] #주어진 phone_number에서 뒤 네 자리 제외한 값을 *로 변환하고 뒤 네 자리 문자열 더해줌

• 음양 더하기

  문제 설명: 어떤 정수들이 있습니다. 이 정수들의 절댓값을 차례대로 담은 정수 배열 absolutes와 이 정수들의 부호를 차례대로 담은 불리언 배열 signs가 매개변수로 주어집니다. 실제 정수들의 합을 구하여 return 하도록 solution 함수를 완성해주세요.

• 입출력 예*: (1) absolutes=[4,7,12], sign=[true,false,true], result는 9 (2) absolutes=[1,2,3], sign=[false,false,true], result는 0
• 입출력 예 설명*: (입출력 예 #1) signs가 [true,false,true] 이므로, 실제 수들의 값은 각각 4, -7, 12입니다.따라서 세 수의 합인 9를 return 해야 합니다. (입출력 예 #2) signs가 [false,false,true] 이므로, 실제 수들의 값은 각각 -1, -2, 3입니다. 따라서 세 수의 합인 0을 return 해야 합니다.

    def solution(absolutes, signs):
        answer = 0 #answer 변수 
        for i in range(len(absolutes)): #absolutes 배열 수만큼 반복문
            if signs[i] == True: #만약 sign[i]이 True 라면
                answer += absolutes[i] #answer에 absolutes[i] 더하기
            else: #만약 sign[i]이 False 라면
                answer -= absolutes[i] #answer에 absolutes[i] 빼기
        return answer

    def solution(absolutes, signs):
        return sum(absolutes if sign else -absolutes for absolutes, sign in zip(absolutes, signs)) 
        #zip 내장 함수 활용해 (absolutes, signs) 자료형 묶어줌
        #이후 반복문 돌려 sign이 true면 absolutes 요소 더하고, false면 absolutes 요소를 빼 합을 구함

• 제일 작은 수 제거하기

  문제 설명: 정수를 저장한 배열, arr 에서 가장 작은 수를 제거한 배열을 리턴하는 함수, solution을 완성해주세요. 단, 리턴하려는 배열이 빈 배열인 경우엔 배열에 -1을 채워 리턴하세요. 예를들어 arr이 [4,3,2,1]인 경우는 [4,3,2]를 리턴 하고, [10]면 [-1]을 리턴 합니다.

• 입출력 예*: (1) arr=[4,3,2,1]이면, [4,3,2] 반환 (2) arr=[10]이면, [-1] 반환

    def solution(arr):
        if len(arr) > 1: #배열 개수가 1보다 크면
            arr.remove(min(arr)) #주어진 배열 arr에서 가장 작은 수를 제거
            return arr 
        else: #배열 개수가 1일 경우
            return [-1] #[-1] 반환

• 없는 숫자 더하기

  문제 설명: 0부터 9까지의 숫자 중 일부가 들어있는 정수 배열 numbers가 매개변수로 주어집니다. numbers에서 찾을 수 없는 0부터 9까지의 숫자를 모두 찾아 더한 수를 return 하도록 solution 함수를 완성해주세요.

• 입출력 예*: (1) numbers=[1,2,3,4,6,7,8,0]이면, 14 반환 (2) numbers=[5,8,4,0,6,7,9]이면, 6 반환
• 입출력 예 설명*: (입출력 예 #1) 5, 9가 numbers에 없으므로, 5 + 9 = 14를 return 해야 합니다. (입출력 예 #2) 1, 2, 3이 numbers에 없으므로, 1 + 2 + 3 = 6을 return 해야 합니다.

    def solution(numbers):
        list = [i for i in range(0, 10)] #리스트에 0부터 9까지 숫자 담음
        new = [] #new라는 배열 선언
        for i in list: #리스트 반복문 
            if i not in numbers: #만약 리스트 원소 i가 numbers에 없으면 new라는 배열에 요소 추가
                new.append(i)
        return sum(new) #new 배열의 합 구함

    def solution(numbers):
        return 45 - sum(numbers) #0부터 9까지의 합은 45, 따라서 45에서 numbers 배열의 합을 빼면 답이 나옴

https://app.codesignal.com/arcade/db

총 84 문제가 있었고 3일 동안 꾸준히 약 20-30 문제씩 풀어서 완료했다. 다 정리하기엔 너무 많고 기억해두면 좋을 것들 위주로 적어보려고 한다.

Order

contestLeaderboard

문제

You are working as a recruiter at a big IT company, and you're actively looking for candidates who take the top places in major programming contests. Since the grand finale of the annual City Competition, you've been reaching out to the top participants from the leaderboard, and successfully so.

You have already interviewed all the prize winners (the top 3 participants), but that's not enough right now. Your company needs more specialists, so now you would like to connect with the participants who took the next 5 places.

The contest leaderboard is stored in a table leaderboard with the following columns:

id: unique id of the participant;
name: the name of the participant;
score: the score the participant achieved in the competition.
The resulting table should contain the names of the participants who took the 4th to 8th places inclusive, sorted in descending order of their places. If there are fewer than 8 participants, the results should contain those who ranked lower than 3rd place.

It is guaranteed that there are at least 3 prize winners in the leaderboard and that all participants have different scores.

--- 내가 작성한 답
CREATE PROCEDURE solution()
BEGIN
    SELECT NAME
    FROM (SELECT NAME, SCORE, RANK () OVER (ORDER BY SCORE DESC) AS RK
        FROM LEADERBOARD) RK_TBL
    WHERE RK >= 4 AND RK <=8;
END

--- 다른 분 답 참고
CREATE PROCEDURE solution()
BEGIN
    select name 
    from leaderboard 
    order by score desc 
    limit 5 offset 3;
END

• 4등부터 8등까지 추출하기 위해서는 RANK () OVER 혹은 LIMIT OFFSET 활용 가능 1) RANK () OVER 을 사용해 SCORE 순으로 나열한 뒤, RANK가 4부터 8인 것 추출 2) LIMIT OFFSET 사용해 3행 그 이후부터 5개 행 추출 (LIMIT 5 OFFSET 3: 0이 가장 첫 번째 행이고, 네 번째 행을 얻고 싶다면 위치 3으로 지정)

gradeDistribution

문제

At the end of every semester your professor for "Introduction to Databases" saves the exam results of every student in a simple database system. In the database table Grades, there are five columns:

Name: the name of the student;
ID: the student's ID number (a 5 byte positive integer);
Midterm1: the result of the first midterm out of 100 points;
Midterm2: the result of the second midterm out of 100 points;
Final: the result of the final exam, this time out of a possible 200 points.
According to school policy, there are three possible ways to evaluate a grade:

Option 1: Grade = 0.25 * Midterm1 + 0.25 * Midterm2 + 0.5 * Final;
Option 2: Grade = 0.5 * Midterm1 + 0.5 * Midterm2;
Option 3: Grade = Final.
Each student's final grade comes from the option that works the best for that student.

As a Teaching Assistant (TA), you need to query the name and id of all the students whose best grade comes from Option 3, sorted based on the first 3 characters of their name. If the first 3 characters of two names are the same, then the student with the lower ID value comes first.

--- 내가 작성한 답
CREATE PROCEDURE solution()
BEGIN
    SELECT NAME, ID
    FROM (SELECT NAME
        , ID
        , MIDTERM1*0.25 + MIDTERM2*0.25 + FINAL*0.5 AS OPTION1
        , MIDTERM1*0.5 + MIDTERM2*0.5 AS OPTION2
        , FINAL AS OPTION3
        FROM GRADES) G_TBL
    WHERE OPTION3 > OPTION2 AND OPTION3 > OPTION1
    ORDER BY LEFT(NAME, 3), ID ;
END

--- 다른 분 답 참고
CREATE PROCEDURE solution()
    SELECT NAME, ID
    FROM GRADES
    WHERE GREATEST(MIDTERM1/4+MIDTERM2/4+FINAL/2, MIDTERM1/2+MIDTERM2/2)<Final 
    ORDER BY LEFT(NAME, 3), ID ;
END

1) 각 Option에 대한 점수 계산한 서브쿼리 사용하여, Option3가 Option1과 Option2보다 큰 경우 추출하고 이름 앞 세 자리, 아이디에 따라 정렬 2) 서브쿼리 쓰지 않고 Where 절으로 Option1, Option2 중 더 큰 것이 Option3 보다 작은 경우만 추출 (여기서 *GREATEST *활용해 가장 큰 수 추출 가능, GREATEST의 반대는 LEAST)

mischievousNephews

문제

Your nephews Huey, Dewey, and Louie are staying with you over the winter holidays. Ever since they arrived, you've hardly had a day go by without some kind of incident - the little rascals do whatever they please! Actually, you're not even mad; the ideas they come up with are pretty amazing, and it looks like there's even a system to their mischief.

You decided to track and analyze their behavior, so you created the mischief table in your local database. The table has the following columns:

mischief_date: the date of the mischief (of the date type);
author: the nephew who caused the mischief ("Huey", "Dewey" or "Louie");
title: the title of the mischief.
It looks like each of your nephews is active on a specific day of the week. You decide to check your theory by creating another table as follows:
The resulting table should contain four columns, weekday, mischief_date, author, and title, where weekday is the weekday of mischief_date (0 for Monday, 1 for Tuesday, and so on, with 6 for Sunday). The table should be sorted by the weekday column, and for each weekday Huey's mischief should go first, Dewey's should go next, and Louie's should go last. In case of a tie, mischief_date should be a tie-breaker. If there's still a tie, the record with the lexicographically smallest title should go first.

It is guaranteed that all entries of mischief are unique.

--- 내가 작성한 답

CREATE PROCEDURE solution()
BEGIN
    SELECT WEEKDAY(MISCHIEF_DATE) WEEKDAY, MISCHIEF_DATE, AUTHOR, TITLE
    FROM MISCHIEF
    ORDER BY 1, FIELD(AUTHOR, 'Huey', 'Dewey', 'Louie'), 2, 4;
END

• Order 절에 FIELD 사용해 각 값에 대한 정렬 가능: FIELD(컬럼, 값1, 값2, 값3)

Group

travelDiary

문제

You are an avid traveler and you've visited so many countries that when people ask you where you've been, you can't even remember all of them! Luckily, every time you travel somewhere you write down the trip information in your diary. Now you want to get a list of all the different countries that you have visited using the information in your diary.

The diary is represented as a table diary, which has the following columns:

id: the unique ID of the trip;
travel_date: the date the trip began;
country: the country to which you traveled.
Given this diary table, create a semicolon-separated list of all the distinct countries you've visited, sorted lexicographically, and put the list in a table that has a single countries column.

-- 내가 작성한 답

CREATE PROCEDURE solution()
BEGIN
    SELECT GROUP_CONCAT(DISTINCT COUNTRY SEPARATOR ';') COUNTRIES
    FROM DIARY
    ORDER BY COUNTRY;
END

• Country 컬럼을 합치고 ;으로 각 Country마다 구분해주어야 하기 때문에 Group_concat로 Country 컬럼 값을 합치고 Separator ';' 을 활용해 ;로 구분: Group_concat(컬럼 Separator 'something')

soccerPlayers

You have a table soccer_team that contains information about the players in your favorite soccer team. This table has the following structure:

id: the unique ID of the player;
first_name: the first name of the player;
surname: the last name of the player;
player_number: the number that the player wears (the number is guaranteed to be unique).
Create a semicolon-separated list of all the players, sorted by their numbers, and put this list in a table under a column called players. The information about each player should have the following format: first_name surname #number.

-- 내가 작성한 답

CREATE PROCEDURE solution()
BEGIN
    SELECT GROUP_CONCAT(FIRST_NAME, ' ', SURNAME, ' ', '#', PLAYER_NUMBER 
                        ORDER BY PLAYER_NUMBER 
                        SEPARATOR '; ') PLAYERS
    FROM SOCCER_TEAM;
END

• first_name surname #number이라는 형식으로 Group_concat하고 separator ; 으로 각 값 구분

marketReport

Your company is planning to expand internationally very soon. You have been tasked with preparing a report on foreign markets and potential competitors.

After some investigation, you've created a database containing a foreignCompetitors table, which has the following structure:

competitor: the name of the competitor;
country: the country in which the competitor is operating.
In your report, you need to include the number of competitors per country and an additional row at the bottom that contains a summary: ("Total:", total_number_of_competitors)

Given the foreignCompetitors table, compose the resulting table with two columns: country and competitors. The first column should contain the country name, and the second column should contain the number of competitors in this country. The table should be sorted by the country names in ascending order. In addition, it should have an extra row at the bottom with the summary, as described above.

--- 내가 작성한 답
CREATE PROCEDURE solution()
BEGIN
    SELECT IF(GROUPING(COUNTRY),'Total:', COUNTRY) COUNTRY, COUNT(COMPETITOR) COMPETITORS
    FROM FOREIGNCOMPETITORS 
    GROUP BY COUNTRY WITH ROLLUP;
END

--- 다른 분 답 참고

CREATE PROCEDURE solution()
BEGIN
    SELECT IFNULL(COUNTRY, "Total:") COUNTRY, COUNT(COMPETITOR) COMPETITORS
    FROM FOREIGNCOMPETITORS 
    GROUP BY COUNTRY WITH ROLLUP;
END

• 각 COUNTRY마다 COMPETITOR를 COUNT 해주고, 총계도 RETURN 해야 하는 문제로, GROUP BY와 ROLLUP 사용 가능
• 단, ROLLUP 집계 컬럼의 경우, 데이터가 없는 것도 아닌데 NULL로 보여지는게 문제이기에 다음 두 방법 사용 가능 1) GROUPING 사용해 ROLLUP 집계 컬럼을 'Total: '로 변환 2) IFNULL 사용해 ROLLUP 집계 컬럼을 'Total: '로 변환

Basic Join 내용 정리

Basic Join

Average Population of Each Continent

Given the CITY and COUNTRY tables, query the names of all the continents (COUNTRY.Continent) and their respective average city populations (CITY.Population) rounded down to the nearest integer.

Note: CITY.CountryCode and COUNTRY.Code are matching key columns.

SELECT CT.CONTINENT, FLOOR(AVG(C.POPULATION)) -- TRUNCATE(AVG(C.POPULATION), 0)
FROM COUNTRY AS CT
JOIN CITY AS C
    on C.COUNTRYCODE = CT.CODE
GROUP BY CT.CONTINENT

• 반올림: ROUND, 올림: CEILING, 내림: FLOOR

The Report

You are given two tables: Students and Grades. Students contains three columns ID, Name and Marks. Ketty gives Eve a task to generate a report containing three columns: Name, Grade and Mark. Ketty doesn't want the NAMES of those students who received a grade lower than 8. The report must be in descending order by grade -- i.e. higher grades are entered first. If there is more than one student with the same grade (8-10) assigned to them, order those particular students by their name alphabetically. Finally, if the grade is lower than 8, use "NULL" as their name and list them by their grades in descending order. If there is more than one student with the same grade (1-7) assigned to them, order those particular students by their marks in ascending order.

Write a query to help Eve.

SELECT (CASE WHEN GRADE < 8 THEN NULL 
             ELSE NAME END) AS NAME -- IF (GRADE < 8, NULL, NAME) AS NAME 
    , GRADE 
    , MARKS
FROM STUDENTS
JOIN GRADES G
    ON MARKS BETWEEN MIN_MARK AND MAX_MARK -- ON G.GRADE = CASE WHEN MARKS BETWEEN MIN_MARK AND MAX_MARK THEN GRADE END
ORDER BY GRADE DESC, NAME, MARKS 

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 0 from your result.

SELECT H.HACKER_ID, NAME, SUM(SCORE)
FROM (SELECT HACKER_ID, CHALLENGE_ID, MAX(SCORE) SCORE FROM SUBMISSIONS GROUP BY 1, 2) S
LEFT JOIN HACKERS H
    on H.HACKER_ID = S.HACKER_ID
WHERE SCORE != 0
GROUP BY 1, 2
ORDER BY 3 DESC, 1

Top Competitors

Julia just finished conducting a coding contest, and she needs your help assembling the leaderboard! Write a query to print the respective hacker_id and name of hackers who achieved full scores for more than one challenge. Order your output in descending order by the total number of challenges in which the hacker earned a full score. If more than one hacker received full scores in same number of challenges, then sort them by ascending hacker_id.

SELECT H.HACKER_ID, NAME
FROM SUBMISSIONS S 
JOIN CHALLENGES C
    on C.CHALLENGE_ID = S.CHALLENGE_ID
JOIN DIFFICULTY D
    on C.DIFFICULTY_LEVEL = D.DIFFICULTY_LEVEL
JOIN HACKERS H 
    on H.HACKER_ID = S.HACKER_ID 
WHERE D.SCORE = S.SCORE
GROUP BY 1, 2
HAVING COUNT(S.CHALLENGE_ID) > 1
ORDER BY COUNT(S.CHALLENGE_ID) DESC, 1

• CHALLENGE_ID 기준으로 제출 TBL과 챌린지 TBL 조인
• 이 테이블에 DIFFICULTY_LEVEL 기준으로 DIFFICULTY TBL 조인
• 이 테이블에 HACKER_ID 기준으로 HACKERS TBL과 조인
• HAVING COUNT(CHALLENGE_ID) > 1: 한 챌린지 이상

Challenges

Julia 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.

SELECT H.HACKER_ID, H.NAME, COUNT(*) as CNT 
FROM HACKERS as H
JOIN CHALLENGES C 
    on C.HACKER_ID = H.HACKER_ID
GROUP BY 1, 2
HAVING
    CNT = (SELECT COUNT(*) 
           FROM CHALLENGES
           GROUP BY HACKER_ID 
           ORDER BY COUNT(*) DESC 
           LIMIT 1) OR
    CNT IN (SELECT CNT 
            FROM (SELECT HACKER_ID, COUNT(*) AS cnt 
                  FROM CHALLENGES
                  GROUP BY 1) SUB
            GROUP BY CNT
            HAVING COUNT(*)=1)
ORDER BY 3 DESC, 1

Ollivander's Inventory

SELECT W.ID, WP.AGE, W.COINS_NEEDED, W.POWER 
FROM WANDS W
JOIN WANDS_PROPERTY WP
    ON WP.CODE = W.CODE
WHERE WP.IS_EVIL=0 
    AND W.COINS_NEEDED in (SELECT MIN(W1.COINS_NEEDED) 
                           FROM WANDS as W1
                           JOIN WANDS_PROPERTY as WP1
                                ON WP1.CODE = W1.CODE
                           WHERE W1.POWER = W.POWER 
                            AND WP1.AGE = WP.AGE)
ORDER BY 4 DESC, 2 DESC

위 사이트는 해커랭크에서 SQL 문제를 풀어볼 수 있는 사이트다. 문제를 풀면서 조금 헷갈리거나 어려웠던 문제나 기억하고 싶은 내용 위주로 정리를 하려고 한다.

Basic Select

Weather Observation Station 3

Query a list of CITY names from STATION for cities that have an even ID number. Print the results in any order, but exclude duplicates from the answer.

SELECT DISTINCT CITY
FROM STATION
WHERE ID % 2 = 0

• 연산자 산술 연산자 %: 나머지 연산으로, 왼쪽의 값을 오른쪽 값으로 나눈 나머지를 반환

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.

(SELECT CITY, LENGTH(CITY) 
FROM STATION 
ORDER BY LENGTH(CITY), CITY
LIMIT 1)
UNION
(SELECT CITY, LENGTH(CITY)
FROM STATION 
ORDER BY LENGTH(CITY) DESC, CITY 
LIMIT 1)

(SELECT CITY, LENGTH(CITY) 
FROM STATION 
WHERE LENGTH(CITY) = (SELECT MIN(LENGTH(CITY)) FROM STATION)
ORDER BY 1
LIMIT 1)
UNION
(SELECT CITY, LENGTH(CITY) 
FROM STATION 
WHERE LENGTH(CITY) = (SELECT MAX(LENGTH(CITY)) FROM STATION)
ORDER BY 1
LIMIT 1)

• UNION: 2개 이상 테이블에 존재하는 같은 성격의 값을 하나의 쿼리로 추출
• 참고 1> CITY 문자 길이와 이름 순으로 정렬해 가장 짧은 문자 길이의 이름 및 길이 추출, 또 문자 길이(내림차순)와 이름 순으로 정렬해 가장 긴 문자 길이의 이름 및 길이 추출한 뒤 UNION으로 합치기 2> WHERE 조건에 CITY 문자 길이가 MIN인 값을 넣어 가장 짧은 문자 길이의 이름 및 길이 추출, 또 WHERE 조건에 CITY 문자 길이가 MAX인 값을 넣어 가장 긴 문자 길이의 이름 및 길이 추출한 뒤 UNION으로 합치기

Weather Observation Station 6

Query the list of CITY names starting with vowels (i.e., a, e, i, o, or u) from STATION. Your result cannot contain duplicates.

SELECT DISTINCT CITY
FROM STATION
WHERE LEFT(CITY, 1) IN ('a','e','i','o','u')

SELECT DISTINCT CITY
FROM STATION
WHERE REGEXP_LIKE(CITY, '^[aeiou]')

SELECT DISTINCT CITY
FROM STATION
WHERE CITY REGEXP '^a|^e|^i|^o|^u' 

SELECT DISTINCT CITY
FROM STATION
WHERE CITY REGEXP '^[aeiou]' 

• LEFT(문자열, 길이): 문자열을 받아서 왼쪽부터 원하는 길이만큼 자르는 함수

• REGEXP_LIKE(문자열, 정규식 패턴): 정규식을 비교하여 일치할 경우 추출해주는 함수

• REGEXP: 정규 표현식(Regular Expression)을 활용해 기본 연산자보다 더 복잡한 문자열 조건을 걸어 데이터 검색 가능

  • |(수직선): |로 구분된 문자에 해당하는 문자열 찾기 (ex> "하이|안녕" -> '하이' 또는 '안녕'에 해당하는 문자열 찾기)

  • [] : 안에 나열된 패턴에 해당하는 문자열 찾기 (ex> "[abc]d" -> 'ad' 또는 'bd' 또는 'cd'인 문자열 찾기)

  • ^ : 시작하는 문자열을 찾기 (ex> "^폰" -> '폰'으로 시작하는 문자열 찾기)

  • $ : 끝나는 문자열을 찾기 (ex> "고기$"-> '고기'로 끝나는 문자열을 찾음)

Weather Observation Station 7

Query the list of CITY names ending with vowels (a, e, i, o, u) from STATION. Your result cannot contain duplicates.

SELECT DISTINCT CITY
FROM STATION
WHERE RIGHT(CITY, 1) IN ('a','e','i','o','u')

SELECT DISTINCT CITY
FROM STATION
WHERE CITY REGEXP '[aeiou]$'

SELECT DISTINCT CITY
FROM STATION
WHERE CITY REGEXP 'a$|e$|i$|o$|u$' 

SELECT DISTINCT CITY
FROM STATION
WHERE REGEXP_LIKE(CITY, '[aeiou]$')

• RIGHT(문자열, 길이): 문자열을 받아서 오른쪽부터 원하는 길이만큼 자르는 함수

Weather Observation Station 8

Query the list of CITY names from STATION which have vowels (i.e., a, e, i, o, and u) as both their first and last characters. Your result cannot contain duplicates.

SELECT CITY
FROM STATION
WHERE LEFT(CITY, 1) IN ('a', 'e', 'i', 'o', 'u') and RIGHT(CITY, 1) IN ('a', 'e', 'i', 'o', 'u')

SELECT CITY
FROM STATION
WHERE CITY REGEXP '^[aeiou]' and CITY REGEXP '[aeiou]$'

SELECT CITY
FROM STATION
WHERE REGEXP_LIKE (CITY, '^[aeiou]') and REGEXP_LIKE (CITY, '[aeiou]$')

Weather Observation Station 11

Query the list of CITY names from STATION that either do not start with vowels or do not end with vowels. Your result cannot contain duplicates.

SELECT DISTINCT CITY
FROM STATION
WHERE LEFT(CITY, 1) NOT IN ('a','e','i','o','u') or RIGHT(CITY, 1) NOT IN ('a','e','i','o','u')

SELECT DISTINCT CITY
FROM STATION
WHERE CITY REGEXP '^[^aeiou]' or CITY REGEXP '[^aeiou]$'

• 참고
  • ^x: x로 시작하는 문자열을 찾기
  • [^xy] : not의 의미로, x및 y를 제외한 문자 의미함
  • 위 예시에서 '^[^aeiou]'의 뜻은 aeiou를 제외한 문자로 시작하는 문자열 찾기

Higher Than 75 Marks

Query the Name of any student in STUDENTS who scored higher than 75 Marks. Order your output by the last three characters of each name. If two or more students both have names ending in the same last three characters (i.e.: Bobby, Robby, etc.), secondary sort them by ascending ID.

SELECT NAME
FROM STUDENTS
WHERE MARKS > 75
ORDER BY RIGHT(NAME, 3), ID 

Advanced Select

Type of Triangle

Write a query identifying the type of each record in the TRIANGLES table using its three side lengths. Output one of the following statements for each record in the table:

Equilateral: It's a triangle with 3 sides of equal length.
Isosceles: It's a triangle with 2 sides of equal length.
Scalene: It's a triangle with 3 sides of differing lengths.
Not A Triangle: The given values of A, B, and C don't form a triangle.

SELECT CASE WHEN A + B <= C OR B + C <= A OR A + C <= B THEN 'Not A Triangle'
            WHEN A = B AND A = C THEN 'Equilateral'
            WHEN A = B OR B = C OR A = C THEN 'Isosceles'
            WHEN A <> B OR B <> C THEN 'Scalene'
            END
FROM TRIANGLES;

• 참고: CASE WHEN 구문 사용해 해당 조건에 맞는 값 출력

The PADS

Generate the following two result sets:

1. Query an alphabetically ordered list of all names in OCCUPATIONS, immediately followed by the first letter of each profession as a parenthetical (i.e.: enclosed in parentheses). For example: AnActorName(A), ADoctorName(D), AProfessorName(P), and ASingerName(S).
2. Query the number of ocurrences of each occupation in OCCUPATIONS. Sort the occurrences in ascending order, and output them in the following format:

   There are a total of [occupation_count] [occupation]s.

   where occupation_count is the number of occurrences of an occupation in OCCUPATIONS and occupation is the lowercase occupation name. If more than one Occupation has the same occupation_count, they should be ordered alphabetically.

SELECT CONCAT(Name, '(', LEFT(OCCUPATION, 1), ')')
FROM OCCUPATIONS
ORDER BY NAME;

SELECT CONCAT('There are a total of ', COUNT(*), ' ', LOWER(OCCUPATION), 's.')
FROM OCCUPATIONS
GROUP BY OCCUPATION
ORDER BY COUNT(*), OCCUPATION;

• CONCAT(문자열1, 문자열2,...): 여러 문자열을 하나로 합칠 때 CONCAT 함수 사용, 파라미터를 두 개 이상 사용 가능
• LOWER(문자열): LOWER 함수는 모든 문자열을 소문자로 통일시켜주는 함수이고, 이와 반대되는 함수는 UPPER 함수 (즉, UPPER 함수는 컬럼의 문자열을 모두 대문자 알파벳으로 변경)

New Companies

Given the table schemas below, write a query to print the company_code, founder name, total number of lead managers, total number of senior managers, total number of managers, and total number of employees. Order your output by ascending company_code.

Note: The tables may contain duplicate records. The company_code is string, so the sorting should not be numeric. For example, if the company_codes are C_1, C_2, and C_10, then the ascending company_codes will be C_1, C_10, and C_2.

SELECT C.COMPANY_CODE
    , FOUNDER
    , COUNT(DISTINCT L.LEAD_MANAGER_CODE)
    , COUNT(DISTINCT S.SENIOR_MANAGER_CODE)
    , COUNT(DISTINCT M.MANAGER_CODE)
    , COUNT(DISTINCT E.EMPLOYEE_CODE)
FROM Company C
LEFT JOIN LEAD_MANAGER L
    ON C.COMPANY_CODE = L.COMPANY_CODE
LEFT JOIN SENIOR_MANAGER S 
    ON L.LEAD_MANAGER_CODE = S.LEAD_MANAGER_CODE
LEFT JOIN MANAGER M
    ON M.SENIOR_MANAGER_CODE = S.SENIOR_MANAGER_CODE
LEFT JOIN EMPLOYEE AS E
    ON E.MANAGER_CODE = M.MANAGER_CODE
GROUP BY 1, 2
ORDER BY 1

Binary Tree Nodes

You are given a table, BST, containing two columns: N and P, where N represents the value of a node in Binary Tree, and P is the parent of N. Write a query to find the node type of Binary Tree ordered by the value of the node. Output one of the following for each node:

    Root: If node is root node.
    Leaf: If node is leaf node.
    Inner: If node is neither root nor leaf node.

SELECT N,
      (CASE WHEN P is null then 'Root'
            WHEN N NOT IN (SELECT DISTINCT P FROM BST WHERE P is not null) THEN 'Leaf'
            ELSE 'Inner' END) Node
FROM BST
ORDER BY 1

• 참고: Node의 경우 P가 null값이면 Root이고, P 리스트 안에 없으면 Leaf고, 다른 경우는 Inner라고 할 수 있기에 위와 같이 작성

Occupations

Pivot the Occupation column in OCCUPATIONS so that each Name is sorted alphabetically and displayed underneath its corresponding Occupation. The output column headers should be Doctor, Professor, Singer, and Actor, respectively.

Note: Print NULL when there are no more names corresponding to an occupation.

SELECT MIN(CASE WHEN OCCUPATION = 'DOCTOR' THEN NAME END) AS DOCTOR
     , MIN(CASE WHEN OCCUPATION = 'PROFESSOR' THEN NAME END) AS PROFESSOR
     , MIN(CASE WHEN OCCUPATION = 'SINGER' THEN NAME END) AS SINGER
     , MIN(CASE WHEN OCCUPATION = 'ACTOR' THEN NAME END) AS ACTOR
FROM (SELECT NAME, OCCUPATION, RANK() OVER (PARTITION BY OCCUPATION ORDER BY NAME ASC) RK
      FROM OCCUPATIONS) RANKLISTS
GROUP BY RK

• 참고: OCCUPATION을 기준으로 그룹핑해주어 OCCUPATION별 NAME 순으로 RANK 매겨주는 서브 쿼리 작성 후, 그 서브쿼리에서 DOCTOR, PROFESSOR, SINGER, ACTOR 출력 (이때 MIN을 사용하고 RANK를 GROUP BY 해주어야 함, MAX()를 이용해도 동일한 결과 <- 마지막에 GROUP BY를 사용하기 위해 집계 함수 사용)

Aggregation

The Blunder

Samantha was tasked with calculating the average monthly salaries for all employees in the EMPLOYEES table, but did not realize her keyboard's key was broken until after completing the calculation. She wants your help finding the difference between her miscalculation (using salaries with any zeros removed), and the actual average salary.

Write a query calculating the amount of error, and round it up to the next integer.

SELECT CEILING(AVG(SALARY) - AVG(REPLACE(SALARY, 0, ''))) 
FROM EMPLOYEES

• CEILING: 해당 숫자보다 크거나 같은 최소 정수 반환
• 참고: 반대로 FLOOR 함수는 해당 숫자보다 작거나 같은 최대 정수 반환
  • 예시: 11.92 숫자에 대해 각각 CEILING은 12, FLOOR는 11를 반환
• REPLACE(Column,'해당문자','바꿀문자'): 어떠한 문자를 다른 문자로 변환하고 싶을 때 사용, 위 문제에서는 SALARY 컬럼에서 0을 ''로 변환(즉, SALARY 컬럼에서 0을 없앤다는 의미)

Top Earners

We define an employee's total earnings to be their SALARY*MONTHS monthly worked, and the maximum total earnings to be the maximum total earnings for any employee in the Employee table. Write a query to find the maximum total earnings for all employees as well as the total number of employees who have maximum total earnings. Then print these values as 2 space-separated integers.

SELECT MAX(SALARY*MONTHS)
    , COUNT(EMPLOYEE_ID) 
FROM EMPLOYEE
WHERE SALARY*MONTHS = (SELECT MAX(SALARY*MONTHS) FROM EMPLOYEE)

SELECT SALARY*MONTHS   
    , COUNT(SALARY*MONTHS) 
FROM EMPLOYEE
GROUP BY 1
ORDER BY 1 DESC
LIMIT 1

Weather Observation Station 15

Query the Western Longitude (LONG_W) for the largest Northern Latitude (LAT_N) in STATION that is less than 137.2345. Round your answer to 4 decimal places.

SELECT ROUND(LONG_W, 4)
FROM STATION
WHERE LAT_N = (SELECT MAX(LAT_N) FROM STATION WHERE LAT_N < 137.2345)

SELECT ROUND(LONG_W, 4) FROM STATION
WHERE LAT_N < 137.2345
ORDER BY LAT_N DESC LIMIT 1;

Weather Observation Station 18

Consider P1(a, b) and P2(c, d) to be two points on a 2D plane.

a happens to equal the minimum value in Northern Latitude (LAT_N in STATION). b happens to equal the minimum value in Western Longitude (LONG_W in STATION). c happens to equal the maximum value in Northern Latitude (LAT_N in STATION). d happens to equal the maximum value in Western Longitude (LONG_W in STATION). Query the Manhattan Distance between points P1 and P2 and round it to a scale of 4 decimal places.

SELECT ROUND(ABS(MAX(LAT_N)-MIN(LAT_N)) + ABS(MAX(LONG_W)-MIN(LONG_W)), 4)
FROM STATION

• ABS: 절대값 구할 때 사용하는 함수로, 위 문제에서는 LAT_N의 최대값과 LAT_N의 최솟값의 차이의 절대값과 LONG_W의 최대값과 LAT_N의 최솟값의 차이의 절댓값을 구할 때 사용

Weather Observation Station 19

Consider P1(a, c) and P2(b, d) to be two points on a 2D plane where (a, b) are the respective minimum and maximum values of Northern Latitude (LAT_N) and (c, d) are the respective minimum and maximum values of Western Longitude (LONG_W) in STATION.

Query the Euclidean Distance between points P1 and P2 and format your answer to display 4 decimal digits.

SELECT ROUND(SQRT(POWER(MAX(LAT_N)-MIN(LAT_N), 2) + POWER(MAX(LONG_W)-MIN(LONG_W), 2)), 4)
FROM STATION

• POWER(숫자, 거듭제곱 횟수): 숫자를 제곱 횟수만큼 제곱하고 싶을 때 사용하는 함수
• SQRT: 제곱근을 구할 때 사용하는 함수

Weather Observation Station 20

A median is defined as a number separating the higher half of a data set from the lower half. Query the median of the Northern Latitudes (LAT_N) from STATION and round your answer to 4 decimal places.

SELECT ROUND(LAT_N, 4)
FROM (SELECT *, PERCENT_RANK () OVER (ORDER BY LAT_N) RK
      FROM STATION) TBL
WHERE RK = 0.5

• PERCENT_RANK: 행의 PERCENTILE RANK를 구하고 싶을 때 사용하는 함수로, 위 문제에서는 MEDIAN값을 구하기 위해 LAT_N 순으로 정렬하고 PERCENTILE RANK를 매기는 서브쿼리 작성, 그 후 그 서브쿼리에서 RANK가 0.5(즉, 중앙에 위치한)인 값을 추출

SQL 고득점 KIT

GROUP BY

가격대 별 상품 개수 구하기(lev2)

SELECT (CASE WHEN PRICE < 10000 THEN 0
        ELSE TRUNCATE (PRICE, -4) 
        END) as PRICE_GROUP
    , COUNT(PRODUCT_ID) as PRODUCTS
FROM PRODUCT
GROUP BY 1
ORDER BY 1

• TRUNCATE(column, 숫자): 숫자를 버릴 자릿수 아래로 버림 (숫자가 음수일 경우, 정수에 뒤에서부터 지정된 자릿수까지 0으로 처리)

진료과별 총 예약 횟수 출력하기(lev2)

SELECT MCDP_CD as '진료과코드'
    , COUNT(PT_NO) as '5월예약건수'
FROM APPOINTMENT
WHERE MONTH(APNT_YMD) = '05' # DATE_FORMAT(APNT_YMD, '%Y-%m') = '2022-05'
GROUP BY MCDP_CD
ORDER BY 2, 1

성분으로 구분한 아이스크림 총 주문량(lev2)

SELECT INGREDIENT_TYPE, SUM(TOTAL_ORDER) as TOTAL_ORDER
FROM FIRST_HALF F
LEFT JOIN ICECREAM_INFO I
    on F.FLAVOR = I.FLAVOR
GROUP BY 1
ORDER BY 2

고양이와 개는 몇 마리 있을까(lev2)

SELECT ANIMAL_TYPE, COUNT(ANIMAL_TYPE) as COUNT
FROM ANIMAL_INS
GROUP BY 1
ORDER BY 1

입양 시각 구하기(1)(lev2)

SELECT HOUR(DATETIME) as HOUR
    , COUNT(ANIMAL_ID) as COUNT
FROM ANIMAL_OUTS
WHERE HOUR(DATETIME) >= 9 AND HOUR(DATETIME) < 20
GROUP BY 1
ORDER BY 1

• HOUR(날짜): 날짜 중 시간대만 반환 (9시 -> 9)

동명 동물 수 찾기(lev2)

SELECT NAME, COUNT(NAME) as COUNT
FROM ANIMAL_INS
GROUP BY 1
HAVING COUNT(NAME) > 1
ORDER BY 1

자동차 종류 별 특정 옵션이 포함된 자동차 수 구하기(lev2)

SELECT CAR_TYPE, COUNT(CAR_ID) as CARS
FROM CAR_RENTAL_COMPANY_CAR
WHERE OPTIONS REGEXP '시트'
# WHERE OPTIONS LIKE '%시트%'
# WHERE OPTIONS REGEXP '통풍시트|열선시트|가죽시트'
GROUP BY 1
ORDER BY 1

• REGEXP '가|나|다|라': 컬럼에 '가' 또는 '나' 또는 '다' 를 포함한 레코드 모두 출력 (= where column like '%가%' or column like '%나%' or column like '%다%')

• REGEXP '가': 컬럼에 '가'를 포함한 모든 레코드 출력 (= where column like '%가%')

카테고리 별 도서 판매량 집계하기(lev3)

SELECT CATEGORY, SUM(SALES) as TOTAL_SALES
FROM BOOK B
LEFT JOIN BOOK_SALES BS
    on B.BOOK_ID = BS.BOOK_ID 
WHERE DATE_FORMAT(SALES_DATE, "%Y-%m") LIKE '2022-01'
GROUP BY 1
ORDER BY 1

즐겨찾기가 가장 많은 식당 정보 출력하기(lev3)

SELECT FOOD_TYPE, REST_ID, REST_NAME, FAVORITES
FROM (SELECT *, RANK () OVER (PARTITION BY FOOD_TYPE ORDER BY FAVORITES DESC) AS RK 
    FROM REST_INFO) AS R
WHERE R.RK = 1
ORDER BY 1 DESC

• RANK() OVER (PARTITION BY 그룹핑 column ORDER BY 정렬 column): 순위 구할 때 사용하는 함수로, PARTITION BY는 특정 컬럼 기준으로 그룹핑한다는 의미, ORDER BY는 특정 컬럼 기준으로 정렬한다는 의미

대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기(lev3)

SELECT MONTH(START_DATE) as MONTH, CAR_ID, COUNT(HISTORY_ID) as RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE CAR_ID IN (SELECT CAR_ID
                FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
                WHERE MONTH(START_DATE) BETWEEN 8 and 10
                GROUP BY 1
                HAVING COUNT(HISTORY_ID) >= 5)
AND MONTH(START_DATE) BETWEEN 8 and 10
GROUP BY 1, 2
ORDER BY 1, 2 DESC

• 참고: WHERE 서브쿼리 사용해 8월부터 10월까지 총 대여 횟수가 5회 이상인 CAR_ID만 추출

식품분류별 가장 비싼 식품의 정보 조회하기(lev4)

SELECT CATEGORY, PRICE as MAX_PRICE, PRODUCT_NAME
FROM (SELECT *, RANK () OVER (PARTITION BY CATEGORY ORDER BY PRICE DESC) AS RK
    FROM FOOD_PRODUCT) R
WHERE CATEGORY IN ('과자','국','김치','식용유')
    AND RK = 1
ORDER BY 2 DESC

SELECT CATEGORY, PRICE AS MAX_PRICE, PRODUCT_NAME
FROM FOOD_PRODUCT
WHERE CATEGORY IN ('과자', '국', '김치', '식용유')
   AND PRICE IN (SELECT MAX(PRICE) FROM FOOD_PRODUCT GROUP BY CATEGORY)
ORDER BY 2 DESC

• 참고: RANK 함수 사용하거나, 혹은 WHERE 절에 GROUP BY 사용하는 방법

년, 월, 성별 별 상품 구매 회원 수 구하기(lev4)

SELECT YEAR(SALES_DATE) as YEAR, MONTH(SALES_DATE) as MONTH, GENDER, COUNT(DISTINCT OS.USER_ID) as USERS
FROM ONLINE_SALE OS
LEFT JOIN USER_INFO UI
    on UI.USER_ID = OS.USER_ID
WHERE GENDER IS NOT NULL
GROUP BY 1, 2, 3
ORDER BY 1, 2, 3

저자 별 카테고리 별 매출액 집계하기(lev4)

SELECT AUTHOR_ID, AUTHOR_NAME, CATEGORY, SUM(TOTAL_SALES) AS TOTAL_SALES
FROM (SELECT B.BOOK_ID, CATEGORY, B.AUTHOR_ID, AUTHOR_NAME, (PRICE*SALES) AS TOTAL_SALES
    FROM BOOK B
    JOIN AUTHOR A
        on A.AUTHOR_ID = B.AUTHOR_ID
    JOIN BOOK_SALES BS
        on B.BOOK_ID = BS.BOOK_ID
    WHERE MONTH(SALES_DATE) = 1
) BOOK_TBL
GROUP BY 1, 2, 3
ORDER BY 1, 3 DESC

WITH TBL as (
SELECT AUTHOR_ID, AUTHOR_NAME, CATEGORY, (SALES*PRICE) as TOTAL_SALES
FROM BOOK_SALES BS
LEFT JOIN (SELECT BOOK_ID, CATEGORY, A.AUTHOR_ID, AUTHOR_NAME, PRICE
            FROM BOOK B
            LEFT JOIN AUTHOR A
            on B.AUTHOR_ID = A.AUTHOR_ID) BA
    on BS.BOOK_ID = BA.BOOK_ID
WHERE MONTH(SALES_DATE) = 1
)

SELECT AUTHOR_ID, AUTHOR_NAME, CATEGORY, SUM(TOTAL_SALES) AS TOTAL_SALES
FROM TBL
GROUP BY 1, 2, 3
ORDER BY 1, 3 DESC

• 참고: 서브 쿼리로 해결 가능 혹은 WITH문 사용 가능

입양 시각 구하기(2)(lev4)

SET @HOUR = -1;

WITH TIME_TBL as (
SELECT (@HOUR := @HOUR +1) as HOUR
FROM ANIMAL_OUTS
WHERE @HOUR < 23
)

SELECT TT.HOUR, IFNULL(COUNT, 0) as COUNT
FROM TIME_TBL TT
LEFT JOIN (SELECT HOUR(DATETIME) as HOUR, COUNT(ANIMAL_ID) as COUNT
            FROM ANIMAL_OUTS
            GROUP BY 1
            ORDER BY 1) AO
    ON TT.HOUR = AO.HOUR

• SET: 사용자 정의 변수로, SET @변수이름 = 대입값; 으로 변수 지정 (위 문제에서는 변수 HOUR를 -1으로 지정하고 HOUR을 -1부터 +1씩 증가시킨다는 뜻(0~23))

  WITH RECURSIVE HOUR as (
  SELECT 0 as HOUR
  UNION ALL
  SELECT HOUR+1 FROM HOUR WHERE HOUR<23
  )
  

SELECT HOUR, COUNT(ANIMAL_ID) as COUNT FROM HOUR H LEFT JOIN ANIMAL_OUTS AO on H.HOUR = HOUR(AO.DATETIME) GROUP BY 1

- **WITH RECURSIVE**: 재귀쿼리로, 이를 이용해 실제 테이블을 생성하지 않아도 가상 테이블 생성 가능 (메모리 상에 가상의 테이블 저장하는 방법)

    - 형식
    ```
    WITH RECURSIVE table as(
        SELECT 초기값 as column1
        UNION ALL
        SELECT column1의 계산식 FROM table WHERE 제어문
        )
    ```
    - 반드시 UNION 사용
    - 반복되는 마지막 문장의 경우 WHERE 뒤에 항상 정지조건 요구됨


### JOIN

#### [상품 별 오프라인 매출 구하기(lev2)](https://school.programmers.co.kr/learn/courses/30/lessons/131533)

SELECT PRODUCT_CODE, (PRICE*SUM(SALES_AMOUNT)) AS SALES FROM PRODUCT P INNER JOIN OFFLINE_SALE O on P.PRODUCT_ID = O.PRODUCT_ID GROUP BY 1 ORDER BY 2 DESC, 1

- **INNER JOIN**: 교집합을 의미, 두 테이블을 매치할 때 겹치지 않는 행이 존재할 경우 그 행은 결과에서 제외됨 

#### [조건에 맞는 도서와 저자 리스트 출력하기(lev2)](https://school.programmers.co.kr/learn/courses/30/lessons/144854)

SELECT BOOK_ID, AUTHOR_NAME, DATE_FORMAT(PUBLISHED_DATE, '%Y-%m-%d') AS PUBLISHED_DATE FROM BOOK B LEFT JOIN AUTHOR A on A.AUTHOR_ID = B.AUTHOR_ID WHERE CATEGORY = '경제' ORDER BY 3

- **LEFT JOIN**: 왼쪽 테이블 기준으로 오른쪽의 테이블 매치

#### [없어진 기록 찾기(lev3)](https://school.programmers.co.kr/learn/courses/30/lessons/59042)

SELECT AO.ANIMAL_ID, AO.NAME FROM ANIMAL_OUTS AO LEFT JOIN ANIMAL_INS AI on AI.ANIMAL_ID = AO.ANIMAL_ID WHERE INTAKE_CONDITION IS NULL ORDER BY 1, 2

#### [오랜 기간 보호한 동물(1)(lev3)](https://school.programmers.co.kr/learn/courses/30/lessons/59044)

SELECT AI.NAME, AI.DATETIME FROM ANIMAL_INS AI LEFT JOIN ANIMAL_OUTS AO on AI.ANIMAL_ID = AO.ANIMAL_ID WHERE AO.DATETIME IS NULL ORDER BY 2 LIMIT 3

#### [있었는데요 없었습니다(lev3)](https://school.programmers.co.kr/learn/courses/30/lessons/59043)

SELECT AI.ANIMAL_ID, AI.NAME FROM ANIMAL_INS AI LEFT JOIN ANIMAL_OUTS AO on AI.ANIMAL_ID = AO.ANIMAL_ID WHERE AI.DATETIME > AO.DATETIME ORDER BY AI.DATETIME

#### [보호소에서 중성화한 동물(lev4)](https://school.programmers.co.kr/learn/courses/30/lessons/59045)

SELECT AI.ANIMAL_ID, AI.ANIMAL_TYPE, AI.NAME FROM ANIMAL_INS AI LEFT JOIN ANIMAL_OUTS AO ON AI.ANIMAL_ID = AO.ANIMAL_ID WHERE AI.SEX_UPON_INTAKE != AO.SEX_UPON_OUTCOME

#### [주문량이 많은 아이스크림들 조회하기(lev4)](https://school.programmers.co.kr/learn/courses/30/lessons/133027)

SELECT FH.FLAVOR FROM FIRST_HALF FH LEFT JOIN JULY J on J.FLAVOR = FH.FLAVOR GROUP BY 1 ORDER BY FH.TOTAL_ORDER + SUM(J.TOTAL_ORDER) DESC LIMIT 3

#### [그룹별 조건에 맞는 식당 목록 출력하기(lev4)](https://school.programmers.co.kr/learn/courses/30/lessons/131117)

WITH MEMBER_COUNT as( SELECT M.MEMBER_ID, MEMBER_NAME, COUNT(REVIEW_ID) as REVIEW_COUNT FROM MEMBER_PROFILE M LEFT JOIN REST_REVIEW R on R.MEMBER_ID = M.MEMBER_ID GROUP BY 1, 2 ORDER BY 3 DESC )

SELECT MEMBER_NAME, REVIEW_TEXT, DATE_FORMAT(REVIEW_DATE, '%Y-%m-%d') as REVIEW_DATE FROM REST_REVIEW R LEFT JOIN MEMBER_COUNT M on M.MEMBER_ID = R.MEMBER_ID WHERE REVIEW_COUNT = (SELECT MAX(REVIEW_COUNT) FROM MEMBER_COUNT) ORDER BY 3, 1

#### [5월 식품들의 총매출 조회하기(lev4)](https://school.programmers.co.kr/learn/courses/30/lessons/131124)

SELECT FP.PRODUCT_ID, PRODUCT_NAME, PRICE*SUM(AMOUNT) as TOTAL_SALES FROM FOOD_ORDER FO INNER JOIN FOOD_PRODUCT FP on FP.PRODUCT_ID = FO.PRODUCT_ID WHERE DATE_FORMAT(PRODUCE_DATE, '%Y-%m') = '2022-05' GROUP BY 1, 2 ORDER BY 3 DESC, 1

#### [상품을 구매한 회원 비율 구하기 (lev5)](https://school.programmers.co.kr/learn/courses/30/lessons/131534)

WITH UI as( SELECT USER_ID FROM USER_INFO UI WHERE YEAR(JOINED) = '2021' )

SELECT YEAR(SALES_DATE) as YEAR , MONTH(SALES_DATE) as MONTH , COUNT(DISTINCT OS.USER_ID) as PUCHASED_USERS , ROUND(COUNT(DISTINCT OS.USER_ID)/(SELECT COUNT(USER_ID) FROM UI), 1) as PUCHASED_RATIO FROM UI INNER JOIN ONLINE_SALE OS on OS.USER_ID = UI.USER_ID GROUP BY 1, 2 ORDER BY 1, 2

### String, Date

#### [자동차 대여 기록에서 장기/단기 대여 구분하기(lev1)](https://school.programmers.co.kr/learn/courses/30/lessons/151138)

SELECT HISTORY_ID , CAR_ID , DATE_FORMAT(START_DATE, '%Y-%m-%d') START_DATE , DATE_FORMAT(END_DATE, '%Y-%m-%d') END_DATE , (CASE WHEN DATEDIFF(END_DATE, START_DATE) + 1 >= 30 THEN '장기 대여' ELSE '단기 대여' END) as RENT_TYPE FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY WHERE DATE_FORMAT(START_DATE, '%Y-%m') = '2022-09' ORDER BY 1 DESC

- **DATEDIFF(날짜1, 날짜2)**: 날짜 차이 계산하는 함수 

#### [카테고리 별 상품 개수 구하기(lev2)](https://school.programmers.co.kr/learn/courses/30/lessons/131529)

SELECT SUBSTR(PRODUCT_CODE, 1, 2) as CATEGORY, COUNT(PRODUCT_ID) FROM PRODUCT GROUP BY 1 ORDER BY 1

- **SUBSTR(문자열, 시작 위치값)**: 시작 위치값 숫자 (5라면 5번째) 문자열부터 읽으라는 뜻

- **SUBSTR(원본 문자열,시작 위치값,가져올 길이값)**: 시작 위치값 숫자 (5라면 5번째) 문자열부터 읽고, 가져올 길이값 (3이라면 3글자를) 가져오라는 뜻


#### [DATETIME에서 DATE로 형 변환(lev2)](https://school.programmers.co.kr/learn/courses/30/lessons/59414)

SELECT ANIMAL_ID, NAME, DATE_FORMAT(DATETIME, '%Y-%m-%d') as '날짜' FROM ANIMAL_INS ORDER BY 1

- **DATE_FORMAT(날짜, 형식)**: 날짜를 해당 형식으로 출력
- **참고** %Y: 네 자리 년도, %m: 숫자 월, %d: 숫자 일

#### [중성화 여부 파악하기(lev2)](https://school.programmers.co.kr/learn/courses/30/lessons/59409)

SELECT ANIMAL_ID , NAME , (CASE WHEN SEX_UPON_INTAKE LIKE '%Neutered%' or SEX_UPON_INTAKE LIKE '%Spayed%' THEN 'O' ELSE 'X' END) as '중성화' FROM ANIMAL_INS ORDER BY ANIMAL_ID

- **CASE**: 조건에 따라서 값 지정해주는 구문
    - 형식
    ```
    CASE   
        WHEN 조건1 THEN 결과값1 
        WHEN 조건2 THEN 결과값2 
        ELSE 결과값3 
    END
    ```




#### [이름에 el이 들어가는 동물 찾기(lev2)](https://school.programmers.co.kr/learn/courses/30/lessons/59047)

SELECT ANIMAL_ID, NAME FROM ANIMAL_INS WHERE NAME LIKE '%EL%' AND ANIMAL_TYPE = 'Dog' ORDER BY NAME

- **LIKE**: 특정 문자 포함되어 있는 것만 추출
- **참고** hi로 시작하는 데이터 검색: like 'hi%', hi로 끝나는 데이터 검색: like '%hi', hi가 들어가는 데이터 검색: '%hi%'

#### [루시와 엘라 찾기(lev2)](https://school.programmers.co.kr/learn/courses/30/lessons/59046)

SELECT ANIMAL_ID, NAME, SEX_UPON_INTAKE FROM ANIMAL_INS WHERE NAME IN ('Lucy', 'Ella', 'Pickle', 'Rogan', 'Sabrina', 'Mitty')

- **IN**: WHERE 조건에 사용할 수 있으며, WHERE 뒤에 쓴 컬럼과 IN에 나열한 조건들 중 일치하는 행을 가져온다는 뜻 (여기서 나열한 조건들은 OR조건으로 검색: 조건1이거나 조건2이거나 조건3이거나 해당 조건에 일치하는 모든 내용을 추출)


#### [조건별로 분류하여 주문상태 출력하기(lev3)](https://school.programmers.co.kr/learn/courses/30/lessons/131113)

SELECT ORDER_ID , PRODUCT_ID , DATE_FORMAT(OUT_DATE, '%Y-%m-%d') as OUT_DATE , (CASE WHEN DATE_FORMAT(OUT_DATE, '%Y-%m-%d') <= '2022-05-01' then '출고완료' WHEN OUT_DATE IS NULL THEN '출고미정' ELSE '출고대기' END) as '출고여부' FROM FOOD_ORDER ORDER BY 1

#### [오랜 기간 보호한 동물(2)(lev3)](https://school.programmers.co.kr/learn/courses/30/lessons/59411)

SELECT AI.ANIMAL_ID, AI.NAME FROM ANIMAL_OUTS AO INNER JOIN ANIMAL_INS AI on AI.ANIMAL_ID = AO.ANIMAL_ID ORDER BY DATEDIFF(AO.DATETIME, AI.DATETIME) DESC LIMIT 2

#### [취소되지 않은 진료 예약 조회하기(lev4) ](https://school.programmers.co.kr/learn/courses/30/lessons/132204)

SELECT APNT_NO, PT_NAME, A.PT_NO, A.MCDP_CD, DR_NAME, APNT_YMD FROM APPOINTMENT A LEFT JOIN PATIENT P on P.PT_NO = A.PT_NO LEFT JOIN DOCTOR D on D.DR_ID = A.MDDR_ID WHERE APNT_CNCL_YN = 'N' and DATE_FORMAT(APNT_YMD, '%Y-%m-%d') = '2022-04-13'
and A.MCDP_CD = 'CS' ORDER BY 6

#### [자동차 대여 기록 별 대여 금액 구하기(lev4)](https://school.programmers.co.kr/learn/courses/30/lessons/151141)

WITH CAR_HISTORY as ( SELECT HISTORY_ID , CH.CAR_ID , DATEDIFF(END_DATE, START_DATE)+1 as DURATION , DAILY_FEE , (CASE WHEN DATEDIFF(END_DATE, START_DATE)+1 >= 7 AND DATEDIFF(END_DATE, START_DATE)+1 <30 THEN '7일 이상' WHEN DATEDIFF(END_DATE, START_DATE)+1 >= 30 AND DATEDIFF(END_DATE, START_DATE)+1 <90 THEN '30일 이상' WHEN DATEDIFF(END_DATE, START_DATE)+1 >=90 THEN '90일 이상' ELSE '7일 미만' END) as DURATION_TYPE FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY CH LEFT JOIN CAR_RENTAL_COMPANY_CAR CC on CH.CAR_ID = CC.CAR_ID WHERE CAR_TYPE = '트럭' )

SELECT HISTORY_ID , (CASE WHEN CH.DURATION_TYPE = '7일 미만' THEN DAILY_FEEDURATION ELSE CAST((DAILY_FEE(1-DISCOUNT_RATE0.01))DURATION as SIGNED INTEGER) END) AS FEE FROM CAR_HISTORY CH LEFT JOIN (SELECT * FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN CP WHERE CAR_TYPE = '트럭') CP on CH.DURATION_TYPE = CP.DURATION_TYPE ORDER BY 2 DESC, 1 DESC

```

• 참고: WITH문으로 먼저 대여 기록 테이블 생성 후(1-3), 이를 자동차 종류 별 대여 기간 종류 별 할인 정책 정보 테이블 조인해 데이터 추출(4-5) 1> 대여 기록 정보 테이블과 자동차 정보 테이블 조인 2> 대여 기록 기간(대여 종료일-대여 시작일+1) 뜻하는 DURATION 컬럼 생성 3> 대여 기록 기간에 따라 DURATION_TYPE(7일 이상, 30일 이상, 90일 이상, 7일 미만) 컬럼 생성 4> 이후 이 테이블을 할인 정책 정보 테이블과 조인 5> DURATION_TYPE이 7일 미만이면 일별 비용x기간으로 비용 계산, 이외는 DURATION_TYPE에 따라 할인율 적용해 (일별 비용x할인율)x기간으로 비용 계산

SQL 고득점 KIT

SELECT

평균 일일 대여 요금 구하기(lev1)

SELECT ROUND(AVG(DAILY_FEE)) as AVERAGE_FEE 
FROM CAR_RENTAL_COMPANY_CAR
WHERE CAR_TYPE = 'SUV'

• ROUND(숫자, 반올림할 자릿수): 숫자를 반올림할 자릿수+1 자릿수에서 반올림

흉부외과 또는 일반외과 의사 목록 출력하기(lev1)

SELECT DR_NAME, DR_ID, MCDP_CD, DATE_FORMAT(HIRE_YMD, '%Y-%m-%d') as HIRE_YMD
FROM DOCTOR
WHERE MCDP_CD IN ('CS', 'GS')
ORDER BY HIRE_YMD DESC, DR_NAME

• DATE_FORMAT(날짜, 형식): 날짜를 해당 형식으로 출력
• 참고 %Y: 네 자리 년도, %m: 숫자 월, %d: 숫자 일

조건에 맞는 도서 리스트 출력하기(lev1)

SELECT BOOK_ID, DATE_FORMAT(PUBLISHED_DATE, "%Y-%m-%d") as PUBLISHED_DATE
FROM BOOK
WHERE YEAR(PUBLISHED_DATE) = '2021'
    and CATEGORY = '인문'
ORDER BY PUBLISHED_DATE

인기있는 아이스크림(lev1)

SELECT FLAVOR
FROM FIRST_HALF
ORDER BY TOTAL_ORDER DESC, SHIPMENT_ID

• ORDER BY DESC: 내림차순 정렬

강원도에 위치한 생산공장 목록 출력하기(lev1)

SELECT FACTORY_ID, FACTORY_NAME, ADDRESS
FROM FOOD_FACTORY 
WHERE ADDRESS LIKE '%강원도%' 
ORDER BY FACTORY_ID

• LIKE: 특정 문자 포함되어 있는 것만 추출
• 참고 hi로 시작하는 데이터 검색: like 'hi%', hi로 끝나는 데이터 검색: like '%hi', hi가 들어가는 데이터 검색: '%hi%'

과일로 만든 아이스크림 고르기(lev1)

SELECT F.FLAVOR
FROM FIRST_HALF F
LEFT JOIN ICECREAM_INFO I
    on F.FLAVOR = I.FLAVOR
WHERE TOTAL_ORDER > 3000
    and INGREDIENT_TYPE = 'fruit_based'
ORDER BY TOTAL_ORDER DESC

• LEFT JOIN: 왼쪽 테이블 기준으로 오른쪽의 테이블 매치

어린 동물 찾기(lev1)

SELECT ANIMAL_ID, NAME 
FROM ANIMAL_INS
WHERE INTAKE_CONDITION != 'AGED'

조건에 맞는 회원수 구하기(lev1)

SELECT COUNT(USER_ID) as USERS
FROM USER_INFO
WHERE YEAR(JOINED) = '2021'
    and AGE >= 20 and AGE < = 29

• COUNT(column): 해당 컬럼 수 세기
• YEAR(날짜): 날짜 중 년도만 반환

상위 n개 레코드(lev1)

SELECT NAME FROM ANIMAL_INS 
ORDER BY DATETIME
LIMIT 1

• LIMIT + n(수): 맨 위에서부터 n개까지의 정보 조회

12세 이하인 여자 환자 목록 출력하기(lev1)

SELECT PT_NAME, PT_NO, GEND_CD, AGE, COALESCE(TLNO, 'NONE') as TLNO
FROM PATIENT
WHERE AGE <= 12
    and GEND_CD = 'W'
ORDER BY AGE DESC, PT_NAME

• COALESCE(A, B, C): A가 NULL이 아니면 A 리턴, B가 NULL이 아니면 B 리턴, 둘 다 아닐 경우 C 리턴

재구매가 일어난 상품과 회원 리스트 구하기(lev2)

SELECT USER_ID, PRODUCT_ID
FROM ONLINE_SALE
GROUP BY USER_ID, PRODUCT_ID
HAVING COUNT(USER_ID) > 1 
ORDER BY USER_ID, PRODUCT_ID DESC

• HAVING: GROUP BY 다음에 수행되는 WHERE 같은 절로, 컬럼의 총 갯수가 어떤 숫자 초과인 경우 COUNT(column) > 숫자 이 형태로 사용

3월에 태어난 여성 회원 목록 출력하기(lev2)

SELECT MEMBER_ID, MEMBER_NAME, GENDER, DATE_FORMAT(DATE_OF_BIRTH, '%Y-%m-%d') AS DATE_OF_BIRTH
FROM MEMBER_PROFILE
WHERE MONTH(DATE_OF_BIRTH) = 03
    and GENDER = 'W'
    and TLNO is not null
ORDER BY MEMBER_ID

• MONTH(날짜): 날짜 중 월만 반환

서울에 위치한 식당 목록 출력하기(lev4)

WITH rr as(
SELECT REST_ID, ROUND(AVG(REVIEW_SCORE), 2) as SCORE
FROM REST_REVIEW
GROUP BY 1
)

SELECT r.REST_ID, REST_NAME, FOOD_TYPE, FAVORITES, ADDRESS, SCORE
FROM REST_INFO r
INNER JOIN rr
    on r.REST_ID = rr.REST_ID
WHERE ADDRESS LIKE '서울%' 
GROUP BY r.REST_ID
ORDER BY SCORE DESC, FAVORITES DESC

SELECT rr.REST_ID, REST_NAME, FOOD_TYPE, FAVORITES, ADDRESS, ROUND(AVG(REVIEW_SCORE), 2) as SCORE
FROM REST_INFO ri
JOIN REST_REVIEW rr
    ON ri.REST_ID = rr.REST_ID
WHERE ADDRESS LIKE '서울%'
GROUP BY 1, 2, 3, 4, 5
ORDER BY 6 DESC, FAVORITES DESC

오프라인/온라인 판매 데이터 통합하기(lev4)

SELECT DATE_FORMAT(SALES_DATE, '%Y-%m-%d') AS SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT
FROM ONLINE_SALE 
WHERE MONTH(SALES_DATE) = '03'
UNION 
SELECT DATE_FORMAT(SALES_DATE, '%Y-%m-%d') AS SALES_DATE, PRODUCT_ID, NULL as USER_ID, SALES_AMOUNT
FROM OFFLINE_SALE
WHERE MONTH(SALES_DATE) = '03'
ORDER BY SALES_DATE, PRODUCT_ID, USER_ID

• UNION: 2개 이상 테이블에 존재하는 같은 성격의 값을 하나의 쿼리로 추출

  SUM, MAX, MIN

가장 비싼 상품 구하기(lev1)

SELECT MAX(PRICE) as MAX_PRICE
FROM PRODUCT

최댓값 구하기(lev1)

SELECT MAX(DATETIME) AS '시간' 
FROM ANIMAL_INS

최솟값 구하기(lev2)

SELECT MIN(DATETIME) 
FROM ANIMAL_INS

동물 수 구하기(lev2)

SELECT COUNT(ANIMAL_ID) 
FROM ANIMAL_INS

중복 제거하기(lev2)

SELECT COUNT(DISTINCT NAME)  
FROM ANIMAL_INS
WHERE NAME IS NOT NULL

가격이 제일 비싼 식품의 정보 출력하기(lev2)

SELECT *
FROM FOOD_PRODUCT
WHERE PRICE = (SELECT MAX(PRICE) as PRICE 
                FROM FOOD_PRODUCT)

IS NULL

경기도에 위치한 식품창고 목록 출력하기(lev1)

SELECT WAREHOUSE_ID, WAREHOUSE_NAME, ADDRESS, COALESCE(FREEZER_YN, 'N') as FREEZER_YN #IFNULL(FREEZER_YN, 'N') AS FREEZER_YN
FROM FOOD_WAREHOUSE
WHERE ADDRESS LIKE '경기%'
ORDER BY WAREHOUSE_ID

이름이 있는 동물의 아이디(lev1)

SELECT ANIMAL_ID
FROM ANIMAL_INS
WHERE NAME IS NOT NULL
ORDER BY ANIMAL_ID

나이 정보가 없는 회원 수 구하기(lev1)

SELECT COUNT(USER_ID) AS USERS
FROM USER_INFO
WHERE AGE IS NULL

NULL 처리하기(lev2)

SELECT ANIMAL_TYPE, IFNULL(NAME, 'No name'), SEX_UPON_INTAKE
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

• IFNULL(column, 값): 해당 컬럼이 null 이라면 특정 값 반환