SQL 코드카타
문제 61
서울에 위치한 식당 목록 출력하기
REST_INFO와 REST_REVIEW 테이블에서 서울에 위치한 식당들의 식당 ID, 식당 이름, 음식 종류, 즐겨찾기수, 주소, 리뷰 평균 점수를 조회하는 SQL문을 작성해주세요. 이때 리뷰 평균점수는 소수점 세 번째 자리에서 반올림 해주시고 결과는 평균점수를 기준으로 내림차순 정렬해주시고, 평균점수가 같다면 즐겨찾기수를 기준으로 내림차순 정렬해주세요
- 처음 답안 (틀림)
SELECT I.REST_ID,
I.REST_NAME,
I.FOOD_TYPE,
I.FAVORITES,
I.ADDRESS,
ROUND(AVG(R.REVIEW_SCORE), 2) AS SCORE -- 집계함수와 다른 컬럼을 SELECT절에서 함께 불러옴
FROM REST_INFO I
LEFT JOIN REST_REVIEW R
ON I.REST_ID = R.REST_ID
WHERE I.ADDRESS LIKE '%서울%'
GROUP BY I.REST_ID
ORDER BY SCORE DESC, I.FAVORITES DESC;
- 두 번째 답안 (틀림)
SELECT I.REST_ID,
I.REST_NAME,
I.FOOD_TYPE,
I.FAVORITES,
I.ADDRESS,
A.SCORE AS SCORE
FROM REST_INFO I
INNER JOIN (SELECT I.REST_ID,
ROUND(AVG(R.REVIEW_SCORE), 2) AS SCORE
FROM REST_INFO I
INNER JOIN REST_REVIEW R
ON I.REST_ID = R.REST_ID
WHERE I.ADDRESS LIKE '%서울%' -- '서울'이 포함된 주소
GROUP BY I.REST_ID) A -- '서울'시 OO구
ON I.REST_ID = A.REST_ID -- 인천시 '서울'구
ORDER BY SCORE DESC, I.FAVORITES DESC;
- 최종 답안
SELECT I.REST_ID,
I.REST_NAME,
I.FOOD_TYPE,
I.FAVORITES,
I.ADDRESS,
A.SCORE AS SCORE
FROM REST_INFO I
INNER JOIN (SELECT I.REST_ID,
ROUND(AVG(R.REVIEW_SCORE), 2) AS SCORE
FROM REST_INFO I
INNER JOIN REST_REVIEW R
ON I.REST_ID = R.REST_ID
WHERE I.ADDRESS LIKE '서울%' -- '서울'로 시작하는 주소
GROUP BY I.REST_ID) A
ON I.REST_ID = A.REST_ID
ORDER BY SCORE DESC, I.FAVORITES DESC;
틀린 이유
- GROUP BY 사용 규칙 어김
SELECT절에 GROUP BY에 넣지 않은 컬럼들과 집계 함수 사용한 컬럼 하나를 같이 불러옴 - 서울에 위치한 식당 필터링 할 때 LIKE '%서울%' 사용
'%서울%'은 '서울시', '서울구', '서울동'이 포함된 주소를 모두 불러온다.
'서울%'로 필터링하여 '서울시', '서울특별시' 등 시 정보만 포함된 주소 불러오는 것이 맞다.
개선점
- 집계 함수 사용한 컬럼과 그렇지 않은 컬럼을 함께 불러와야 할 때 서브쿼리 사용
- GROUP BY 절 사용하면,
SELECT 절에는 GROUP BY에 명시된 컬럼 또는 집계 함수 사용한 컬럼만 쓸 수 있다. - 집계 함수를 서브쿼리에서 독립적으로 계산한다.
- 원본 테이블과 서브쿼리를 조인한다
- 메인 쿼리의 SELECT절에서 서브쿼리의 집계(aggregation) 컬럼과
원본 테이블의 세부 정보(detail) 컬럼을 불러온다.
- GROUP BY 절 사용하면,
SELECT t.column1,
s.aggregation_column
FROM table t
JOIN (subquery) subquery_name s
on t.column = s.column;
2. 주소 필터링
서울시 → LIKE '서울%'
- 참고 자료
Gemini 검색 바탕으로 내 언어로 정리
SQL 개인 과제
문제 7
연령대별 고객의 수를 파악하고 이탈률*을 대리변수로 충성도를 파악하고자 합니다.
이탈률 = Attrited 수 / 전체 수 * 100
고객 나이의 군집화
- 20대 이하: '20s or less'
- 30대: '30s'
- 40대: '40s'
- 50대 이상: '50s or more'
연령대별로 다음의 통계 도출
- 고객 수
- 이탈률 (전체 고객 중 Attrited Customer 비율, 소수점 3자리까지)
서브쿼리를 사용해 연령대를 구분하고 이탈률을 계산할 것
- 최종 쿼리
SELECT Age_Group,
Total_Customers,
ROUND(CAST(Attrited_Customers AS FLOAT) / Total_Customers * 100, 3)
AS Attrition_Rate
FROM (SELECT CASE WHEN Customer_Age < 30 THEN '20s or less'
WHEN Customer_Age BETWEEN 30 AND 39 THEN '30s'
WHEN Customer_Age BETWEEN 40 AND 49 THEN '40s'
WHEN Customer_Age >= 50 then '50s or more'
END AS Age_Group,
COUNT(*) as Total_Customers,
COUNT(CASE WHEN Attrition_Flag = 'Attrited Customer'
THEN Attrition_Flag ELSE NULL END) AS Attrited_Customers
FROM sparta.bankchurners
GROUP BY Age_Group) a
ORDER BY Age_Group;
-- 서브쿼리에 고객 연령대를 CASE WHEN으로 군집화한다.
-- 서브쿼리에서 연령대별 고객 수를 COUNT(*)로 구한다.
-- 연령대별 Attrited 고객 수를 COUNT(CASE WHEN 조건)으로 구한다.
-- 메인 쿼리에서 서브 쿼리에서 정의한 Age_Group, Total_Customers 불러온다.
-- 서브 쿼리에서 정의한 개념 바탕으로 이탈률(Attrition_Rate) 구한다.
새롭게 알게 된 것
- [ANSI SQL] COUNT(CASE WHEN ): 조건부 집계(conditional aggregation)
- COUNT(CASE WHEN condition THEN 1 ELSE NULL END) 또는
COUNT(CASE WHEN condition THEN 1 END): ELSE NULL 생략 가능
조건 만족하는 행의 수만 셈
- COUNT(CASE WHEN condition THEN 1 ELSE NULL END) 또는
- 복잡한 비율 구할 때 (ex. 시간대별 전환율)
분자와 분모를 서브쿼리에서 정의한 뒤,
메인 쿼리에서 다음과 같이 쓴다.- 분자 * 100.0 / 분모
- CAST(분자 AS FLOAT) / 분모 * 100
- CASE WHEN 분모=0 THEN 0.0
ELSE CAST(분자 AS FLOAT) / 분모 * 100 END AS cr - 예시: 시간대별 구매 전환율
전환율 = (전환 수 / 총 잠재 고객) * 100
구매 전환율 = (구매 횟수 / 방문 수) * 100
시간대별 구매 전환율 = (시간대별 구매 횟수 / 시간대별 방문 수) * 100 - 분자: 서브쿼리에서 시간대별 구매 횟수
분모: 서브쿼리에서 시간대별 방문 수
시간대별 구매 전환율: 메인 쿼리에서 CASE WHEN 시간대별 방문 수 = 0 THEN 0.0
ELSE CAST(시간대별 구매 횟수 AS FLOAT) / 시간대별 방문 수 * 100 END AS hpcr
- [MySQL] 시간대별 전환율, 연령대별 이탈률 템플릿
- FROM 절 서브쿼리에 CASE WHEN ~ AS time으로 시간대별 군집화
- GROUP BY time
- 분모: COUNT(*) AS d 또는
COUNT(DISTINCT user_id) AS d - 분자: COUNT(CASE WHEN condition THEN 1 ELSE NULL END) AS m
- 메인 쿼리에 시간대별 전환율 정의: 분모가 0일 때 0으로 나누는 오류 방지 위해
CASE WHEN 분모 = 0 THEN 0.0
ELSE 분자 * 100.0 / 분모 END AS calculated_rate
SELECT clustering,
CASE WHEN total_count = 0 THEN 0.0 -- 분모가 0일 경우 0.0으로 처리
ELSE ROUND(conditional_count * 100.0 / total_count, 2) -- 비율 구하는 식
END AS conversion_rate
FROM
(
SELECT CASE WHEN condition1 THEN '그룹1' -- 군집화
WHEN condition2 THEN '그룹2'
ELSE '기타' END AS clustering,
COUNT(*) AS total_count, -- 분모
COUNT(CASE WHEN 조건 THEN 1 ELSE NULL END) AS conditional_count -- 분자
FROM table
WHERE -- 선택 사항
GROUP BY clustering -- 군집화 결과로 그룹핑
-- (MySQL 한정 SELECT에서 정의한 CASE WHEN의 별칭을
-- GROUP BY에서 사용 가능)
) AS grouped_data
ORDER BY column; -- 선택 사항
4. [Oracle, PostgreSQL, SQL Server, MySQL 등] 시간대별 전환율, 연령대별 이탈률 템플릿
SELECT clustering,
CASE WHEN total_count = 0 THEN 0.0 -- 분모가 0일 경우 0.0으로 처리
ELSE ROUND(conditional_count * 100.0 / total_count, 2) -- 비율 구하는 식
END AS conversion_rate
FROM
(
SELECT CASE WHEN condition1 THEN '그룹1' -- 군집화
WHEN condition2 THEN '그룹2'
ELSE '기타' END AS clustering,
COUNT(*) AS total_count, -- 분모
COUNT(CASE WHEN 조건 THEN 1 ELSE NULL END) AS conditional_count -- 분자
FROM table
WHERE -- 선택 사항
GROUP BY CASE WHEN condition1 THEN '그룹1' -- 군집화 결과로 그룹핑
WHEN condition2 THEN '그룹2' -- GROUP BY에 SELECT의 별칭을 바로 쓸 수 없음!
ELSE '기타' END -- CASE WHEN 구문 전체를 다시 사용!
) AS grouped_data
ORDER BY column; -- 선택 사항
- 참고 자료
Gemini 검색 후 내 언어로 정리
https://docs.tosspayments.com/resources/glossary/conversion
https://advertising.amazon.com/ko-kr/library/guides/conversion-rate
https://www.interviewquery.com/p/sql-count-case-when