SQL 달리기반 문제

📗 1주차에 배울 내용

1. 데이터 속 김서방 찾기
2. 날짜별 획득포인트 조회하기
3. 이용자의 포인트 조회하기
4. 단골 고객님 찾기
5. 가장 높은 월급을 받는 직원은?
6. 가장 많이 팔린 품목은?
7. 예산이 가장 큰 프로젝트?
8. 888
9. 999

---

1.  데이터 속 김서방 찾기

select count(name) as count_name
from users
where name like "김%"

---

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

select date(created_at) as created_at , round(avg(point), 0) as average_points
from point_users
group by date(created_at)

---

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

select u.user_id, u.email, coalesce(point, 0)
from users u left join point_users p on u.user_id = p.user_id
order by point desc

---

4.  단골 고객님 찾기?!

-- 1번 : 고객별로 주문 건수와 총 주문 금액을 조회하는 SQL 쿼리를 작성하시오
         조건) 출력결과에는 고객 이름, 주문건수, 총 주문 금액이 포함되어야 한다
         주문이 없는 고객도 결과에 포함되어야 한다.
select customername as 'CustomerName', count(customerid) as 'OderCount' , sum(totalamount) as 'TotalSpent'
from orders o left join customers c on o.customerid = c.customer_id
group by c.customername

-- 2번 : 나라별 총 주문 금액이 가장 높은 고객의 이름과  그 고객의 총 주문 금액을 
         조회하는 쿼리를 작성하시오
SELECT country, customername AS Top_Customer, totalspent
FROM (
SELECT country, customername, SUM(totalamount) AS totalspent,
           RANK() OVER (PARTITION BY country ORDER BY SUM(totalamount) DESC) AS rnk
FROM orders o
LEFT JOIN customers c ON o.customerid = c.customer_id
GROUP BY country, customername
) a;

---

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

1번 문제는 셀프조인을 이용!

-- 1번 : 각 직원의 이름, 부서, 월급 그리고 그 직원이 속한 부서에서 가장 높은 월급을 받고 있는 
         직원의 이름과 월급을 조회하는 쿼리를 작성하시오
select e.name, e.department, e.salary, t.Top_Earner, t.Top_Salary
FROM employees e
join (
    select department, name as Top_Earner, salary as Top_Salary
    from employees
    where (department, salary) in (
        select department, max(salary)
        from employees
        group by department
    )
) t on e.department = t.department;

-- 2번 : 부서별로 평균 월급이 가장 높은 부서의 이름과 해당 부서의 평균 월급을 조회하는 쿼리를
         작성하시오
select department as Department, avg(salary) as Avg_Salary
from employees
group by department
order by avg(salary) desc
limit 1

---

6. 가장 많이 팔린 품목은 ? (준비중...)

-- 1번 : 고객이 구매한 모든 제품의 총 금액을 계산하고 고객이름 총 구매금액 주문수를 출력하는 쿼리를 작성하시오
select c.CustomerName as "CustomerName", sum(p.price*o.Quantity) as "TotalAmount", count(o.CustomerID) as "OrderCount"
from Products p left JOIN  Orders o on p.ProductID = o.ProductID left join Customers c on o.CustomerID = c.CustomerID
group by c.CustomerName

-- 2번 : 각 제품의 카테고리 별로 가장많이 팔린 제품의 이름과 총 판매량을 조회하는 쿼리를 작성하시오
SELECT Category, ProductName AS Top_Product, TotalSold
FROM (
    SELECT p.Category, p.ProductName,
           SUM(o.Quantity) AS TotalSold,
           ROW_NUMBER() OVER (PARTITION BY p.Category ORDER BY SUM(o.Quantity) DESC) AS ranking
    FROM Products p
    JOIN Orders o ON p.ProductID = o.ProductID
    GROUP BY p.Category, p.ProductName
) sub1
WHERE ranking = 1;

---

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

준비중...

---

8. 준비중...

---

9. 준비중...