[SQL] 프로그래머스 SQL 고득점 Kit GROUP BY (1)

---

즐겨찾기가 가장 많은 식당 정보 출력하기

1. FOOD_TYPE 별로 GROUP BY 한 뒤에 MAX(FAVORITES)로 음식종류별로 가장 많은 즐겨찾기 수 찾고, WITH 절 사용해서 임시테이블 만들기
2. FOOD_TYPE, REST_ID, REST_NAME, FAVORITES 조회
3. REST_INFO 테이블에 MAX_FAVORITES 테이블을  FOOD_TYPE과 FAVORITES을 기준으로 INNER JOIN
4. FOOD_TYPE 기준 내림차순으로 정렬

WITH MAX_FAVORITES AS (SELECT FOOD_TYPE, 
                              MAX(FAVORITES) AS FAVORITES
                       FROM REST_INFO
                       GROUP BY FOOD_TYPE)

SELECT A.FOOD_TYPE, A.REST_ID, A.REST_NAME, A.FAVORITES
FROM REST_INFO AS A
INNER JOIN MAX_FAVORITES AS B
ON A.FOOD_TYPE = B.FOOD_TYPE
   AND A.FAVORITES = B.FAVORITES
ORDER BY FOOD_TYPE DESC

 

프로그래머스

---

고양이와 개는 몇 마리 있을까

1. ANIMAL_TYPE과 ANIMAL_TYPE 별 COUNT한 값 조회 
2. ANIMAL_TYPE 별로 GROUP BY 한 뒤에 각각 몇 마리인지 집계
3. ANIMAL_TYPE 기준으로 정렬

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

프로그래머스

---

동명 동물 수 찾기

1. NAME과 NAME 별 COUNT한 값 조회 
2. NAME이 NULL 값이 아닌 데이터만 추출
3. NAME 별로 GROUP BY 한 뒤에 각각 몇 번 쓰였는지 집계하고, 2번 이상 쓰인 데이터만 추출
4. NAME 기준으로 정렬

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

 

프로그래머스

---

년, 월, 성별 별 상품 구매 회원 수 구하기

1. SALES_DATE에서 YEAR과 MONTH를 추출한 값 조회 
2. ONLINE_SALE 테이블의 USER_ID에서 중복값을 제거하고 YEAR, MONTH, GENDER 별 COUNT한 값 조회
3. ONLINE_SALE 테이블에 USER_INFO 테이블을 USER_ID을 기준으로 LEFT JOIN
4. GENDER이 NULL 값이 아닌 데이터만 추출
5. YEAR, MONTH, GENDER 별로 GROUP BY 해서 각각 상품을 구매한 회원수 집계
6. YEAR, MONTH, GENDER 기준으로 정렬

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

프로그래머스

---

입양 시각 구하기(1)

1. 재귀쿼리로 9시부터 19시까지 임시 TIME 테이블 생성
2. HOUR과 HOUR 별 ANIMAL_ID를 COUNT한 값 조회
3. TIME 테이블에 ANIMAL_OUTS 테이블을 HOUR을 기준으로 LEFT JOIN
4. HOUR 별로 GROUP BY 해서 시간대 별로 입양이 몇 건이나 발생했는지 집계
5. HOUR  기준으로 정렬

WITH RECURSIVE TIME AS (
    SELECT 9 AS HOUR
    UNION ALL
    SELECT HOUR+1 
    FROM TIME 
    WHERE HOUR<19)
    
SELECT 
    HOUR,
    COUNT(ANIMAL_ID) AS COUNT
FROM TIME AS A
LEFT JOIN ANIMAL_OUTS AS B
ON A.HOUR = HOUR(B.DATETIME)
GROUP BY HOUR
ORDER BY HOUR

 

프로그래머스

---

입양 시각 구하기(2)

1. 재귀쿼리로 0시부터 23시까지 임시 TIME 테이블 생성
2. HOUR과 HOUR 별 ANIMAL_ID를 COUNT한 값 조회
3. TIME 테이블에 ANIMAL_OUTS 테이블을 HOUR을 기준으로 LEFT JOIN
4. HOUR 별로 GROUP BY 해서 시간대 별로 입양이 몇 건이나 발생했는지 집계
5. HOUR  기준으로 정렬

WITH RECURSIVE TIME AS (
    SELECT 0 AS HOUR
    UNION ALL
    SELECT HOUR+1 
    FROM TIME 
    WHERE HOUR<23)

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

 

프로그래머스

---

가격대 별 상품 개수 구하기

1. PRICE를 10000으로 나눈 몫에 10000을 곱한 값 조회
2. PRICE_GROUP 별 COUNT한 값 조회 
3. PRICE_GROUP 별로 GROUP BY 해서 가격대 별로 상품 개수 구하기
4. PRICE_GROUP 기준으로 정렬

SELECT 
    (PRICE DIV 10000 * 10000) AS PRICE_GROUP,
    COUNT(*) AS PRODUCTS
FROM PRODUCT 
GROUP BY PRICE_GROUP
ORDER BY PRICE_GROUP

 

프로그래머스