[오라클 SQL] 프로그래머스 - 조건에 맞는 사용자와 총 거래금액 조회하기

지적 및 질문은 언제나 환영입니다 !

 

2023년 10월 20일

문제 링크 : 프로그래머스 - 조건에 맞는 사용자와 총 거래금액 조회하기

 

문제 설명

(문제 설명에 잘못된 정보들이 있어 문제 의도에 맞게 임의로 수정하였습니다.) 

다음은 중고 거래 게시판 정보를 담은 USED_GOODS_BOARD 테이블과 중고 거래 게시판 이용 고객 정보를 담은 USED_GOODS_USER 테이블입니다. USED_GOODS_BOARD 테이블은 다음과 같으며 BOARD_ID, WRITER_ID, TITLE, CONTENTS, PRICE, CREATED_DATE, STATUS, VIEWS는 게시글 ID, 작성자 ID, 게시글 제목, 게시글 내용, 가격, 작성일, 거래상태, 조회수를 의미합니다.

 

 

USED_GOODS_USER 테이블은 다음과 같으며 USER_ID, NICKNAME, CITY, STREET_ADDRESS1, STREET_ADDRESS2, TLNO는 각각 회원 ID, 닉네임, 시, 도로명 주소, 상세 주소, 전화번호를 를 의미합니다.

(NICKANME은 오타 -> NICKNAME 이 옳은 표기)

 

 

문제

USED_GOODS_BOARD와 USED_GOODS_USER 테이블에서 완료된 중고 거래의 총금액이 70만 원 이상인 사람의 회원 ID, 닉네임, 총거래금액을 조회하는 SQL문을 작성해주세요. 결과는 총 거래금액을 기준으로 오름차순 정렬해주세요.

 

입력 예시

(예시 데이터대로 삽입하면, USER_GOODS_USER에 없는 유저 ID 데이터가 USED_GOODS_BOARD 데이터로 삽입을 시도하기에 무결성 제약조건에 위배됨. USED_GOODS_USER에 임의의 튜플을 추가해주겠음.)

 

USED_GOODS_BOARD 테이블이 다음과 같고,

 

USED_GOODS_USER 테이블이 다음과 같다.

+

miyeon89	에코	강릉시	정원로 53	B동 102호	01000000001
sangjune1	토뭉	시흥시	서촌상가 4길	A동 101호	01012341234

(USED_GOODS_BOARD에 명시되어있는 작성자 ID 중, USED_GOODS_USER에 존재하지 않는 작성자 추가) 

 

출력 예시

SQL을 실행하면 다음과 같이 출력되어야 한다.

 

---

 

TABLE 생성

 

USED_GOODS_USER 테이블

CREATE TABLE USED_GOODS_USER(
       USER_ID VARCHAR(50) NOT NULL,
       NICKNAME VARCHAR(100) NOT NULL,
       CITY VARCHAR(100) NOT NULL,
       STREET_ADDRESS1 VARCHAR(100) NOT NULL,
       STREET_ADDRESS2 VARCHAR(100),
       TLNO VARCHAR(20) NOT NULL,
       PRIMARY KEY(USER_ID)
);

 

USED_GOODS_BOARD 테이블

CREATE TABLE USED_GOODS_BOARD(
       BOARD_ID VARCHAR(5) NOT NULL,
       WRITER_ID VARCHAR(50) NOT NULL,
       TITLE VARCHAR(100) NOT NULL,
       CONTENTS VARCHAR(1000) NOT NULL,
       PRICE NUMBER NOT NULL,
       CREATED_DATE DATE NOT NULL,
       STATUS VARCHAR(10) NOT NULL,
       VIEWS NUMBER NOT NULL,
       PRIMARY KEY(BOARD_ID),
       FOREIGN KEY(WRITER_ID) REFERENCES USED_GOODS_USER(USER_ID)
);

 

 

예시 데이터 삽입

 

USED_GOODS_USER 데이터 삽입

INSERT INTO USED_GOODS_USER VALUES('cjfwls91', '점심만금식', '성남시', '분당구 내정로 185', '501호', '01036344964');
INSERT INTO USED_GOODS_USER VALUES('zkzkdh1', '후후후', '성남시', '분당구 내정로 35', '가동 1202호', '01032777543');
INSERT INTO USED_GOODS_USER VALUES('spdlqj12', '크크큭', '성남시', '분당구 수내로 206', '2019동 801호', '01087234922');
INSERT INTO USED_GOODS_USER VALUES('xlqpfh2', '잉여킹', '성남시', '분당구 수내로 1', '001-004', '01064534911');
INSERT INTO USED_GOODS_USER VALUES('dhfkzmf09', '찐찐', '성남시', '분당구 수내로 13', 'A동 1107호', '01053422914');
INSERT INTO USED_GOODS_USER VALUES('miyeon89', '에코', '강릉시', '정원로 53', 'B동 102호', '01000000001');
INSERT INTO USED_GOODS_USER VALUES('sangjune1', '토뭉', '시흥시', '서촌상가 4길', 'A동 101호', '01012341234');

 

 

USED_GOODS_BOARD 데이터 삽입

INSERT INTO USED_GOODS_BOARD VALUES('B0001', 'zkzkdh1', '캠핑의자', '가벼워요 깨끗한 상태입니다. 2개', 25000, '2022-11-29', 'SALE', 34);
INSERT INTO USED_GOODS_BOARD VALUES('B0002', 'miyeon89', '벽걸이 에어컨', '엘지 휘센 7평', 100000, '2022-11-29','SALE', 55);
INSERT INTO USED_GOODS_BOARD VALUES('B0003', 'dhfkzmf09', '에어팟 맥스', '에어팟 맥스 스카이 블루 색상 판매합니다.', 450000, '2022-11-26', 'DONE', 67);
INSERT INTO USED_GOODS_BOARD VALUES('B0004', 'sangjune1', '파파야나인 포르쉐 푸쉬카', '예민하신분은 피해주세요', 30000, '2022-11-30', 'DONE', 78);
INSERT INTO USED_GOODS_BOARD VALUES('B0005', 'zkzkdh1', '애플워치7', '애플워치7 실버 스텐 45미리 판매합니다.', 700000, '2022-11-30', 'DONE', 99);

 

---

 

질의 요구사항

1. 모든 회원의 ID와 각 회원이 거래한 총 거래금액을 조회. 총 거래금액을 기준으로 오름차순 정렬
2. 모든 회원의 ID와 NICKNAME, 그리고 각 회원이 거래한 총 거래금액을 조회. 총 거래금액을 기준으로 오름차순 정렬
3. 회원들 중 완료한 거래에 대한 총 거래금액이 70만원 이상인 모든 회원의 ID와 NICKNAME, 그리고 각 회원이 거래한 총 거래금액을 조회. 총 거래금액을 기준으로 오름차순 정렬

 

SQL 쿼리문 작성

 

질의 요구사항대로 쿼리문을 하나씩 작성해보며 접근해보겠다.

 

1. 모든 회원의 ID와 회원의 ID와 각 회원이 거래한 총 거래금액을 조회. 총 거래금액을 기준으로 오름차순 정렬

 

USED_GOODS_BOARD 릴레이션에서 WRITER_ID를 그룹화 애트리뷰트로 지정 후, WRITER_ID의 누적 PRICE를 합산(집단함수 SUM)하면 된다.

이후, 합산 금액을 나타내는 애트리뷰트는 오름차순으로 정렬한다.

 

 

쿼리문

SELECT WRITER_ID, SUM(PRICE) TOTAL_SALES
FROM USED_GOODS_BOARD
GROUP BY WRITER_ID
ORDER BY TOTAL_SALES;

 

1번 질의 결과 릴레이션

 

 

 

2. 모든 회원의 ID와 NICKNAME, 그리고 각 회원이 거래한 총 거래금액을 조회. 총 거래금액을 기준으로 오름차순 정렬

 

1번 질의 결과 릴레이션에 NICKNAME 애트리뷰트를 추가하면 된다.

방법은 USED_GOODS_USER 릴레이션의 USER_ID 애트리뷰트와 1번 질의 결과 릴레이션의 WRITER_ID 애트리뷰트가 같은 도메인과 값을 나타내는 점을 이용한 조인이다.

조인 후, NICKNAME 애트리뷰트를 SELECT문에 추가한다.

 

 

쿼리문

SELECT R1.WRITER_ID, U.NICKNAME, R1.TOTAL_SALES
FROM USED_GOODS_USER U,(
    SELECT WRITER_ID, SUM(PRICE) TOTAL_SALES
    FROM USED_GOODS_BOARD
    GROUP BY WRITER_ID
    ) R1
WHERE U.USER_ID = R1.WRITER_ID
ORDER BY TOTAL_SALES;

 

2번 질의 결과 릴레이션

 

3. 회원들 중 완료한 거래에 대한 총 거래금액이 70만원 이상인 모든 회원의 ID와 NICKNAME, 그리고 각 회원이 거래한 총 거래금액을 조회. 총 거래금액을 기준으로 오름차순 정렬

 

USED_GOODS_BOARD 릴레이션에서 STATUS가 'DONE'인 즉, 거래가 완료된 거래액의 총 금액이 70만원 이상인 모든 회원에 대한 ID, NICKNAME, 총 거래금액을 도출해야 한다.

 

1번 쿼리문에 대해, 거래가 완료되지 않은 튜플들을 걸러내기 위해 WHRER절을 추가하고 총 거래금액이 70만원 미만인 회원들에 대한 정보를 걸러내기 위해 HAVING절을 추가하면 된다.

 

SELECT R1.WRITER_ID, U.NICKNAME, R1.TOTAL_SALES
FROM USED_GOODS_USER U,(
    SELECT WRITER_ID, SUM(PRICE) TOTAL_SALES
    FROM USED_GOODS_BOARD
    WHERE STATUS = 'DONE'
    GROUP BY WRITER_ID
    HAVING SUM(PRICE) >= 700000 ) R1
WHERE U.USER_ID = R1.WRITER_ID
ORDER BY TOTAL_SALES;

 

3번 질의 결과 릴레이션

 

 

 

3번 질의에 대한 쿼리문을 끝으로, 문제가 요구하는 결과 릴레이션 도출에 성공하였다.

 

---

 

풀이 후기

중첩 질의, 그룹화, 집단함수, HAVING절, 조인을 이해하고다룰 수 있느냐 없느냐가 이 문제의 체감 난이도를 결정한다고 생각한다.