SQL 코딩테스트 문제 풀이(2) - 가구 판매의 비중이 높았던 날 찾기(solvesql, CTE, CASE WHEN, COUNT, SQL 처리 순서)

 

📝목차

1. SQL 코딩테스트 문제 소개 - 가구 판매의 비중이 높았던 날 찾기(solvesql)
2. 풀이 - 각각 계산 후 JOIN
3. CHAT-GPT로 최적화하기
4. 꿀팁_SQL의 처리 순서

 

정답 바로가기

 

SQL 코딩테스트 문제 풀이 두 번째 시간이다.

1. SQL 코딩테스트 문제 소개 - 가구 판매의 비중이 높았던 날 찾기(solvesql)

이번 문제는 solvesql 사이트> 데이터리안 SQL 캠프 입문반> 3페이지에서 접할 수 있다.

난이도는 4로 조금 어려운 정도이며, 처음 SQL 문제를 접하는 사람에게는 다소 복잡하게 느껴질 것이다.

solvesql 사이트는 해설을 따로 제공하지 않기 때문에 본 글을 통해 도움을 얻길 바란다!

 

https://solvesql.com/problems/characteristics-of-orders/

https://solvesql.com/problems/characteristics-of-orders/

해당 문제의 유형은 여러 조건에 해당하는 데이터들만 추출한 뒤 전체 주문 중 가구 주문의 비율을 계산하는 것이다.

필터링 후 전체 주문 수와 가구 주문수를 구하고, 비율을 계산하면 된다.

다양한 문법과 서브쿼리를 활용하는 문제로 답이 엄청 복잡하지는 않지만, 생각을 많이 해야 한다.

 

나는 3단계로 문제를 해결했다.

  1. WITH AS를 통해 조건에 해당하는 날짜를 추출
  2. 해당 날짜의 일별 주문수, 일별 Furniture 주문수 구하기
  3. JOIN을 통해 합친 후, 비율을 계산

 

주의점

Order_ID를 count 해서 주문 수를 구하는데, 이때 Order_ID가 중복된다는 것을 조심해야 한다.

그리고 하나의 주문에서 여러 제품을 한 번에 구매하는 경우엔 하나의 Order_ID에서 여러 Category(Furniture, Office Supplies, Technology)의 제품을 구매할 수도 있다.

문제에서 다소 모호하게 설명이 되어있는 부분이 있는데 Furniture 카테고리의 제품을 하나라도 구매한 주문이면 해당 주문은 Furniture 주문으로 처리하면 된다.(이거 때문에 좀 헤맸다;;;)

 

2. 풀이 - 각각 계산 후 JOIN

1) CTE(Common Table Expression, 공통 테이블 식)를 통해 조건에 해당하는 날짜를 추출하기

with T_date as(
    SELECT order_date
    from records
    GROUP By order_date
    having count(distinct order_id)>=10
)

 

SQL 쿼리 실행 결과

실행 결과

 

 

우선 GROUP BY와 HAVING을 통해, 일별 주문수를 구한 뒤 주문수가 10개 이상인 날짜를 추출한다.

이때 count는 distinct order_id를 통해 중복되는 주문을 제외시켜야 한다.

 

CTE(Common Table Expression, 공통 테이블 식), 즉 WITH AS를 사용하면 반복해서 서브쿼리를 사용하는 대신에, 쿼리 결과를 테이블에 저장해 놓고 사용할 수 있다.

위 실행 결과는 as 뒤의 괄호 부분을 실행시킨 결과이며, T_date에 이걸 임시 저장해 둔다.

2) 해당 날짜의 일별 주문수, 일별 Furniture 주문수 구하기

일별 주문수 구하기

with T_date as(
    SELECT order_date
    from records
    GROUP By order_date
    having count(distinct order_id)>=10
)
-- 일별 주문수 구하기
select order_date, count(distinct order_id) all_cnt
from records
where order_date in (select order_date from T_date)
group by order_date

 

SQL 쿼리 실행 결과

실행 결과

 

WHERE order_date IN (select order_date from T_date)를 통해 T_date 테이블의 주문 날짜와 일치하는 날만 추출하여

group by를 통해 일별 주문수를 계산한다.

 

일별 Furniture 주문수 구하기

with T_date as(
    SELECT order_date
    from records
    GROUP By order_date
    having count(distinct order_id)>=10
)
-- 일별 Furniture 주문수 구하기
select order_date, count(distinct order_id) furniture
from records
where order_date in (select order_date from T_date)
and category = 'Furniture'
group by order_date

 

SQL 쿼리 실행 결과

실행 결과

일별 주문수 계산과 동일하지만, WHERE 절에 AND category = 'Furniture'를 추가하여 Furniture에 해당하는 주문만 카운트한다.

3) JOIN을 통해 합친 후, 비율을 계산

일별 주문수와 일별 Furniture 주문수를 구하는 쿼리를 각각 작성했으니 이제 이 두 테이블을 날짜를 Key로 하여 JOIN 한 뒤, Furniture 주문의 비율을 구하면 된다.

with T_date as(
    SELECT order_date
    from records
    GROUP By order_date
    having count(distinct order_id)>=10
)

select T1.order_date, furniture, round(furniture/cast(all_cnt as float)*100,2) furniture_pct

--일별 주문수 구하기
from (select order_date, count(distinct order_id) all_cnt
  from records
  where order_date in (select order_date from T_date)
  group by order_date) T1
  
join 

--일별 Furniture 주문수 구하기
  (select order_date, count(distinct order_id) furniture
  from records
  where order_date in (select order_date from T_date)
    and category = 'Furniture'
  group by order_date) T2

ON T1.order_date=T2.order_date
--furniture 비율 40 이상인 날만 필터링 후 furniture 비율과 날짜 기준 정렬
where furniture_pct>=40
order by furniture_pct desc, T1.order_date asc

 

SQL 쿼리 실행 결과

실행 결과

갑자기 너무 복잡해져서 놀랐을 수도 있지만 앞서 작성한 쿼리로 추출한 테이블을 order_date 기준으로 JOIN 하고, 비율을 구했을 뿐이다.

round(furniture/cast(all_cnt as float)*100,2) furniture_pct

이 부분이 비율을 계산하는 부분인데, 자료형을 실수로 변경해 줘야지 나눗셈 연산이 제대로 되기 때문에 cast(all_cnt as float)을 통해 해결하였다.

round(a, b)는 a를 소수 b자리까지 출력하라는 의미다.

 

뒤의 WHERE절과 ORDER BY를 통해 Furniture 주문 비율이 40 이상인 날만 필터링한 뒤, 비율이 높은 순으로(동일한 경우 날짜가 빠른 순으로) 정렬하였다.

 

3. CHAT-GPT로 최적화하기

쿼리가 너무 복잡해서 CHAT-GPT로 최적화를 해보았다.

모범 답안

WITH daily_counts AS (
  SELECT
    order_date,
    COUNT(DISTINCT order_id) AS total_cnt,
    COUNT(DISTINCT CASE WHEN category = 'Furniture' THEN order_id END) AS furniture_cnt
  FROM records
  GROUP BY order_date
  HAVING COUNT(DISTINCT order_id) >= 10
)
SELECT
  order_date,
  furniture_cnt   AS furniture,
  ROUND(furniture_cnt * 100.0 / total_cnt, 2) AS furniture_pct
FROM daily_counts
WHERE furniture_cnt * 1.0 / total_cnt >= 0.4
ORDER BY
  furniture_pct DESC,
  order_date    ASC;

 

가장 큰 차이는 COUNT 함수 안에서 CASE WHEN 구문을 사용하여 필터링을 한다는 점이다.

COUNT 내에서 CASE WHEN으로 필터링이 가능하다는 것은 처음 알게 된 사실..!

이를 통해 기존에 원본 테이블 records를 두 번 스캔하던 것을 한 번 스캔하는 것으로 개선했다.

 

또한 비율 계산할 때, 분자에 100.0을 곱해주면서 자동으로 자료형을 float으로 변화시켰다.

 

*참고로 CTE를 사용하지 않고 하나의 메인 쿼리로만 작성도 가능하다. 카운트와 비율 계산을 SELECT 절에서 한 번에 하고, HAVING 절에 비율 조건을 추가하면 된다. 하지만 그러면 COUNT(DISTINCT CASE ~)가 두 번 평가되기 때문에 성능이 저하된다. (현대 DBMS에서는 차이가 크지는 않지만)

 

4. 꿀팁_SQL의 처리 순서

메인 쿼리의 WHERE 절을 보자.

furniture_pct >=0.4

가 아니라

furniture_cnt * 1.0 / total_cnt >= 0.4

를 사용한다.

 

SQL에서는 SELECT 절의 별칭(alias)을 WHERE 절에서 참조할 수 없다. 여기에는 SQL의 처리 순서에 비밀이 있다.

SQL의 처리 순서

1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY

위와 같은 순서로 SQL 쿼리가 실행된다. 따라서 WHERE 단계에서는 아직 SELECT에서 정의한 별칭이 만들어지기 전이라 참조가 불가능하다.(표준 SQL의 경우임, RDBMS에 따라 HAVING절이나 GROUP BY절에서 참조가 가능할 수도 있음)

 

 

마무리

오늘은 두 번째 SQL 코딩테스트 문제 풀이를 해보았다. solvesql의 '가구 판매의 비중이 높았던 날 찾기' 문제였으며 CTE, CASE WHEN, COUNT, SQL 처리 순서에 대해 더 자세히 공부하는 계기가 되었다. 다음엔 어떤 문제를 풀어볼까? 두근두근~