🚩오늘의 문제는?

SQL - level 4

1.오프라인/온라인 판매 데이터 통합하기
2.입양 시각 구하기(2)
3.특정 세대의 대장균 찾기
4.FrontEnd 개발자 찾기

1.오프라인/온라인 판매 데이터 통합하기

문제 정의

문제 풀이

오답

SELECT DISTINCT DATE_FORMAT(A.SALES_DATE,'%Y-%m-%d') AS SALES_DATE, A.PRODUCT_ID, IFNULL(USER_ID, 'NULL') AS USER_ID ,
SUM(A.SALES_AMOUNT) AS SALES_AMOUNT
FROM 
(
    
SELECT  PRODUCT_ID, SALES_AMOUNT, SALES_DATE
FROM ONLINE_SALE

UNION ALL
    
SELECT PRODUCT_ID, SALES_AMOUNT, SALES_DATE
FROM OFFLINE_SALE) A

LEFT JOIN

(
SELECT USER_ID , PRODUCT_ID, SALES_AMOUNT, SALES_DATE
FROM ONLINE_SALE) B

ON A.PRODUCT_ID=B.PRODUCT_ID AND A.SALES_AMOUNT=B.SALES_AMOUNT AND A.SALES_DATE=B.SALES_DATE  

WHERE A.SALES_DATE LIKE '%2022-03%'

GROUP BY SALES_DATE, PRODUCT_ID, USER_ID 

ORDER BY 1 ASC, 2 ASC, 3 ASC;

정답

SELECT DATE_FORMAT(SALES_DATE,'%Y-%m-%d') AS SALES_DATE, PRODUCT_ID, USER_ID ,SUM(SALES_AMOUNT) AS SALES_AMOUNT
FROM 
(SELECT USER_ID, PRODUCT_ID, SALES_AMOUNT, SALES_DATE
FROM ONLINE_SALE
UNION ALL
SELECT NULL AS USER_ID,PRODUCT_ID, SALES_AMOUNT, SALES_DATE
FROM OFFLINE_SALE) T1
WHERE SALES_DATE LIKE '%2022-03%'
GROUP BY SALES_DATE, PRODUCT_ID, USER_ID
ORDER BY 1 ASC, 2 ASC, 3 ASC;

주의할 점

-- 1. 'NULL'로 하면 문자열로 인식
-- 2. UNION ALL을 통해 데이터 앞뒤로 출력되도록 하기, 이때 NULL 인 점 확인해서 NULL AS 로 바꾸기

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

문제 정의

문제 풀이

정답

-- 1. 1에서 24까지 간단한 정수 시리즈 생성
WITH RECURSIVE N_SEQUENCE AS 
(SELECT 0 AS HOUR
UNION ALL
SELECT HOUR+1 FROM N_SEQUENCE
WHERE HOUR < 23)

-- 2.JOIN
SELECT HOUR, COUNT(ANIMAL_ID) AS COUNT
FROM N_SEQUENCE A LEFT JOIN ANIMAL_OUTS B
ON A.HOUR=HOUR(B.DATETIME)
GROUP BY HOUR
ORDER BY HOUR;

주의할 점

-- 문제: 주어진 테이블에는 없는 시간대가 존재한다.
-- > 재귀쿼리를 활용해서 없는 시간대를 생성해야한다.
-- 참고자료: https://jjon.tistory.com/entry/Recursive-CTECommon-Table-Expression-%ED%99%9C%EC%9A%A9

3.특정 세대의 대장균 찾기

문제 정의

문제 풀이

정답

WITH RECURSIVE NUMBERS AS (
    SELECT ID, PARENT_ID, 1 AS LEVEL
    FROM ECOLI_DATA
    WHERE PARENT_ID IS NULL
    
    UNION ALL
    
    SELECT A.ID, A.PARENT_ID, B.LEVEL + 1 AS LEVEL
    FROM ECOLI_DATA A
    INNER JOIN NUMBERS B ON A.PARENT_ID = B.ID
)

SELECT ID FROM NUMBERS WHERE LEVEL IN (3) ORDER BY 1 ASC;

주의할 점

-- 해당 문제는 세대 수를 확인, 계층을 확인해야함
-- 재귀 함수를 이용해서 자료들의 계층을 확인하기
-- 참고자료:https://tak2k.tistory.com/52

4.FrontEnd 개발자 찾기

문제 정의

문제 풀이

정답

SELECT DISTINCT ID , EMAIL, FIRST_NAME, LAST_NAME
FROM SKILLCODES A JOIN DEVELOPERS B
ON A.CODE & B.SKILL_CODE = A.CODE
WHERE CATEGORY IN ('Front End')
ORDER BY 1 ASC;

주의할 점

-- 중간에 한번 틀렸었음 . 이유 ) 중복 문제
-- 주의할 점 : 개발자의 정보가 중복되어 나타날 수 있다
-- WHY? 여러 NAME (할 수 있는 언어)를 할 수 있을 경우 결과값이 여러번 출력되기 때문이다.
-- 해결 : select distinct