24/01/02

sql 코드카타

문제93. 1075. Project Employees I

join함수 : (inner) join, left join

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| project_id  | int     |
| employee_id | int     |
+-------------+---------+
(project_id, employee_id) is the primary key of this table.
employee_id is a foreign key to Employee table.
Each row of this table indicates that the employee with employee_id is working on the project with project_id.

 

Table: Employee

+------------------+---------+
| Column Name      | Type    |
+------------------+---------+
| employee_id      | int     |
| name             | varchar |
| experience_years | int     |
+------------------+---------+
employee_id is the primary key of this table. It's guaranteed that experience_years is not NULL.
Each row of this table contains information about one employee.

 

Write an SQL query that reports the average experience years of all the employees for each project, rounded to 2 digits.

Return the result table in any order.

The query result format is in the following example.

select p.project_id,
       round(avg(e.experience_years), 2) average_years
from project p left join employee e using(employee_id)
group by 1

select p.project_id,
       round(avg(e.experience_years), 2) average_years
from project p left join employee e using(employee_id)
group by 1

 

아직 이런 애매한?상황에서 left join과 inner join이 무슨 차이인지 헷갈려서 기록. 풀어보니 위에 두 개 모두 답이다. 아래에 나오는 고급문제 7번도 left join, inner join 둘 다 결과가 같이 나와서 더 헷갈림. 더 공부해둬야겠다는 의미로 기록.

 

---

연휴 개인과제

 

중급 문제

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

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

select 고객명,
	   count(주문ID) 총주문횟수,
	   sum(주문당구매액) 총구매금액
from 
(
select o.state 주,
       o.CustomerName 고객명,
	   o.`Order ID` 주문ID,
	   sum(d.amount) 주문당구매액
from list_of_orders o join order_details d using(`order id`)
group by 1, 2, 3
order by 1 asc
)ㅁ
group by 1
having count(주문ID) >=3
order by 1, 2

 

 

문제 5: "평균 수량을 초과하는 주문 찾기"

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

select order_id,
	   sum(quantity) 주문수량,
	   max(카테고리별평균주문수량),
	   if(sum(quantity)>max(카테고리별평균주문수량), '초과', '초과X') 카테고리평균초과여부
from
(
select category,
       `order id` order_id,
        quantity,
		avg(quantity) over (partition by category) 카테고리별평균주문수량
from order_details
order by 2
)ㅁ
group by 1

 

 

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

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

select order_id,
	   category,
	   주문당매출액,
	   rank() over (partition by category order by 주문당매출액 desc) 매출순위,
	   sum(주문당매출액) over (partition by category order by 주문당매출액 desc) 누적매출액
from
(
select category,
	   `order id` order_id,
	   sum(amount) 주문당매출액
from order_details
group by 1, 2
)ㅁ
order by 2, 4

 

 

고급 문제

문제 7: "주별 매출 순위 및 평균 매출 목표 달성 여부 확인"

list_of_orders, order_details, 그리고 sales_target 세 개의 테이블이 있습니다. list_of_orders 테이블은 주문 ID(order_id)와 주문이 이루어진 주(State)를, order_details 테이블은 각 주문의 금액(Amount)과 이익(Profit)을, sales_target 테이블은 각 카테고리별 매출 목표(Target)를 포함합니다. 이 테이블들을 사용하여 각 주별로 주문의 총 금액과 이익을 계산하고, 각 주 내에서 주문의 매출 순위를 결정하세요. 또한, 각 주문의 총 금액이 해당 카테고리의 평균 매출 목표의 50%를 달성했는지 여부도 판단하세요.

select state,
	   customername,
       order_id,
       category,
	   max(주별총금액) 주별매출금액,
	   max(주별총이익) 주별총이익,
	   max(주내매출순위) 주내매출순위,
	   max(각주문카테고리별매출) 각주문카테고리별매출,
	   if(max(각주문카테고리별매출)>=0.5*max(target), '성공', '실패') '매출50%달성여부'
from
(
select o.customername customername,
	   o.`Order ID` order_id,
	   o.State state,
	   d.amount amount,
	   d.category category,
	   t.target target,
       sum(d.amount) over (partition by o.state) 주별총금액, 
       sum(d.profit) over (partition by o.state) 주별총이익,
       dense_rank() over (partition by o.state order by d.amount desc) 주내매출순위,
       sum(d.amount) over (partition by d.`order id`, d.category) 각주문카테고리별매출
from list_of_orders o join order_details d on o.`Order ID` =d.`Order ID` join sales_target t on d.category = t.category
where date_format(str_to_date(o.`order date`, '%d-%m-%Y'), '%Y-%m') = DATE_FORMAT(STR_TO_DATE(CONCAT('01-', month_of_order), '%d-%b-%Y'), '%Y-%m')
)a
group by 1, 2, 3, 4
order by 9, 1, 7

 

 

문제 8: 고객 평생 가치(CLV) 분석

당신은 list_of_orders와 order_details 두 테이블을 가지고 있으며, 이를 통해 고객별 평생 가치를 분석하고자 합니다. list_of_orders 테이블은 고객 이름(CustomerName), 주문 날짜(order_date), 주문 ID를 포함하고, order_details 테이블은 각 주문의 금액(Amount)과 수익(Profit)을 포함합니다. 고객별로 총 구매액, 총 주문 횟수, 주문 간 평균 일수를 계산하고, 이를 기반으로 고객의 평생 가치를 분석하세요.

 

select 주,
	   고객명,
	   총주문액,
	   총주문횟수,
	   주문간평균일수,
	   총주문액/총주문횟수*if(주문간평균일수 is null, 1/365, 주문간평균일수) LTV
from
(
select 주,
	   고객명,
	   sum(주문액) 총주문액,
	   count(distinct 주문번호) 총주문횟수,
	   if(avg(datediff(날짜, 이전날짜))=0, null, avg(datediff(날짜, 이전날짜))) 주문간평균일수
from 
(
select o.`order id` 주문번호,
       o.state 주,
	   o.CustomerName 고객명,
	   d.amount 주문액,
	   str_to_date(o.`order date`, '%d-%m-%Y') 날짜,
	   str_to_date(lag(o.`Order Date`, 1) over (partition by o.customername, o.state order by  o.`order id`, o.`order date`), '%d-%m-%Y') 이전날짜
from list_of_orders o join order_details d using(`order id`)
order by 2, 3, 1, 6
)a
group by 1, 2
order by 1, 2
)ㅠ

 

 

---

TMI

-거진 하루를 개인과제 하면서 다 보냈다. 연휴기간의 여행+술+퍼질러짐으로 인해 연휴엔 초급과 중급문제만 대충 쭉 풀었다가 컨디션 꽝 돼서 오늘 낮동안 내내 고급문제 푼다고 날렸는데....ㅎ(나 하루종일 뭐한거지...)풀다보니 중급문제를 잘못풀었어서 다시 푸느라 저녁 특강 시간 전까지 거의 통으로 하루를 날렸다. 그래도 나쁘지 않게 푼 것 같아 다행.