구름 아카이브

https://leetcode.com/problems/patients-with-a-condition/description/

replace, substring, concat, left, right, lower, upper, trim, length, format, instr 정도만 알아도 SQL 데이터 변형에 큰 문제가 없겠지만, Regex 문법 까지 익숙해지면 빠른 전처리에 큰 도움이 될 것 같다.

Write a solution to find the patient_id, patient_name, and conditions of the patients who have Type I Diabetes. Type I Diabetes always starts with DIAB1 prefix.

Return the result table in any order.

The result format is in the following example.

Example 1:

Input: 
Patients table:
+------------+--------------+--------------+
| patient_id | patient_name | conditions   |
+------------+--------------+--------------+
| 1          | Daniel       | YFEV COUGH   |
| 2          | Alice        |              |
| 3          | Bob          | DIAB100 MYOP |
| 4          | George       | ACNE DIAB100 |
| 5          | Alain        | DIAB201      |
+------------+--------------+--------------+
Output: 
+------------+--------------+--------------+
| patient_id | patient_name | conditions   |
+------------+--------------+--------------+
| 3          | Bob          | DIAB100 MYOP |
| 4          | George       | ACNE DIAB100 | 
+------------+--------------+--------------+
Explanation: Bob and George both have a condition that starts with DIAB1.

#  conditions 열에 'DIAB1'이라는 문자열을 포함하는 모든 행을 선택

SELECT * 
FROM patients 
WHERE conditions REGEXP '\\bDIAB1'

https://leetcode.com/problems/average-selling-price/description/

15번째 케이스에서 Prices 테이블의 product_id가 3이상으로 추가되면서,

UnitSold 테이블에 없는 데이터 값도 결과값에 으로 나타나야 했기에 INNER 를 LEFT로 수정하고,

조건 결과값으로, Null값도 함께 나타날 수 있도록 WHERE문 끝에 "or UnitsSold.product_id IS NULL" 을 이어붙인 뒤 COALESCE를 ROUND 앞에 씌워 NULL값 처리를 해야했다.

잠깐 생각하게 만들었던 문제

다음부턴 예시에 보이지 않는 테스트 케이스를 예상해서 작성해봐야겠다.

*** JOIN에 AND 조건을 덧붙이는 방식보다, WHERE로 조건을 추가 작성하는 것이 확실히 쿼리속도가 더 빨랐다.

Write a solution to find the average selling price for each product. average_price should be rounded to 2 decimal places.

Return the result table in any order.

The result format is in the following example.

Example 1:

Input: 
Prices table:
+------------+------------+------------+--------+
| product_id | start_date | end_date   | price  |
+------------+------------+------------+--------+
| 1          | 2019-02-17 | 2019-02-28 | 5      |
| 1          | 2019-03-01 | 2019-03-22 | 20     |
| 2          | 2019-02-01 | 2019-02-20 | 15     |
| 2          | 2019-02-21 | 2019-03-31 | 30     |
+------------+------------+------------+--------+
UnitsSold table:
+------------+---------------+-------+
| product_id | purchase_date | units |
+------------+---------------+-------+
| 1          | 2019-02-25    | 100   |
| 1          | 2019-03-01    | 15    |
| 2          | 2019-02-10    | 200   |
| 2          | 2019-03-22    | 30    |
+------------+---------------+-------+
Output: 
+------------+---------------+
| product_id | average_price |
+------------+---------------+
| 1          | 6.96          |
| 2          | 16.96         |
+------------+---------------+
Explanation: 
Average selling price = Total Price of Product / Number of products sold.
Average selling price for product 1 = ((100 * 5) + (15 * 20)) / 115 = 6.96
Average selling price for product 2 = ((200 * 15) + (30 * 30)) / 230 = 16.96

SELECT Prices.product_id
    --   ,start_date
    --   ,end_date
    --   ,price
    --   ,SUM(units)
    --   ,SUM(price*units) AS mutiply_price_units
      ,COALESCE(ROUND(SUM(price*units)/SUM(units),2),0) AS average_price

FROM Prices
     LEFT JOIN UnitsSold ON Prices.product_id = UnitsSold.product_id
WHERE UnitsSold.purchase_date BETWEEN Prices.start_date AND Prices.end_date or UnitsSold.product_id IS NULL
GROUP BY product_id

https://leetcode.com/problems/movie-rating/description/

포트폴리오 쓴다고, 그동안 쿼리연습을 못했다.

이전 기억 상기시키기에 좋은 쉬운문제

Example 1:

Input: 
Movies table:
+-------------+--------------+
| movie_id    |  title       |
+-------------+--------------+
| 1           | Avengers     |
| 2           | Frozen 2     |
| 3           | Joker        |
+-------------+--------------+
Users table:
+-------------+--------------+
| user_id     |  name        |
+-------------+--------------+
| 1           | Daniel       |
| 2           | Monica       |
| 3           | Maria        |
| 4           | James        |
+-------------+--------------+
MovieRating table:
+-------------+--------------+--------------+-------------+
| movie_id    | user_id      | rating       | created_at  |
+-------------+--------------+--------------+-------------+
| 1           | 1            | 3            | 2020-01-12  |
| 1           | 2            | 4            | 2020-02-11  |
| 1           | 3            | 2            | 2020-02-12  |
| 1           | 4            | 1            | 2020-01-01  |
| 2           | 1            | 5            | 2020-02-17  | 
| 2           | 2            | 2            | 2020-02-01  | 
| 2           | 3            | 2            | 2020-03-01  |
| 3           | 1            | 3            | 2020-02-22  | 
| 3           | 2            | 4            | 2020-02-25  | 
+-------------+--------------+--------------+-------------+
Output: 
+--------------+
| results      |
+--------------+
| Daniel       |
| Frozen 2     |
+--------------+
Explanation: 
Daniel and Monica have rated 3 movies ("Avengers", "Frozen 2" and "Joker") but Daniel is smaller lexicographically.
Frozen 2 and Joker have a rating average of 3.5 in February but Frozen 2 is smaller lexicographically.

-- 1. 가장 많은 수의 영화에 평점 매긴 유저 이름 반환, 동률일경우 알파벳순으르 빠른사람 상위 1명
-- 2. 2020년 2월, 평균평점이 가장 높은 영화이름 동률일경우 알파벳순으르 빠른영화 상위 1개
-- 3. 위/아래 테이블 병합



WITH CTE AS(
                SELECT MovieRating.rating
                      ,MovieRating.created_at
                      ,Users.name
                      ,Movies.title
                
                FROM MovieRating
                     INNER JOIN Movies ON MovieRating.movie_id=Movies.movie_id
                     INNER JOIN Users ON MovieRating.user_id=Users.user_id
            )

(SELECT name AS results
FROM CTE
GROUP BY name
ORDER BY count(name) DESC, name
LIMIT 1)


UNION ALL


(SELECT title AS results
FROM CTE
WHERE DATE_FORMAT(created_at, "%Y-%m") = "2020-02"
GROUP BY title
ORDER BY AVG(rating) DESC, title
LIMIT 1)

https://leetcode.com/problems/game-play-analysis-iv/description/

미뤄뒀던 포스팅을 이제야.... 한 20개 더 밀려있다.

기존의 511. Game Play Analysis I 와 유사한 문제

최초 접속 후, 다음날 다시 접속한 게임플레이어의 비율을 구하면 된다. 

Example 1:

Input: 
Activity table:
+-----------+-----------+------------+--------------+
| player_id | device_id | event_date | games_played |
+-----------+-----------+------------+--------------+
| 1         | 2         | 2016-03-01 | 5            |
| 1         | 2         | 2016-03-02 | 6            |
| 2         | 3         | 2017-06-25 | 1            |
| 3         | 1         | 2016-03-02 | 0            |
| 3         | 4         | 2018-07-03 | 5            |
+-----------+-----------+------------+--------------+
Output: 
+-----------+
| fraction  |
+-----------+
| 0.33      |
+-----------+
Explanation: 
Only the player with id 1 logged back in after the first day he had logged in so the answer is 1/3 = 0.33

SELECT ROUND(COUNT(DISTINCT player_id) / (SELECT COUNT(DISTINCT player_id) FROM Activity), 2) AS fraction
FROM Activity
WHERE (player_id, DATE_SUB(event_date, INTERVAL 1 DAY))
  IN (SELECT player_id
           , MIN(event_date) AS first_login 
      FROM Activity 
      GROUP BY player_id)

idea

1. WHERE문의 IN 속에, player_id 별 최초접속일을 모아둔다. 

2. event_date에서 하루를 뺀 값이 IN 값에 들어 있는 최초접속일과 일치하는 player_id를 찾도록 WHERE문 조건을 최종 작성한다(최초 접속 후 다음날 접속한 기록이 있다면, 해당 접속 일자에서 -1을 빼면 최초접속일이 될 것이다)

3. WHERE문을 통해 걸러진 플레이어 수/전체 플레이어 수

https://leetcode.com/problems/game-play-analysis-i/

쉬운 문제이지만, 윈도우 함수를 간단히 활용해보기에 좋은 문제

# 플레이어의 최초 로그인 날짜 구하기

1) 기본 풀이

SELECT player_id
     , MIN(event_date) AS first_login
FROM Activity
GROUP BY player_id




# 플레이어의 최초 로그인 날짜 구하기

2) 윈도우 함수 + WITH 문 활용

WITH login AS 
(
     SELECT ROW_NUMBER() OVER (PARTITION BY player_id ORDER BY event_date) AS player_login
          , player_id
          , event_date AS first_login
     FROM Activity
)

SELECT player_id
     , first_login
FROM login
WHERE player_login =1

https://leetcode.com/problems/restaurant-growth/description/

WITH문을 2번 중첩하여 문제를 풀이했었고,
내 풀이만 다른 분들과 달랐기에, 해결과정이 기억에 많이 남았던 문제 중 하나였다.

 
지금은 단순 풀이를 위한 쿼리작성이지만, 나중에는 쿼리 최적화 관점에서도 작성해보면 좋을 것 같다.

-> 쿼리 최적화 관련자료

1321. Restaurant Growth -> 이전 6일 ~ 당일 amount 합산

# 방문일별 계산
WITH grouped_visit_on AS 
(
        SELECT visited_on 
             , SUM(amount) AS amount
        FROM Customer
        GROUP BY visited_on
)
, visit_7 AS 
(    
# 방문일별 이전 6일 ~ 0일 까지 합산 및 AVG 계산, Named window
        SELECT visited_on
            , SUM(amount) OVER (ORDER BY visited_on ROWS BETWEEN 6 PRECEDING AND 0 FOLLOWING) AS amount 
            , ROUND(AVG(amount) OVER (ORDER BY visited_on ROWS BETWEEN 6 PRECEDING AND 0 FOLLOWING),2) AS average_amount
        FROM grouped_visit_on
)

# 최초 구매일로부터 6일 이후의 값들만 출력 == 1월 7일 부터
SELECT visited_on
     , amount
     , average_amount
FROM visit_7
WHERE visited_on >= (SELECT DATE_ADD(MIN(visited_on), INTERVAL 6 DAY) FROM customer)

# 1월 7일 -> WHERE 조건절 서브쿼리 삽입
#SELECT DATE_ADD(MIN(visited_on), INTERVAL 6 DAY

https://leetcode.com/problems/consecutive-numbers/description/

기존에 테이블 조인 방식으로 풀었던 문제이지만, LEAD() 를 활용해서도 풀어볼 수 있었다.
큰 차이는 없지만, 윈도우함수를 활용하는 방법이 조금 더 직관적으로 이해하기 좋았음.

1) 윈도우 함수 풀이: LEAD

SELECT DISTINCT l.NUM AS ConsecutiveNums
FROM(
     SELECT NUM
          , LEAD(NUM,1) OVER (ORDER BY id) AS next
          , LEAD(NUM,2) OVER (ORDER BY id ) AS afternext
     FROM logs
 ) l
WHERE l.Num=next AND l.next=l.afternext




2) JOIN : 기존에 풀었던 방식

SELECT DISTINCT l.num AS ConsecutiveNums
FROM logs AS l
           INNER JOIN logs AS l_next ON l.id + 1 = l_next.id
           INNER JOIN logs AS l_next2 ON l.id + 2 =  l_next2.id

WHERE l.num = l_next.num AND l_next.num = l_next2.num

https://leetcode.com/problems/department-top-three-salaries/

습득한 점:

SELECT val
     , ROW_NUMBER() OVER (ORDER BY val) AS row_number
     , RANK() OVER (ORDER BY val) AS rank
     , DENSE_RANK() OVER (ORDER BY val) AS dense_rank
FROM Sample;

- row_number는 어떻게 해서든 123456 으로 순위 매김 (중복포함)

- rank는 동일 값에 같은 등수를 부여, 공동 등수의 갯수를 고려한, 다음 순위를 매김.
  아래 예시의 경우에는 2등과 5등은 스킵한 뒤 순위를 매김

- dense_rank는 공동 등수를 하나로 보고 다음 순위를 매김

문제:

A company's executives are interested in seeing who earns the most money in each of the company's departments. A high earner in a department is an employee who has a salary in the top three unique salaries for that department.

Write a solution to find the employees who are high earners in each of the departments.

Return the result table in any order.

요약: # 각 부서에서 직원들의 급여 순으로, 부서별 1위 2위 3위 찾기 (급여가 같은 사람이 2명이상이라면 모두 포함시키기)

문제파악:

풀이:

난이도가 hard 이지만 어렵진 않았다.

전체적인 틀은 같지만,

FROM 절에 넣어도 되고 WITH 절에 넣어도 괜찮을 것 같다.

1) WITH절 활용

WITH COUNTER AS (
      SELECT Dep.name DepName
          , Emp.name EmpName
          , salary
          , DENSE_RANK() OVER (PARTITION BY departmentid ORDER BY salary DESC) AS denserank
      FROM Employee AS Emp
          INNER JOIN Department AS Dep ON Dep.id = Emp.departmentId
                 )

SELECT DepName AS Department
     , EmpName AS Employee
     , salary
FROM COUNTER
WHERE denserank <=3

2) FROM절 서브쿼리 활용

SELECT t.department
     , t.employee
     , t.salary
     
FROM(
    SELECT department.name AS dapartment
        , employee.name AS employee
        , employee.salary
        , DENSE_RANK() OVER (PARTITION BY departmentid ORDER BY salary DESC) AS dr

    FROM Employee
        INNER JOIN dapartment ON employee.departmentId = dapartment.id
    ) t
WHERE t.dr <=3

https://leetcode.com/problems/trips-and-users/description/

습득한 점:

SUM함수의 새로운 표현법을 알 수 있었다.

문제:

The cancellation rate is computed by dividing the number of canceled (by client or driver) requests with unbanned users by the total number of requests with unbanned users on that day.

Write a solution to find the cancellation rate of requests with unbanned users (both client and driver must not be banned) each day between "2013-10-01" and "2013-10-03". Round Cancellation Rate to two decimal points.

Return the result table in any order.

문제파악:

10/1 ~ 10/3 까지 일자별 여행 취소율을 구하는 문제이다.

취소율은

dividing the number of canceled (by client or driver) requests with unbanned users by the total number of requests with unbanned users on that day.

쿼리 작성 중, 테스트 케이스가 통과 안되는 건이 하나 있었다.

테스트 케이스가 참 이상했다.

저 Input대로 라면, 전체 요청건수는 1개, 그중 취소 요청건수는 1개라 취소율은 100%여야 함에도 불구하고,

Expected에는 아예 값 자체가 보이지 않았다....

그래서 그냥 취소율이 1이 되는 경우는 아예 발생하지 않도록 쿼리를 일부 수정했다.

1. banned 컬럼 값이 No 인 값들의 user_id만 찾을 수 있도록 서브쿼리 작성

2. 만들어 둔 서브 쿼리 자체를 WHERE 조건으로 활용      

 - clinet_id, driver_id 의 컬럼 값들 중, 서브쿼리로 만들어지는 user_id와 일치하는 조건에 만족하는 SELECT 값을 찾을 수 있도록!

(Trips 테이블의 clinet_id, driver_id //// Users 테이블의 user_id는 foreign key 이다)

3. Between 활용하여, 문제에서 원하는 날짜 지정

4. 날짜 별 취소율을 구하는 것이기 때문에 Group by를 request_at기준으로 지정

5. 취소율을 구할 수 있도록,  완료건이 아닌 취소건의 갯수/ status의 총 갯수 를 구한 뒤, Round 활용해서 소수점 처리

6. 정렬조건은 필요없다고 나와있음

- With 구문을 활용하면 서브쿼리를 2번 적지 않을 수 있다.

- Alias 지정할 때, 중간에 빈칸이 있으면 ' ' 으로 묶어줘야 한다.

풀이:

SELECT request_at AS DAY
     , ROUND(SUM(status != 'completed') / COUNT(status),2) AS 'Cancellation Rate'
      
FROM Trips
WHERE client_id IN ( 
                  SELECT users_id
                  FROM Users
                  WHERE banned = 'NO'
                  )
AND driver_id IN ( 
                  SELECT users_id
                  FROM Users
                  WHERE banned = 'No'
                    )
AND request_at BETWEEN DATE("2013-10-01") AND DATE ("2013-10-03")
GROUP BY request_at

 https://school.programmers.co.kr/learn/courses/30/lessons/59045

습득한 점:

WHERE ~ IN () 처럼, LIKE IN () 도 가능할까 싶었는데, 불가능했다.

대신 아래처럼 AND를 전체로 묶은 뒤, 안쪽을 OR로 엮어줄 수는 있었다.

...

WHERE SEX_UPON_INTAKE LIKE 'Intact%'
AND (SEX_UPON_OUTCOME LIKE 'Spayed%' OR SEX_UPON_OUTCOME LIKE 'Neutered%')

...

문제:

보호소에 들어올 당시에는 중성화1되지 않았지만,

보호소를 나갈 당시에는 중성화된 동물의 아이디와 생물 종, 이름을 조회하는 아이디 순으로 조회하는 쿼리 작성.

즉, 들어올 땐, 중성화X, 나갈땐 중성화O 에 해당하는 데이터를 찾으면 된다.

문제파악:

처음 문제 딱 보고, 보호소에서 나가는 동물 중에 중성화 안된 동물이 있는지 궁금해서 찾아보니 있었다.

## 보호소에서 나가는 동물 중, 중성화 안된 동물 2마리 확인

SELECT *
FROM ANIMAL_OUTS
WHERE SEX_UPON_OUTCOME LIKE 'Intact%'

결국은 들어올 때의 SEX_UPON_INTAKE 컬럼은 "Intacts%" 이고,

동시조건으로 나갈 때의 SEX_UPON_OUTCOME 컬럼은 "Sprayed% 혹은 "Neutral%" 인 데이터를 찾으면 된다.

ANIMAL_ID 기준으로 테이블 2개 조인 후, WHERE 조건에 위의 2개 항목 엮어준 뒤 ORDER BY 까지 작성해주면 된다.

풀이:

보호소에서 나가는 동물 중, 중성화 안된 동물 2마리 확인

SELECT *
FROM ANIMAL_OUTS
WHERE SEX_UPON_OUTCOME LIKE 'Intact%'





찾는 데이터: 들어올 땐, 중성화X, 나갈땐 중성화 O

SELECT INS.ANIMAL_ID
     , INS.ANIMAL_TYPE
     , INS.NAME
#     , SEX_UPON_INTAKE
#     , SEX_UPON_OUTCOME
FROM ANIMAL_INS AS INS
     INNER JOIN ANIMAL_OUTS AS OUTS ON INS.ANIMAL_ID = OUTS.ANIMAL_ID


WHERE SEX_UPON_INTAKE LIKE 'Intact%'
AND (SEX_UPON_OUTCOME LIKE 'Spayed%' OR SEX_UPON_OUTCOME LIKE 'Neutered%')
ORDER BY ANIMAL_ID


혹은 AND SEX_UPON_OUTCOME NOT LIKE 'Intact%'도 가능할 것 같다.

https://school.programmers.co.kr/learn/courses/30/lessons/131116

습득한점:

*** LIMIT은 서브쿼리에 들어가면 쿼리 실행자체가 안된다는 점

*** GROUP BY된 상태로 SELECT 함수 인자로 MAX func 활용할 때,
      각 그룹의 첫번째 값이 끌려올 뿐, MAX func가 적용되지 않는 점

문제:

FOOD_PRODUCT 테이블에서 식품분류별로 가격이 제일 비싼 식품의 분류, 가격, 이름을 조회하는 쿼리작성.

이때 식품분류가 '과자', '국', '김치', '식용유'인 경우만 출력.

결과는 식품 가격을 기준으로 내림차순 정렬.

문제파악:

각 카테고리(식품분류)에서 최대값을 나타내는 식품명(PRODUCT_NAME)을 찾아야 하는데,

'면' 그룹내 PRICE 값이 같은 항목이 존재함. 그룹별 최대값으로서 PRICE 값은 4950원으로 서로 같지만, 식품명은 다른 상황.

"이때 식품분류가 '과자', '국', '김치', '식용유'인 경우만 출력" 라는 조건이 없었다면, 이 문제는 잘못 설계된 문제

풀이:

처음에는 고유값 PRODUCT_ID를 활용해서 PRICE 값 기준 내림차순 정렬하여 첫번째 값만 찾은 뒤,
그렇게 찾은 PRODUCT_ID를 WHERE 절에 활용하고 싶었지만...

서브쿼리에는 LIMIT 를 사용할 수 없더라.

LIMIT을 억지로 감싸는 방법이 있는 것 같지만, 코드 가독성이 안좋을 것 같다.

결국 그룹별 PRICE 최대값을 찾아, 이를 조건에 넣어 매칭시키기로 했다.

각 식품분류(CATEGORY)별 MAX(PRICE) 값만 추출 후,

이를 첫번째 일치 조건으로 활용하기 위해 서브쿼리로 감싸서 WHERE 구문에 삽입

두번째 식품분류 일치 조건은 AND CATEGORY IN ( '과자', '국', 김치', '식용유' ) 으로 엮여주기

    조건1: 추출한 MAX(PRICE)값이 FOOD_PRODUCT 테이블의 PRICE 값과 같은지

    조건2: 식품 분류가 '과자', '국', '김치', '식용유' 해당하는지

마지막으로 내림차순 쿼리까지 잘 작성해주면 된다.

SELECT CATEGORY
      ,PRICE AS MAX_PRICE
      ,PRODUCT_NAME
FROM FOOD_PRODUCT
WHERE PRICE IN  
     ( 
     SELECT MAX(PRICE)
     FROM FOOD_PRODUCT
     GROUP BY CATEGORY
     )
AND CATEGORY IN ('과자', '국', '김치', '식용유')
ORDER BY MAX_PRICE DESC

습득한점:

조회수의 최대값을 찾아야 할 때, 나는 당연히 최대값을 찾고, BOARD_ID를 MAX(VIEW) 를 SELECT 문에 함께 붙이면,
RDBMS이기에 자동으로 조회수가 최대값인 로우의 BOARD_ID를 뽑아낼 수 있을 줄 알았지만 아니었다.

그렇게 친절하지는 않은 듯 하다.

MAXVIEW는 MAXVIEW대로나오고,1줄짜리 row이니,

나머지 데이터들은 전체데이터의 첫번째열을 반환하는 것 같았다.

mysql의 새로운 특성을 파악했다.

문제:

USED_GOODS_BOARD와 USED_GOODS_FILE 테이블에서

조회수가 가장 높은 중고거래 게시물에 대한 첨부파일 경로를 조회하는 쿼리 작성.

첨부파일 경로는 FILE ID를 기준으로 내림차순 정렬

기본적인 파일경로는 /home/grep/src/ 이며, 게시글 ID를 기준으로 디렉토리가 구분되고,

파일이름은 파일 ID, 파일 이름, 파일 확장자로 구성되도록 출력

* 조회수가 가장 높은 게시물은 하나만 존재

문제파악:

게시물 서두에서 mysql의 새로운 특성을 파악했기에,

BOARD 테이블에서 VIEW의 최댓값만을 찾은 뒤,

이를 다시 BOARD 테이블에 WHERE조건으로 넣고 이에 해당하는 BOARD_ID를 찾으면 된다.

즉 서브쿼리를 겹겹으로 사용하면 된다.

또 이 문제에서는 새롭게, 컬럼의 내용을 이어붙여야 하는 조건이 있었다.

CONCAT(A, B, C) 이런식으로 필요한 컬럼을 이어 붙였다.

컬럼 외에 기재가 필요한 문자열의 경우 '' 혹은 ' 로 반드시 감싸줘야 한다.

DB언어 외에도 JAVA던 Python이던 마찬가지이다.

보통 문법적으로 의미를 가지는 경우에는 ' ' 를 활용하고

이외 별 의미가 없는 일반 문자열은 " "를 사용하면 된다.

사실 두개 중 어떤 것을 쓰던 상관이 없는데, 회사에 입사하면 개발자들끼리 관습적으로 정해놓는 룰이 있다.

이에 맞춰서, 혹은 혼자서만 사용한다면 개인 취향껏 사용하면 된다.

그 외는 문제가 시키는 데로 하면 된다.

풀이:

SELECT CONCAT('/home/grep/src/', BOARD_ID, '/', FILE_ID, FILE_NAME, FILE_EXT) AS FILE_PATH
FROM USED_GOODS_FILE
WHERE BOARD_ID = (

                SELECT BOARD_ID
                FROM USED_GOODS_BOARD
                WHERE VIEWS = (
                                WITH MAX_VIEWS AS (
                                     SELECT MAX(VIEWS) AS VIEW
                                     FROM USED_GOODS_BOARD   
                                                  )
                                SELECT *
                                FROM MAX_VIEWS   
                                )
                  )
ORDER BY FILE_ID DESC

*** WHERE 조건에 들어가는 WITH 문 대신에,

    SELECT MAX(VIEWS) FROM USED_GOODS_BOARD를 사용해도 된다.

습득한 점:

이제 Lv 4 문제도 3문제정도 밖에 남지 않아서, 벌써부터 난이도가 쉬워지고 있다.
그동안 5 난이도 부터 역순으로 문제풀어서, 3 난이도 문제 차례인데, 정답률 80%이하 인 것들만 풀어야 할듯 하다.

더불어, 윈도우 함수(LEAD, LAG, RANK, DENSE_RANK, ROW_NUMBER 등등) 혹은 사용자 정의 함수를 활용하는 문제가
프로그래머스에는 없는 듯해서, 3 난이도를 다 풀고나면 리트코드나 해커랭크 문제를 풀어야 할 것 같다.

아니면 Data Camp 라는 곳도 알아봐야겠다. (유료라 들었는데)

문제:

FOOD_PRODUCT와 FOOD_ORDER 테이블에서

생산일자가 2022년 5월인 식품들의 식품 ID, 식품 이름, 총매출을 조회하는 쿼리 작성

결과는 총매출을 기준으로 내림차순 정렬,

총매출이 같다면 식품 ID를 기준으로 오름차순 정렬

문제파악:

Product 테이블은 총 데이터 row 갯수가 40줄

Order 테이블은 총 데이터 row 갯구가 30줄 이었다.

Product 테이블의 상품 id가 고유값으로 적힌 테이블이라,

Order 테이블이 데이터 row 수가 더 많을 줄 알았는데, 아니었다.

따라서 전체 상품 중 일부 상품만 있는 경우 + 그 일부의 상품 중 상품 id값의 중복인 데이터가 있는 경우

이 2가지만 고려해서 문제를 풀면 된다.

LEFT 조인이 필요한 상황은 아니다.
INNER 조인 후, 2022년 5월의 데이터만 WHERE 조건에 넣고, 총 매출을 계산하는 "TOTAL_SALES" 컬럼을 새로 추가한다.

여기까지 작성 후, 코드를 실행하면 상품id가 중복되는 데이터 row가 존재 할 수 있는 결과값이 나온다.

문제에서 원하는 값은 상품 id가 중복되는 데이터들은 총 매출액을 합산해야 한다.

예를 들어,

5월 10일에 참치캔 총 매출액이 350,000원이고, 5월 21일에 참치캔 총 매출액이 200,000원 이라면

결과값으로 나와야 하는 5월의 참치캔 총 매출액은 550,000원으로 계산되어야 하는 식이다.

고로 GROUP BY로 PRODUCT_ID를 묶어주고, 이에 대한 합산을 위해

PRICE*AMOUNT를 SUM으로 감싸준다.   

PRICE*AMOUNT AS TOTAL_SALES 

>>> SUM(PRICE*AMOUNT) AS TOTAL_SALES

정렬조건은 문제에 쓰여진 그대로 기재하면 된다.

풀이:

SELECT FP.PRODUCT_ID
     , FP.PRODUCT_NAME
     , SUM(PRICE*AMOUNT) AS TOTAL_SALES
     
FROM FOOD_PRODUCT AS FP
     INNER JOIN FOOD_ORDER AS FO ON FP.PRODUCT_ID = FO.PRODUCT_ID
WHERE PRODUCE_DATE LIKE '2022-05%'

GROUP BY PRODUCT_ID
ORDER BY TOTAL_SALES DESC
       , PRODUCT_ID ASC

습득한 점:

서울에 위치한 식당들의 식당 ID, 식당 이름, 음식 종류, 즐겨찾기수, 주소, 리뷰 평균 점수를 조회하는 쿼리를 작성하는 것이니
조회되는 쿼리는 모두 서울에 위치한 식당이어야 하고,

예상되는 테이블 형태는 아래와 같으므로 (적기만 하면 되는 컬럼은 제외했음)

문제에서는 식당 이름에 대해서 그룹화 하란 말이 없었지만, 결국은 식당 별 리뷰 평균을 구하는 것과 같다.

고로, 문제에서 ~~~ 그룹 별 이라는 말이 없어도, 문제에서 원하는 테이블 형태를 예상해서

원하는 컬럼에 GROUP BY 를 붙여 계산해도 된다.

문제:

REST_INFO와 REST_REVIEW 테이블에서

서울에 위치한 식당들의 식당 ID, 식당 이름, 음식 종류, 즐겨찾기수, 주소, 리뷰 평균 점수를 조회하는 쿼리 작성.

리뷰 평균점수는 소수점 세 번째 자리에서 반올림,

결과는 평균점수를 기준으로 내림차순 정렬,

평균점수가 같다면 즐겨찾기수를 기준으로 내림차순 정렬

문제파악:

리뷰 평균점수를 구하는 산술계산 이외에는 크게 어려운 점이 없는 문제였다.

식당 별로 그룹화하라는 언급은 없었지만 REST_ID 별로 그룹화 한 뒤, REVIEW_SCORE에 대해 평균값을 계산하면 된다.

또한 테이블을 붙이는 겸, WITH 구문+ LEFT 함수 활용해서 미리 서울에 위치하는 식당만 뽑을 수 있도록 테이블을 새로 만들고 이를
REST_REVIEW 테이블과 조인했다.

WHERE 구문에는 LEFT를 사용했는데, ADDRESS 컬럼의 앞 글자 2개만 따기 위해 사용했다.

전체 문자열 중 "서울"이 들어가는 조건을 활용하는 경우에,

만약 행정구역 이름 (~구 혹은 ~로) 이후에 이어지는 명칭들에서 "서울"이 들어가 있으면

서울시는 아니지만 빌딩 이름 혹은 지점이름에 "서울"이 들어간다는 이유로 결과값이 걸려들 수 있어서 LEFT를 사용했다.

실제로 부산에도 서울빌딩이라는 건물이 있고,

서울에도 마곡 광안리라는 음식점이 있다.

아래의 아브뉴프랑이 아닌, "아브뉴서울" 이라고 적혀있으면 결과값에 걸려드는 것이다.

다소 억지스럽겠지만, 나는 절대 데이터를 믿으면 안된다고 생각한다.

풀이:

WITH SEOUL_REST AS (
    SELECT *
    FROM REST_INFO
    WHERE LEFT(ADDRESS,2) ='서울'
                )

SELECT SR.REST_ID
     , REST_NAME
     , FOOD_TYPE
     , FAVORITES
     , ADDRESS
     , ROUND(AVG(REVIEW_SCORE),2) AS SCORE
     
FROM SEOUL_REST AS SR
     INNER JOIN REST_REVIEW AS RR ON SR.REST_ID = RR.REST_ID

GROUP BY SR.REST_ID
ORDER BY SCORE DESC

습득한 점:

이전에는 테이블을 보이는데로 조인했다면,
지금은 테이블이 조인되었을 때의 형태를 머릿속으로 고려하면서 쿼리를 구성할 수 있게되었다.

문제:

USER_INFO 테이블과 ONLINE_SALE 테이블에서 년, 월, 성별 별로 상품을 구매한 회원수를 집계하는 쿼리문 작성

결과는 년, 월, 성별을 기준으로 오름차순 정렬.

이때, 성별 정보가 없는 경우 결과에서 제외.

문제파악:

크게 어려워 보이는 부분은 없었다.

다만 "동일한 날짜, 회원 ID, 상품 ID 조합에 대해서는 하나에 판매 데이터만 존재한다" 이 언급만 조금 더 고려하면 되었다.

ONLINE_SALE만을 볼때, 날짜, 회원ID, 상품ID 조합에 대해서는 고유의 판매데이터만 존재한다고 했지만,
ONLINE_SALE 테이블과 USER_INFO 테이블이 합쳐졌을 때의 상황에서는 중복데이터가 생겨날 수 있음

(USER_ID 마다 성별은 다르게 설정될테니 제외, 나머지 AGE(나이)나 JOINED(가입일)로 인해 중복데이터가 발생될 수는 있을 것 같다)

해서, USER_ID를 카운트 할때는 DISTINCT가 필요하다.

풀이:

SELECT YEAR(sales_date) AS YEAR
     , MONTH(sales_date) AS MONTH
     , gender AS GENDER
     , COUNT(DISTINCT OS.USER_ID) AS USERS
     
FROM ONLINE_SALE AS OS
     INNER JOIN USER_INFO AS UI ON OS.USER_ID = UI.USER_ID
WHERE GENDER IS NOT NULL       
GROUP BY 1,2,3
ORDER BY 1 ASC ,2 ASC ,3 ASC

링크: https://gooreumwave.tistory.com/43

습득한 점: 

단순히 코드를 붙여넣기 하기보단

문제를 풀기 위해 생각했었던 중간과정을 기록해보는 것이
나중에 다시 복습할 때, 좋을 것 같음.

또한 다음부턴 쿼리 작성 시간도 카운트 해보면 좋을 것 같다.

(쿼리 작성 속도가 점점 더 빨라지는 것 같아서 다행이다)

문제:

문제파악:

JULY 테이블의 strawberry 값이 2개 값으로 표출되어

FLAVOR 컬럼 기준으로 누적합 함수를 쓰면 풀 수 있지 않을까 했지만, 순간의 착각이었다.

FLAVOR 컬럼을 기준으로 누적합이 생성되는 것을 확인했다...

즉, JULY 테이블에서 한가지 맛을 거듭할때마다 아래 예시 처럼 CUM_SUM 컬럼에 누적합이 더해졌었다.

ex)

문제에서 원하는 부분은

출하번호는 다르지만, 같은 맛인 사과에 대한 TOTAL_ORDER만 합치고, 나머지 과일은 그대로 두면 되었었다.

그래서, 방법을 바꾸어 SELECT 문에 FLAVOR 별 그룹 합계를 구하고

FIRST_HALF 절과 INNER JOIN 이후에 이를 활용테이블로 삼아 합계 상위 3개만 뽑아냈다.

풀이:

1. JULY 테이블의 경우, 출하량이 많아 같은 맛의 아이스크림이라도 다른 출하번호를 갖게된다고 명시되어 있어서,
쿼리를 작성하기 전 이 부분을 직접 확인

SELECT COUNT(FLAVOR) = 7
FROM FIRST_HALF

SELECT COUNT(DISTINCT FLAVOR) = 7
FROM FIRST_HALF

SELECT COUNT(FLAVOR) = 8
FROM JULY

2. JULY 테이블에만 중복된 값(JULY 테이블의 딸기만 SHIPMENT_ID 가 2개)이 존재함을 파악할 수 있었고,
이 테이블에 대하여 그룹별 합계를 계산하고 이를 테이블로 활용하기 위해 WITH 문으로 감쌌다.

** FROM 절 or WITH 절 중 아무거나 사용해도 될 것 같음.

WITH COUNTER AS (
        SELECT FLAVOR
             , SUM(TOTAL_ORDER ) AS TOTAL_JULY
        FROM JULY
        GROUP BY FLAVOR
                )                           

3. 다음으로 새로 생성한 테이블에 FLAVOR 기준으로 FIRST_HALF를 조인했다.

이 과정에서 SELECT 문에 그룹별 FLAVOR 총 주문량이 합쳐진 컬럼이 필요했고,

SUM(TOTAL_JULY+TOTAL_ORDER) AS TOTAL_SUM 로 처리했다.

다시금 FLAVOR 별로 묶어 준 뒤, 총 주문량 기준으로 내림차순으로 정렬했고,

상위 3개의 총 주문량의 데이터만 나올 수 있도록 LIMIT 을 조정했다.

SELECT FIRST_HALF.FLAVOR
     , SUM(TOTAL_JULY+TOTAL_ORDER) AS TOTAL_SUM
FROM FIRST_HALF
     INNER JOIN COUNTER ON COUNTER.FLAVOR = FIRST_HALF.FLAVOR
GROUP BY FIRST_HALF.FLAVOR     
ORDER BY TOTAL_SUM DESC    # 내림차순
LIMIT 3

4. 문제에서 원하는 컬럼은 오직 FLAVOR 컬럼이었기에, TOTAL_SUM을 지우니 내림차순이 적용이 되지 않았다.

(정렬기준의 컬럼이 사라져 버리니까)

해서, 이를 다시 서브쿼리문으로 재활용하기 위해 괄호로 묶어서 FROM 절의 괄호에 넣었다.

풀이:

SELECT FLAVOR
FROM ( 
    
        WITH COUNTER AS (
                SELECT FLAVOR
                     , SUM(TOTAL_ORDER ) AS TOTAL_JULY
                FROM JULY
                GROUP BY FLAVOR
                        )                              

        SELECT FIRST_HALF.FLAVOR
             , SUM(TOTAL_JULY+TOTAL_ORDER) AS TOTAL_SUM
        FROM FIRST_HALF
             INNER JOIN COUNTER ON COUNTER.FLAVOR = FIRST_HALF.FLAVOR
        GROUP BY FIRST_HALF.FLAVOR     
        ORDER BY TOTAL_SUM DESC    # 내림차순
        LIMIT 3    

    )sub

습득한점:

코딩테스트 출제 문제 인 것 같은데, 어렵지는 않았으나 접근할 수 있는 방법이 생각보다 많아보였다.

조인으로도, WITH로도 풀 수 있을 것 같다.

문제:

데이터 분석 팀에서는 우유(Milk)와 요거트(Yogurt)를 동시에 구입한 장바구니가 있는지에 대한 이력 파악

우유와 요거트를 동시에 구입한 장바구니의 아이디를 조회하는 쿼리 작성

정렬은 아이디로

문제파악:

NAME 컬럼에
'Milk', 'Yogurt'가 모두 들어가는 값만 걸러낸다.

여기서 걸러낸 결과 NAME 카운트 값이 Milk =1 , Yogurt=1 이라고 당연하게 생각했던게 실수 였다.

2개인것도 3개 이상인 것도 고려를 해야했다.

아무튼 WHERE NAME IN ~으로는 2개 이상인 값들만 찾을 수 있으니, 이를 처리할 방법을 찾아야 했는데,
COUNT(DISTINCT NAME) = 2를 조건으로 걸면,

반드시 Milk 1개, Yogurt 1개의 구성으로 이루어진 CART_ID만 찾을 수 있고,

이 과정에서 예를들어 Yogurt만 있는 CART_ID,  Milk만 있는 CART_ID를 걸러낼 수 있다.

풀이:

SELECT CART_ID
FROM CART_PRODUCTS
WHERE NAME IN ('Milk', 'Yogurt')
GROUP BY CART_ID
HAVING COUNT(DISTINCT NAME)=2
ORDER BY CART_ID

습득한 점:

문제를 너무 느긋하게 풀고 있다.

다음 문제부터는 집중해서 최대한 빠르게 풀어보자

문제:

2022년 1월의 도서 판매 데이터를 기준으로 저자 별, 카테고리 별 매출액(TOTAL_SALES = 판매량 * 판매가) 구하기

저자 ID(AUTHOR_ID),

저자명(AUTHOR_NAME),

카테고리(CATEGORY),

매출액(SALES) 리스트를 출력하는 쿼리 작성
결과는 저자 ID를 오름차순으로, 저자 ID가 같다면 카테고리를 내림차순 정렬

문제파악:

문제 파악 할 것은 크게 없었다.

2022년 1월의 도서 판매 데이터를 기준으로 저자 별, 카테고리 별 매출액(TOTAL_SALES = 판매량 * 판매가) 구하기
- 위 조건만 유의하면 쉽게 풀 수 있다.

풀이:

SELECT B.AUTHOR_ID
     , AUTHOR_NAME
     , CATEGORY
     # , PRICE
     # , SALES
     , SUM(PRICE * SALES) AS TOTAL_SALES

FROM BOOK B
     INNER JOIN AUTHOR A ON B.AUTHOR_ID = A.AUTHOR_ID
     INNER JOIN BOOK_SALES S ON B.BOOK_ID = S.BOOK_ID
WHERE SALES_DATE LIKE '2022-01%'
GROUP BY AUTHOR_ID, CATEGORY
ORDER BY AUTHOR_ID, CATEGORY DESC

링크: https://school.programmers.co.kr/learn/courses/30/lessons/77487

습득한 점:

실제 코테라고 쫄지마, Lv 1 수준이야

문제:

이 서비스에서는 공간을 둘 이상 등록한 사람을 "헤비 유저"라고 부름.

헤비 유저가 등록한 공간의 정보를 아이디 순으로 조회하는 쿼리 작성

문제파악:

HOST_ID의 갯수가 2개 이상인 HOST_ID만 따로 뽑고, 원본데이터의 WHERE 조건절로 활용

풀이

-- 원하는 조건의 HOST_ID만 뽑은 뒤, 이를 원본데이터에 조건으로 넣기

# SELECT *
# FROM PLACES
# WHERE HOST_ID IN (
#              SELECT HOST_ID
#              FROM PLACES
#              GROUP BY HOST_ID
#              HAVING COUNT(HOST_ID)>=2
#             )
# ORDER BY ID            


-- 반대 풀이

SELECT *
FROM PLACES
WHERE HOST_ID NOT IN (
             SELECT HOST_ID
             FROM PLACES
             GROUP BY HOST_ID
             HAVING COUNT(HOST_ID)=1
            )
ORDER BY ID

링크: https://school.programmers.co.kr/learn/courses/30/lessons/131537

습득한 점: 

* 컬럼의 값을 NULL 로 표시해야 할 경우에는, 문자열 "NULL" 나타내는 것이 아닌 NULL 로만 기재

* UNION , UNION ALL?

     - UNION은 두 개의 테이블을 하나로 만드는 연산.

        두 개 테이블의 컬럼 수, 컬럼 데이터 형식이 모두 일치해야 함.

       UNION 연산은 두 개의 테이블을 하나로 합치면서 중복된 데이터를 제거한다. 정렬을 발생

     - UNION ALL은 중복을 제거하거나 정렬을 유발하지 않는다. 중복제거 없이 전부 보여줌

        두 개 테이블의 컬럼 수가 같아야 하는 것은 동일

- Reference

https://prinha.tistory.com/entry/MySQL-COALESCE-%ED%95%A8%EC%88%98

https://whitepro.tistory.com/580

https://gooreumwave.tistory.com/25

문제:

ONLINE_SALE 테이블과 OFFLINE_SALE 테이블에서

2022년 3월의 오프라인/온라인 상품 판매 데이터의 판매 날짜, 상품ID, 유저ID, 판매량을 출력

OFFLINE_SALE 테이블의 판매 데이터의 USER_ID 값은 NULL 로 표시

판매일을 기준으로 오름차순 정렬,

판매일이 같다면 상품 ID를 기준으로 오름차순,

상품ID까지 같다면 유저 ID를 기준으로 오름차순 정렬

문제 파악:

두개 테이블을 합쳐서, 날짜에 맞는 조건들만 건져내면 된다.

날짜만 포맷을 바꿔서 바꿔주면 되었음.

아래와 같이 NULL 값 표기가 필요할 때는,

문자열 "NULL" 나타내는 것이 아닌 NULL 로만 기재하는 것만 유의하면 쉽게 풀 수 있는 문제

* 아래처럼 With 구문을 쓰지 않고 테이블 2개를 한번에 묶고, 정렬해줘도 풀 수 있을 것 같다.

(SELECT *

FROM TABLE_1

UNION

SELECT *

FROM TABLE_2)

ORDER BY ~~~

풀이:

WITH COUNTER AS (

        SELECT DATE_FORMAT(SALES_DATE, "%Y-%m-%d") AS SALES_DATE
             , PRODUCT_ID
             , USER_ID
             , SALES_AMOUNT
        FROM ONLINE_SALE AS ONSALE

        UNION ALL

        SELECT DATE_FORMAT(SALES_DATE, "%Y-%m-%d") AS SALES_DATE
             , PRODUCT_ID
             , NULL AS USER_ID
             , SALES_AMOUNT
        FROM OFFLINE_SALE AS OFFSALE
                )

                
SELECT *
FROM COUNTER
WHERE SALES_DATE LIKE '2022-03%'
ORDER BY SALES_DATE,PRODUCT_ID,USER_ID