[SQL] 달리기반 1~4-1

Q1. 김씨 성을 가지고 있는 교육생의 수 구하기

SELECT count(distinct(user_id)) as 'name_cnt'
from users 
where name like '김%'
;

처음엔 distinct를 사용하지 않았었는데,
물론 user_id는 웬만하면 unique key를 주겠지만, 혹시 모르니 disticnt까지 써주는 게 논리적으로 더 적절한 코드라고 생각.

Q2. 유저별 계정 생성 일자와 획득한 포인트 평균 구하기

select DATE(created_at) as created_at,
		#substr(created_at, 1,10) as created_at,
	round(avg(point),0) as 'average_points'
from point_users  
group by 1
;

처음엔 DATE 함수를 쓰지 않고, 어차피 날짜 데이터니까 substr(created_at, 1, 10)으로 했었다.
다만 이 역시 논리적으로 날짜 데이터의 년월일 값을 추출하는 게 더 적절하다는 점, 그리고 실질적으로 날짜 데이터를 추출할 필요가 있는 문제가 나왔을 시를 대비해 DATE 함수의 용도를 익혀야 한다는 점에서 유의해야 할 듯.

Q3. 유저별 아이디, 이메일, 획득한 포인트 합계

단, users 테이블에 있지만 point_users 테이블에는 없는 유저들도 포인트 0으로 포함해야 함

SELECT a.user_id as 'user_id',
	a.email as 'email',
	coalesce(b.point, 0) as 'point'
from users a left join point_users b
on a.user_id = b.user_id
order by point desc
;

처음에는 if문으로 작성했으나(if(b.point is null, 0, b.point) as 'point'), null값을 처리하는 함수인 coalesce를 잊고 있었다
point_users 테이블에 없는 유저는 b.point가 null값일테고, 그렇다면 coalesce 함수로 인해 자연스럽게 0이 반환된다.

Q4-1. 고객별 주문 건수와 총 주문 금액 조회

단, 주문을 한 적 없는 고객도 결과에 포함되어야 함

SELECT
    c.CustomerName,
    count(*) OrderCount,
    sum(o.totalamount) TotalSpent
FROM orders o right join customers c
on o.customerid = c.customerid
group by c.CustomerName
;

Q4-1. 나라별 총 주문 금액이 가장 높은 고객의 이름과 총 주문 금액 조회

select 
    country, top_customer, top_spent
from 
    (
    select 
        c.country country,
        c.customername top_customer,
        total_amount top_spent,
        row_number() over (partition by country order by total_amount desc) rowno
    from
        (
        select customerid,
            sum(totalamount) total_amount
        from orders
        group by customerid
        ) o
        join customers c
        on o.customerid = c.customerid
    )
where rowno = 1
;

일단 코드가 굉장히 지저분하고 복잡하다
서브쿼리를 2개 중첩해서 사용한 탓인데,
1) 첫 서브쿼리로 유저별 총 주문 금액을 계산하고,
2) 이 인라인뷰와 고객 테이블을 조인해서 국가별 row_number를 계산하고
3) 이렇게 나온 두번째 서브쿼리에서 row_number=1인 값만 조회했기 때문
mysql이라면 limit를 써서 서브쿼리를 1번만 쓸 수 있었을 수도 있겠다
정확한 건 추후 답안지를 확인하고 비교해보는 식으로 해야할 듯