코드 카타

문제 & 제출 답안

• 1204. Last Person to Fit in the Bus

  -- 쿼리 구현 시간 순서
  --방법 1 : Sum over 함수
  WITH A AS(
  SELECT person_name, sum(weight) over (order by turn) as cum_sum
  FROM queue
  )
  
  SELECT person_name
  FROM A
  WHERE cum_sum <= 1000
  ORDER BY cum_sum DESC LIMIT 1
  
  -- 방법 2-1 : Self join 후 order by limit 1
  WITH A AS(
  SELECT q1.turn, q1.person_name, sum(q2.weight) AS cum_sum
  FROM queue q1
  LEFT JOIN queue q2
  ON q1.turn >= q2.turn
  GROUP BY q1.turn
  HAVING sum(q2.weight) <= 1000
  ORDER BY q1.turn, q2.turn
  )
  
  SELECT person_name 
  FROM A
  ORDER BY cum_sum DESC LIMIT 1
                      
  -- 방법 2-2 : Self join 후 where in (max())
  WITH A AS(
  SELECT q1.turn, q1.person_name, sum(q2.weight) AS cum_sum
  FROM queue q1
  LEFT JOIN queue q2
  ON q1.turn >= q2.turn
  GROUP BY q1.turn
  HAVING sum(q2.weight) <= 1000
  ORDER BY q1.turn, q2.turn
  )
  
  SELECT person_name 
  FROM A
  WHERE cum_sum in (
                      SELECT max(cum_sum)
                      FROM A
                      )
  
  -- 방법 3 : 서브쿼리
  WITH A AS(
  SELECT q1.turn, q1.person_name, 
      (SELECT sum(q2.weight) 
      FROM queue q2
      WHERE q1.turn >= q2.turn)
      AS cum_sum
  FROM queue q1
  )
  
  SELECT person_name
  FROM A
  WHERE cum_sum <= 1000
  ORDER BY cum_sum DESC LIMIT 1
  

  🔒 구해볼 것 : 몸무게 누적합이 1000 넘기 전의 마지막 사람의 이름

  🔑 세부 구현

  1. 몸무게 누적합 컬럼 생성
     • 현재 누적합 = 이전 누적합 + 현재 몸무게
     • 방법 1 : Sum over
       • sum(weight) over (order by turn)
     • 방법 2 : Self Join
       • sum(q2.weight)
       • FROM queue q1
       • LEFT JOIN queue q2
       • ON q1.turn ≥ q2.turn
       • GROUP BY q1.turn
     • 방법 3 : 서브쿼리
       • sum(q2.weight)
       • FROM queue q2
       • WHERE q1.turn ≥ q2.turn
  2. 1000 넘는 행의 바로 직전 행 출력
     • WHERE cum_sum ≤ 1000
     • ORDER BY cum_sum DESC LIMIT 1

  • 쿼리테스트 1

    📌 테이블 설명

    이름	데이터 타입	설명
    PersonType	nchar(2)	사람의 주요 유형: SC = 상점 연락처, IN = 개인(소매) 고객, SP = 영업 사원, EM = 직원(비영업), VC = 공급업체 연락처, GC = 일반 연락처
    EmailPromotion	int	0 = 이메일 프로모션을 받지 않음, 1 = 회사에서만 이메일 프로모션을 받기를 원함, 2 = 회사 및 파트너사로부터 이메일 프로모션을 받기를 원함.

    📌 쿼리

    SELECT count(*) as customer_count
    FROM Person_Person pp
    WHERE (pp.EmailPromotion = 1 OR pp.EmailPromotion = 2)
    	and pp.PersonType = 'IN';

    🔒 구해볼 것 : 이메일 프로모션에 동의한 “개인(소매)” 고객의 수

    🔑 세부 구현

    1. 조건 1 : 이메일 프로모션 동의
       • WHERE (pp.EmailPromotion = 1 OR pp.EmailPromotion = 2)
    2. 조건 2 : 사람 유형이 개인(소매) 고객
       • pp.PersonType = 'IN'

  • 쿼리테스트2

    📌 테이블 설명

    📌 쿼리

    WITH scpp AS(
    			SELECT sc.CustomerID , pp.FirstName as first_name, pp.LastName as last_name
    			FROM Sales_Customer sc
    			LEFT JOIN Person_Person pp
    			ON sc.PersonID = pp.BusinessEntityID
    ), ssohod as(
    			SELECT ssoh.CustomerID as customer_id , sum(ssod.OrderQty) AS total_quantity
    			FROM Sales_SalesOrderHeader ssoh
    			LEFT JOIN Sales_SalesOrderDetail ssod
    			ON ssoh.SalesOrderID = ssod.SalesOrderID
    			WHERE ssoh.OrderDate LIKE '2011-10%'
    			GROUP BY 1
    			HAVING sum(ssod.OrderQty) >= 70
    )
    SELECT ssohod.customer_id , scpp.first_name, scpp.last_name, ssohod.total_quantity
    FROM ssohod
    LEFT JOIN scpp
    ON ssohod.Customer_ID = scpp.customerID
    ORDER BY 1;

    🔒 구해볼 것 : 2011년 10월에 제품 70개 이상 구매한 고객 정보 (고객 ID로 오름차순 정렬)

    🔑 세부 구현

    1. 2011년 10월 판매 제품 수량 정보 출력
       • FROM Sales_SalesOrderHeader ssoh
       • LEFT JOIN Sales_SalesOrderDetail ssod
       • ON ssoh.SalesOrderID = ssod.SalesOrderID
       • WHERE ssoh.OrderDate LIKE '2011-10%'
    2. 고객별 구매 수량 종합
       • sum(ssod.OrderQty)
       • GROUP BY ssoh.customerID
    3. 조건 : 구매 수량 70개 이상
       • HAVING sum(ssod.OrderQty) ≥ 70
    4. 고객의 이름과 성 출력
       • SELECT sc.CustomerID , pp.FirstName as first_name, pp.LastName as last_name
       • FROM Sales_Customer sc
       • LEFT JOIN Person_Person pp
       • ON sc.PersonID = pp.BusinessEntityID
    5. JOIN
       • SELECT ssohod.customer_id , scpp.first_name, scpp.last_name, ssohod.total_quantity
       • FROM ssohod
       • LEFT JOIN scpp
       • ON ssohod.Customer_ID = scpp.customerID
       • ORDER BY 1;

오답노트

• 누적합 구하는 방법
  💯 정답 (혹은 더 좋은 답)

  -- 쿼리 구현 시간 순서
  --방법 1 : Sum over 함수
  WITH A AS(
  SELECT person_name, sum(weight) over (order by turn) as cum_sum
  FROM queue
  )
  
  SELECT person_name
  FROM A
  WHERE cum_sum <= 1000
  ORDER BY cum_sum DESC LIMIT 1
  
  -- 방법 2-1 : Self join 후 order by limit 1
  WITH A AS(
  SELECT q1.turn, q1.person_name, sum(q2.weight) AS cum_sum
  FROM queue q1
  LEFT JOIN queue q2
  ON q1.turn >= q2.turn
  GROUP BY q1.turn
  HAVING sum(q2.weight) <= 1000
  ORDER BY q1.turn, q2.turn
  )
  
  SELECT person_name 
  FROM A
  ORDER BY cum_sum DESC LIMIT 1
                      
  -- 방법 2-2 : Self join 후 where in (max())
  WITH A AS(
  SELECT q1.turn, q1.person_name, sum(q2.weight) AS cum_sum
  FROM queue q1
  LEFT JOIN queue q2
  ON q1.turn >= q2.turn
  GROUP BY q1.turn
  HAVING sum(q2.weight) <= 1000
  ORDER BY q1.turn, q2.turn
  )
  
  SELECT person_name 
  FROM A
  WHERE cum_sum in (
                      SELECT max(cum_sum)
                      FROM A
                      )
  
  -- 방법 3 : 서브쿼리
  WITH A AS(
  SELECT q1.turn, q1.person_name, 
      (SELECT sum(q2.weight) 
      FROM queue q2
      WHERE q1.turn >= q2.turn)
      AS cum_sum
  FROM queue q1
  )
  
  SELECT person_name
  FROM A
  WHERE cum_sum <= 1000
  ORDER BY cum_sum DESC LIMIT 1
  

  😅 내가 쓴 답

  -- CASE WHEN, LAG 활용 시도했으나 실패 ㅜㅜ
  SELECT
  			CASE WHEN LAG(weight) over(order by weight) is null then weight
  			ELSE LAG(weight) over(order by weight) + weight
  FROM queue

  • 틀린 이유 : sum over() 함수, JOIN 시 ON 뒤에 부등호 활용 방법 몰랐음.
• 다중 WITH 문
  💯 정답 (혹은 더 좋은 답)

  WITH scpp AS(
  			SELECT sc.CustomerID , pp.FirstName as first_name, pp.LastName as last_name
  			FROM Sales_Customer sc
  			LEFT JOIN Person_Person pp
  			ON sc.PersonID = pp.BusinessEntityID
  ), ssohod as(
  			SELECT ssoh.CustomerID as customer_id , sum(ssod.OrderQty) AS total_quantity
  			FROM Sales_SalesOrderHeader ssoh
  			LEFT JOIN Sales_SalesOrderDetail ssod
  			ON ssoh.SalesOrderID = ssod.SalesOrderID
  			WHERE ssoh.OrderDate LIKE '2011-10%'
  			GROUP BY 1
  			HAVING sum(ssod.OrderQty) >= 70
  )
  SELECT ssohod.customer_id , scpp.first_name, scpp.last_name, ssohod.total_quantity
  FROM ssohod
  LEFT JOIN scpp
  ON ssohod.Customer_ID = scpp.customerID
  ORDER BY 1;

  😅내가 쓴 답

  -- 정답과 동일

  • 틀린 이유 : 두개 이상의 WITH문 사용 시, 첫 번째 WITH 선언 이후 ‘,’ 사용 후 정의
  • 알게 된 문법 : 다중 WITH 문 사용법