초급 문제
문제 1: "최소 주문 날짜 찾기"
주어진 테이블 'list_of_orders'에는 여러 주문들의 날짜가 저장되어 있습니다. 주문 날짜는 'dd-mm-yyyy' 형식으로 저장되어 있습니다. 이 테이블에서 가장 이른 주문 날짜를 찾아보세요.
▼ 해설 예시
SQL 문은 'list_of_orders' 테이블에서 'order_date' 열의 값들 중 가장 이른 날짜를 찾는데 사용됩니다. 'STR_TO_DATE' 함수는 날짜를 문자열에서 날짜 형식으로 변환하는 데 사용되며, 이 경우 'dd-mm-yyyy' 형식으로 설정되어 있습니다. 이 문제의 목적은 날짜 데이터를 다루고, 함수를 사용하여 형식을 변환하는 방법을 이해하는 것입니다.
▶ 제출안 (X)
select max(str_to_date(order_date, '%d-%m-%Y')) latest_day
from list_of_orders
▶ 정답
SELECT MIN(order_date)
FROM list_of_orders
WHERE STR_TO_DATE(order_date, '%d-%m-%Y');
▶ 정답 풀이를 참조한 내 제출안 정리
- 문제에서 제시한 ‘가장 이른 날짜’를 '가장 최근 날짜' 잘못 이해해서 MAX 함수 잘못 적용해 오답 (이른 = early)
└ 문제 제목도 '[최소] 주문 날짜' 구하기 였음 > 제발 문제를 잘 읽자..
- SELECT 절을 길게 쓰면 가독성이 떨어지므로, 조건은 가급적 WHERE 절에 넣는 것이 좋음
- 쿼리 마무리하면 ; 로 끝났음을 표시해줘야
문제 2: "카테고리별 총 매출 계산하기"
'order_details' 테이블에는 다양한 주문들의 세부 정보가 저장되어 있습니다. 각 주문은 특정 'Category'에 속하며, 각 주문의 'Amount'는 그 주문의 매출 금액을 나타냅니다. 이 테이블을 사용하여 각 카테고리별 총 매출 금액을 계산하세요.
▼ 해설 예시
이 SQL 문은 'order_details' 테이블에서 각 'Category'별로 'Amount' 값을 합산하여 총 매출 금액을 계산하는 데 사용됩니다. 'GROUP BY' 절은 같은 'Category' 값을 가진 행들을 그룹화하여, 각 그룹에 대한 총합을 계산합니다. 이 문제의 목적은 데이터를 그룹화하고, 그룹별로 집계 함수를 적용하는 방법을 이해하는 것입니다.
▶ 제출안 (O)
select Category, sum(Amount) Amount
from order_details od
group by 1
order by 2 desc
▶ 정답
SELECT Category, SUM(Amount) AS Total_Sales
FROM order_details
GROUP BY Category;
▶ 정답 풀이를 참조한 내 제출안 정리
- grroup by로 묶을 때 숫자로 넣으면 편하지만, 가독성을 위해 칼럼명을 넣어주는 버릇을 들이는 것이 좋을 듯
문제 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 l.CustomerName , sum(o.Amount) Amount
from list_of_orders l
inner join order_details o on l.order_id =o.order_id
group by 1
order by sum(o.Amount) desc
limit 5
▶ 정답
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;
중급 문제
문제 4: "여러 주문을 한 고객들의 총 구매액 계산하기"
두 테이블 'list_of_orders'와 'order_details'가 있습니다. list_of_orders 테이블은 고객 이름과 주문 ID를, order_details 테이블은 각 주문의 상세 금액을 포함하고 있습니다. 이 테이블들을 사용하여, 3개 이상의 주문을 한 고객들의 이름, 총 주문 횟수, 그리고 그들의 총 구매액을 계산하세요.
▶ 제출안 (X)
select l.CustomerName , sum(o.Quantity) total_quantity , sum(o.Amount) total_amount
from list_of_orders l
left join order_details o on l.order_id = o.order_id
group by 1
having sum(o.Quantity) >= 3
order by 2 desc, 3 desc(해설)
이 SQL 문은 먼저 list_of_orders과 order_details 테이블을 join합니다. 고객이름별(CustomerName)로 그룹핑(group by)하고, 주문횟수(Quantity)의 합계(sum)와 구매액(Amount)의 합계(sum) 구합니다. 이때 having 절에 고객 중에 총 주문건수가 3건 이상(having sum(o.Quantity) >= 3)인 고객 조건을 필터링 합니다. 이 문제의 목적은 테이블 간의 조인과, 그룹화, 집계 함수의 사용, 그리고 having 절을 활용해 그룹핑 조건을 제한하는 것입니다.
▶ 정답
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개 이상의 주문]을 잘못 해석하여 오답
└ 전체 주문 수량(quantity)이 아니라, 주문 자체를 몇 번 했는지 즉 distinct order_id의 개수 계산 필요
문제 5: "평균 수량을 초과하는 주문 찾기"
'order_details' 테이블에는 다양한 주문들의 상세 정보가 저장되어 있습니다. 이 테이블의 각 행은 주문 ID('order_id'), 카테고리('Category'), 그리고 주문 수량('Quantity')을 포함합니다. 이 테이블을 사용하여 각 카테고리별 평균 주문 수량을 초과하는 모든 주문들을 찾아보세요.
▶ 제출안 (△)
#1 서브쿼리
select order_id
from order_details o
left join (select Category, avg(Quantity) avg_Quantity
from order_details
group by 1) a on o.Category=a.Category
where (Quantity -round(avg_Quantity,0)) >= 0
#2 서브쿼리+윈도우 함수
select order_id
from (select *, avg(Quantity) over(partition by Category) avg_Quantity
from order_details)a
where (Quantity-avg_Quantity) >= 0(해설)
이 SQL 문은 먼저 order_details에서 카테고리별(group by Category) 평균값(avg)을 구합니다. order_details 테이블과 카테고리별 평균값 쿼리문을 카테고리 기준으로 조인(JOIN)한 뒤, WHERE 절에서 각 ORDER_ID별 주문 수량이 평균값을 초과하는 값을 제한하고, 최종 order_id 값을 추출합니다.
혹은 avg 윈도우 함수를 이용하여 카테고리별 평균값 칼럼 추가한 테이블을 만들고, 이를 from 절 서브쿼리로 넣어 where 절에 각 주문 수량이 카테고리별 평균값을 초과하는 값을 제한하고 order_id 추출합니다.
이 문제의 목적은 카테고리별 평균값 구한 뒤, 서브쿼리를 활용해 개별 행의 주문 수량 값과 평균값을 비교해 원하는 조건을 추출하는 것입니다.
▶ 정답
SELECT
order_id,
Category,
Quantity
FROM
order_details
WHERE
Quantity > (
SELECT AVG(Quantity)
FROM order_details AS od2
WHERE od2.Category = order_details.Category
);
▶ 정답 풀이를 참조한 내 제출안 정리
- SELCT 절에 카테고리와 수량 정보 추가 출력 필요
- 전체 평균 수량 값은 WHERE절에 서브쿼리로 넣고, 서브쿼리 안 일치하는 카테고리값도 WHERE 절에 조건으로 넣는 것이 가능
└ 웬만한 조건은 WHERE 절에 잘 넣어서 출력하는 것이 최적화된 쿼리라고 함
└ 셀프 조인하지 않고, where 절 안 서브쿼리에서 where을 통해 카테고리가 일치하는 값을 찾는 부분이 어려움(이게 가능하다니?)
└ [where o1.Category =o2.Category] 이 부분이 그룹핑 역할을 해서 카테고리별 평균값 도출이 가능한듯
- 평균보다 큰 값은 [기준값 > 평균값] 으로 구하면 되는데, [(기준값 - 평균값) >=0]으로 다소 복잡하게 구현
문제 6: "카테고리별 매출액 순위 및 누적합계 계산하기"
'order_details' 테이블에는 주문의 상세 정보가 포함되어 있습니다. 이 테이블은 주문 ID('order_id'), 카테고리('Category'), 그리고 주문의 금액('Amount')을 포함합니다. 이 테이블을 사용하여 각 카테고리 내에서 각 주문의 매출액 순위와 그 카테고리 내의 누적 매출액을 계산하세요.
▶ 제출안 (O)
select order_id,
Amount,
Category,
rank() over(partition by Category order by Amount desc) ranking,
sum(Amount) over(partition by Category order by Category, Amount desc) cum_sum
from order_details od(해설)
이 SQL 문은 order_details 테이블에서 윈도우 함수를 활용해 카테고리별 매출액 순위(rank)와 카테고리 내 누적 합(sum)을 구합니다. 각 윈도우 함수에서 partition by로 Category 칼럼을 그룹핑합니다. rank 함수에서는 order by 매출액 내림차순으로 매출액이 높은 순으로 순위를 지정하고, sum 함수에서는 order by에 카테고리(오름차순)와 매출액(내림차순) 정렬 조건을 모두 추가하여 순차적으로 합계가 발생하도록 합니다. 마지막으로 각 주문의 매출액 순위와 그 카테고리 내의 누적 매출액을 잘 알 수 있도록 주문id, 매출액, 카테고리, 카테고리별 매출액 순위, 카테고리별 누적 매출액을 추출합니다. 이 문제의 목적은 윈도우 함수를 활용해 행과 행 간의 관계를 정의하여 순위와 누적합을 구하는 것입니다.
▶ 정답
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;
▶ 정답 풀이를 참조한 내 제출안 정리
- rank 함수에 order by는 amount 값만 해도 충분
고급 문제
문제 7. "주별 매출 순위 및 평균 매출 목표 달성 여부 확인"
'list_of_orders', 'order_details', 그리고 'sales_target' 세 개의 테이블이 있습니다. 'list_of_orders' 테이블은 주문 ID('order_id')와 주문이 이루어진 주('State')를, 'order_details' 테이블은 각 주문의 금액('Amount')과 이익('Profit')을, 'sales_target' 테이블은 각 카테고리별 매출 목표('Target')를 포함합니다. 이 테이블들을 사용하여 각 주별로 주문의 총 금액과 이익을 계산하고, 각 주 내에서 주문의 매출 순위를 결정하세요. 또한, 각 주문의 총 금액이 해당 카테고리의 평균 매출 목표의 50%를 달성했는지 여부도 판단하세요.
▶ 제출안 (X)
select A.State,
A.order_id,
sum(A.Amount) over (partition by A.State) state_Amount,
sum(A.Profit) over (partition by A.State) state_Profit,
rank() over (partition by A.State order by (A.Amount+A.Profit) desc)revenue_ranking,
if(A.Amount >= B.avg_Target_50per, '달성', '미달성') achieve
from (select l.State , o.Category , l.order_id, sum(o.Amount)Amount, sum(o.Profit) Profit
from list_of_orders l
inner join order_details o on l.order_id = o.order_id
group by 1,2,3) A
left join (select Category , (avg(Target))/2 as avg_Target_50per
from sales_target st
group by 1) B on A.Category = B.Category(해설)
이 SQL 문은 list_of_orders, order_details 테이블을 JOIN하여 주(State) - 카테고리(Category) - 주문(order_id)별 총 주문액(Amount)과 총 이익(Profit)을 먼저 추출합니다. 1차 JOIN한 테이블은 서브쿼리로 넣어, sales_target 테이블과 2차로 JOIN해 카테고리별 평균 매출 목표(Target)의 50%에 해당하는 값을 계산합니다. 마지막으로 모두 조인된 테이블에서 윈도우 함수 중 SUM으로 주별 총 주문금액과 총 이익을 계산합니다. 이어 RANK 함수를 활용해 주 내의 각 주문 금액 순위를 구하고, IF함수로 평균 매출 목표 기준 달성 여부에 따라 값을 달성/미달성으로 표기합니다. 이 문제의 목적은 3가지 테이블의 적절한 JOIN, 윈도우 함수를 통한 범주 내 합계와 순위 구하기, IF 함수로 조건에 맞는 칼럼을 추가하는 것입니다.
▶ 정답
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;
▶ 정답 풀이를 참조한 내 제출안 정리
- 문제에서 '각 주별로 주문의 총 금액과 이익을 계산'하라는 부분을 각 주의 '전체 주문 금액과 이익'을 일컫는 것으로 해석(A State의 총 주문금액과 총 이익)하였으나, 정답 쿼리 확인 시 각 주에 속한 order id별 총금액을 의미하였음(주-주문별)
- 정답 쿼리에서 group by에 [at.Avg_Target]이 없으면 오류가 나는가?
└ Avg_Trarget는 with절 테이블에서 나온 칼럼으로, Category 그룹핑을 통해 탄생한 값이라 그룹핑 값 없으면 출력 불가
└ 쿼리 마지막 group by에 [at.Avg_Target] 대신 [at.Category]를 넣어도 결과 도출 가능!
└ with절에 그룹핑을 적용한 값을 사용하면 최종 쿼리에도 해당 값과 관계가 있는 칼럼을 그룹바이에 지정 필요!!
문제 8: 고객 평생 가치(CLV) 분석
당신은 'list_of_orders'와 'order_details' 두 테이블을 가지고 있으며, 이를 통해 고객별 평생 가치를 분석하고자 합니다. 'list_of_orders' 테이블은 고객 이름('CustomerName'), 주문 날짜('order_date'), 주문 ID를 포함하고, 'order_details' 테이블은 각 주문의 금액('Amount')과 수익('Profit')을 포함합니다. 고객별로 총 구매액, 총 주문 횟수, 주문 간 평균 일수를 계산하고, 이를 기반으로 고객의 평생 가치를 분석하세요.
▶ 제출안 (??)
with table1 as (select l.CustomerName,
str_to_date(l.order_date, '%d-%m-%Y') order_date,
sum(o.Amount) amount , sum(o.Quantity) quantity, sum(o.Profit) profit
from list_of_orders l
left join order_details o on l.order_id = o.order_id
group by 2, 1
order by 1)
select CustomerName,
total_amount,
total_quantity, avg_period,
ROUND((((unit_cost*avg_quantity)+avg_profit)*avg_period),0) CLV
from (
select A.CustomerName,
avg(A.amount) avg_amount,
avg(A.Quantity) avg_quantity,
avg(A.profit) avg_profit,
(SUM(A.amount)/SUM(A.Quantity)) unit_cost , SUM(A.amount) total_amount,
sum(A.Quantity) total_quantity,
round(avg(if(datediff(A.order_date,B.order_date)=0, 1,(datediff(A.order_date,B.order_date)+1))),0) avg_period
from table1 A
cross join table1 B on A.CustomerName=B.CustomerName
where A.order_date >= B.order_date
group by 1
) table2
group by 1
order by 5 desc, 2 desc, 3 desc(해설)
이 SQL 문은 list_of_orders, order_details 테이블을 JOIN하여, 고객명(CustomerName), 주문날짜(order_date), 총 주문액(amount), 총 주문수(quantity), 총이익(profit)를 WITH 절로 묶어 table1 테이블을 새로 생성합니다. table1은 셀프로 CROSS JION 하여 각 주문날짜 차이의 평균과 객단가(SUM(A.amount)/SUM(A.Quantity)) 등을 구합니다. 마지막으로, 고객별 총 구매액, 총 주문 횟수, 주문 간 평균 일수를 계산한 뒤, 고객별 (((객단가*평균 주문건수)+수익)*주문간 평균 일수)으로 고객의 평생 가치(CLV)를 구합니다. 이 문제의 목적은 각 테이블에서 필요한 수치를 계산식으로 뽑은 뒤, 고민한 고객 평생 가치(CLV) 계산식에 맞춰 최종 값을 도출하는 것입니다.
▶ 정답
WITH Customer_Order_Details AS (
SELECT
lo.CustomerName,
STR_TO_DATE(lo.order_date, '%d-%m-%Y') AS order_date,
od.Amount,
ROW_NUMBER() OVER (PARTITION BY lo.CustomerName ORDER BY lo.order_date) AS rn
FROM
list_of_orders AS lo
JOIN
order_details AS od ON lo.order_id = od.order_id
),
Order_Intervals AS (
SELECT
CustomerName,
DATEDIFF(order_date, LAG(order_date) OVER (PARTITION BY CustomerName ORDER BY order_date)) AS days_between_orders
FROM
Customer_Order_Details
WHERE
rn > 1
),
Customer_Stats AS (
SELECT
cod.CustomerName,
SUM(cod.Amount) AS Total_Amount,
COUNT(DISTINCT cod.order_date) AS Total_Orders,
AVG(oi.days_between_orders) AS Avg_Days_Between_Orders
FROM
Customer_Order_Details AS cod
LEFT JOIN
Order_Intervals AS oi ON cod.CustomerName = oi.CustomerName
GROUP BY
cod.CustomerName
)
SELECT
cs.CustomerName,
cs.Total_Amount,
cs.Total_Orders,
cs.Avg_Days_Between_Orders,
cs.Total_Amount / cs.Total_Orders AS Avg_Order_Value,
cs.Total_Orders / NULLIF(cs.Avg_Days_Between_Orders, 0) AS Purchase_Frequency,
(cs.Total_Amount / cs.Total_Orders) * (cs.Total_Orders / NULLIF(cs.Avg_Days_Between_Orders, 0)) * 1 AS Estimated_CLV -- 1년 기준 CLV
FROM
Customer_Stats AS cs;
▶ 정답 풀이를 참조한 내 제출안 정리
- CLV 값 연산에 필요한 값은 WITH절이나 VIEW로 빼서 정리하면 메인 쿼리가 깔끔하고 간결해짐
└ 저장할 때 임시테이블 명은 내용을 포괄할 수 있는 것으로 지정하는 것이 좋음 (다른 사람도 알아볼 수 있도록)
- 정답 분석
└ WITH절 ① Customer_Order_Details: 유저이름, 주문날짜, 금액, 각 이름별로 인덱스 추가(row_number)
② Order_Intervals: ①Customer_Order_Details을 가져와서 주문수가 2번 이상인 유저이름, 날짜 간격*
* 날짜 간격은 DATEDIFF 함수 활용 > 주문일자 & 이전행(LAG함수)의 주문일자 빼기)
: 이전 행이 없다면(주문이 1번이라면) NULL, 날짜가 하나라면 0으로 출력됨
③ Customer_Stats: ① + ② 조인하여 유저별 유저명, 총금액, unique 주문일자수, 주문간 평균일수
└ 메인 쿼리: WITH 절 ③을 불러와서 필요한 값과 수식에 맞는 CLV 값 도출
· 평균 주문 가치: 유저별 총 주문금액 / 유저별 주문건수 #한 번 주문할 때 주문하는 금액
· 주문 빈도: 유저별 주문 수 / 유저별 주문간 평균 일수 #며칠에 한 번씩 주문하는지
· CLV(1년 기준): 평균 주문 가치 * 주문 빈도
- NULLIF(expression1, expression2) 함수 활용법
└ expression1과 expression2가 같으면 결과는 NULL, 다르면 expression1의 값 반환
└ 사용한 이유: Avg_Days_Between_Orders가 0이 되어, 분모로 나눠질 때 오류 발생하므로 모두 NULL 처리
[문제 풀이로 새로 알 게 된 사실]
- Q5. WHERE 절에 넣은 서브쿼리에 WHERE 절을 또 적용할 수 있음 (조인과 비슷한 효과가 있는듯)
- Q7. WITH절에 GROUP BY된 칼럼을 사용할 때는 메인 쿼리에도 GROUP BY 해줘야 하는듯
- Q8. WITH 절 작성 순서대로 앞서 적은 임시 테이블을 참조하여 신규 테이블 생성이 가능함
- Q8. 나누기나 곱하기 등 연산을 할 때 0이나 NULL 값을 고려해 NULLIF를 함수 적용 여부 검토가 필요함
- (TIP) SQL에서 띄어쓰기가 적용된 텍스트는 따옴표가 아니라 `어퍼스트로피로 감싸주기
(cf) 기초 세팅 사항
[기초 셋팅]
데이터 IMPORT : csv -> table 이름 변경까지
- list of Orders.csv → list_of_orders
- Order Details.csv → order_details
- Sales target.csv → sales_tartget
[ER Digram 활용]
- list_of_orders (order_id) → order_details (order_id) 연결
- order_details (Category) → salese_target(Category) 연결
[SQL을 진행하기 위한 DEFAULT QUERY (컬럼 변경)]
ALTER TABLE order_details CHANGE `Sub-Category` sub_category VARCHAR(25);
ALTER TABLE order_details CHANGE `Order ID` order_id VARCHAR(25);
ALTER TABLE list_of_orders CHANGE `Order Date` order_date VARCHAR(25);
ALTER TABLE list_of_orders CHANGE `Order ID` order_id VARCHAR(25);
ALTER TABLE sales_target CHANGE `Month of Order Date` month_of_order VARCHAR(25);
'문제풀이' 카테고리의 다른 글
| 태블로: 개인 과제 풀이 및 해설 정리 (0) | 2024.03.15 |
|---|---|
| 머신러닝: 개인 과제 풀이 및 해설 정리 (1) | 2024.02.05 |
| 데이터 분석 기초: 개인 과제 풀이 및 해설 정리 (1) | 2024.01.29 |
| 파이썬: 개인 과제(필수) 풀이 및 해설 정리 (1) | 2024.01.29 |
