오늘도 데이터분석

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 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 작성 

 

[SQL 코드]

SELECT  f1.emp_no AS 직원번호
      ,f1.fam_nm AS 쌍둥이1
      ,f2.fam_nm AS 쌍둥이2
FROM 
  (SELECT *
   FROM fam_c
   WHERE birth_ymd BETWEEN '20120101' AND '20151231'
   AND rel_type_cd = 'A27') f1
   ,fam_c f2
WHERE f1.emp_no = f2.emp_no
      AND f1.birth_ymd = f2.birth_ymd
      AND f1.fam_nm <> f2.fam_nm;

2) . 20210321 기준으로 재직중인 임직원이며. 생년월일이 20120101 에서 20151231인 자녀를 가진 임직원을 구하는 sql을 작성하세요.

[주어진 질의결과]

[SQL 코드]

SELECT  e.emp_no AS 직원번호
      ,f.fam_nm AS 자녀성명
      ,f.birth_ymd AS 자녀생년월일 
FROM emp_c e
   INNER JOIN fam_c f on e.emp_no = f.emp_no
WHERE e.retire_ymd >= '20210321' 
     AND f.birth_ymd BETWEEN '20120101' AND '20151231'
     AND f.rel_type_cd = 'A27';

[제가 한 질의결과]

3) . 20210321 기준으로 재직중인 임직원이며. 사내부부인 임직원의 직원번호를 구하는 sql을 작성하세요.

[주어진 질의결과]

[SQL 코드]

SELECT e.emp_no AS EMP_NO
FROM emp_c e
    INNER JOIN fam_rel_c fam_r ON e.emp_no = fam_r.emp_no
WHERE e.retire_ymd >= '20210321' 
     AND fam_r.end_ymd >= '20210321' 
     AND fam_r.rel_type_cd IN ('A02', 'A18')

[제가 한 질의결과]

3-2) . 현재 날짜 기준 재직중인 “이씨 성＂을 가진 직원의 자녀명수가 몇 명인지 리스트를 만드세요.

[SQL 코드]

SUBSTR  사용

SELECT emp.emp_no AS 직원번호 
     , emp.emp_nm AS 직원명
     , COUNT(*) AS 자녀명수
FROM emp_c emp
    INNER JOIN fam_c fam ON emp.emp_no = fam.emp_no
WHERE SUBSTR(emp.emp_nm, 1, 1) = '이'
      AND SYSDATE BETWEEN HIRE_YMD AND RETIRE_YMD
      AND rel_type_cd = 'A27'
GROUP BY emp.emp_no, emp.emp_nm ;

LIKE 사용 

SELECT emp.emp_no AS 직원번호 
     , emp.emp_nm AS 직원명
     , COUNT(*) AS 자녀명수
FROM emp_c emp
    INNER JOIN fam_c fam ON emp.emp_no = fam.emp_no
WHERE emp.emp_nm LIKE '이%'
      AND SYSDATE BETWEEN HIRE_YMD AND RETIRE_YMD
      AND rel_type_cd = 'A27'
GROUP BY emp.emp_no, emp.emp_nm ;

[제가 한 질의결과]

3-3) . 자녀 명수가 2명 이상인 직원의 리스트를 구하세요.  (직원번호 / 성명 / 자녀명수 ) 

[SQL 코드]

SELECT e.emp_no AS 직원번호
      ,e.emp_nm AS 직원성명
      ,COUNT(*) AS 자녀명수
FROM emp_c e
     INNER JOIN fam_c f ON e.emp_no = f.emp_no 
WHERE f.rel_type_cd = 'A27'
GROUP BY e.emp_no, e.emp_nm
HAVING  COUNT(*) >= 2;

[제가 한 질의결과]

3-4) . 현재 기준 조직의 부서별 직원이 몇 명인지 구하는 sql을 작성하세요. (조직코드, 조직명, 직원수)

[SQL 코드]

SELECT org.org_cd AS 조직코드 
     , org.org_nm AS 조직명
     , COUNT(emp.emp_no) AS 직원수
FROM org_c org
     INNER JOIN emp_c emp ON emp.org_cd = org.org_cd
WHERE SYSDATE BETWEEN STA_YMD AND END_YMD
GROUP BY org.org_cd, org.org_nm;

[제가 한 질의결과]

<과제 목표>

현업이 제출한 데이터 추출 요청서를 확인 후 요구사항의 애매모호성 제거하여 데이터 추출 작업 준비하기.

상황

내년부터 우리 회사에 직원 복지항목으로 자녀 학자금에 대한 지원이 추가됨.

관련하여 인사팀에서 현황을 파악하고자 관련 데이터 추출을 요청함.

1. 현업의 요구사항을 모두 파악 후 추출하려는 데이터의 레이아웃을 그려보세요

*자녀순서는 몇 번째 자녀인지 의미 

데이터는 은퇴한 직원의 정보도 담고 있지만 재직 중인 임직원을 대상으로 하는 복지혜택이기 때문에 재직여부는 필요없을 것 같다.  

FEEDBACK 이후

2열: 테이블

3열: 컬럼

2. 애매모호한 부분에 밑줄을 긋고 어떻게 명확히 할 지 수정해 보세요.

- 기혼의 임직원 중 자녀가 1명 이상의 인 자

미혼모, 미혼부도 있을 수 있다 생각.

재작자중 자녀코드를 가지고 있고 자녀가 1명이상이면 기혼으로 간주

- 자녀 나이가 5~7세인자

나이의 기준이 만나이인지 세는 나이(한국식나이)인지 연나이인지 애매모호함

또한 만나이를 기준으로 삼을 경우 어느 날짜를 기준으로 만나이를 할 것인지도 부정확함

만나이로 할 경우에는 "2022년 01월 01일 기준으로 만5~7세 자녀"로 기준 세우는 것이 명확함.

그리고 나이보다 출생연도를 기준으로 세우는 것도 명확하다고 생각.

예를 들어 2022년 기준으로 한국식 나이 5세~7세에 해당되는 "2016년 01월 01일 ~ 2018년 12월 31일 사이 출생자"

- 해당자녀의 나이, 성별표시

마찬가지로 나이의 기준이 애매모호함.

만나이, 연나이, 세는나이(한국식나이) 기준 명확화 필요

나이, 생년월일, 성별 추출

- 쌍생아의 경우 대표 1명만 표시

쌍생아의 기준: 관계코드가 자녀이면서 생년월일이 같은 경우

쌍생아 대표의 기준이 애매모호함. 

쌍생아의 경우에도 자녀 순서가 나뉘어 있음. 쌍생아 중 첫째를 대표로 표시 

또는 이름으로 오름차순 기준, 상위자로 추출,

+ 추가사항

- 2명이상의 자녀가 나이기준에 충족될 경우에 혜택 중복 여부 -> 나이가 더 많은 자녀를 대표 1명으로 표시 

- 임직원의 연차기준에 따른 복지혜택 적용 여부 -> 입사연도 상관없이 모든 임직원 복지적용 or 입사한 지 2년이상된 직원부터 복지 적용 등 기준세우기

FEEDBACK 이후 추가

배우자가 사내부부인 경우는 남편, 아내 중 한쪽만 가능

사내커플인 경우 부를 대상자에 포함하고 모는 제외

 배우자가 사내부부인 경우 배우자 명을 표시

대상자 부 기준으로 모의  직원번호 및 배우자 명을 함께 추출

3. SQL을 사용하여 문제에 답하세요.

(1) 직원번호 10004141 의 이름은? 

SELECT emp_no, emp_nm
FROM emp_c
WHERE emp_no = 10004141;

결과

(2) 직원번호 10004141 는 몇 명의 가족이 있나요? 

SELECT COUNT(emp_no)
FROM fam_c
WHERE emp_no = 10004141;

결과

(3) 직원번호 10004141 의 소속 부서 코드는? 

SELECT org_cd
FROM emp_c
WHERE emp_no = 10004141;

결과

(4) 직원번호 10004141 의 소속 부서 명은?  

SELECT org_nm
FROM org_c
WHERE org_cd = (
  SELECT org_cd
  FROM emp_c
  WHERE emp_no = 10004141
);

결과

(5) 남자 / 여자 직원이 몇 명인지 한번에 알 수 있는 sql 작성

SELECT gender_cd, COUNT(*)
FROM emp_c
GROUP BY gender_cd
ORDER BY gender_cd

(6) 직원별 자녀가 몇 명인지 한번에 알 수 있는 sql 작성

SELECT emp_no, COUNT(*) AS 자녀수
FROM fam_c
WHERE rel_type_cd = 'A27'
GROUP BY emp_no;

결과

(7) 생년월일이 1970년 1월 1일 이전인 직원의 수를 구하는 sql 작성

SELECT COUNT(emp_no)
FROM emp_c
WHERE birth_ymd < 19700101

결과

(8) 현재 재직 중인 직원의 전체 수를 구하는 sql 작성

SELECT COUNT(emp_no)
FROM emp_c
WHERE retire_ymd LIKE '9999%'

SELECT COUNT(emp_no)
FROM emp_c
WHERE retire_ymd = '9999/12/31'

결과

과제1) 이번 캠프에 사용되는 4개의 테이블에 대해서 탐색해 보고 그 결과를 기술하여 보세요.
과제 목적: 데이터 및 테이블의 전략적 탐색능력 배양

EMP_C

SELECT * FROM EMP_C;

사용목적: 임직원의 입사 및 은퇴 정보가 저장된 테이블

특징 

1. 사번,직원이름,성별,생년월일, 부서코드,은퇴일자,고용일자 정보가 저장됨

2. 사번(EMP_NO)은 중복되지 않는 식별번호

3. 성별은 1, 2 로 분류

4. 사원 당 하나의 행을 가짐 

5. RETIRE_YMD에서 9999년은 지금도 다니고 있는 걸로 추정 

FAM_C

SELECT * FROM FAM_C;

사용목적: 임직원의 가족정보가 저장된 테이블

특징

1. 사번, 가족이름, 관계코드, 성별, 생년월일 등 정보가 저장됨

2. 부모, 자식, 형제 등 가족관계가 코드로 저장

3. EMP_C 테이블의 EMP_NO와 연관

4. 가족의 성별은 ‘1’, ‘2’ 로 분류

SELECT EMP_NO , COUNT(*) FROM FAM_C GROUP BY EMP_NO;

5. 임직원 한 명(사번)당 여러 행을 가질 수 있음

    -> 임직원 한 명당 가족 1명부터 최대13명까지 가족의 정보가 담겨 있음

SELECT rel_type_cd, child_seq FROM FAM_C WHERE child_seq IS NOT NULL;

6.  CHILD_SEQ 속성값이 있는 REL_TYPE_CD는 A27

   -> A27은 자녀 코드, CHILD_SEQ 컬럼은 자녀 순서(첫째,둘째,셋째)로 추정

ORG_C

SELECT * FROM ORG_C;

사용목적: 회사의 부서(지점) 정보가 저장된 테이블

특징

1. 부서코드, 부서이름, 지점타입, 시작날짜, 종료날짜 등 정보가 저장됨

  -> SUPER_ORG_CD, MGR_ORG_CD, AREA_ORG_CD 가 무슨 속성인지 정확히는 모르겠지만

      MGR_ORG_CD는 관리자부서코드, AREA_ORG_CD는 지역코드로 추정됨.

2. 각 지점이 코드로 저장

3. EMP_C 테이블의 ORG_CD와 연관됨

SELECT org_type FROM ORG_C GROUP BY org_type ORDER BY org_type;

4. 지점은 A~N, 총 11개의 타입으로 나뉨

5. 코드, 이름이 동일한 지점이 여러 행을 가질 수 있음

FAM_REL_C

SELECT * FROM FAM_REL_C;

사용목적: 가족 중에 임직원이 있는 경우, 직원과 해당 가족정보가 저장된 테이블

특징

SELECT emp_no, COUNT(*) FROM FAM_REL_C GROUP BY emp_no;

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