프로그래머스 | SQL 스터디 10

SELECT

재구매가 일어난 상품과 회원 리스트 구하기 (Lv2)

문제 설명

​

문제

ONLINE_SALE 테이블에서 동일한 회원이 동일한 상품을 재구매한 데이터를 구하여, 재구매한 회원 ID와 재구매한 상품 ID를 출력하는 SQL문을 작성해주세요. 결과는 회원 ID를 기준으로 오름차순 정렬해주시고 회원 ID가 같다면 상품 ID를 기준으로 내림차순 정렬해주세요.

​

풀이

SELECT로 회원 ID, 상품 ID 를 조회

 

GROUP BY에서 회원 ID, 상품 ID로 그룹화

 

HAVING에서 개수가 1보다 큰 것으로 지정

 

ORDER BY에서 회원 ID는 오름차순, 상품 ID는 내림차순 정렬

SELECT USER_ID,PRODUCT_ID
FROM ONLINE_SALE
GROUP BY USER_ID, PRODUCT_ID
HAVING COUNT(*) > 1
ORDER BY USER_ID ASC, PRODUCT_ID DESC;

 

---

SUM, MAX, MIN

연도별 대장균 크기의 편차 구하기 (Lv2)

문제 설명

​

문제

분화된 연도(YEAR), 분화된 연도별 대장균 크기의 편차(YEAR_DEV), 대장균 개체의 ID(ID) 를 출력하는 SQL 문을 작성해주세요. 분화된 연도별 대장균 크기의 편차는 분화된 연도별 가장 큰 대장균의 크기 - 각 대장균의 크기로 구하며 결과는 연도에 대해 오름차순으로 정렬하고 같은 연도에 대해서는 대장균 크기의 편차에 대해 오름차순으로 정렬해주세요.

​

풀이

WITH에서 대장균의 최대 크기를 구하는 YEAR_MAX를 설정

 

JOIN으로 두 구문을 연결 (공통 컬럼 YEAR 활용)

 

SELECT에서 원하는 YEAR , YEAR_DEV, ID 를 조회

 

ORDER BY에서 연도 오름차순, 편차 오름차순 정렬

WITH YEAR_MAX AS (
    SELECT MAX(SIZE_OF_COLONY) MAX_SIZE, YEAR(DIFFERENTIATION_DATE) YEAR
    FROM ECOLI_DATA
    GROUP BY YEAR(DIFFERENTIATION_DATE)
)

SELECT ym.YEAR, ym.MAX_SIZE-(SIZE_OF_COLONY) YEAR_DEV, ID
FROM ECOLI_DATA
JOIN YEAR_MAX ym ON YEAR(DIFFERENTIATION_DATE) = ym.YEAR
ORDER BY YEAR ASC, YEAR_DEV ASC;

 

---

GROUP BY

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

문제 설명

​

문제

USER_INFO 테이블과 ONLINE_SALE 테이블에서 년, 월, 성별 별로 상품을 구매한 회원수를 집계하는 SQL문을 작성해주세요. 결과는 년, 월, 성별을 기준으로 오름차순 정렬해주세요. 이때, 성별 정보가 없는 경우 결과에서 제외해주세요.

​

풀이

SELECT에서 년, 월, 성별, 중복이 없는 사용자 카운팅 을 조회

 

JOIN에서 두 테이블을 연결(공통 컬럼 USER_ID 사용)

 

WHERE에서 GENDER 정보가 없는 것을 제외하기 위한 조건 설정

 

GROUP BY에서 년,월,성별 기준으로 그룹화

 

ORDER BY에서 년,월,성별 오름차순 정렬

SELECT YEAR(SALES_DATE) YEAR, MONTH(SALES_DATE) MONTH,
	GENDER, COUNT(DISTINCT(OS.USER_ID)) USERS
FROM ONLINE_SALE OS
JOIN USER_INFO UI ON UI.USER_ID = OS.USER_ID
WHERE UI.GENDER IS NOT NULL
GROUP BY YEAR(SALES_DATE), MONTH(SALES_DATE), GENDER
ORDER BY YEAR, MONTH, GENDER

 

---

JOIN

⭐⭐상품을 구매한 회원 비율 구하기 (Lv5)

문제 설명

​

문제

USER_INFO 테이블과 ONLINE_SALE 테이블에서 2021년에 가입한 전체 회원들 중 상품을 구매한 회원수와 상품을 구매한 회원의 비율(=2021년에 가입한 회원 중 상품을 구매한 회원수 / 2021년에 가입한 전체 회원 수)을 년, 월 별로 출력하는 SQL문을 작성해주세요. 상품을 구매한 회원의 비율은 소수점 두번째자리에서 반올림하고, 전체 결과는 년을 기준으로 오름차순 정렬해주시고 년이 같다면 월을 기준으로 오름차순 정렬해주세요.

​

풀이

WITH에서 21년에 가입한 총 회원수를 계산, 이를 TOTAL이라 지칭

 

SELECT에서 년도, 월, 구매한 사용자수, 비율을 조회

 

JOIN에서 USER_ID를 기준으로 두 개의 테이블을 join

 

WHERE에서 가입한 년도가 2021년으로 설정

 

GROUP BY 에서 년도, 월별로 그룹화

 

ORDER BY에서 년도, 월별로 오름차순 정렬

WITH TOTAL AS (
    SELECT COUNT(*) T_C
    FROM USER_INFO
    WHERE YEAR(JOINED) = 2021
)

SELECT YEAR(SALES_DATE) YEAR, MONTH(SALES_DATE) MONTH, 
    COUNT(DISTINCT(OS.USER_ID)) AS PURCHASED_USERS, 
    ROUND(COUNT(DISTINCT(OS.USER_ID))/ (SELECT T_C FROM TOTAL),1) PUCHASED_RATIO
FROM ONLINE_SALE OS
JOIN USER_INFO UI ON OS.USER_ID = UI.USER_ID
WHERE YEAR(UI.JOINED) = 2021
GROUP BY YEAR(SALES_DATE), MONTH(SALES_DATE)
ORDER BY YEAR, MONTH ASC;

 

---

String, Date

취소되지 않은 진료 예약 조회하기 (Lv4)

문제 설명

​

문제

PATIENT, DOCTOR 그리고 APPOINTMENT 테이블에서 2022년 4월 13일 취소되지 않은 흉부외과(CS) 진료 예약 내역을 조회하는 SQL문을 작성해주세요. 진료예약번호, 환자이름, 환자번호, 진료과코드, 의사이름, 진료예약일시 항목이 출력되도록 작성해주세요. 결과는 진료예약일시를 기준으로 오름차순 정렬해주세요.

​

풀이

SELECT에서 진료예약번호, 환자이름, 환자번호, 진료과코드, 의사이름, 진료예약일시 조회

 

JOIN에서 DOCTOR와 PATIENT 테이블들을 각각 연결

 

WHERE에서 진료과코드('CS'), 진료취수여부('N'), 진료예약일시('2022-04-13%')를 설정

 

ORDER BY에서 진료예약일시를 기준으로 오름차순 정렬

SELECT A.APNT_NO, P.PT_NAME, P.PT_NO, A.MCDP_CD, D.DR_NAME, A.APNT_YMD
FROM APPOINTMENT A
JOIN DOCTOR D ON D.DR_ID = A.MDDR_ID
JOIN PATIENT P ON P.PT_NO = A.PT_NO
WHERE A.MCDP_CD = 'CS' 
    AND A.APNT_CNCL_YN = 'N' 
    AND A.APNT_YMD LIKE '2022-04-13%'
ORDER BY A.APNT_YMD;