오늘도 데이터분석

swap salary 문제 바로가기

[풀이]

UPDATE salary 
SET sex  = CASE WHEN sex = 'm' THEN 'f'
                WHEN sex = 'f' THEN 'm'
                END;

UPDATE salary 
SET sex  = CASE WHEN sex = 'm' THEN 'f'
                ELSE 'm' END;

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

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

문제정리

쿼리 두개 작성

1. 이름을 알파벳 순으로 정렬하고 이름 옆에 직업의 맨 앞 글자만 괄호쳐서 나타내는 쿼리 작성

2. 직업의 수와 직업이름을 소문자로 바꿔 결과값을 

"There are a total of [occupation_count] [occupation]s." 이 문장으로 나타내는 쿼리 작성 

   직업수로 내림차순 정렬하되, 동일한 값이면 직업이름순 정렬

[풀이]

- SUBSTR으로 문자열 앞 글자만 자름 

- 문자열 붙이는 CONCAT 함수 사용

-소문자로 바꾸는 LOWER함수 사용

SELECT CONCAT(name, '(', SUBSTR(occupation, 1, 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;

비교적 수월하게 두 쿼리를 작성했는데 계속 에러가 나서 문제가 뭔가 했더니 해결책은 ; 세미콜론 이었다.

UNION은 두 쿼리의 컬럼 수가 맞아야 하기 때문에 사용하지 못한다.

MySQL은 쿼리 끝에 세미콜론을 쓰지 않아도 되지만

오라클처럼 혹시나 해서 세미콜론을 써보니 결과값이 연달아서 잘 나온다. 

new companies 문제 바로가기 

[문제]

Amber's conglomerate corporation just acquired some new companies. Each of the companies follows this hierarchy: 

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.

- 각 회사코드, 창립자이름, 리드매니저 총수, 시니어 매니저 총수, 매니저 총수, 직원 총수를 프린트하는 쿼리 작성
- 회사코드로 내림차순 정렬 
- 레코드는 중복될 수 있음
- 회사코드는 숫자가 아닌 문자  
   C_1,C_10, C_2 순서로 정렬되어야 함 

[풀이]

SELECT co.company_code
      ,co.founder
      ,COUNT(DISTINCT lm.lead_manager_code)
      ,COUNT(DISTINCT sm.senior_manager_code)
      ,COUNT(DISTINCT ma.manager_code)
      ,COUNT(DISTINCT em.employee_code)
FROM company co
     LEFT JOIN lead_manager lm ON lm.company_code = co.company_code
     LEFT JOIN senior_manager sm ON sm.company_code = lm.company_code
     LEFT JOIN manager ma ON ma.company_code = sm.company_code
     LEFT JOIN employee em ON em.company_code = ma.company_code
GROUP BY co.company_code ,co.founder
ORDER BY co.company_code

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.

 => LAT_N의 중앙값을 구하고 소수점 4자리까지 나타태는 쿼리 작성

[풀이]

MYSQL

- LAT_N 보다 큰 데이터 수와 LAT_N보다 작은 데이터 수가 같을 때 => 중앙값  

SELECT ROUND(LAT_N,4) 
FROM station s
WHERE (SELECT COUNT(*)
       FROM STATION 
       WHERE LAT_N < s.LAT_N)
     =(SELECT COUNT(*) 
       FROM STATION 
       WHERE LAT_N > s.LAT_N);

- 윈도우함수 PERCENT_RANK 사용 

PERCENT_RANK 함수

: 맨 위 행을 0, 맨 아래(끝) 행을 1로 놓고 

각 행에 대해 현재 행이 위치하는 백분위 순위값 반환.

반환값의 범위는 0보다 크거나 같고 1보다 작거나 같다. (0 <= x <=1)

반환 값 = (rank - 1) / (total row - 1)

SELECT ROUND(LAT_N,4)
FROM (SELECT LAT_N, 
            PERCENT_RANK() OVER (ORDER BY LAT_N ASC) per
    FROM STATION) s2
WHERE per = 0.5

Oracle

- Median 함수  사용

SELECT ROUND(MEDIAN(Lat_N), 4)
FROM Station;

Weather Observation Station 19 문제 바로가기 

[문제]

*유클리디안 거리(유클리디안 유사성)

: n차원에서 두 점 사이의 거리를 구하는 공식 

2차원 상에서 두 점 P(x1, y1), P(x2,y2)이 있는 경우 

두 점간의 거리는 √{(x2-x1)² +(y2-y1)²} 

문제정리 
2차원에서 P(a,c) , P(b,d) 두 점간의 유클리디안 거리를 구하는 쿼리 입력, 
결과값은 소수점 4자리까지 반올림

a = LAT_N 최소값
b = LAT_N 최대값
c = LONG_W 최소값
d = LONG_W 최대값

[풀이]

이 문제에서 결과를 구하는 유클리디안 공식은 √{(b-a)² +(d-c)²} 

1.  WITH로 각 좌표의 a,b,c,d값을 넣음 

  (간단하기 때문에 WITH 안쓰고 바로 계산식에 넣어도 되지만 계산식이 더 깔끔하고 알아보기 쉽게 쓰고 싶어서 사용함 )

2. 제곱함수 POWER 사용

 : POWER(제곱할 숫자, 제곱횟수) 

   ex) POWER(2, 2) = 4

3. 제곱근 함수 SQRT 사용 

 : SQRT(제곱근할 숫자)

   ex) SQRT(16) = 4

4. ROUND 함수로 반올림 

WITH p AS (
SELECT MIN(lat_n) a
     , MAX(lat_n) b
     , MIN(long_w) c
     , MAX(long_w) d
FROM station)

SELECT ROUND(SQRT(POWER(b-a, 2)+POWER(d-c,2)),4)
FROM p

OUTPUT

184.1616

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.

End_Date가 연속적이면(=다음 start_date와 같으면) 같은 프로젝트이다.
서로 다른 프로젝트의 start, end date가 나오도록 쿼리 입력.
결과는 프로젝트 걸린 기간이 작은 순대로, 기간이 동일할 경우 start date 순 대로 정렬

=>  start date가 end date에 없으면 개별 프로젝트가 새로 시작하는(연속이 끊기는) start date
      end date가 start date에 없으면 같은 프로젝트가 끝나는(연속이 끊기는) end date

풀이

1. end date에 없는 start date, start date에 없는 end date 추출

2. 시작날짜가 종료날짜보다 작아야 함

3. 시작날짜 기준으로 그룹화, 종료날짜는 최소날짜

4. 종료날짜와 시작날짜 차이 계산 -> 정렬, 차이 동일하면 start date기준 정렬

* 종료날짜와 시작날짜 차이 계산

   DATEDIFF 함수 사용

DATEDIFF : 두 날짜 사이의 일수 계산
DATEDIFF(날짜1, 날짜2) => 날짜1 - 날짜2 값이 나옴 
*날짜 안에 시간이 포함되도 일수 계산만 나옴
*날짜 범위를 초과하면 (ex. 22-13-01) NULL 반환

최종 쿼리

SELECT start_date, MIN(end_date)
FROM (SELECT start_date 
      FROM Projects
      WHERE start_date NOT IN (SELECT end_date FROM Projects)) s, 
      (SELECT end_date 
      FROM Projects
      WHERE end_date NOT IN (SELECT start_date FROM Projects)) e
WHERE start_date < end_date
GROUP BY start_date
ORDER by DATEDIFF(MIN(end_date), start_date), start_date

Ollivander's Inventory 문제 바로가기 

문제 

Hermione decides the best way to choose is by determining the minimum number of gold galleons needed to buy each non-evil wand of high power and age. Write a query to print the id, age, coins_needed, and power of the wands that Ron's interested in, sorted in order of descending power. If more than one wand has same power, sort the result in order of descending age.

1. 론이 관심있을만한 wand 기준 : power, age는 높고, non-evil wand(is_evil = 0)이며 코인은 최소로 드는 wand
2. 론이 관심있을만한 wand의 id, age, coins_needed, power 를 결과값으로 나타내는 쿼리 입력
3. power 내림차순(정렬기준1), power같으면 age 내림차순(정렬기준2)
* wand_property 테이블에서 code와 age 매핑은 1:1이다 => 한 코드마다 나이 하나, 즉 동일 코드 = 동일 나이

문제 이해하는데 완전 오래걸림.....; 더 자세한 정보 및 예시는 위 문제링크에서 확인

풀이 

SELECT w.id, p.age, w.coins_needed, w.power
FROM (SELECT code, power, MIN(coins_needed)coins_needed
      FROM wands
      GROUP BY code, power) sub               -- code(age), power를 기준으로 최소 코인값만 저장
      INNER JOIN wands_property p ON sub.code = p.code -- property 조인 
      INNER JOIN wands w ON sub.code = w.code
                        AND sub.coins_needed = w.coins_needed 
                        AND sub.power = w.power  -- id 속성을 위해 wands 다시 조인 
WHERE p.is_evil = 0
ORDER BY power DESC, age DESC;

*PC화면에서 보기 적합하게 작성되었습니다. 

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.

정리
- Hackers 테이블과 Challenges 테이블을 가지고 해커 id, 이름, 학생이 한 챌린지의 총 수를 결과값으로 나타내는 쿼리 입력 (*한 학생이 여러 챌린지 수행함)
- 결과는 챌린지의 수에 따라 내림차순(정렬기준1), 시행한 챌린지 수가 같으면 해커 id(정렬기준2)로 정렬
- 단, 시행한 챌린지의 수가 같을 때 그 챌린지 수가 MAX값이 아니면 결과에서 제외

Sample Input 0

Hackers Table: 

 Challenges Table: 

Sample Output 0

21283 Angela 6
88255 Patrick 5
96196 Lisa 1

For Sample Case 0, we can get the following details:

Students  5077 and 62743  both created  4 challenges, but the maximum number of challenges created is 6 so these students are excluded from the result.

풀이 

풀이순서 
- 두 테이블 조인
- hacker_id와 name으로 그룹화 
- having으로 challenge_created(각 학생이 시행한 챌린지 수) 조건문
  1) challenge_created가 MAX값일 경우 
  2) challenge_created가 중복값이 없는 경우
- 정렬기준 충족

STEP 1

두 테이블을 조인하고 해커 id, 이름, challenges_created를 결과로 나타냄

각 학생별로 id, name, 시행한 챌린지 수가 결과로 나옴

SELECT h.hacker_id, h.name, COUNT(challenge_id) challenges_created
FROM hackers h JOIN challenges c ON c.hacker_id = h.hacker_id
GROUP BY h.hacker_id, h.name

이 쿼리의 결과는 challenges_created 학생별로 시행한 챌린지 수가 같은 경우(중복)가 나옴

가장 큰 값(max)을 제외하고는 중복되는 값은 제외해줘야 함. 그래서 조건 필요

STEP 2

HAVING으로 challenges_created 조건

2-1 challenge_created가 MAX값일 경우 

SELECT MAX(challenge_created)
FROM (SELECT hacker_id,
                            COUNT(*) challenge_created
            FROM challenges
            GROUP BY hacker_id) sub

2-2 challenge_created가 중복값이 없는 경우

SELECT challenge_created
FROM (SELECT hacker_id,
                            COUNT(*) challenge_created
              FROM challenges
             GROUP BY hacker_id) sub
GROUP BY challenge_created
HAVING COUNT(*) = 1

최종 HAVING 문

각 조건을 서브쿼리로 지정

HAVING challenges_created IN (SELECT challenge_created
                                                      FROM (SELECT hacker_id,
                                                                                  COUNT(*) challenge_created
                                                                    FROM challenges
                                                                    GROUP BY hacker_id) sub
                                                      GROUP BY challenge_created
                                                      HAVING COUNT(*) = 1)
          OR challenges_created = (SELECT MAX(challenge_created)
                                                      FROM (SELECT hacker_id,
                                                                                   COUNT(*) challenge_created
                                                                    FROM challenges
                                                                    GROUP BY hacker_id) sub)

STEP 3 

정렬기준 포함한 최종 쿼리

SELECT h.hacker_id, h.name, COUNT(challenge_id) challenges_created
FROM hackers h 
     JOIN challenges c ON c.hacker_id = h.hacker_id
GROUP BY h.hacker_id, h.name
HAVING  challenges_created IN (SELECT challenge_created
                               FROM (SELECT hacker_id,                                  
                                     COUNT(*) challenge_created
                                  FROM challenges
                                  GROUP BY hacker_id) sub
                            GROUP BY challenge_created
                            HAVING COUNT(*) = 1)
    OR challenges_created = (SELECT MAX(challenge_created)
                             FROM (SELECT hacker_id, 
                                   COUNT(*) challenge_created
                                    FROM challenges
                                    GROUP BY hacker_id) sub)
ORDER BY challenges_created DESC, h.hacker_id

위와 동일한 쿼리를 WITH문 사용하는 경우 

WITH counter AS(
  SELECT h.hacker_id
       , h.name
       , COUNT(*) created_challenges
  FROM hackers h
      INNER JOIN challenges c ON h.hacker_id = c.hacker_id
  GROUP BY h.hacker_id, h.name
) 

SELECT  counter.hacker_id
      , counter.name
      , counter.created_challenges
FROM counter
WHERE created_challenges = (SELECT MAX(created_challenges) 
                            FROM counter)
OR created_challenges IN (SELECT created_challenges 
                          FROM counter 
                          GROUP BY created_challenges 
                          HAVING COUNT(*) = 1) 
ORDER BY counter.created_challenges DESC, counter.hacker_id