[프로그래머스] 7주차 MySQL 스터디 2

🌟 1. SELECT - [오프라인/온라인 판매 데이터 통합하기] _ LEVEL 4

다음은 어느 의류 쇼핑몰의 온라인 상품 판매 정보를 담은 ONLINE_SALE 테이블과 오프라인 상품 판매 정보를 담은 OFFLINE_SALE 테이블 입니다.

ONLINE_SALE 테이블과 OFFLINE_SALE 테이블에서 2022년 3월의 오프라인/온라인 상품 판매 데이터의 판매 날짜, 상품ID, 유저ID, 판매량을 출력하는 SQL문을 작성해주세요.

OFFLINE_SALE 테이블의 판매 데이터의 USER_ID 값은 NULL 로 표시해주세요.

결과는 판매일을 기준으로 오름차순 정렬해주시고 판매일이 같다면 상품 ID를 기준으로 오름차순, 상품ID까지 같다면 유저 ID를 기준으로 오름차순 정렬해주세요.

 

[ 정답 쿼리 ]

WITH SUB AS (
SELECT sales_date, product_id, user_id, sales_amount
FROM online_sale
UNION ALL 
SELECT sales_date, product_id, NULL AS user_id, sales_amount
FROM offline_sale
)

SELECT 
    DATE_FORMAT(sales_date,"%Y-%m-%d") sales_date
    , product_id
    , user_id
    , sales_amount
FROM SUB
WHERE sales_date LIKE "2022-03%"
ORDER BY sales_date, product_id, user_id;

 

[ 풀이 ]

• online_sale 테이블과 offline_sale 테이블을 UNION할 때 전제 조건이 두 테이블의 field 구성이 같아야 한다는 것.
• 따라서 UNION 전에 offline_sale 테이블에는 존재하지 않는 user_id 필드에 대한 작업이 필요하다

---

2. SELECT - [역순 정렬하기] _ LEVEL 1

ANIMAL_INS 테이블은 동물 보호소에 들어온 동물의 정보를 담은 테이블입니다.

동물 보호소에 들어온 모든 동물의 이름과 보호 시작일을 조회하는 SQL문을 작성해주세요.

이때 결과는 ANIMAL_ID 역순으로 보여주세요. SQL을 실행하면 다음과 같이 출력되어야 합니다.

 

[ 정답 쿼리 ]

SELECT name, datetime
FROM animal_ins
ORDER BY animal_id DESC;

---

3. GROUP BY - [고양이와 개는 몇 마리 있을까] _ LEVEL 2

ANIMAL_INS 테이블은 동물 보호소에 들어온 동물의 정보를 담은 테이블입니다.

동물 보호소에 들어온 동물 중 고양이와 개가 각각 몇 마리인지 조회하는 SQL문을 작성해주세요. 이때 고양이를 개보다 먼저 조회해주세요.

 

[ 정답 쿼리 ]

SELECT animal_type, COUNT(*) AS count
FROM animal_ins
GROUP BY 1
ORDER BY 1;

 

[ 풀이 ]

• animal_type 별로 집계를 하면 되는 간단한 문제
• 고양이(Cat)을 개(Dog) 보다 먼저 조회 = ORDER BY animal_type ASC

---

4. SELECT - [아픈 동물 찾기] _ LEVEL 1

ANIMAL_INS 테이블은 동물 보호소에 들어온 동물의 정보를 담은 테이블입니다.

동물 보호소에 들어온 동물 중 아픈 동물1의 아이디와 이름을 조회하는 SQL 문을 작성해주세요. 이때 결과는 아이디 순으로 조회해주세요.

 

[ 정답 쿼리 ]

SELECT animal_id, name
FROM animal_ins
WHERE intake_condition = 'Sick'
ORDER BY 1;

---

🌟🌟🌟 5. JOIN - [상품을 구매한 회원 비율 구하기] _ LEVEL 5

다음은 어느 의류 쇼핑몰에 가입한 회원 정보를 담은 USER_INFO 테이블과 온라인 상품 판매 정보를 담은 ONLINE_SALE 테이블 입니다.

USER_INFO 테이블과 ONLINE_SALE 테이블에서 2021년에 가입한 전체 회원들 중 상품을 구매한 회원수와 상품을 구매한 회원의 비율

(=2021년에 가입한 회원 중 상품을 구매한 회원수 / 2021년에 가입한 전체 회원 수)을 년, 월 별로 출력하는 SQL문을 작성해주세요.

상품을 구매한 회원의 비율은 소수점 두번째자리에서 반올림하고, 전체 결과는 년을 기준으로 오름차순 정렬해주시고 년이 같다면 월을 기준으로 오름차순 정렬해주세요.

 

[ 정답 쿼리 ]

WITH JOINED_2021 AS 
(
    SELECT user_id
    FROM user_info
    WHERE YEAR(joined) = 2021
)

SELECT 
    YEAR(sales_date) AS year
    , MONTH(sales_date) AS month
    , COUNT(DISTINCT user_id) AS purchased_users
    , ROUND(COUNT(DISTINCT user_id)/(SELECT COUNT(*) FROM JOINED_2021),1) AS purchased_ratio
FROM online_sale
WHERE user_id IN (SELECT user_id FROM JOINED_2021)
GROUP BY 1,2
ORDER BY 1,2;

 

[ 풀이 ]

• 회원 정보와 관련된 테이블로부터 2021년 가입자만 필터링한 임시 테이블을 WITH 구문으로 저장 후 사용
• 해당 문제의 예시에서도 보이듯이, 동일 회원이 한달 이내에 중복 구매한 내역에 대해서는 1번으로 카운팅하고 있으므로(중복 제거) DISITINCT 키워드를 적절히 활용할 것
• 비율을 구할 때 분모에 들어가는 값은 고정으로, 스칼라 서브쿼리를 활용
  • COUNT(DISTINCT user_id)/(SELECT COUNT(*) FROM JOINED_2021)

실행 결과

---

6. String, Date - [루시와 엘라 찾기] _ LEVEL 2

ANIMAL_INS 테이블은 동물 보호소에 들어온 동물의 정보를 담은 테이블입니다.

동물 보호소에 들어온 동물 중 이름이 Lucy, Ella, Pickle, Rogan, Sabrina, Mitty인 동물의 아이디와 이름, 성별 및 중성화 여부를 조회하는 SQL 문을 작성해주세요.

이때 결과는 아이디 순으로 조회해주세요.

 

[ 정답 쿼리 ]

SELECT animal_id, name, sex_upon_intake
FROM animal_ins
WHERE name IN ('Lucy', 'Ella', 'Pickle', 'Rogan', 'Sabrina', 'Mitty')
ORDER BY animal_id;