[문제풀이] SQL개인과제(초급/중급)

스파르타코딩클럽에서 제공하는 문제의 풀이를 기반으로 작성하였습니다.

 

초급

 

문제1. "최소 주문 날짜 찾기"

Q.  list_of_orders에는 여러 주문들의 날짜가 저장되어 있습니다.

주문 날짜는 'dd-mm-yyyy' 형식으로 저장되어 있습니다.

이 테이블에서 가장 이른 주문 날짜를 찾아보세요.

 

A. 작성답안

SELECT min(str_to_date(order_date,'%d-%m-%Y'))-- 3. order_date을 날짜타입으로 바꾸어 가장 작은 날짜조회
from list_of_orders테이블에서  -- 1.list_of_orders테이블에서 
where str_to_date(order_date,'%d-%m-%Y') is not null; -- 2. 데이터 안에 공백을 제거한 필터링 해서

 

 

A. 답안 쿼리

SELECT 
MIN(order_date) -- 3. 최소값을 조회
FROM list_of_orders -- 1. 테이블에서
WHERE STR_TO_DATE(order_date, '%d-%m-%Y'); --2. order_date를 날짜형으로 바꾸어

 

A. 답안비교

작성답안	답안쿼리 날짜형으로 바꿔지지 않았다???

 

 

문제2. "카테고리별 총 매출 계산하기

Q. order_details 테이블에는 다양한 주문들의 세부 정보가 저장되어 있습니다. 각 주문은 특정 'Category'에 속하며, 각 주문의 'Amount'는 그 주문의 매출 금액을 나타냅니다. 이 테이블을 사용하여 각 카테고리별 총 매출 금액을 계산하세요.

 

A. 작성답안

SELECT category, sum(Amount) -- 3. 카테고리와 카테고리별로 집계함수를 통해 amount 조회
from order_details -- 1. order_details에서
group by category; -- 2. category별로 그룹화한

 

A. 답안쿼리

SELECT Category, SUM(Amount) AS Total_Sales
FROM order_details
GROUP BY Category;

 

A. 답안비교

작성답안	답안쿼리

 

 

 

문제3. 상위 5명의 고객별 총 구매액 찾기

Q. 두 개의 테이블 order_details와 list_of_orders가 있습니다. order_details 테이블은 각 주문의 세부사항을 포함하고 있으며, list_of_orders 테이블은 주문에 대한 고객의 이름과 주문 ID를 포함합니다.

각 고객별로 총 구매액을 계산하여, 구매액이 가장 높은 상위 5명의 고객을 찾아보세요.

 

A. 작성답안

SELECT orders.CustomerName , 
	sum(detail.amount) total_amount -- 집계함수를 통해 Name별 매출액의 총합 조회(별칭:total_amount)
from order_details detail join list_of_orders orders  -- order_id을 기준으로 두 테이블 조인
	on detail.order_id = orders.order_id
group by orders.CustomerName -- CustomerName으로 그룹화
order by total_amount DESC  -- total_amount 내림차순
limit 5 -- 상위 5행만 조회

 

A. 답안쿼리

SELECT CustomerName, SUM(od.Amount) AS Total_Purchases
FROM order_details AS od
JOIN list_of_orders AS lo ON od.order_id = lo.order_id
GROUP BY CustomerName
ORDER BY Total_Purchases DESC
LIMIT 5;

 

A. 답안비교

작성쿼리	답안쿼리

 

---

중급

 

문제 4: "여러 주문을 한 고객들의 총 구매액 계산하기"

Q. 두 테이블 list_of_orders와 order_details가 있습니다. list_of_orders 테이블은 고객 이름과 주문 ID를, order_details 테이블은 각 주문의 상세 금액을 포함하고 있습니다. 이 테이블들을 사용하여, 3개 이상의 주문을 한 고객들의 이름, 총 주문 횟수, 그리고 그들의 총 구매액을 계산하세요.

 

A. 작성쿼리

with temp as (
SELECT orders.CustomerName, 
	detail.order_id , 
	detail.Amount 
from list_of_orders orders join order_details detail on orders.order_id = detail.order_id 
) --1.list_of_orders와 order_details을 order_id기준으로 조인 후 임시테이블 저장
SELECT customername, -- 4. 고객이름, 
	count(distinct order_id), -- 4. (고객이름별)순 주문건수와
	sum(amount) --(고객이름별) 4. 총 주문건의 합을 조회
from temp
group by customername -- 2. 고객이름별 그룹화
having count(distinct order_id) >= 3 -- 3. (고객이름별)순 주문건수가 3개 이상을 필터링

 

A. 답안쿼리

SELECT 
    lo.CustomerName,
    COUNT(DISTINCT lo.order_id) AS Total_Orders,
    SUM(od.Amount) AS Total_Amount
FROM 
    list_of_orders AS lo
JOIN 
    order_details AS od ON lo.order_id = od.order_id
GROUP BY 
    lo.CustomerName
HAVING 
    COUNT(DISTINCT lo.order_id) >= 3 --- 3건 이상

 

A. 답안비교

작성쿼리(생략)	답안쿼리

 

 

5. 평균 수량을 초과하는 주문 찾기

Q. order_details 테이블에는 다양한 주문들의 상세 정보가 저장되어 있습니다. 이 테이블의 각 행은 주문 ID(order_id), 카테고리(Category), 그리고 주문 수량(Quantity)을 포함합니다. 이 테이블을 사용하여 각 카테고리별 평균 주문 수량을 초과하는 모든 주문들을 찾아보세요.

-> 문제요약: 각 카테고리별 평균 주문 수량을 초과하는 모든 주문 조회

-> 카테고리 평균 주문건수 

 

order_details 테이블 특징

동일한 order_id라도 서브카테고리 및 기타 사항이 다르면 행이 구분됨, 각 값을 가짐

-> 따라서 동일한 B-26601의 clthing카테고리의 총 주문건은 5+3인 8건으로 집계

A. 작성쿼리

SELECT order_id ,c
	ategory ,
    	sum(Quantity) 
from order_details a -- 1. order_details테이블에서
group by order_id ,
	Category --2. order_id ,Category별로 그룹화한다 *같은카테고리, 같은 주문이어도 세부카테고리가 다르면 구분되어 있기 때문
having sum(Quantity) > (SELECT avg(quantity) 
			from order_details b
			where a.category = b.category)
order by order_id,category;

 

A. 답안쿼리

SELECT 
    order_id, 
    Category, 
    Quantity
FROM 
    order_details
WHERE 
    Quantity > (
        SELECT AVG(Quantity) 
        FROM order_details AS od2 
        WHERE od2.Category = order_details.Category
    );

 

작성쿼리- order_id,category 별 그룹화 진행	답안쿼리

 

문제 6: "카테고리별 매출액 순위 및 누적합계 계산하기"

Q order_details 테이블에는 주문의 상세 정보가 포함되어 있습니다. 이 테이블은 주문 ID(order_id), 카테고리(Category), 그리고 주문의 금액(Amount)을 포함합니다. 이 테이블을 사용하여 각 카테고리 내에서 각 주문의 매출액 순위와 그 카테고리 내의 누적 매출액을 계산하세요.-> 문제요약: 카테고리 내에서 각 주문의 매출액 순위와 그 카테고리 내의 누적 매출액

 

A. 작성쿼리

SELECT order_id, category, amount, -- 주문아이디, 카테고리, 매출액을 조회하고
	rank()over(partition by category order by amount desc) ranking,
    -- 순위를 매겨라, 어떤거냐면, category 별로 구분하여 amount의 내림차순으로
	sum(amount) over(partition by category order by amount desc) sum
    -- 합해라, 어떤거냐면, category 별로 구분하여 amount의 내림차순으로 누적해서 합해라
FROM order_details -- 테이블에서

 

B. 답안쿼리

SELECT 
    order_id, 
    Category, 
    Amount, 
    SUM(Amount) OVER (PARTITION BY Category ORDER BY Amount DESC) 
		AS Running_Total,
    RANK() OVER (PARTITION BY Category ORDER BY Amount DESC)
	  AS Amount_Rank
FROM 
    order_details;

작성쿼리	답안쿼리