코테 준비 | Percentage of Users Attended a Contest / Queries Quality and Percentage

☑️ Queries Quality and Percentage

[문제]

• We define query quality as: The average of the ratio between query rating and its position.
• We also define poor query percentage as: The percentage of all queries with rating less than 3.
• Both quality and poor_query_percentage should be rounded to 2 decimal places.

---

[문제 풀이]

정답 코드

SELECT QUERY_NAME
     ,ROUND(AVG(RATING/POSITION),2) AS QUALITY
     ,ROUND(COUNT(IF(RATING<3,RATING,NULL))/COUNT(RATING)*100.0,2) AS POOR_QUERY_PERCENTAGE
FROM QUERIES
GROUP BY QUERY_NAME

 

단계적 설명 

1. quality  컬럼 생성 : 쿼리 점수(rating)를 포지션(position) 으로 나눈 값의 평균 비율   
   • 퀴리 점수 /포지션 의 평균 집계 : avg(rating/position)
2.  poor_query_percentage 컬럼 생성 : 점수가 3 미만인 쿼리수의 퍼센트 비중   
   
   • 점수(rating)이 3 미만인 쿼리수 : if로 해당 조건 만족시 rating 값, 아닐때 null 로 반환하여 count로 갯수 집계  
   • 전체 쿼리 수 : rating로 카운팅  
3. 비율 컬럼은 소수점 2자리 까지 출력
   
   • 출력값에 round( 값, 2) 적용

개선 코드

SELECT QUERY_NAME
     ,ROUND(AVG(RATING/POSITION),2) AS QUALITY
     ,ROUND(SUM(IF(RATING<3,1,0)*100.0)/COUNT(*),2) AS POOR_QUERY_PERCENTAGE
FROM QUERIES
GROUP BY QUERY_NAME

• poor_query_percentage 컬럼 생성시, 조건 만족시 1, 아닐때 0을 반환하여 집계 함수 sum 사용 할 수 있음. 
• 기준 컬럼 query_name 이 pk 이므로 전체 쿼리수는 아스타(*) 로 집계 가능 
• 두 쿼리다 성능면 에서는 큰 차이는 없다.

[KEY POINT & TIP]

🔑 그룹핑과 집계 함수 적절히 사용할수있냐
🔑조건별 집계 & 컬럼 추가 생성 할수 있느냐

☑️  Percentage of Users Attended a Contest

[문제]

• Write a solution to find the percentage of the users registered in each contest rounded to two decimals.
• Return the result table ordered by percentage in descending order. In case of a tie, order it by contest_id in ascending order.

---

[문제 풀이]

정답 코드

SELECT R.CONTEST_ID
       ,ROUND(COUNT(DISTINCT R.USER_ID)/ COUNT(DISTINCT U.USER_ID)*100.0,2) AS PERCENTAGE
FROM USERS AS U,REGISTER AS R
GROUP BY R.CONTEST_ID
ORDER BY 2 DESC,1 ASC

 

단계적 설명 

1. 콘테스트 아이디 별 등록 유저 비율 조회 
   • 카티션 곱 진행 : `왜?` 분모에 USERS 테이블의 전체 유저를 카운팅 필요. LEFT/RIGHT/INNER JOIN 불가. OUTER JOIN 형태만 가능한데 LEFT&RIGHT 조인은 쿼리가 길어지니 카티션 곱 & 그룹핑 선택
   • 콘테스트 아이디로 그룹핑 후 등록 ID , 전체 ID COUNT 집계 ( 카티션 곱으로 동일 행 3개씩 추가 생성 됐으므로 중복 제거 DISTINCT 사용) 
2. 비율 출력 양식 
   
   • ROUND(값, 2) 로 양식 맞춰줌 
3. 정렬 기준 
   
   • 퍼센트 DESC(내림차), 콘테스트ID (오름차,ASC 생략)

오류 코드

-- 오류코드 : USERS 테이블의 USER_ID 전체 카운팅 불가 --
SELECT R.CONTEST_ID
      ,ROUND(COUNT(DISTINCT R.USER_ID) / COUNT(DISTINCT U.USER_ID),2) AS PERCENTAGE
FROM USERS U 
	INNER JOIN REGISTER R ON U.USER_ID=R.USER_ID
GROUP BY R.CONTEST_ID
ORDER BY 2 DESC,1

 

개선 코드

-- 개선 코드 : 스칼라 서브쿼리 활용 -- 
SELECT contest_id
       ,ROUND(COUNT(DISTINCT user_id) * 100 / (SELECT COUNT(user_id) FROM Users), 2) AS percentage 
FROM Register
GROUP BY contest_id
ORDER BY percentage DESC, contest_id

• 앞선 쿼리에서 카티션 곱 사용으로 불필요한 행 추가 & 추가 필터링 필요 했음. 성능이 좋지않은 카티션 곱 제외 다른 방식으로 풀고자 함 
• 스칼라 서브쿼리 채택 
  • percentage 컬럼의 분모에는 `USERS 테이블의 전체 유저 수` 1개의 값이 필요함. 스칼라 서브쿼리 사용하여 전체 갯수 출력 후 계산 함
• 불필요한 행 추가 & 중복값 제거 

[KEY POINT & TIP]

🔑다른 테이블의 값(1개)만 가져올때 스칼라 (SELECT 절) 서브 쿼리 활용 할것 
🔑카티션곱은 불필요한 행 결합으로 중복값 필터링이 필요하며, 성능이 매우 낮음. 출력 시간도 오래 걸림.