문제 1~2. 쿼리테스트 2회차
- Sales_SalesOrderDetail은 SalesOrderID를 통해 Sales_SalesOrderHeader와 외래 키 관계를 가집니다. 각 판매 주문 세부 정보는 특정 판매 주문 ID에 속합니다.
- Sales_SalesOrderHeader는 CustomerID를 통해 Sales_Customer 테이블과 외래 키 관계를 가집니다. 각 판매 주문 ID는 특정 고객과 연결되어 있습니다.
- Sales_Customer는 PersonID를 통해 Person_Person 테이블(BusinessEntityID)과 외래 키 관계를 가집니다. 이는 각 고객이 하나의 개인 정보와 연결됨을 의미합니다.
문제 1번
당신은 마케팅 팀의 일원으로서, 최근에 진행된 이메일 프로모션 캠페인에 관심이 있습니다. 해당 프로모션에 동의한 고객 수를 추산해야 합니다. 'Person_Person' 테이블을 사용하여 다음 조건을 만족하는 고객의 수를 구하세요 :
1. 이메일 프로모션에 "동의"한 고객
2. 해당 고객들 중 "개인(소매)" 고객의 수
[출력 값 예시]
'customer_count' (고객 수)
다음과 같이 결과 출력이 되어야 합니다. (해당 테이블은 예시이며, 실제 정답과 다를 수 있습니다.)
+----------------+
| customer_count |
+----------------+
| ... |
+----------------+💡 문제 풀이
`내 풀이`
SELECT count(*) AS customer_count
FROM Person_Person pp
WHERE EmailPromotion != 0
AND PersonType = 'IN';
`모범 답안`
SELECT COUNT(DISTINCT BusinessEntityID) as customer_count
FROM Person_Person
WHERE EmailPromotion > 0 -- 0 = 이메일 프로모션을 받지 않음
AND PersonType = 'IN' -- IN = 개인(소매) 고객
`오답노트`
- 고유한 유저 수를 구할 때는 distinct를 꼭 쓰는 습관을 들이자 (pk라 어차피 중복값 없다고 하더라도 습관습관!)
문제 2번
당신의 회사는 지난 2011년 10월 동안 자사 제품을 많이 주문한 고객들에게 특별 할인 쿠폰을 제공할 예정입니다. 이를 위해 2011년 10월 한 달 동안 회사 제품을 주문한 고객을 조회하고 구매수량별로 고객등급을 매기려 합니다. 결과는 총 주문 수량을 내림차순으로 정렬해주세요.
'Sales_SalesOrderHeader', 'Sales_SalesOrderDetail', 'Sales_Customer', 'Person_Person' 테이블을 사용하여 다음 조건을 만족하는 고객 목록을 구하세요:
1. 주문 날짜가 “2011-10-01 부터 2011-10-31 사이”에 해당
2. 고객의 총 주문 수량에 따라 5가지 등급으로 분류
1. VIP : 100 개 이상
2. GOLD : 70 개 이상 100 개 미만
3. SILVER : 40 개 이상 70 개 미만
4. BRONZE : 20 개 이상 40 개 미만
5. BASIC : 20 개 미만
3. 해당 고객들의 기본 정보를 포함, 총 주문 수량 기준 내림차순 정렬하여 출력
[출력 값 예시]
customer_id(고객 ID), first_name(이름), last_name(성), total_quantity(총 주문 수량), customer_class(고객 등급)
다음과 같이 결과 출력이 되어야 합니다. (해당 테이블은 예시이며, 실제 정답과 다를 수 있습니다.)
+-------------+------------+-------------+----------------+----------------+
| customer_id | first_name | last_name | total_quantity | customer_class |
+-------------+------------+-------------+----------------+----------------+
| 29486 | Kim | Abercrombie | 92 | GOLD |
| 29510 | Cecil | Allison | 115 | VIP |
| 29525 | Teresa | Atkinson | 103 | VIP |
...
+-------------+------------+-------------+----------------+----------------+💡 문제 풀이
`내 풀이`
WITH t1 AS
(SELECT sc.CustomerID
, pp.FirstName
, pp.LastName
, SUM(ssod.OrderQty) AS total_quantity
FROM Sales_SalesOrderHeader ssoh
LEFT JOIN Sales_SalesOrderDetail ssod ON ssoh.SalesOrderID = ssod.SalesOrderID
LEFT JOIN Sales_Customer sc ON ssoh.CustomerID = sc.CustomerID
LEFT JOIN Person_Person pp ON sc.PersonID = pp.BusinessEntityID
WHERE SUBSTR(ssoh.OrderDate,1,7)='2011-10'
GROUP BY sc.CustomerID
)
SELECT *,
CASE WHEN total_quantity < 20 THEN 'BASIC'
WHEN total_quantity BETWEEN 20 AND 39 THEN 'BRONZE'
WHEN total_quantity BETWEEN 40 AND 69 THEN 'SILVER'
WHEN total_quantity BETWEEN 70 AND 99 THEN 'GOLD'
ELSE 'VIP'
END AS customer_class
FROM t1
ORDER BY total_quantity DESC;
`모범 답안`
SELECT c.customerid as customer_id
, p.firstname as first_name
, p.lastname as last_name
, SUM(so.orderqty) AS total_quantity
, case when SUM(so.orderqty) >= 100 then 'VIP'
when SUM(so.orderqty) >= 70 then 'GOLD'
when SUM(so.orderqty) >= 40 then 'SILVER'
when SUM(so.orderqty) >= 20 then 'BRONZE'
else 'BASIC' end as customer_class
FROM Sales_Customer c
INNER JOIN Person_Person p ON c.personid = p.businessentityid
INNER JOIN Sales_SalesOrderHeader soh ON c.customerid = soh.customerid
INNER JOIN Sales_SalesOrderDetail so ON soh.salesorderid = so.salesorderid
WHERE DATE(orderdate) BETWEEN '2011-10-01' AND '2011-10-31' -- 2011-10 주문
GROUP BY c.customerid, p.firstname, p.lastname
ORDER BY total_quantity desc;
`오답노트`
- 서브쿼리 없이도 문제를 풀 수 있다!!
- case문에서 굳이 between쓰지 않고 vip등급부터 >= 조건을 거는 게 보기도 더 깔끔하고 간단할 듯
- 날짜 범위를 조건으로 걸 때는 문자열에 사용하는 SUBSTR보다 DATE함수를 사용하는 습관을 들여보자!
-> DATE(날짜) BETWEEN 범위시작 AND 범위끝 - 참고로 DATE함수 없이 그냥 BETWEEN을 걸면 범위끝 부분이 시분초 00:00:00으로 집계됨
ex. BETWEEN '2011-10-01' AND '2011-10-31'로 하면 2011-10-31 00:00:00까지의 주문 정보만 집계되고 10/31 그 이후 시간대의 주문 정보는 누락되기 때문에결과가 달라짐. 그렇다고 끝을 '2011-11-01'로 하면 2011-11-01 00:00:00에 발생한 주문이 있다면 같이 집계되기 때문에 그것도 결과가 달라짐.
✅ 추가 : DBeaver에서 기본키 설정하기
- 설정 전후로 `DESC`로 테이블 속성 확인 `DESC 테이블명`;
-- 기본구조
alter table 테이블명
add constraint 제약조건 primary key (컬럼명); -- 제약조건은 사용자가 지정하는 고유 이름, 생략가능!
-- 예시(스크립트에 한 번 실행해 보세요!)
alter table Person_Person
add constraint pk_person primary key (BusinessEntityID);
desc Person_Person; -- PK설정 확인
✅ 추가 : DBeaver에서 외래키 설정하기
- 외래키 설정 안 되어있어도 ON을 통해 JOIN할 수 있음. 다만, 키 설정이 되어있으면 성능면(인덱스 적용)과 데이터 무결성(기본키는 UNIQUE해야하며 NULL이 존재할 수 없음) 관점에서도 좋아서 주로 조인(ON)기준으로 사용하는 것
[데이터베이스] 키(Key)와 인덱스(Index)에 대해
데이터베이스의 테이블에 대한 검색 속도를 향상시켜주는 자료구조테이블의 특정 컬럼에 인덱스를 생성한다는 것은, 해당 컬럼의 데이터를 정렬한 후 별도의 메모리 공간에 데이터의 물리적
velog.io
-- 기본구조
alter table 테이블명
add constraint 제약조건명 foreign key (외래키컬럼명)
references 참조테이블명 (기본키컬럼명);
-- 예시
-- [문제] Sales_SalesOrderDetail은 SalesOrderID를 통해 Sales_SalesOrderHeader와 외래 키 관계를 가집니다. 각 판매 주문 세부 정보는 특정 판매 주문 ID에 속합니다.
alter table Sales_SalesOrderDetail
add constraint fk_orderdetail_orderheader foreign key (SalesOrderID)
references Sales_SalesOrderHeader (SalesOrderID);
desc Sales_SalesOrderDetail; -- FK설정확인. ----> *외래키의 상태표시: MUL (열이 인덱스의 일부. 중복값 허용 의미!)
문제 3. 저자 별 카테고리 별 매출액 집계하기
2022년 1월의 도서 판매 데이터를 기준으로 저자 별, 카테고리 별 매출액(TOTAL_SALES = 판매량 * 판매가) 을 구하여, 저자 ID(AUTHOR_ID), 저자명(AUTHOR_NAME), 카테고리(CATEGORY), 매출액(SALES) 리스트를 출력하는 SQL문을 작성해주세요.
결과는 저자 ID를 오름차순으로, 저자 ID가 같다면 카테고리를 내림차순 정렬해주세요.
프로그래머스
SW개발자를 위한 평가, 교육, 채용까지 Total Solution을 제공하는 개발자 성장을 위한 베이스캠프
programmers.co.kr
💡 문제 풀이
`내 풀이`
with bs as
(SELECT b.BOOK_ID, b.PRICE, b.AUTHOR_ID, b.CATEGORY, sum(s.SALES) AS SUM_SALES
FROM BOOK_SALES s
JOIN BOOK b
ON b.BOOK_ID = s.BOOK_ID
WHERE SUBSTR(s.SALES_DATE,1,7) = '2022-01'
GROUP BY b.BOOK_ID
ORDER BY b.BOOK_ID
)
SELECT a.AUTHOR_ID,
a.AUTHOR_NAME,
bs.CATEGORY,
sum(bs.SUM_SALES*bs.PRICE) AS TOTAL_SALES
FROM bs
JOIN AUTHOR a
ON a.AUTHOR_ID = bs.AUTHOR_ID
GROUP BY a.AUTHOR_ID, bs.CATEGORY
ORDER BY a.AUTHOR_ID, bs.CATEGORY DESC;
`오답노트`
- 문제 제대로 읽기!! 처음에 문제 제대로 안 읽어서 매출'액'이 아니라 매출량으로 구해서 시간 낭비함
- 첫번째 서브쿼리에서 나온 sum(s.SALES)는 BOOK_ID를 기준으로 집계된 값이니까 저자별, 카테고리별로 구분할 때는 다시 한번 sum(bs.SUM_SALES * bs.PRICE)부분처럼 sum 처리를 해야 함