[Group By 11~15] SQL 코딩테스트 고득점 Kit

11. 고양이와 개는 몇 마리 있을까 (LV2)
12. 동명 동물 수 찾기 (LV2)
13. 년, 월, 성별 별 상품 구매 회원 수 구하기 (LV4)
14. 입양 시각 구하기(1) (LV2)
15. 입양 시각 구하기(2) (LV4)

11. 고양이와 개는 몇 마리 있을까 (LV2)

정답:

SELECT ANIMAL_TYPE
    ,  COUNT(ANIMAL_ID) AS count
  FROM ANIMAL_INS
 GROUP
    BY ANIMAL_TYPE
 ORDER
    BY ANIMAL_TYPE ASC

12. 동명 동물 수 찾기 (LV2)

정답:

SELECT NAME
    ,  COUNT(*) AS 'COUNT'
  FROM ANIMAL_INS
 WHERE NAME IS NOT NULL
 GROUP
    BY NAME
HAVING COUNT(*) >= 2
 ORDER
    BY NAME

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

정답:

SELECT YEAR(s.SALES_DATE) AS YEAR
    ,  MONTH(s.SALES_DATE) AS MONTH
    ,  i.GENDER
    ,  COUNT(DISTINCT i.USER_ID) AS USERS
  FROM USER_INFO AS i
  JOIN ONLINE_SALE AS s ON i.USER_ID = s.USER_ID
 WHERE i.GENDER IS NOT NULL
 GROUP
    BY YEAR(s.SALES_DATE), MONTH(s.SALES_DATE), i.GENDER
 ORDER
    BY YEAR(s.SALES_DATE), MONTH(s.SALES_DATE), i.GENDER

14. 입양 시각 구하기(1) (LV2)

정답:

SELECT HOUR(DATETIME) AS HOUR
    ,  COUNT(ANIMAL_ID) AS COUNT
  FROM ANIMAL_OUTS
 WHERE HOUR(DATETIME) BETWEEN 9 AND 19
 GROUP
    BY HOUR(DATETIME)
 ORDER
    BY HOUR(DATETIME)

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

정답:

WITH RECURSIVE HOURS AS (
    SELECT 0 AS HOUR
    UNION ALL
    SELECT HOUR + 1 FROM HOURS WHERE HOUR < 23
)
SELECT h.HOUR,
       COUNT(a.ANIMAL_ID) AS COUNT
  FROM HOURS AS  h
       LEFT JOIN ANIMAL_OUTS a
       ON HOUR(a.DATETIME) = h.HOUR
 GROUP 
    BY h.HOUR
 ORDER 
    BY h.HOUR