[TIL] SQL 달리기 과제

Lv1. 데이터 속 김서방 찾기

Q. 여러분들은 스파르타코딩클럽의 분석가로 취직했습니다. DBeaver를 테스트 해볼 겸 “김”씨로 시작하는 이용자들 수를 세어 보기로 했습니다.

정답1) 'name like' 문법 활용

select count(1) name_cnt
from users
where name like "김%"

정답2) substring 활용

select count(1) name_cnt
from users
where SUBSTR(name,1,1) ='김'

Lv2. 날짜별 획득포인트 조회하기

Q. 이번에는 이용자들이 잘 활동하고 있는지 보고자 합니다. 포인트가 많을수록 활동을 잘하고 있다고 생각 할 수 있습니다. 날짜별로 획득한 포인트가 점점 늘어나는지 줄어드는지 확인해 봅시다.

select SUBSTR(created_at,1,10) created_at, 
	   round(avg(point))average_points 
from point_users 
group by 1

Lv3. 이용자의 포인트 조회하기

Q. 이번에는 이용자들 별로 획득한 포인트를 학생들에게 이메일로 보내려고 합니다. 이를 위한 자료를 가공해봅시다. 특히 users 테이블에는 있으나 point_users 에는 없는 유저가 있어요. 이 유저들의 경우 point를 0으로 처리합시다.

select u.user_id, u.email,
	   COALESCE(pu.point,0) as point
from users u left join point_users pu on u.user_id = pu.user_id 
order by 3 desc

Lv4. 단골 고객님 찾기

Q1. 고객별로 주문 건수와 총 주문 금액을 조회하는 SQL 쿼리를 작성해주세요.
-> 출력 결과에는 고객 이름, 주문 건수, 총 주문 금액이 포함되어야 합니다. 단, 주문을 한 적이 없는 고객도 결과에 포함되어야 합니다.

SELECT p2.CustomerName, 
	   count(OrderID) OrderCount,
	   sum(p1.TotalAmount) TotalSpent
from 
practice1 p1 left join practice2 p2 on p1.CustomerID = p2.CustomerID 
group by 1 

Q2. 나라별로 총 주문 금액이 가장 높은 고객의 이름과 그 고객의 총 주문 금액을 조회하는 SQL 쿼리를 작성해주세요.

select Country,
	   CustomerName as Top_customer,
	   Total_spent as Top_Spent
from 
(
select country,
	   CustomerName,
	   sum(TotalAmount) Total_spent,
	   rank() over(partition by country order by sum(TotalAmount) desc) ranking
from
(
SELECT p2.Country,
	   p2.CustomerName, 
	   p1.TotalAmount
from practice1 p1 left join practice2 p2 on p1.CustomerID = p2.CustomerID
) a
group by 1,2
) b
where ranking = 1
order by 1 desc

Lv4. 가장 높은 월급을 받는 직원은?

Q1. 각 직원의 이름, 부서, 월급, 그리고 그 직원이 속한 부서에서 가장 높은 월급을 받고 있는 직원의 이름과 월급을 조회하는 SQL 쿼리를 작성해주세요.

select p3.Name, p3.Department, p3.Salary, t.Top_Earner, t.Top_Salary
from practice3 p3 inner join 
(
select Department,
	   Name as Top_Earner,
	   Salary as Top_Salary
from
(
select Department,
	   Name,
	   Salary,
	   rank() over(partition by Department order by Salary desc) ranking
from practice3 p3
) a
where ranking = 1
) t
on p3.Department = t.Department

Q2. 부서별로 평균 월급이 가장 높은 부서의 이름과 해당 부서의 평균 월급을 조회하는 SQL 쿼리를 작성해주세요.

select Department, 
	   avg(salary)
from practice3 p3 
group by 1
order by 2 desc
limit 1 

Lv5. 가장 많이 팔린 품목은?

Q1. 각 고객이 구매한 모든 제품의 총 금액을 계산하고, 고객 이름, 총 구매 금액, 주문 수를 출력하는 SQL 쿼리를 작성해주세요.

select p6.CustomerName,
	   sum(quantity*price) as TotalAmount,
	   count(orderID) as OrderCount
from 
(
select p5.OrderID,
	   p5.CustomerID,
	   p4.ProductName,
	   p4.Price,
	   p5.Quantity
from practice5 p5 left join practice4 p4 on p5.ProductID = p4.ProductID
) a 
left join practice6 p6 on a.CustomerID = p6.CustomerID 
Group by 1

Q2. 각 제품 카테고리별로 가장 많이 팔린 제품의 이름과 총 판매량을 조회하는 SQL 쿼리를 작성해주세요.

select category,
		   ProductName as Top_product,
		   TotalSold
	from
	(
	select p4.Category,
		   p4.ProductName,
		   sum(p5.quantity) as TotalSold,
		   rank() over(partition by category order by sum(p5.quantity) desc) ranking
	from practice5 p5 left join practice4 p4 on p5.ProductID = p4.ProductID 
	group by 1,2
	) a 
	where ranking = 1

Lv5. 예산이 가장 큰 프로젝트는?

Q1. 각 직원이 속한 부서에서 가장 높은 월급을 받는 직원들만 포함된 결과를 조회하는 SQL 쿼리를 작성해주세요.

select Name,
	   Department,
	   Salary	  
from
(
select Name, 
	   Department,
	   salary,
	   rank() over(partition by Department order by salary desc) ranking
from employees
) a 
where ranking = 1

Q2. 직원이 참여한 프로젝트 중 예산이 10,000 이상인 프로젝트만을 조회하는 SQL 쿼리를 작성해주세요.

select e.Name,
	   a.ProjectName,
	   a.Budget
from 
(
select ep.EmployeeID,
	   ep.ProjectID,
	   p.ProjectName,
	   p.Budget
from 
employeeproject ep left join projects p on ep.ProjectID = p.ProjectID
) a 
left join employees e on e.EmployeeID = a.EmployeeID
where a.Budget >= 10000
order by 3 desc