[Join 7~12] SQL 코딩테스트 고득점 Kit

7. 있었는데요 없었습니다 (LV 3)
8. 오랜 기간 보호한 동물(1) (LV 3)
9. 보호소에서 중성화한 동물 (LV 4)
10. 상품 별 오프라인 매출 구하기 (LV 2)
11. 상품을 구매한 회원 비율 구하기 (LV 5)
12. FrontEnd 개발자 찾기 (LV 4)

7. 있었는데요 없었습니다 (LV 3)

정답:

SELECT i.ANIMAL_ID
    ,  i.NAME
  FROM ANIMAL_INS AS i
  JOIN ANIMAL_OUTS AS o ON i.ANIMAL_ID = o.ANIMAL_ID
 WHERE i.DATETIME > o.DATETIME
 ORDER
    BY i.DATETIME ASC

8. 오랜 기간 보호한 동물(1) (LV 3)

정답:

SELECT i.NAME
    ,  i.DATETIME
  FROM ANIMAL_INS AS i
LEFT
  JOIN ANIMAL_OUTS AS o ON i.ANIMAL_ID = o.ANIMAL_ID
WHERE o.ANIMAL_ID IS NULL
 ORDER
    BY i.DATETIME
 LIMIT 3

9. 보호소에서 중성화한 동물 (LV 4)

정답:

SELECT i.ANIMAL_ID
    ,  i.ANIMAL_TYPE
    ,  i.NAME
  FROM ANIMAL_INS AS i
  JOIN ANIMAL_OUTS AS o ON i.ANIMAL_ID = o.ANIMAL_ID
 WHERE i.SEX_UPON_INTAKE LIKE 'Intact%' 
   AND o.SEX_UPON_OUTCOME NOT LIKE 'Intact%' 
 ORDER
    BY i.ANIMAL_ID

10. 상품 별 오프라인 매출 구하기 (LV 2)

정답:

SELECT p.PRODUCT_CODE
    ,  SUM(p.PRICE * s.SALES_AMOUNT) AS SALES
  FROM OFFLINE_SALE AS s
LEFT
  JOIN PRODUCT AS p ON s.PRODUCT_ID = p.PRODUCT_ID
 GROUP
    BY p.PRODUCT_CODE
 ORDER
    BY SALES DESC, p.PRODUCT_CODE

11. 상품을 구매한 회원 비율 구하기 (LV 5)

정답:

WITH MONTHLY_PURCHASE AS (
    SELECT YEAR(s.SALES_DATE) AS YEAR
        ,  MONTH(s.SALES_DATE) AS MONTH
        ,  COUNT(DISTINCT s.USER_ID) AS PURCHASED_USERS
      FROM USER_INFO AS u
      JOIN ONLINE_SALE AS s ON u.USER_ID = s.USER_ID
     WHERE YEAR(u.JOINED) = 2021
     GROUP
        BY YEAR(s.SALES_DATE), MONTH(s.SALES_DATE)
)
, TOTAL_USERS AS (
    SELECT COUNT(DISTINCT USER_ID) AS TOT_USERS
      FROM USER_INFO
     WHERE YEAR(JOINED) = 2021
)
SELECT m.YEAR
    ,  m.MONTH
    ,  m.PURCHASED_USERS
    ,  ROUND(m.PURCHASED_USERS/t.TOT_USERS, 1) AS PURCHASE_RATIO
  FROM MONTHLY_PURCHASE AS m
CROSS
  JOIN TOTAL_USERS AS t 
 ORDER 
    BY m.YEAR, m.MONTH

쿼리 설명:

• CROSS JOIN을 써서: 월별 구매자 수 (m.PURCHASED_USERS)와 전체 가입자 수 (t.TOTAL)
  → 이 둘을 각 월마다 나누어서 비율(PURCHASE_RATIO)을 구함
• TOTAL_USERS는 행이 하나인 테이블이기 때문에 MONTHLY_PURCHASE의 각 행에 그 값을 반복적으로 붙여야 할 때 CROSS JOIN을 사용하면 됨!

LV 5 이여서 그런지 2번이랑 같이 제일 어려운 문제 중 하나였다..

12. FrontEnd 개발자 찾기 (LV 4)

정답:

WITH SKILL_CATEGORY AS (
    SELECT d.ID, d.EMAIL, d.FIRST_NAME, d.LAST_NAME
        ,  GROUP_CONCAT(s.CATEGORY) AS CATEGORY
      FROM DEVELOPERS AS d
    LEFT
      JOIN SKILLCODES AS s ON (d.SKILL_CODE & s.CODE != 0)
     GROUP
        BY d.ID, d.EMAIL, d.FIRST_NAME, d.LAST_NAME
     ORDER
        BY d.ID 
)
SELECT ID, EMAIL, FIRST_NAME, LAST_NAME
  FROM SKILL_CATEGORY
 WHERE CATEGORY LIKE '%Front%'