요즘 데이터 엔지니어링 인강과 Tableau 대시보드를 하나 만들었더니
본의아니게 프로그래머스 Lv.3 업로드에 소홀하게 되었다.
바로 시작해보도록 하자!
다음은 중고 거래 게시판 정보를 담은 USED_GOODS_BOARD
테이블과 중고 거래 게시판 첨부파일 정보를 담은 USED_GOODS_FILE
테이블입니다. USED_GOODS_BOARD
테이블은 다음과 같으며
BOARD_ID
, WRITER_ID
, TITLE
, CONTENTS
, PRICE
, CREATED_DATE
, STATUS
, VIEWS
는 게시글 ID, 작성자 ID, 게시글 제목, 게시글 내용, 가격, 작성일, 거래상태, 조회수를 의미합니다.
Column name | Type | Nullable |
---|---|---|
BOARD_ID | VARCHAR(5) | FALSE |
WRITER_ID | VARCHAR(50) | FALSE |
TITLE | VARCHAR(100) | FALSE |
CONTENTS | VARCHAR(1000) | FALSE |
PRICE | NUMBER | FALSE |
CREATED_DATE | DATE | FALSE |
STATUS | VARCHAR(10) | FALSE |
VIEWS | NUMBER | FALSE |
USED_GOODS_USER
테이블은 다음과 같으며 USER_ID
, NICKNAME
, CITY
, STREET_ADDRESS1
, STREET_ADDRESS2
, TLNO
는 각각 회원 ID, 닉네임, 시, 도로명 주소, 상세 주소, 전화번호를 를 의미합니다.
Column name | Type | Nullable |
---|---|---|
USER_ID | VARCHAR(50) | FALSE |
NICKANME | VARCHAR(100) | FALSE |
CITY | VARCHAR(100) | FALSE |
STREET_ADDRESS1 | VARCHAR(100) | FALSE |
STREET_ADDRESS2 | VARCHAR(100) | TRUE |
TLNO | VARCHAR(20) | FALSE |
USED_GOODS_BOARD
와 USED_GOODS_USER
테이블에서 완료된 중고 거래의 총금액이 70만 원 이상인 사람의 회원 ID, 닉네임, 총거래금액을 조회하는 SQL문을 작성해주세요.
결과는 총거래금액을 기준으로 오름차순 정렬해주세요.
USED_GOODS_BOARD
테이블이 다음과 같고
BOARD_ID | WRITER_ID | TITLE | CONTENTS | PRICE | CREATED_DATE | STATUS | VIEWS |
---|---|---|---|---|---|---|---|
B0001 | zkzkdh1 | 캠핑의자 | 가벼워요 깨끗한 상태입니다. 2개 | 25000 | 2022-11-29 | SALE | 34 |
B0002 | miyeon89 | 벽걸이 에어컨 | 엘지 휘센 7평 | 100000 | 2022-11-29 | SALE | 55 |
B0003 | dhfkzmf09 | 에어팟 맥스 | 에어팟 맥스 스카이 블루 색상 판매합니다. | 450000 | 2022-11-26 | DONE | 67 |
B0004 | sangjune1 | 파파야나인 포르쉐 푸쉬카 | 예민하신분은 피해주세요 | 30000 | 2022-11-30 | DONE | 78 |
B0005 | zkzkdh1 | 애플워치7 | 애플워치7 실버 스텐 45미리 판매합니다. | 700000 | 2022-11-30 | DONE | 99 |
USED_GOODS_USER
테이블이 다음과 같을 때
USER_ID | NICKNAME | CITY | STREET_ADDRESS1 | STREET_ADDRESS2 | TLNO |
---|---|---|---|---|---|
cjfwls91 | 점심만금식 | 성남시 | 분당구 내정로 185 | 501호 | 01036344964 |
zkzkdh1 | 후후후 | 성남시 | 분당구 내정로 35 | 가동 1202호 | 01032777543 |
spdlqj12 | 크크큭 | 성남시 | 분당구 수내로 206 | 2019동 801호 | 01087234922 |
xlqpfh2 | 잉여킹 | 성남시 | 분당구 수내로 1 | 001-004 | 01064534911 |
dhfkzmf09 | 찐찐 | 성남시 | 분당구 수내로 13 | A동 1107호 | 01053422914 |
SQL을 실행하면 다음과 같이 출력되어야 합니다.
USER_ID | NICKNAME | TOTAL_SALES |
---|---|---|
zkzkdh1 | 후후후 | 700000 |
SELECT u.USER_ID, u.NICKNAME,
SUM(B.PRICE) AS TOTAL_SALES
FROM USED_GOODS_BOARD b
INNER JOIN USED_GOODS_USER u
ON b.WRITER_ID = u.USER_ID
WHERE b.STATUS = 'DONE'
GROUP BY u.USER_ID
HAVING TOTAL_SALES >= 700000
ORDER BY TOTAL_SALES;
이 문제는 비교적 간단한? 서브쿼리 없이도 풀이할 수 있다.
다만 WHERE
조건과 HAVING
조건에 어떤 것이 들어가야하는지 고민해봐야된다.
다음은 어느 자동차 대여 회사의 자동차 대여 기록 정보를 담은
CAR_RENTAL_COMPANY_RENTAL_HISTORY
테이블입니다.
CAR_RENTAL_COMPANY_RENTAL_HISTORY
테이블은 아래와 같은 구조로 되어있으며,
HISTORY_ID
, CAR_ID
, START_DATE
, END_DATE
는 각각 자동차 대여 기록 ID, 자동차 ID, 대여 시작일, 대여 종료일을 나타냅니다.
Column name | Type | Nullable |
---|---|---|
HISTORY_ID | INTEGER | FALSE |
CAR_ID | INTEGER | FALSE |
START_DATE | DATE | FALSE |
END_DATE | DATE | FALSE |
CAR_RENTAL_COMPANY_RENTAL_HISTORY
테이블에서 2022년 10월 16일에 대여 중인 자동차인 경우 '대여중' 이라고 표시하고, 대여 중이지 않은 자동차인 경우 '대여 가능'을 표시하는 컬럼(컬럼명: AVAILABILITY
)을 추가하여
자동차 ID와 AVAILABILITY
리스트를 출력하는 SQL문을 작성해주세요.
이때 반납 날짜가 2022년 10월 16일인 경우에도 '대여중'으로 표시해주시고 결과는 자동차 ID를 기준으로 내림차순 정렬해주세요.
예를 들어 CAR_RENTAL_COMPANY_RENTAL_HISTORY
테이블이 다음과 같다면
HISTORY_ID | CAR_ID | START_DATE | END_DATE |
---|---|---|---|
1 | 4 | 2022-09-27 | 2022-09-27 |
2 | 3 | 2022-10-03 | 2022-10-04 |
3 | 2 | 2022-10-05 | 2022-10-05 |
4 | 1 | 2022-10-11 | 2022-10-16 |
5 | 3 | 2022-10-13 | 2022-10-15 |
6 | 2 | 2022-10-15 | 2022-10-17 |
2022년 10월 16일에 대여 중인 자동차는 자동차 ID가 1, 2인 자동차이고, 대여 가능한 자동차는 자동차 ID가 3, 4이므로, '대여중' 또는 '대여 가능' 을 표시하는 컬럼을 추가하고, 자동차 ID를 기준으로 내림차순 정렬하면 다음과 같이 나와야 합니다.
CAR_ID | AVAILABILITY |
---|---|
4 | 대여 가능 |
3 | 대여 가능 |
2 | 대여중 |
1 | 대여중 |
SELECT CAR_ID
, CASE WHEN SUM(AVAILABILITY) > 0 THEN "대여중"
ELSE "대여 가능" END AS AVAILABILITY
FROM (SELECT CAR_ID
, CASE WHEN "2022-10-16" BETWEEN START_DATE AND END_DATE THEN 1
ELSE 0 END AS AVAILABILITY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY) AS z
GROUP BY CAR_ID
ORDER BY CAR_ID DESC
문제에서 요구하는 22년 10월 16일에 대여중인 자동차를 '대여중'이라고 반환하기 위해서는
먼저 해당 기간에 대여중인지 파악해야 할 것이다.
따라서 FROM절 서브쿼리에 CAR_ID와 22년 10월 16일이 START_DATE와 END_DATE에 포함되어있는지 확인하는 쿼리를 AVIAILABILITY
로 이름지어준 다음,
CAR_ID 별로 대여중이면 1, 대여중이 아니면 0이 반환되기 때문에
이를 GROUP BY로 묶어 준뒤 SUM(AVAILAVILITY)
가 1 초과이면 대여중이기 때문에 이를 통해 대여중인지 아닌지 알 수 있을 것이라 생각하고 쿼리를 작성했다.
다음은 어느 자동차 대여 회사의 자동차 대여 기록 정보를 담은 CAR_RENTAL_COMPANY_RENTAL_HISTORY
테이블입니다. CAR_RENTAL_COMPANY_RENTAL_HISTORY
테이블은 아래와 같은 구조로 되어있으며, HISTORY_ID
, CAR_ID
, START_DATE
, END_DATE
는 각각 자동차 대여 기록 ID, 자동차 ID, 대여 시작일, 대여 종료일을 나타냅니다.
Column name | Type | Nullable |
---|---|---|
HISTORY_ID | INTEGER | FALSE |
CAR_ID | INTEGER | FALSE |
START_DATE | DATE | FALSE |
END_DATE | DATE | FALSE |
CAR_RENTAL_COMPANY_RENTAL_HISTORY
테이블에서 대여 시작일을 기준으로 2022년 8월부터 2022년 10월까지 총 대여 횟수가 5회 이상인 자동차들에 대해서 해당 기간 동안의 월별 자동차 ID 별 총 대여 횟수(컬럼명: RECORDS
) 리스트를 출력하는 SQL문을 작성해주세요.
결과는 월을 기준으로 오름차순 정렬하고, 월이 같다면 자동차 ID를 기준으로 내림차순 정렬해주세요.
특정 월의 총 대여 횟수가 0인 경우에는 결과에서 제외해주세요.
예를 들어 CAR_RENTAL_COMPANY_RENTAL_HISTORY
테이블이 다음과 같다면
HISTORY_ID | CAR_ID | START_DATE | END_DATE |
---|---|---|---|
1 | 1 | 2022-07-27 | 2022-08-02 |
2 | 1 | 2022-08-03 | 2022-08-04 |
3 | 2 | 2022-08-05 | 2022-08-05 |
4 | 2 | 2022-08-09 | 2022-08-12 |
5 | 3 | 2022-09-16 | 2022-10-15 |
6 | 1 | 2022-08-24 | 2022-08-30 |
7 | 3 | 2022-10-16 | 2022-10-19 |
8 | 1 | 2022-09-03 | 2022-09-07 |
9 | 1 | 2022-09-18 | 2022-09-19 |
10 | 2 | 2022-09-08 | 2022-09-10 |
11 | 2 | 2022-10-16 | 2022-10-19 |
12 | 1 | 2022-09-29 | 2022-10-06 |
13 | 2 | 2022-10-30 | 2022-11-01 |
14 | 2 | 2022-11-05 | 2022-11-05 |
15 | 3 | 2022-11-11 | 2022-11-11 |
대여 시작일을 기준으로 총 대여 횟수가 5회 이상인 자동차는 자동차 ID가 1, 2인 자동차입니다.
월 별 자동차 ID별 총 대여 횟수를 구하고 월 오름차순, 자동차 ID 내림차순으로 정렬하면 다음과 같이 나와야 합니다.
MONTH | CAR_ID | RECORDS |
---|---|---|
8 | 2 | 2 |
8 | 1 | 2 |
9 | 2 | 1 |
9 | 1 | 3 |
10 | 2 | 2 |
SELECT MONTH(START_DATE) AS MONTH, CAR_ID, COUNT(*) AS RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE START_DATE >= '2022-08-01'
AND START_DATE < '2022-11-01'
AND CAR_ID IN (SELECT CAR_ID FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE START_DATE >= '2022-08-01'
AND START_DATE < '2022-11-01'
GROUP BY CAR_ID
HAVING COUNT(CAR_ID) >= 5)
GROUP BY 1, 2
HAVING RECORDS > 0
ORDER BY 1, 2 DESC;
먼저 22년 8월부터 22년 10월까지 대여중인 자동차들의 조건을 구하는데
그 중 총 대여횟수가 5회 이상인 자동차를 알아보기 위해서는
CAR_ID
로 GROUPING하여 해당하는 카운트가 5 이상인지 알아보면 됐었다.
따라서 WHERE
절의 CAR_ID
가 위의 조건에 해당하는지를 반환하는 식을
IN 서브쿼리에 작성한 다음
본 쿼리에도 똑같은 START_DATE 조건을 걸어주고 이를 카운트해주는 것을 RECORDS
로 이름지어주어
MONTH
와 RECORDS
로 GROUPING해주었다.
다음은 식당의 정보를 담은 REST_INFO
테이블입니다. REST_INFO
테이블은 다음과 같으며
REST_ID
, REST_NAME
, FOOD_TYPE
, VIEWS
, FAVORITES
, PARKING_LOT
, ADDRESS
, TEL
은 식당 ID, 식당 이름, 음식 종류, 조회수, 즐겨찾기수, 주차장 유무, 주소, 전화번호를 의미합니다.
Column name | Type | Nullable |
---|---|---|
REST_ID | VARCHAR(5) | FALSE |
REST_NAME | VARCHAR(50) | FALSE |
FOOD_TYPE | VARCHAR(20) | TRUE |
VIEWS | NUMBER | TRUE |
FAVORITES | NUMBER | TRUE |
PARKING_LOT | VARCHAR(1) | TRUE |
ADDRESS | VARCHAR(100) | TRUE |
TEL | VARCHAR(100) | TRUE |
REST_INFO
테이블에서 음식종류별로 즐겨찾기수가 가장 많은 식당의 음식 종류, ID, 식당 이름, 즐겨찾기수를 조회하는 SQL문을 작성해주세요. 이때 결과는 음식 종류를 기준으로 내림차순 정렬해주세요.
REST_INFO
테이블이 다음과 같을 때
REST_ID | REST_NAME | FOOD_TYPE | VIEWS | FAVORITES | PARKING_LOT | ADDRESS | TEL |
---|---|---|---|---|---|---|---|
00001 | 은돼지식당 | 한식 | 1150345 | 734 | N | 서울특별시 중구 다산로 149 | 010-4484-8751 |
00002 | 하이가쯔네 | 일식 | 120034 | 112 | N | 서울시 중구 신당동 375-21 | NULL |
00003 | 따띠따띠뜨 | 양식 | 1234023 | 102 | N | 서울시 강남구 신사동 627-3 1F | 02-6397-1023 |
00004 | 스시사카우스 | 일식 | 1522074 | 230 | N | 서울시 서울시 강남구 신사동 627-27 | 010-9394-2554 |
00005 | 코슌스 | 일식 | 15301 | 123 | N | 서울특별시 강남구 언주로153길 | 010-1315-8729 |
SQL을 실행하면 다음과 같이 출력되어야 합니다.
FOOD_TYPE | REST_ID | REST_NAME | FAVORITES |
---|---|---|---|
한식 | 00001 | 은돼지식당 | 734 |
일식 | 00004 | 스시사카우스 | 230 |
양식 | 00003 | 따띠따띠뜨 | 102 |
SELECT FOOD_TYPE, REST_ID, REST_NAME, FAVORITES
FROM REST_INFO
WHERE FOOD_TYPE IN (
SELECT FOOD_TYPE
FROM REST_INFO
GROUP BY FOOD_TYPE
HAVING FAVORITES = MAX(FAVORITES)
)
ORDER BY FOOD_TYPE DESC;
이번에는 각 FOOD_TYPE
별로 가장 많은 즐겨찾기(FAVORITE
) 수를 가진 값들을 반환하는 쿼리를 작성하면 되는데
이는 IN 서브쿼리로 구현하였다.
이제 Lv.3 업로드까지 끝이 났다.
Lv.4를 풀어야하기 때문에 Lv.4를 푼다음 다시 돌아오도록 하겠다!