[SQL 코딩테스트] 리트코드 Basic Aggregate Functions 정답

[EASY] 620. Not Boring Movies
% / 연산자

 

조인 풀다가 넘어오니까 문제가 애교 수준으로 보인다..^^ 

나머지를 구하는 연산자 %는 처음 써봤다. 또 하나 배워간다!

SELECT ID, MOVIE, DESCRIPTION, RATING
FROM CINEMA
WHERE ID % 2 = 1
      AND DESCRIPTION <> 'BORING'
ORDER BY RATING DESC

 

[EASY] 1251. Average Selling Price
SUM /계산식

 

WITH의 편리함(?)에 빠져버린 나... 조인으로 더 쉽게 풀어내고 싶은데 어렵다.

WITH AVG_PRICE AS (SELECT P.PRODUCT_ID,
       IFNULL(ROUND(P.PRICE * U.UNITS,2),0)
       AS AVERAGE_PRICE,
       U.UNITS
FROM PRICES P
     LEFT JOIN UNITSSOLD U
     ON P.PRODUCT_ID = U.PRODUCT_ID
     AND U.PURCHASE_DATE BETWEEN P.START_DATE AND P.END_DATE
)

SELECT PRODUCT_ID, IFNULL(ROUND(SUM(AVERAGE_PRICE) / SUM(UNITS), 2),0) AS AVERAGE_PRICE
FROM AVG_PRICE
GROUP BY 1

 

다시 한 번... 그냥 WITH에 쓴 식을 괄호 안에 한 번만 더 합치면 되는건데 이렇게 구현이 되나? 하고 중간에 자꾸 생각이 멈춘다. GROUP BY 이해도의 문제일 수도. WITH 구문 안 거치고 최대한 한번에 써보기 연습.

SELECT P.PRODUCT_ID,
       IFNULL(ROUND(SUM(P.PRICE * U.UNITS)/SUM(UNITS),2),0)
       AS AVERAGE_PRICE
FROM PRICES P
     LEFT JOIN UNITSSOLD U
     ON P.PRODUCT_ID = U.PRODUCT_ID
     AND U.PURCHASE_DATE BETWEEN P.START_DATE AND P.END_DATE
GROUP BY 1

 

[EASY] 1075. Project Employees I 
AVG
⏰ 4:24

SELECT P.PROJECT_ID, ROUND(AVG(EXPERIENCE_YEARS),2) average_years
FROM PROJECT P
     JOIN EMPLOYEE E
     ON P.EMPLOYEE_ID = E.EMPLOYEE_ID
GROUP BY 1

 

[EASY] 1633. Percentage of Users Attended a Contest 
SUBQUARY
⏰ 10:41

 

용케 서브쿼리로 풀어냈다. 한 발짝 한 발짝 전진중!

SELECT CONTEST_ID, ROUND((COUNT(USER_ID) / 
        (SELECT COUNT(*) FROM USERS)) * 100, 2) AS PERCENTAGE
FROM REGISTER
GROUP BY CONTEST_ID
ORDER BY percentage DESC, contest_id ASC

 

[EASY] 1211. Queries Quality and Percentage
계산식
⏰ 12:27

SELECT QUERY_NAME, 
       ROUND(SUM(RATING / POSITION) / COUNT(RATING), 2) AS QUALITY,
       ROUND((COUNT(IF(RATING < 3, RATING, NULL)) 
       / COUNT(RATING) * 100), 2) AS poor_query_percentage
FROM QUERIES
WHERE QUERY_NAME IS NOT NULL
GROUP BY 1

 

[MEDIUM] 1193. Monthly Transactions I
COUNT / SUM / IF
⏰ 19:18

 

으아악... 이렇게 오래 걸릴 문제가 아니었는데. 복잡하게 생각해서 셀프조인까지 갔다가 다시 처음으로 돌아와서 심플하게 풀었다.

SELECT DATE_FORMAT(TRANS_DATE,'%Y-%m') AS month, country,
       COUNT(STATE) AS trans_count,
       COUNT(IF(STATE = 'APPROVED',STATE,NULL)) AS approved_count,
       SUM(AMOUNT) AS trans_total_amount,
       SUM(IF(STATE = 'APPROVED',AMOUNT,0)) AS approved_total_amount 
FROM TRANSACTIONS         
GROUP BY 1,2

 

[MEDIUM] 1174. Immediate Food Delivery II
COUNT / IF / IN / MIN
⏰ 9:25

 

프로그래머스 GROUP BY 챕터에서 배운 MAX의 활용법과... WHERE절의 튜플을 복습하며 풀었다.

SELECT ROUND(COUNT(IF(ORDER_DATE = customer_pref_delivery_date, ORDER_DATE, NULL))/COUNT(ORDER_DATE)*100,2) AS immediate_percentage 
FROM DELIVERY
WHERE (CUSTOMER_ID, ORDER_DATE) IN (
      SELECT CUSTOMER_ID, MIN(ORDER_DATE)
      FROM DELIVERY
      GROUP BY CUSTOMER_ID
)

 

[MEDIUM] 550. Game Play Analysis IV
COUNT / JOIN / INTERVAL
⏰ 21:20

 

지금까지 배운 걸 총동원해서 JOIN으로 풀었는데, 맞추고 난 뒤 다른 정답들을 구경하다보니 역시 더 간단한 방법이 있었다.

아무튼 문제를 맞닥뜨렸을 때 머리가 덜 복잡해지고 더 빨리 결과를 뽑을 수 있는 방법을 바로 떠올릴 수 있도록 다양한 경우의 수를 계속 머리에 집어넣어보자.

 

[정답 1] JOIN 활용하기

SELECT ROUND(COUNT(A2.PLAYER_ID)/COUNT(A1.PLAYER_ID),2) AS FRACTION
FROM ACTIVITY A1
     LEFT JOIN (SELECT PLAYER_ID, EVENT_DATE
     FROM ACTIVITY
     ) A2
     ON A1.EVENT_DATE + INTERVAL 1 DAY = A2.EVENT_DATE
     AND A1.PLAYER_ID = A2.PLAYER_ID
WHERE (A1.PLAYER_ID, A1.EVENT_DATE) IN (
    SELECT PLAYER_ID, MIN(EVENT_DATE)
    FROM ACTIVITY
    GROUP BY PLAYER_ID)

 

[정답 2] SELECT.문 계산 시에 서브쿼리 활용하기

SELECT ROUND(COUNT(DISTINCT PLAYER_ID) / (SELECT COUNT(DISTINCT PLAYER_ID) FROM ACTIVITY), 2) AS FRACTION
FROM ACTIVITY
WHERE (PLAYER_ID, EVENT_DATE) IN (
    SELECT PLAYER_ID, MIN(EVENT_DATE) + INTERVAL 1 DAY AS FIRST_DATE
    FROM ACTIVITY
    GROUP BY PLAYER_ID
)

 

---

 

생각보다 계산식이 현업에서 가장 많이 쓰이는 형식 중 하나일 수도 있겠다는 생각이 든다.
조금만 생각해봐도 클릭률, 접속률, 객단가 등등 대부분의 지표가 곱셈 나눗셈으로 이루어져 있으니까. 

숫자의 영역에서는 항상 더 긴장하게 된다.

 

오늘은 EASY만으로도 탈탈 털려서 MEDIUM은 미래의 나에게 맡긴다... 힘내... 조금 더 숙련된 뒤에는 더 쉽게 풀릴거야... (그러길 바래...) 화이팅!