with

Orders 테이블:

 

OrderID	CustomerID	OrderDate	TotalAmount
101	1	2024-01-01	150
102	2	2024-01-03	200
103	1	2024-01-04	300
104	3	2024-01-04	50
105	2	2024-01-05	80
106	4	2024-01-06	400

 

Customers 테이블:

CustomerID CustomerName Country

1	Alice	USA
2	Bob	UK
3	Charlie	USA
4	David	Canada

요구사항:

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

      CustomerName	OrderCount	TotalSpent
      Alice	2	450
      Bob	2	280
      Charlie	1	50
      David	1	400

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

      Country Top	Customer Top	Spent
      USA	Alice	450
      UK	Bob	280
      Canada	David	400

제약사항:

• 두 쿼리 모두 서브쿼리, JOIN, GROUP BY, HAVING 등을 사용해 풀 수 있어야 한다.
• 주문을 한 적이 없는 고객도 첫 번째 쿼리 결과에 포함되어야 한다.

1. 주문이 한적이없는 고객도 고려해야하므로 coalesce를 사용

새로운 부분은 as로 설정

SELECT 
    c.CustomerName,
    COUNT(o.OrderID) AS OrderCount,
    COALESCE(SUM(o.Amount), 0) AS TotalSpent
FROM 
    Customers c
LEFT JOIN 
    Orders o ON c.CustomerID = o.CustomerID
GROUP BY 
    c.CustomerID, c.CustomerName
ORDER BY 
    c.CustomerName;

2.as로 설정

WITH CustomerSpending AS (
    SELECT 
        c.Country,
        c.CustomerName,
        SUM(o.TotalAmount) AS TotalSpent
    FROM Customers c
    JOIN Orders o ON c.CustomerID = o.CustomerID
    GROUP BY c.Country, c.CustomerName
),
RankedCustomers AS (
    SELECT 
        Country, 
        CustomerName AS Top_Customer, 
        TotalSpent AS Top_Spent,
        RANK() OVER (PARTITION BY Country ORDER BY TotalSpent DESC) AS rnk
    FROM CustomerSpending
)
SELECT Country, Top_Customer, Top_Spent
FROM RankedCustomers
WHERE rnk = 1;

with문이 없다면 서브 쿼리 중첩을써야하는데 그러면 보기 안좋기에 이렇게함