[본캠프 54일차] QCC ⑤, 태블로 공부

물맨두 2025. 5. 2. 15:06

아 QCC 이렇게 못 볼 줄은 몰랐는데, 주말에 ADsP 공부도 해야 하는데 QCC 1회차부터 문제들 다시 풀어봐야 할 듯하다. 이번 QCC는 1번 문제를 제외하고는 문제를 잘 못 읽어서 놓친 조건들도 꽤 있어서 문제를 꼼꼼히 살펴야겠다고 반성했다.

오늘 한 일은,

SQL 공부
- [QCC] 5회차 응시하기
- [QCC - 해설 세션] 수강하기
- [QCC] 5회차 오답노트 정리하기
태블로 공부
- [실습으로 배우는 태블로] 2주차 수강하기

SQL 공부: 5회차 QCC 회고

1. 전체 상담 건수 대비 분류되지 않은 상담 비율 구하기

스파르타생명(주) 프로그램에서는 일부 상담이 정확한 카테고리로 분류되지 못하는 경우가 있습니다. category 컬럼의 값이 n/a 또는 NULL로 되어 있는 경우, 해당 상담을 '분류되지 않은 상담'으로 간주하고, 해당 상담이 전체 상담 중 차지하는 비율을 구하는 쿼리를 작성해주세요.
- 결과는 반올림하여 소수점 첫 번째 자리까지만 반환해야 함
- call_date 값이 2024년 4월 15일까지인 상담을 대상으로 함

-- 제출한 쿼리 (오답 처리됨)
SELECT ROUND(
        (SELECT COUNT(*)
         FROM calls
         WHERE (category NOT IN ('emergency assistance', 
                                 'authorisation', 
                                 'benefits')) AND
               (call_date < '2024-04-16')) /
        COUNT(*), 1)*100 uncategorised_call_pct
FROM calls
WHERE call_date < '2024-04-16'

가장 처음에는 WHERE절에 'WHERE category IS NULL'을 적어놓고 'SELECT *'로 확인했는데 아무것도 뜨질 않아서(근데 왜 지금 해보니까 조회되지..? 아깐 아무런 행도 안 떴는데.....) 다음으로 SELECT절에 'SELECT DISTINCT category'로 조회해봤다.

(지금은 NULL값도 잘 뜨지만 시험 때는 그냥 비어서 저 행이 찌그러진 채로 나왔었는데..? 아무튼,)

NULL이나 n/a가 아닌 값은 총 3개(emergency assistance, authorisation, benefits)임을 확인하고 그러면 저 3값이 아닌 행들을 모두 세는 방향으로 작성하면 분자인 분류되지 않은 상담 건수를 구할 수 있을 거란 생각에 위의 쿼리를 적게 되었다.

-- 1번 문제의 튜터님 정답 쿼리
select 
  round((sum(if(category = 'n/a' or category is null, 1, 0))/
    count(1)) * 100, 1) as uncategorized_call_pct
from calls
where date_format(call_date, '%Y-%m-%d') < '2024-04-16'

그런데 튜터님의 정답 쿼리와 그 결과 테이블을 보면 19.9%가 나와야 하는데 나는 왜 10.0%가 뜬 것이지...

일단 오답의 원인 중 하나는 ROUND() 함수의 사용이다.

튜터님의 답안을 보면 uncategorized_call_pct를 구할 때 가장 최종 단계에서 반올림을 한다(분류되지 않은 상담 건수를 전체 상담 건수로 나누고 → 그 다음에 100을 곱하고 → 소수점 둘째 자리에서 반올림하기). 그런데 나는 반올림을 하고서 100을 곱해서 문제가 발생했다.

-- 제출한 쿼리에서 ROUND() 함수의 사용 고침[첫 번째] (근데 여전히 오답임)
SELECT ROUND(((SELECT COUNT(*)
        FROM calls
        WHERE (category NOT IN ('emergency assistance', 'authorisation', 'benefits')) AND
              (call_date < '2024-04-16')) / 
        COUNT(*))*100, 1) uncategorised_call_pct
FROM calls
WHERE call_date < '2024-04-16'

그런데 그것만이 문제가 아니었다. ROUND() 함수를 적절한 방식으로 사용해도 11.8%로 여전히 정답인 19.9%가 아니다.

그래서 살펴본 것이 분자인 '분류되지 않은 상담'을 집계하는 방식이다.

혹시나 나는 튜터님처럼 call_date를 DATE_FORMAT()으로 안 정리하고서 그냥 해서 그런가 싶어서 튜터님과 동일하게도 바꿔봤지만 그건 의미가 없었다(참고로 나는 테이블 설명에서 call_date의 데이터 타입이 타임 스탬프이길래 날짜 형식으로 변환해주지 않아도 싶겠다 생각했다).

그래서 실제로 category의 각 값마다 해당하는 상담 건수가 몇인지 집계해봤다.

emergency assistance가 252건, authorisation이= 173건, benefits이 286건, 분류되지 않은 상담(NULL 혹은 n/a)이 총 177건으로 집계됐는데, 나는 105건인 것을 보면 n/a만 집계된 것 같다. COUNT(*)로 집계하면 NULL값인 행을 포함하여 집계한다고 알고 있었는데 그렇게 집계되지 않은 이유를 모르겠어서 튜터님께 질문하러 갔는데, WHERE절에서 NOT IN으로 거르는 과정에서 문자열이 아닌 NULL값이 걸러지며 n/a만 집계된 것 같다고 답변해주셨다.

[참고1] MySQL Blog Archive - "A must-know about NOT IN in SQL - more antijoin optimization"

[참고2] MySQL 8.4 Reference Manual / ... / Query-Related Issues / Problems with NULL Values

In SQL, the NULL value is never true in comparison to any other value, even NULL. An expression that contains NULL
always produces a NULL value unless otherwise indicated in the documentation for the operators and functions involved in the expression.

튜터님이 답변해주신 내용 토대로 좀 더 SQL의 작동 방식에 대해 찾아봤더니, NULL값은 어떤 비교에서도 True를 반환하지 않기에 WHERE절을 통과할 수 없는 것이라고 했다(∵WHERE절은 해당 조건이 참인 행만 필터링하기에). 그래서 이러한 특성 때문에 공식 문서에서 덧붙이기를 NULL값을 다룰 때는 IS NULL, IS NOT NULL 연산자와 IFNULL() 함수를 쓰라고 명시하고 있었다.

앞서 105건만 조회된 쿼리에 IS NULL 연산자를 사용한 조건도 추가해주니 이제야 177건을 집계한다

-- 제출한 쿼리에서 ROUND() 함수 사용과 WHERE절에서 NULL을 다루는 부분도 고침[두 번쩨] (정답 처리됨)
SELECT ROUND((
		(SELECT COUNT(*)
         FROM calls
         WHERE ((category NOT IN ('emergency assistance', 'authorisation', 'benefits')) OR
                (category IS NULL)) AND
               (call_date < '2024-04-16')) / 
        COUNT(*))*100, 1) uncategorised_call_pct
FROM calls
WHERE call_date < '2024-04-16'

-- 두 번째 수정안에서 유지보수 측면에서 더 나은 쿼리로 수정함[세 번쩨] (정답 처리됨)
SELECT ROUND((
		(SELECT COUNT(*)
         FROM calls
         WHERE ((category = 'n/a') OR (category IS NULL)) AND
               (call_date < '2024-04-16')) / 
        COUNT(*))*100, 1) uncategorised_call_pct
FROM calls
WHERE call_date < '2024-04-16'

굳이 내가 제출했던 쿼리를 정답이 되게끔 고치자면 위와 같이 고칠 수 있다. 그러나 사실 딱히 좋은 방식은 아니다. category에 새로운 범주가 추가되면 그때마다 WHERE절에 추가해줘야 하니 말이다. 그런 점을 고려해서 한 번 더 고쳐서 세 번째 쿼리까지 써봤다(여전히 튜터님의 정답 쿼리에 비하면 가독성이 안 좋지만).

2. 연령별 주문 전환율 구하기

스파르타 이커머스 부서에서는 최근 회원(2023년 1월 1일부터) 중에서, 연령대별로 주문 전환율을 파악하고자 합니다.
- 결과를 나이 구간 기준으로 오름차순 정렬해야 함
- 결과값을 반올림하여 소수점 2자리로 출력해야 함
- 주문 전환율 : order 이벤트 수 / (view + order 이벤트 수)

-- 2번 문제 제출한 쿼리 (오답 처리됨)
WITH cr AS (
  SELECT ae.event_id,
         ae.user_id,
         ae.event_type,
         up.age_bucket
  FROM app_events ae LEFT JOIN user_profiles up ON ae.user_id = up.user_id
  WHERE up.signup_date > '2023-01-01'
)

SELECT age_bucket,
       ROUND((SELECT COUNT(event_id) FROM cr WHERE event_type = 'order') / COUNT(*), 2)*100 conversion_rate
FROM cr
GROUP BY age_bucket
ORDER BY age_bucket

...? WITH 구문에 WHERE절 조건 왜 저렇게 적어놨지?? 그리고 이번 쿼리도 ROUND() 함수와 100을 곱하는 순서를 여전히 바꿔서 쓰고 있다.

2023년 1월 1일이 포함되게 WHERE절을 수정하고, 백분율을 구하고서 반올림하는 순서로 수정한 후에 실행한 결과 테이블이다. 전환율을 확인하니 값이 제출한 쿼리와 조금 변했지만 여전히 오답이다. 튜터님의 정답 쿼리에 의하면 20대: 35.03%, 30대: 37.45%, 40대: 35.74%가 나와야 한다.

...

뭐가 문제라 내가 작성한 쿼리에선 큰 값이 나왔을지를 보는데 분자인 order 이벤트를 집계하는 방식이 문제였다. 스칼라 서브쿼리를 사용하게 되어 GROUP BY절에 명시한 대로 age_bucket별로 order 이벤트를 집계하지 못하고 있다. 분모는 GROUP BY절에 의해 연령별로 집계되고 있으나, 현재 분자가 전체 cr 테이블에서 모든 order 이벤트 건수로 고정돼 있는 상황인 것이다.

-- 2번 문제 제출한 쿼리에서 ROUND() 함수의 사용, 가입일 조건, 전환율 계산 방식 수정 (정답 처리됨)
WITH cr AS (
  SELECT ae.event_id,
         ae.user_id,
         ae.event_type,
         up.age_bucket
  FROM app_events ae LEFT JOIN user_profiles up ON ae.user_id = up.user_id
  WHERE (up.signup_date >= '2023-01-01') AND
        (ae.event_type IN ('view', 'order'))
)

SELECT age_bucket,
       ROUND((SUM(CASE WHEN event_type = 'order' THEN 1 ELSE 0 END) / COUNT(*))*100, 2) conversion_rate
FROM cr
GROUP BY age_bucket
ORDER BY age_bucket

사실 분자인 order 건수를 집계하는 방식뿐만 아니라 분모 역시 review까지 집계하고 있어 문제였기에 cr 테이블의 WHERE절에 event_type에 대한 조건도 추가했더니 정답이 나왔다.

3. 최단 기간에 우수 고객이 된 사용자 파악하기

스파르타 마케팅팀에서는 '우수 고객'을 다음과 같이 정의하고, 가장 짧은 기간 내에 우수 고객이 된 사용자 1명을 파악하고자 합니다.
- 우수 고객 : 누적 10건 이상의 주문을 완료한 사용자
- 우수 고객을 달성한 기간 산정: 사용자가 첫 주문을 한 이후로 우수 고객 기준을 만족하기까지 소요된 일수로 계산함

-- 3번 문제 제출한 쿼리 (오답 처리됨)
WITH pu AS (
  SELECT user_id,
         COUNT(*) order_cnt,
         MAX(order_datetime) max_date,
         MIN(order_datetime) min_date
  FROM user_orders
  GROUP BY user_id
)

SELECT s.user_id,
       s.days_to_power_user
FROM (
      SELECT user_id,
             DATEDIFF(max_date, min_date) days_to_power_user
      FROM pu
      WHERE order_cnt >= 10
    ) s
WHERE s.days_to_power_user = (
              SELECT ss.days_to_power_user
              FROM (
                SELECT s.user_id,
                       s.days_to_power_user
                FROM (
                    SELECT user_id,
                           DATEDIFF(max_date, min_date) days_to_power_user
                    FROM pu
                    WHERE order_cnt >= 10
                    ) s
                  ) ss
              ORDER BY ss.days_to_power_user LIMIT 1
  )

제출한 쿼리에 따르면 우수 고객이 되는 데 가장 짧은 기간은 9일이었지만, 해설 강의를 들으면서 문제를 비로소 이해했다. 주문 건수가 10건이 넘어가는 사람들의 최초 주문일과 최근 주문일의 기간을 계산해서 가장 그 기간이 짧은 사람을 조회하는 게 아니었다. 최초 주문일로부터 누적 10건을 달성한 날까지의 기간을 산정해야 했던 것이다. 즉 MAX(order_datetime)을 구할 게 아니라는 소리다.

-- 튜터님의 정답 쿼리를 참고하며 작성한 쿼리 (정답 처리됨)
SELECT DISTINCT s.user_id,
       DATEDIFF(lead_nine, order_datetime) days_to_power_user
FROM (
  SELECT user_id,
         order_datetime,
         LEAD(order_datetime, 9) OVER(PARTITION BY user_id) lead_nine
  FROM user_orders
) s
WHERE lead_nine IS NOT NULL
ORDER BY days_to_power_user LIMIT 1

문제를 푸는 데 핵심은 DATEDIFF() 함수를 사용하기 위해 10번째 주문일자를 구하는 것이었는데, LEAD() OVER를 사용하면 구현할 수 있었다.