241030_TIL

아무리 사소하더라도 배움이 없는 날은 없다.

SQL 문제풀이 복습

---

Leetcode

문제 링크
1트(7/23) 때는 불필요하게 쿼리가 복잡했네.
salary를 발라내기 위한 where절 내 서브쿼리 한 번이면 되고
그 외에는 딱히 서브쿼리나 cte를 안 써도 풀 수 있음

SELECT employee_id,
       NAME,
       salary,
       DENSE_RANK()
         OVER(
           ORDER BY salary ASC) AS "team_id"
FROM   Employees
WHERE  salary IN (SELECT salary
                  FROM   Employees
                  GROUP  BY 1
                  HAVING Count(*) >= 2)
ORDER  BY 4 ASC,
          1 ASC;

문제 링크

SELECT user_id,
       MAX(time_stamp) AS "last_stamp"
FROM   Logins
WHERE  YEAR(time_stamp) = '2020'
GROUP  BY 1;

문제 링크
저번 달에 1트(9/1)를 성공했는데도
오늘 풀려니까 정말 헷갈렸던 hard 문제.
풀이 과정을 자세히 복기해 보면,

1. 먼저 user_id의 크고 작음에 관계없이 모든 친구의 조합을 다 구해준다.

SELECT user1_id,
       user2_id
FROM   Friendship
UNION ALL
SELECT user2_id,
       user1_id
FROM   Friendship

2. 그리고 그 결과를 서브쿼리 a로 처리하고, Likes 테이블을 왼쪽 join한다.
   이 때 join의 기준은 a.user2_id = l.user_id이다.
   즉, 왼쪽에 있는 유저(a.user1_id)를 기준으로 그의 친구들(user2_id)이 좋아하는 페이지(page_id)를 보려는 것이다. user1_id와 page_id만 남기고 결과를 출력한다.

SELECT user1_id,
       page_id
FROM   (SELECT user1_id,
               user2_id
        FROM   Friendship
        UNION ALL
        SELECT user2_id,
               user1_id
        FROM   Friendship) a
       LEFT JOIN Likes l
              ON a.user2_id = l.user_id

3. 다시 이 결과를 서브쿼리 b로 처리하고 Likes 테이블(l2)을 한 번 더 왼쪽 join한다. join의 기준은 b.user1_id = l2.user_id와 b.page_id = l2.page_id 이렇게 2개다. 즉, 2번의 과정에서는 친구가 좋아하는 페이지 id를 구했다면, 이번에는 그 페이지 id가 자신이 좋아하는 페이지 id인지를 확인하는 과정이다.
   
   예를 들어, 6번 유저는 page_id 88을 좋아하는데, (2번에서 친구인 1도 88을 좋아함을 확인했고) 6번 유저 자신도 page_id 88을 좋아하므로 이 데이터는 결과 출력에서 제외되어야 한다.

SELECT *
FROM   (SELECT user1_id,
               page_id
        FROM   (SELECT user1_id,
                       user2_id
                FROM   Friendship
                UNION ALL
                SELECT user2_id,
                       user1_id
                FROM   Friendship) a
               LEFT JOIN Likes l
                      ON a.user2_id = l.user_id) b
       LEFT JOIN Likes l2
              ON b.user1_id = l2.user_id
                 AND b.page_id = l2.page_id 

4. 따라서, l2의 값들이 null인 경우만 where절로 골라 주고 user_id, page_id 기준으로 그루핑하면 결과가 완성된다.

SELECT user1_id AS "user_id",
       b.page_id,
       COUNT(*) AS "friends_likes"
FROM   (SELECT user1_id,
               page_id
        FROM   (SELECT user1_id,
                       user2_id
                FROM   Friendship
                UNION ALL
                SELECT user2_id,
                       user1_id
                FROM   Friendship) a
               LEFT JOIN Likes l
                      ON a.user2_id = l.user_id) b
       LEFT JOIN Likes l2
              ON b.user1_id = l2.user_id
                 AND b.page_id = l2.page_id
WHERE  l2.user_id IS NULL
GROUP  BY 1,
          2;

1트 때에도 그랬지만,
이걸 서브쿼리 없이 한 번의 쿼리로 완성시키려고 욕심을 내면
오히려 생각이 더 꼬이기 쉬운 것 같다. 그래서 1트 때도 cte를 만들어서
풀었던 게 아닐까 싶은데, 한 스텝씩 차근히 생각하면 결국 해결할 수 있는 문제다.

의외로 문제를 풀기 위해 필요한 스킬은
union이나 join처럼 그렇게까지 복잡하지는 않은 것들인데,
이것들을 어떤 로직으로 어떻게 연결할지를 생각해 내는 것이 더 중요하다.

---

프로그래머스

문제 링크
where 절 내에 서브쿼리를 넣으면 간단히 해결되는 문제.

SELECT food_type,
       rest_id,
       rest_name,
       favorites
FROM   rest_info
WHERE  ( food_type, favorites ) IN (SELECT food_type,
                                           Max(favorites) AS "max_fav"
                                    FROM   rest_info
                                    GROUP  BY 1)
ORDER  BY 1 DESC;

문제 링크

SELECT MEMBER_ID,
       MEMBER_NAME,
       GENDER,
       DATE_FORMAT(DATE_OF_BIRTH, '%Y-%m-%d') AS "DATE_OF_BIRTH"
FROM   MEMBER_PROFILE
WHERE  GENDER = 'W'
       AND MONTH(DATE_OF_BIRTH) = '3'
       AND TLNO IS NOT NULL
ORDER  BY 1;

문제 링크

SELECT i.REST_ID,
       i.REST_NAME,
       i.FOOD_TYPE,
       i.FAVORITES,
       i.ADDRESS,
       ROUND(AVG(REVIEW_SCORE), 2) AS "SCORE"
FROM   REST_INFO i
       JOIN REST_REVIEW r
         ON i.REST_ID = r.REST_ID
WHERE  ADDRESS LIKE '서울%'
GROUP  BY 1,
          2
ORDER  BY 6 DESC,
          4 DESC;

문제 링크

SELECT o.PRODUCT_ID,
       p.PRODUCT_NAME,
       SUM(AMOUNT * PRICE) AS "TOTAL_SALES"
FROM   FOOD_ORDER o
       JOIN FOOD_PRODUCT p
         ON o.PRODUCT_ID = p.PRODUCT_ID
WHERE  DATE_FORMAT(o.PRODUCE_DATE, '%Y-%m') = '2022-05'
GROUP  BY 1
ORDER  BY 3 DESC,
          1;

문제 링크
서브쿼리로 category와 max_price를 미리 구해놓고
where절을 써서 category도 과자, 국, 김치, 식용유로 특정해 두었다.
그 결과를 FOOD_PRODUCT 테이블과 (inner) join하게 되면
두 테이블에 공통적으로 겹치는 데이터만 남기 때문에
본 쿼리에서는 특별히 할 게 없음.

※ 서브쿼리에서 where절을 쓰는 것과
본 쿼리에서 where절을 쓰는 것에 차이가 많이 날까?

SELECT p.CATEGORY,
       MAX_PRICE,
       PRODUCT_NAME
FROM   FOOD_PRODUCT p
       JOIN (SELECT CATEGORY,
                    MAX(PRICE) AS "MAX_PRICE"
             FROM   FOOD_PRODUCT
             WHERE  CATEGORY IN ( '과자', '국', '김치', '식용유' )
             GROUP  BY 1) a
         ON p.CATEGORY = a.CATEGORY
            AND p.PRICE = a.max_price
ORDER  BY 2 DESC;