필수 과제
초급 문제
문제 1: "최소 주문 날짜 찾기"
주어진 테이블 list_of_orders에는 여러 주문들의 날짜가 저장되어 있습니다. 주문 날짜는 'dd-mm-yyyy' 형식으로 저장되어 있습니다. 이 테이블에서 가장 이른 주문 날짜를 찾아보세요.
- 해결 예시 : SQL 문은
list_of_orders테이블에서order_date열의 값들 중 가장 이른 날짜를 찾는데 사용됩니다.STR_TO_DATE함수는 날짜를 문자열에서 날짜 형식으로 변환하는 데 사용되며, 이 경우 'dd-mm-yyyy' 형식으로 설정되어 있습니다. 이 문제의 목적은 날짜 데이터를 다루고, 함수를 사용하여 형식을 변환하는 방법을 이해하는 것입니다.
- 코드스니펫
SELECT MIN(STR_TO_DATE(order_date, '%d-%m-%Y')) First_Order FROM list_of_orders
문제 2: "카테고리별 총 매출 계산하기"
order_details 테이블에는 다양한 주문들의 세부 정보가 저장되어 있습니다. 각 주문은 특정 'Category'에 속하며, 각 주문의 'Amount'는 그 주문의 매출 금액을 나타냅니다. 이 테이블을 사용하여 각 카테고리별 총 매출 금액을 계산하세요.
- 해결 예시 : 이 SQL 문은
order_details테이블에서 각 'Category'별로 'Amount' 값을 합산하여 총 매출 금액을 계산하는 데 사용됩니다. 'GROUP BY' 절은 같은 'Category' 값을 가진 행들을 그룹화하여, 각 그룹에 대한 총합을 계산합니다. 이 문제의 목적은 데이터를 그룹화하고, 그룹별로 집계 함수를 적용하는 방법을 이해하는 것입니다.
- 코드스니펫
SELECT Category, SUM(Amount) total_amount FROM order_details GROUP BY Category
문제 3: "상위 5명의 고객별 총 구매액 찾기"
두 개의 테이블 order_details와 list_of_orders가 있습니다. order_details 테이블은 각 주문의 세부사항을 포함하고 있으며, list_of_orders 테이블은 주문에 대한 고객의 이름과 주문 ID를 포함합니다. 각 고객별로 총 구매액을 계산하여, 구매액이 가장 높은 상위 5명의 고객을 찾아보세요.
- 해결 예시 : 이 SQL 문은
order_details와list_of_orders테이블을JOIN하여, 각 고객별(CustomerName기준)로 구매액(Amount)의 합계(SUM)를 계산합니다. 결과는 구매액이 많은 순서(DESC)로 정렬되며, 상위 5명의 고객만을 보여주기 위해LIMIT 5를 사용합니다. 이 문제의 목적은 테이블 간의 조인, 그룹화, 집계 함수의 사용, 그리고 결과의 정렬 및 제한을 이해하는 것입니다.
- 코드스니펫
SELECT SUM(Amount) total_amount, CustomerName FROM order_details a JOIN list_of_orders b ON a.order_id=b.order_id GROUP BY CustomerName ORDER BY 1 DESC LIMIT 5
중급 문제
문제 4: "여러 주문을 한 고객들의 총 구매액 계산하기"
두 테이블 list_of_orders와 order_details가 있습니다. list_of_orders 테이블은 고객 이름과 주문 ID를, order_details 테이블은 각 주문의 상세 금액을 포함하고 있습니다. 이 테이블들을 사용하여, 3개 이상의 주문을 한 고객들의 이름, 총 주문 횟수, 그리고 그들의 총 구매액을 계산하세요.
- 코드스니펫
SELECT a.CustomerName, SUM(b.Amount) total_amount, COUNT(a.order_id) total_order FROM list_of_orders a INNER JOIN order_details b ON a.order_id=b.order_id GROUP BY a.CustomerName HAVING total_order>=3 #list_of_orders 테이블과 order_details 테이블을 order_id로 조인한 후, 고객 이름(Amount)과 주문 금액의 합계(total_amount), 총 주문 횟수(total_order)를 구한다. GROUP BY를 통해 고객 이름으로 집계 함수를 묶어 총 주문 횟수가 3개 이상인 고객만 조회한다.
문제 5: "평균 수량을 초과하는 주문 찾기"
order_details 테이블에는 다양한 주문들의 상세 정보가 저장되어 있습니다. 이 테이블의 각 행은 주문 ID(order_id), 카테고리(Category), 그리고 주문 수량(Quantity)을 포함합니다. 이 테이블을 사용하여 각 카테고리별 평균 주문 수량을 초과하는 모든 주문들을 찾아보세요.
- 코드스니펫
SELECT order_id, Category, Quantity FROM order_details a WHERE Quantity > ( SELECT AVG(quantity) FROM order_details WHERE Category = a.Category) #스칼라 서브쿼리를 활용하여 각 카테고리(Category) 별 평균주문수량(AVG(quantity))을 먼저 구하고, 각 카테고리별 평균 주문 수량을 초과(>)하는 주문(order_id)을 조회했다.
문제 6: "카테고리별 매출액 순위 및 누적합계 계산하기"
order_details 테이블에는 주문의 상세 정보가 포함되어 있습니다. 이 테이블은 주문 ID(order_id), 카테고리(Category), 그리고 주문의 금액(Amount)을 포함합니다. 이 테이블을 사용하여 각 카테고리 내에서 각 주문의 매출액 순위와 그 카테고리 내의 누적 매출액을 계산하세요.
- 코드스니펫
SELECT * FROM ( SELECT order_id, Amount, Category, DENSE_RANK() OVER (PARTITION BY Category ORDER BY Amount desc) rank_amount, SUM(Amount) OVER (PARTITION BY Category ORDER BY order_id) cumulative_sum FROM order_details ) a ORDER BY Category, rank_amount #각각의 주문, 매출액, 카테고리와 함께, DENS_RANK를 활용하여 카테고리 별 주문의 매출액 순위(rank_amount)를 구하고, 카테고리 별 누적 매출액(cumulative_sum)을 구한 후 서브쿼리로 묶었다. 해당 서브쿼리를 조회할 때, 카테고리 별, 매출액 순위 별로 정렬했다.
고급 문제
문제 7: "주별 매출 순위 및 평균 매출 목표 달성 여부 확인"
list_of_orders, order_details, 그리고 sales_target 세 개의 테이블이 있습니다. list_of_orders 테이블은 주문 ID(order_id)와 주문이 이루어진 주(State)를, order_details 테이블은 각 주문의 금액(Amount)과 이익(Profit)을, sales_target 테이블은 각 카테고리별 매출 목표(Target)를 포함합니다. 이 테이블들을 사용하여 각 주별로 주문의 총 금액과 이익을 계산하고, 각 주 내에서 주문의 매출 순위를 결정하세요. 또한, 각 주문의 총 금액이 해당 카테고리의 평균 매출 목표의 50%를 달성했는지 여부도 판단하세요.
각 주 별 주문의 총 금액 = SUM(b.Amount)
이익 = SUM(b.Profit)
각 주 내 주문의 매출 순위
- 코드스니펫
SELECT a.State, a.order_id, SUM(b.Amount) total_Amount, SUM(b.Profit) total_Profit, RANK() OVER (PARTITION BY a.State ORDER BY SUM(b.Amount) DESC) AS SalesRank, CASE WHEN SUM(d.Amount) >= 0.5*c.Target THEN 'Achieved' ELSE 'Not Achieved' END as Achievement_Status FROM list_of_orders a inner join order_details b on a.order_id=b.order_id inner join sales_target c on b.Category=c.Category GROUP BY a.State, a.order_id, c.Target SELECT lo.order_id, lo.State, od.Category, SUM(od.Amount) AS TotalAmount, SUM(od.Profit) AS TotalProfit, RANK() OVER (PARTITION BY lo.State ORDER BY SUM(od.Amount) DESC) AS SalesRank, CASE WHEN SUM(od.Amount) >= st.Target * 0.5 THEN 'Yes' ELSE 'No' END AS AchievedTarget FROM list_of_orders lo JOIN order_details od ON lo.order_id = od.order_id JOIN sales_target st ON od.Category = st.Category GROUP BY lo.order_id, lo.State, od.Category, st.Target WITH Average_Targets AS ( SELECT Category, AVG(Target) AS Avg_Target FROM sales_target GROUP BY Category ) SELECT lo.order_id, lo.State, SUM(od.Amount) AS Total_Amount, SUM(od.Profit) AS Total_Profit, RANK() OVER (PARTITION BY lo.State ORDER BY SUM(od.Amount) DESC) AS State_Rank, CASE WHEN SUM(od.Amount) >= at.Avg_Target / 2 THEN 'Met 50% Target' ELSE 'Below 50% Target' END AS Target_Comparison FROM list_of_orders AS lo JOIN order_details AS od ON lo.order_id = od.order_id JOIN Average_Targets AS at ON od.Category = at.Category GROUP BY lo.order_id, lo.State, at.Avg_Target; #내가 작성한 답 WITH Average_Targets AS ( SELECT Category, AVG(Target) AS Avg_Target FROM sales_target GROUP BY Category ) SELECT a.`Order ID`, a.State, SUM(b.Amount) AS Total_Amount, SUM(b.Profit) AS Total_Profit, RANK() OVER (PARTITION BY a.State ORDER BY SUM(b.Amount) DESC) AS State_Rank, CASE WHEN SUM(b.Amount) >= at.Avg_Target / 2 THEN 'Met 50% Target' ELSE 'Below 50% Target' END AS Target_Comparison FROM list_of_orders a JOIN order_details b ON a.`Order ID` = b.`Order ID` JOIN Average_Targets AS at ON b.Category = at.Category GROUP BY a.`Order ID`, a.State, at.Avg_Target; #with절 단일문 범위 내에 존재하는 명명된 임시 결과의 집합 나중에 해당 문 내에서 여러번 참조 가능 데이터 베이스에 저장되는 테이블은 아니다 # WITH 절을 활용해 카테고리 별 평균 목표 매출(Avg_Target)과 카테고리(Category)가 있는 테이블(Average_Target)을 먼저 만든다. list of orders 테이블과 order details 테이블을 JOIN 한 후, 각 주문(order_id)과 주(State), 매출액 합계(Total_Amount)와 이익 합계(Total_Profit)와 함께 RANK OVER를 활용하여 주 내의 매출 순위(State_Rank)를 구하고, CASE WHEN을 활용하여 주문의 총 금액이 해당 카테고리의 평균 매출 목표의 50%를 달성했다면 'Met 50% Target', 달성하지 못했다면 'Below 50% Target'으로 표기해 목표 매출 달성 여부(Target_Comparison)를 확인한다. 카테고리 별 평균 목표 매출액이 필요하므로 앞서 JOIN으로 묶인 두 테이블을 WITH 절로 만든 가상 테이블 Average_Target과 한 번 더 묶었다.
문제 8: 고객 평생 가치(CLV) 분석
당신은 list_of_orders와 order_details 두 테이블을 가지고 있으며, 이를 통해 고객별 평생 가치를 분석하고자 합니다. list_of_orders 테이블은 고객 이름(CustomerName), 주문 날짜(order_date), 주문 ID를 포함하고, order_details 테이블은 각 주문의 금액(Amount)과 수익(Profit)을 포함합니다. 고객별로 총 구매액, 총 주문 횟수, 주문 간 평균 일수를 계산하고, 이를 기반으로 고객의 평생 가치(CLV)를 분석하세요.
- 코드스니펫
각 주 별 주문의 총 금액 = SUM(b.Amount)
이익 = SUM(b.Profit)
각 주 내 주문의 매출 순위
- 코드스니펫
( SELECT lo.CustomerName, COUNT(DISTINCT lo.`Order ID`) AS Total_Orders, SUM(od.Amount) As Total_Amount, MAX(lo.`Order Date`) AS Last_Order_Date, LAG(lo.'Order Date') OVER (PARTITION BY lo.CustomerName ORDER BY lo.'Order date') AS PrevOrderDate FROM List_of_Orders lo JOIN Order_Details od ON lo.`Order ID` = od.`Order ID` GROUP BY lo.CustomerName ) a #내가 작성한 답 WITH Customer_Summary AS ( SELECT lo.CustomerName, COUNT(DISTINCT lo.order_id) AS Total_Orders, SUM(od.Amount) AS Total_Amount, MAX(lo.order_date) AS Last_Order_Date, LAG(lo.order_date) OVER (PARTITION BY lo.CustomerName ORDER BY lo.order_date) AS Prev_Order_Date FROM List_of_Orders lo JOIN Order_Details od ON lo.order_id = od.order_id GROUP BY lo.CustomerName ) SELECT CustomerName, Total_Orders, Total_Amount, Last_Order_Date, AVG(DATEDIFF(order_date, Prev_Order_Date)) AS AVG_Days_Between_Orders, Total_Amount / AVG(DATEDIFF(order_date, Prev_Order_Date)) AS CLV FROM Customer_Summary cs JOIN List_Of_Orders lo on cs.CustomerName = lo.CustomerName GROUP BY CustomerName, Total_Orders, Total_Amount, Last_Order_Date, Prev_Order_Date
