👉 오늘 한 일

• 프로그래머스 SQL 문제풀이
• 책 편집
• 원티드 이력서 특강

프로그래머스 SQL

1. 서울에 위치한 식당 목록 출력하기

• lv 4, SELECT
• 두 테이블을 조인한 후 주소가 서울인 것(서울특별시, 서울시 두 가지 형태로 적혀있기 때문에 앞에서 2글자만 따옴)을 필터링 하고, 식당id로 그룹화 한 후 평균 리뷰 점수를 구한 뒤 소수점 셋째자리에서 반올림.
• 지금까지 평균 구하는 함수가 AVERAGE인 줄 알았는데 헷갈린거였다.. AVG 로 구하자.

SELECT i.rest_id,
    i.rest_name, 
    i.food_type,
    i.favorites,
    i.address,
    ROUND(SUM(r.review_score) / COUNT(r.review_score),2) AS score
FROM rest_info i
JOIN rest_review r
ON i.rest_id = r.rest_id
WHERE LEFT(i.address, 2) = '서울'
GROUP BY i.rest_id
ORDER BY score DESC, i.favorites DESC

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

• 프로그래머스 lv 4, 오프라인/온라인 판매 데이터 통합하기
• lv 4, SELECT
• 처음 생각한 답안
  • 같은 날짜에 같은 제품 id 조합이 있어 제품 id별로 그룹화를 해서 판매량을 더해줘야 하는 줄 알았음. 문제 설명을 읽어보니 동일한 날짜, 상품 ID 조합에 대해서는 하나의 판매 데이터만 존재했음. 그래도 쿼리 결과는 정답과 동일한데 정답 처리가 안돼서 아쉬웠다..

(SELECT DATE_FORMAT(sales_date, "%Y-%m-%d") sales_date,
    product_id,
    user_id,
    SUM(sales_amount) AS sales_amount
FROM online_sale
WHERE DATE_FORMAT(sales_date, "%Y-%m") = "2022-03"
GROUP BY product_id)
UNION ALL
(SELECT DATE_FORMAT(sales_date, "%Y-%m-%d") sales_date,
    product_id,
    NULL AS user_id,
    SUM(sales_amount) AS sales_amount
FROM offline_sale
WHERE DATE_FORMAT(sales_date, "%Y-%m") = "2022-03"
GROUP BY product_id)
ORDER BY sales_date, product_id, user_id

• 다시 생각한 답안
  • GROUP BY와 집계함수를 제거했다.

(SELECT DATE_FORMAT(sales_date, "%Y-%m-%d") sales_date,
    product_id,
    user_id,
    sales_amount
FROM online_sale
WHERE DATE_FORMAT(sales_date, "%Y-%m") = "2022-03")
UNION ALL
(SELECT DATE_FORMAT(sales_date, "%Y-%m-%d") sales_date,
    product_id,
    NULL AS user_id,
    sales_amount
FROM offline_sale
WHERE DATE_FORMAT(sales_date, "%Y-%m") = "2022-03")
ORDER BY sales_date, product_id, user_id

3. 자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기 💡

• lv 3, GROUP BY
• 대여중인 차 id를 먼저 뽑아낸 뒤에 나머지 차들은 대여 가능으로 처리해서 테이블을 합침

(SELECT car_id,
    "대여중" AS availability
FROM car_rental_company_rental_history
WHERE start_date <= "2022-10-16"
AND end_date >= "2022-10-16")
UNION
(SELECT car_id,
    "대여 가능" AS availability
FROM car_rental_company_rental_history
WHERE car_id NOT IN(
    SELECT car_id
    FROM car_rental_company_rental_history
    WHERE start_date <= "2022-10-16"
    AND end_date >= "2022-10-16"))
ORDER BY car_id DESC

• 다른 사람 풀이
  • CASE문을 사용해 대여중 / 대여 가능을 문자열로 반환하도록 한 후 GROUP BY 사용시에도 문자열을 그대로 뱉어낼 수 있도록 MAX로 감싸줌

SELECT 
    CAR_ID,
    MAX(CASE WHEN '2022-10-16' BETWEEN START_DATE AND END_DATE THEN '대여중'
    ELSE '대여 가능' END) AVAILABILTY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
ORDER BY CAR_ID DESC

4. 조건에 맞는 사용자와 총 거래금액 조회하기

• lv 3, GROUP BY
• 두 테이블을 조인한 후 거래 완료된 것만 필터링, 작성자 id로 그룹화. 가격을 SUM해서 총 거래금액을 구함

SELECT a.writer_id, 
    b.nickname,
    SUM(a.price) AS total_sales
FROM used_goods_board a
JOIN used_goods_user b
ON a.writer_id = b.user_id
WHERE a.status = "DONE"
GROUP BY a.writer_id
HAVING SUM(a.price) >= 700000
ORDER BY SUM(a.price)

5. 식품분류별 가장 비싼 식품의 정보 조회하기 💡

• lv 4, GROUP BY
• 다른 사람 풀이도 확인해보기!
• 각 카테고리의 가격이 가장 비싼 row를 불러와서 UNION으로 합침

(SELECT category, price, product_name
FROM food_product
WHERE price IN(
    SELECT max(price)
    FROM food_product
    WHERE category = "과자")
AND category = "과자")
UNION
(SELECT category, price, product_name
FROM food_product
WHERE price IN(
    SELECT max(price)
    FROM food_product
    WHERE category = "국")
AND category = "국")
UNION
(SELECT category, price, product_name
FROM food_product
WHERE price IN(
    SELECT max(price)
    FROM food_product
    WHERE category = "김치")
AND category = "김치")
UNION
(SELECT category, price, product_name
FROM food_product
WHERE price IN(
    SELECT max(price)
    FROM food_product
    WHERE category = "식용유")
AND category = "식용유")
ORDER BY price DESC

• 다른 사람 풀이
  • WHERE절 서브쿼리로 각 카테고리별 최대 가격을 뽑아내고 WHERE절 필터로 활용. 카테고리가 과자, 국, 김치, 식용유인 것만 뽑아내기 위해 IN 사용

SELECT CATEGORY, PRICE MAX_PRICE, PRODUCT_NAME
FROM FOOD_PRODUCT
WHERE PRICE IN
    (
    SELECT MAX(PRICE) MAX_PRICE
    FROM FOOD_PRODUCT
    GROUP BY CATEGORY
    )
AND CATEGORY IN ('과자', '국', '김치', '식용유')
ORDER BY 2 DESC