[TIL]241205_코드카타, 개인과제 문제6

코드카타

41. 조건에 맞는 도서 출력하기

SELECT BOOK_ID, 
    DATE_FORMAT(PUBLISHED_DATE,'%Y-%m-%d')
FROM BOOK
WHERE CATEGORY = '인문'
AND PUBLISHED_DATE LIKE '2021%';

 

42. 평균 일일 대여 요금 구하기

SELECT ROUND(AVG(daily_fee) ,0)'AVERAGE_FEE'
FROM CAR_RENTAL_COMPANY_CAR
WHERE car_type = 'SUV';

 

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

SELECT user_id ,
    nickname ,
    total_sales 
FROM
(
SELECT user_id, nickname,
    ROUND(SUM(PRICE),0) total_sales
FROM used_goods_board ub
JOIN used_goods_user uu ON ub.writer_id = uu.user_id
WHERE STATUS ='DONE'
GROUP BY user_id
    ) sub
WHERE total_sales >= 700000
ORDER BY total_sales;

 

44. 가격대 별 상품 개수 구하기

-- 오답
SELECT     
    CASE WHEN price between 0 AND 9999 THEN '0'
    WHEN price between 10000 AND 19999 THEN '10000'
    WHEN price between 20000 AND 29999 THEN '20000'
    WHEN price between 30000 AND 39999 THEN '30000'
    END AS PRICE_GROUP,
    COUNT(*) PRODUCTS
FROM PRODUCT
GROUP BY 1
ORDER BY 1;

-- >> 가격대 그룹을 하나하나 작성해야함.

 

해결: 가격을 10000으로 나눈 몫에 *10000을 하면 몇만원대인지 알 수 있음

예시) 26500원인 상품 -> (26500 / 10000) 의 몫 : 2 ->  2 * 10000 = 20000원대인 상품임을 알 수 있음

DIV 연산자 사용

-- 정답
SELECT 
    (price DIV 10000)*10000 AS PRICE_GROUP ,
    COUNT(*) AS PRODUCTS
FROM PRODUCT
GROUP BY 1
ORDER BY 1;

 

45. 3월에 태어난 여성 회원 목록 출력하기

SELECT MEMBER_ID,
    MEMBER_NAME,
    GENDER,
    DATE_FORMAT(date_of_birth,'%Y-%m-%d') DATE_OF_BIRTH
FROM member_profile
WHERE date_of_birth LIKE '%-03-%'
AND gender = 'W'
AND tlno IS NOT NULL
ORDER BY member_id;

 

46. 대여 기록이 존재하는 자동차 리스트 구하기

SELECT DISTINCT(cc.CAR_ID)
FROM car_rental_company_car cc
JOIN car_rental_company_rental_history rh ON cc.car_id = rh.car_id
WHERE car_type = '세단'
AND rh.start_date LIKE '%-10-%'
ORDER BY cc.car_id DESC;

 

47. 모든 레코드 조회하기

SELECT *
FROM ANIMAL_INS
ORDER BY ANIMAL_ID;

 

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

SELECT FOOD_TYPE, 
        REST_ID,
        REST_NAME,
        FAVORITES
FROM REST_INFO
WHERE (FOOD_TYPE, FAVORITES) IN
    (
    SELECT FOOD_TYPE, MAX(FAVORITES) 
    FROM REST_INFO
    GROUP BY FOOD_TYPE
    )
ORDER BY FOOD_TYPE DESC;

 

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

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

 

50. 5월 식품들의 총매출 조회하기

SELECT fp.product_id,
    product_name,
    SUM(price * amount) TOTAL_SALES
FROM food_product fp
JOIN food_order fo ON fp.product_id = fo.product_id
WHERE produce_date LIKE '2022-05-%'
GROUP BY product_name
ORDER BY TOTAL_SALES DESC, product_id;

 

 

---

개인과제

[문제6]

각 결제 방식(payment_type)별 결제 금액의 평균 ± 3 표준편차(standard deviation)를 기준으로 이상치를 **‘Yes’/’No’**로 탐지하세요. 결제 금액이 이 범위를 벗어나면 이상치로 간주합니다. 결제 금액이 큰 순으로 정렬해주세요.

• 결과 컬럼: order_id, payment_type, payment_value, is_outlier

-- 각 결제 방식(payment_type)별 결제 금액의 평균과 표준편차(standard deviation)
SELECT order_id, 
	payment_type, 
	payment_value
FROM
(
	SELECT order_id, 
		payment_type, 
		payment_value,
		ROUND(STDDEV(payment_value) OVER(PARTITION BY payment_type), 2) AS 'std_pay',
		ROUND(AVG(payment_value) OVER(PARTITION BY payment_type), 2) AS 'avg_pay'
	FROM payments p 
	ORDER BY payment_value DESC 
) AS sub

-- 작성쿼리
SELECT order_id, 
		payment_type, 
		payment_value,
		CASE WHEN payment_value > sub.avg_pay + 3 * sub.std_pay THEN 'YES'
			WHEN payment_value < sub.avg_pay - 3 * sub.std_pay THEN 'YES'
			ELSE 'NO'
			END AS is_outlier
FROM
(
	SELECT order_id, 
		payment_type, 
		payment_value,
		ROUND(STDDEV(payment_value) OVER(PARTITION BY payment_type), 2) AS 'std_pay',
		ROUND(AVG(payment_value) OVER(PARTITION BY payment_type), 2) AS 'avg_pay'
	FROM payments p 
	ORDER BY payment_value DESC 
) AS sub;

-- 정답
SELECT
	p.order_id,
	p.payment_type,
	p.payment_value,
	CASE
		WHEN p.payment_value < ps.avg_payment - 3 * ps.stddev_payment
		OR p.payment_value > ps.avg_payment + 3 * ps.stddev_payment THEN 'Yes'
		ELSE 'No'
	END AS is_outlier
FROM payments p
JOIN (
	SELECT
		payment_type,
		AVG(payment_value) AS avg_payment,
		STDDEV(payment_value) AS stddev_payment
	FROM payments
	GROUP BY 1) AS ps 
ON p.payment_type = ps.payment_type
ORDER BY 3 desc;

 

---

24/12/12 추가

개인과제 피드백

 

전반적으로 SQL의 목적에 달성하는 점에 있어서 잘 작성하셨고 딱히 흠잡을데는 없습니다. 이제 앞으로 비즈니스 지표를 도입해서 공부해보는 법을 익히시면 되겠습니다. 프로그래머스, leetcode등은 코드카타시간에 푸시도록 하고 오레일리-한빛미디어에서 발행되는 SQL로 시작하는 데이터분석 과 같은 책들을 실습해보시길 바랍니다.