[MySQL] Programmers - MySQL Lv.3,4,5

Programmers - MySQL Lv.3,4,5

https://school.programmers.co.kr/learn/challenges?order=recent&languages=mysql&page=1&levels=3%2C4%2C5

코딩테스트 연습 | 프로그래머스 스쿨

 

1. 없어진 기록 찾기

SELECT animal_id, name
FROM animal_outs
WHERE animal_id NOT IN (SELECT animal_id FROM animal_ins)
ORDER BY animal_id;

 

2. 있었는데요 없었습니다 (*)

SELECT o.animal_id, o.name
FROM animal_outs o
INNER JOIN animal_ins i
ON o.animal_id=i.animal_id
WHERE i.datetime>o.datetime
ORDER BY i.datetime;

 

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

SELECT name, datetime
FROM animal_ins 
WHERE animal_id NOT IN (SELECT animal_id FROM animal_outs)
ORDER BY datetime LIMIT 3;

 

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

# 풀이1
SELECT o.animal_id, o.animal_type, o.name
FROM animal_ins i
INNER JOIN animal_outs o
ON i.animal_id=o.animal_id
WHERE i.sex_upon_intake!=o.sex_upon_outcome;

# 풀이2
SELECT o.animal_id, o.animal_type, o.name
FROM animal_ins i
INNER JOIN animal_outs o
ON i.animal_id=o.animal_id
WHERE i.sex_upon_intake LIKE 'intact%' and o.sex_upon_outcome IN ('Spayed Female', 'Neutered Male');

 

5. 오랜 기간 보호한 동물(2)

# 풀이 1
SELECT o.animal_id, o.name
FROM animal_outs o
INNER JOIN animal_ins i
ON o.animal_id=i.animal_id
ORDER BY DATEDIFF(o.datetime,i.datetime) DESC LIMIT 2;

# 풀이 2
SELECT o.animal_id, o.name
FROM animal_outs o
INNER JOIN animal_ins i
ON o.animal_id=i.animal_id
ORDER BY (o.datetime-i.datetime) DESC LIMIT 2;

※ 내림차순(DESC)로 바꾸지 않아 틀렸었음

 

6. 입양 시각 구하기(2)

WITH RECURSIVE cte AS (
    SELECT 0 AS hour
    UNION ALL
    SELECT hour+1
    FROM cte
    WHERE hour < 23)
    
SELECT cte.hour, COUNT(o.animal_id) AS count
FROM cte
LEFT JOIN animal_outs as o
ON cte.hour=HOUR(o.datetime)
GROUP BY cte.hour
ORDER BY cte.hour;

※ 참고 : https://velog.io/@cyanred9/SQL-Recursive

 

7. 우유와 요거트가 담긴 장바구니 (*)

SELECT cart_id
FROM cart_products
WHERE name in ('Milk', 'Yogurt')
GROUP BY cart_id
HAVING COUNT(DISTINCT(name))>=2
ORDER BY cart_id;

 

8. 헤비 유저가 소유한 장소 (*)

SELECT a.id, a.name, b.host_id
FROM places a
INNER JOIN (SELECT host_id
            FROM places
            GROUP BY host_id
            HAVING count(name)>=2) b
ON a.host_id=b.host_id
ORDER BY a.id;

※ 기존 테이블에서 HOST_ID가 같은 걸 살려야 하므로 서브쿼리 사용!

 

9. 조건별로 분류하여 주문상태 출력하기

SELECT order_id, product_id, DATE_FORMAT(out_date,'%Y-%m-%d') AS OUT_DATE,  
    CASE
        WHEN OUT_DATE > DATE('2022-05-01') THEN '출고대기'
        WHEN DATE('2022-05-01') >= OUT_DATE THEN '출고완료'
        WHEN OUT_DATE IS NULL THEN '출고미정'
     END AS '출고여부'
FROM food_order
ORDER BY order_id;

 

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

SELECT category, price, product_name
FROM food_product
WHERE category in ('과자', '국', '김치', '식용유') AND 
	price in (SELECT MAX(price) FROM food_product GROUP BY category)
GROUP BY category
ORDER BY price DESC;

 

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

SELECT p.product_id, p.product_name, (p.price*o.amount) AS total_sales
FROM food_product p
INNER JOIN (SELECT product_id, SUM(amount) amount 
            FROM food_order 
            WHERE YEAR(produce_date)=2022 and MONTH(produce_date)=5
            GROUP BY product_id) o
ON p.product_id=o.product_id
ORDER BY total_sales DESC, product_id ASC;

 

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

SELECT i.rest_id, i.rest_name, i.food_type, i.favorites, i.address, ROUND(r.score,2) AS score
FROM rest_info i
INNER JOIN (SELECT rest_id, AVG(review_score) AS score FROM rest_review GROUP BY rest_id) r
on i.rest_id=r.rest_id
WHERE i.address LIKE '서울%'
ORDER BY score DESC, favorites DESC;

 

13. 즐겨찾기가 가장 많은 식당 정보 출력하기 (*)

SELECT a.food_type, a.rest_id, a.rest_name, a.favorites
FROM rest_info a
INNER JOIN (SELECT food_type, max(favorites) favorites FROM rest_info GROUP BY food_type) b
ON a.food_type=b.food_type and a.favorites=b.favorites
ORDER BY food_type DESC;

 

14. 그룹별 조건에 맞는 식당 목록 출력하기 (*)

SELECT m.member_name, r.review_text, DATE_FORMAT(r.review_date, '%Y-%m-%d') AS review_date
FROM member_profile AS m, rest_review AS r
WHERE m.member_id=r.member_id AND r.member_id=(SELECT member_id
                                              FROM rest_review
                                              GROUP BY member_id
                                              ORDER BY COUNT(member_id) DESC LIMIT 1)
ORDER BY r.review_date ASC, r.review_text ASC;

 

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

SELECT YEAR(s.sales_date) year, MONTH(s.sales_date) month, i.gender gender, count(DISTINCT(i.user_id)) AS users
FROM user_info i
INNER JOIN online_sale s
ON i.user_id=s.user_id
GROUP BY YEAR(s.sales_date), MONTH(s.sales_date), i.gender
HAVING i.gender IS NOT NULL
ORDER BY year ASC, month ASC, gender ASC;

※ 중복제거가 관건!

 

16. 상품을 구매한 회원 비율 구하기

SELECT YEAR(o.sales_date) AS year,
    MONTH(o.sales_date) AS month,
    COUNT(DISTINCT(o.user_id)) as puchased_users,
    ROUND(COUNT(DISTINCT(o.user_id))/(
        SELECT COUNT(user_id) 
        FROM user_info 
        WHERE joined LIKE '2021%'),1) AS puchased_ratio
FROM online_sale o
INNER JOIN user_info u ON o.user_id=u.user_id
WHERE u.joined LIKE '2021%'
GROUP BY YEAR(o.sales_date), MONTH(o.sales_date)
ORDER BY year ASC, month ASC;

 

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

SELECT DATE_FORMAT(sales_date, '%Y-%m-%d') sales_date, product_id, user_id, sales_amount FROM online_sale
WHERE sales_date LIKE '2022-03%'
UNION
SELECT sales_date, product_id, NULL, sales_amount FROM offline_sale
WHERE sales_date LIKE '2022-03%'
ORDER BY sales_date, product_id, user_id;

 

18. 취소되지 않은 진료 예약 조회하기

SELECT a.apnt_no, p.pt_name, a.pt_no, a.mcdp_cd, d.dr_name, a.apnt_ymd
FROM appointment a
INNER JOIN (SELECT pt_no, pt_name FROM patient) p ON a.pt_no=p.pt_no
INNER JOIN (SELECT dr_name, dr_id FROM doctor) d ON a.mddr_id=d.dr_id
WHERE a.apnt_ymd LIKE '2022-04-13%' AND a.apnt_cncl_yn='n'
ORDER BY apnt_ymd ASC;

 

19. 주문량이 많은 아이스크림들 조회하기

SELECT j.flavor
FROM FIRST_HALF f
INNER JOIN (SELECT total_order, flavor FROM JULY) j ON f.flavor=j.flavor
GROUP BY j.flavor
ORDER BY (f.total_order + sum(j.total_order)) DESC LIMIT 3;