효니의 PM 성장 일지

QUIZ

Query the Name of any student in STUDENTS who scored higher than  Marks. Order your output by the last three characters of each name. If two or more students both have names ending in the same last three characters (i.e.: Bobby, Robby, etc.), secondary sort them by ascending ID.

MY ANSWER

SELECT Name
FROM STUDENTS 
WHERE MARKS > 75
ORDER BY RIGHT(NAME,3) ASC, ID ASC

POINT

문자를 잘라야하는 경우에는 세가지만 생각하기 ! ORDER BY에서도 쓸 수 있다 :) 

• SUBSTRING(문자열, 시작위치, 길이)
• LEFT(문자열, 길이)
• RIGHT(문자열, 길이)

함수 사용 방법

CAST (컬럼명 AS 변경하려는 데이터 TYPE)

변경하려는 데이터 TYPE

CAST 함수 활용 예시

SELECT CONCAT(NAME,'(',SUBSTRING(OCCUPATION,1,1),')')
FROM OCCUPATIONS
ORDER BY NAME;

SELECT CONCAT('There are a total of ', cast(count(*) as char), ' ', lower(OCCUPATION), 's.')
FROM OCCUPATIONS
GROUP BY OCCUPATION
ORDER BY count(OCCUPATION) ASC;

5번 정도 시도했는데 못풀었던 문제를 드디어 풀었다 ! 

이번 문제를 풀면서 배운 것 >> 누적 문제 풀때는 무조건 윈도우함수 기억하자 :) 

Table: Queue

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| person_id   | int     |
| person_name | varchar |
| weight      | int     |
| turn        | int     |
+-------------+---------+
person_id is the primary key column for this table.
This table has the information about all people waiting for a bus.
The person_id and turn columns will contain all numbers from 1 to n, where n is the number of rows in the table.
turn determines the order of which the people will board the bus, where turn=1 denotes the first person to board and turn=n denotes the last person to board.
weight is the weight of the person in kilograms.
 

There is a queue of people waiting to board a bus. However, the bus has a weight limit of 1000 kilograms, so there may be some people who cannot board.

Write an SQL query to find the person_name of the last person that can fit on the bus without exceeding the weight limit. The test cases are generated such that the first person does not exceed the weight limit.

The query result format is in the following example.

 

Example 1:

Input: 
Queue table:
+-----------+-------------+--------+------+
| person_id | person_name | weight | turn |
+-----------+-------------+--------+------+
| 5         | Alice       | 250    | 1    |
| 4         | Bob         | 175    | 5    |
| 3         | Alex        | 350    | 2    |
| 6         | John Cena   | 400    | 3    |
| 1         | Winston     | 500    | 6    |
| 2         | Marie       | 200    | 4    |
+-----------+-------------+--------+------+
Output: 
+-------------+
| person_name |
+-------------+
| John Cena   |
+-------------+
Explanation: The folowing table is ordered by the turn for simplicity.
+------+----+-----------+--------+--------------+
| Turn | ID | Name      | Weight | Total Weight |
+------+----+-----------+--------+--------------+
| 1    | 5  | Alice     | 250    | 250          |
| 2    | 3  | Alex      | 350    | 600          |
| 3    | 6  | John Cena | 400    | 1000         | (last person to board)
| 4    | 2  | Marie     | 200    | 1200         | (cannot board)
| 5    | 4  | Bob       | 175    | ___          |
| 6    | 1  | Winston   | 500    | ___          |
+------+----+-----------+--------+--------------+

이전에는 계속 JOIN해서 풀거나 했었는데 이번에는 아예 윈도우 함수를 통해서 풀었다

완전 짧고 간단하게 풀어서 다행이다 ㅎㅎ

SELECT person_name 
FROM (
      SELECT *, SUM(Weight) OVER (ORDER BY turn RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Total
      FROM Queue
     ) AS NEW
WHERE Total <= 1000
ORDER BY Total DESC
LIMIT 1

다른 사람들 풀이도 보았는데 대부분이 윈도우 함수를 활용해서 풀었고

몇몇이 JOIN을 하는 방식으로 풀었는데 자꾸 이런 문제 풀 때 JOIN에서 ON하는게 헷갈려서 못풀었었다

아래에 나온 것처럼 꼭 ON이 '='이 아닌 부등호로도 진행이 가능하다는 것을 기억하자 :) 

SELECT q1.person_name
FROM Queue q1 JOIN Queue q2 ON q1.turn >= q2.turn
GROUP BY q1.turn
HAVING SUM(q2.weight) <= 1000
ORDER BY SUM(q2.weight) DESC
LIMIT 1

풀다가 몇가지 까먹었던 개념을 다시 잡으면 좋을 것 같아서 써보려고 한다.

오늘 풀었던 1341. Movie Rating 문제는 아래와 같다 

Table: Movies

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| movie_id      | int     |
| title         | varchar |
+---------------+---------+
movie_id is the primary key for this table.
title is the name of the movie.
 

Table: Users

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| user_id       | int     |
| name          | varchar |
+---------------+---------+
user_id is the primary key for this table.
 

Table: MovieRating

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| movie_id      | int     |
| user_id       | int     |
| rating        | int     |
| created_at    | date    |
+---------------+---------+
(movie_id, user_id) is the primary key for this table.
This table contains the rating of a movie by a user in their review.
created_at is the user's review date. 
 

Write an SQL query to:

Find the name of the user who has rated the greatest number of movies. In case of a tie, return the lexicographically smaller user name.
Find the movie name with the highest average rating in February 2020. In case of a tie, return the lexicographically smaller movie name.
The query result format is in the following example.

처음에는 서브쿼리를 사용하다가 너무 복잡해지는 것 같아서 찬찬히 생각해보았다.

어짜피 구해야하는 대상이 2개가 다른 것이라서 따로따로 구해서 UNION으로 합쳐야겠다고 생각했다.

그런데 처음에 영어 문제 해석을 잘못해서 산으로 가다가 30분 만에 파파고 돌려보고 잘못됨을 느꼈다ㅠㅠ

SQL 풀려면 영어도 잘해야하는 현실....🥹

암튼 이번 문제는 각각 문제의 답을 구해서 results로 컬럼명 일치 시키고 결합하는 식으로 풀었다.

(
SELECT title AS results
FROM MovieRating MR
LEFT JOIN Users U ON MR.user_id = U.user_id
LEFT JOIN Movies M ON MR.movie_id = M.movie_id
WHERE DATE_FORMAT(created_at,'%Y-%m') = '2020-02'
GROUP BY M.movie_id
ORDER BY AVG(rating) DESC, title ASC
LIMIT 1
)
UNION ALL
(
SELECT name AS results
FROM MovieRating MR
LEFT JOIN Users U ON MR.user_id = U.user_id
LEFT JOIN Movies M ON MR.movie_id = M.movie_id
GROUP BY MR.user_id 
ORDER BY COUNT(MR.user_id) DESC, name ASC
LIMIT 1
)

오늘의 배운점 ! 

💡 ORDER BY 절에서 AVG, SUM 등 사용 가능하니, SELECT에 보여줘야 하는 거 아니면 이렇게도 사용하자
💡중복값을 제외하라는 예외사항이 없다면 일단은 UNION ALL로 사용해보자 ! 

이번에도 어렵게 풀었다...아 너무 오래걸려 ~~~

오늘도 헷갈린 포인트가 있어서 한번 글을 써봅니다...💗

1211. Queries Quality and Percentage 문제는 아래와 같다 

Table: Queries
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| query_name  | varchar |
| result      | varchar |
| position    | int     |
| rating      | int     |
+-------------+---------+

There is no primary key for this table, it may have duplicate rows.
This table contains information collected from some queries on a database.
The position column has a value from 1 to 500.
The rating column has a value from 1 to 5. Query with rating less than 3 is a poor query.
 
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.

Write an SQL query to find each query_name, the quality and poor_query_percentage.
Both quality and poor_query_percentage should be rounded to 2 decimal places.
Return the result table in any order.
The query result format is in the following example.

Example 1:

Input: 
Queries table:
+------------+-------------------+----------+--------+
| query_name | result            | position | rating |
+------------+-------------------+----------+--------+
| Dog        | Golden Retriever  | 1        | 5      |
| Dog        | German Shepherd   | 2        | 5      |
| Dog        | Mule              | 200      | 1      |
| Cat        | Shirazi           | 5        | 2      |
| Cat        | Siamese           | 3        | 3      |
| Cat        | Sphynx            | 7        | 4      |
+------------+-------------------+----------+--------+

Output: 
+------------+---------+-----------------------+
| query_name | quality | poor_query_percentage |
+------------+---------+-----------------------+
| Dog        | 2.50    | 33.33                 |
| Cat        | 0.66    | 33.33                 |
+------------+---------+-----------------------+

Explanation: 
Dog queries quality is ((5 / 1) + (5 / 2) + (1 / 200)) / 3 = 2.50
Dog queries poor_ query_percentage is (1 / 3) * 100 = 33.33

Cat queries quality equals ((2 / 5) + (3 / 3) + (4 / 7)) / 3 = 0.66
Cat queries poor_ query_percentage is (1 / 3) * 100 = 33.33

테이블 A에서 poor_query가 null인게 있어서 poor_query_percentage가 null이 나오길래

NULL IF를 사용했는데 사실은 IFNULL을 사용해야 하는 것이었다. 그래서 바꿔서 사용했다. 

SELECT A.query_name, quality, IFNULL(ROUND((poor_query/query_count)*100,2),0) AS poor_query_percentage
FROM Queries A
LEFT JOIN (
            SELECT query_name, COUNT(query_name) AS poor_query
            FROM Queries
            WHERE rating < 3
            GROUP BY query_name 
          ) AS B ON A.query_name = B.query_name
LEFT JOIN (
            SELECT query_name, ROUND(SUM(rating/position)/COUNT(query_name),2) AS quality, COUNT(*) AS query_count
            FROM Queries
            GROUP BY query_name
          ) AS C ON A.query_name = C.query_name
GROUP BY A.query_name

💡 SELECT IFNULL(column,0)
: column의 값이 NULL이면 0을 반환하고, NULL이 아닌 경우에는 column 의 값을 반환

💡 SELECT NULLIF (column1, column2)
: column1과 column2의 값이 같으면 NULL을 반환하고, 그렇지 않으면 column1의 값을 반환

리트코드 EASY 레벨 거의 다 풀고 이제 빠르게 풀 수 있는 문제가 없어진 것

+ 너무 바빠서 일주일간 SQL 안했더니 너무 감 떨어지는 것 같아서 다시 열심히 해보려한다 ! 

친구 말로는 코딩은 안하면 진짜 금방 까먹는다고 하는데, 일주일만 놔도 문제 보는데 너무 혼란스러워서

ㅋㅋㅋㅋ 앞으로는 열심히 해야겠다고 느꼈다 아자뵤 ! 하지만 시작하자마자 난관에 봉착...틀린 문제를 리뷰 해보자

197. Rising Temperature 문제는 아래와 같다. 

Table: Weather
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| recordDate    | date    |
| temperature   | int     |
+---------------+---------+
id is the primary key for this table.
This table contains information about the temperature on a certain day.
 
Write an SQL query to find all dates' Id with higher temperatures compared to its previous dates (yesterday).
Return the result table in any order.
The query result format is in the following example.

Example 1:

Input: 
Weather table:
+----+------------+-------------+
| id | recordDate | temperature |
+----+------------+-------------+
| 1  | 2015-01-01 | 10          |
| 2  | 2015-01-02 | 25          |
| 3  | 2015-01-03 | 20          |
| 4  | 2015-01-04 | 30          |
+----+------------+-------------+

Output: 
+----+
| id |
+----+
| 2  |
| 4  |
+----+

Explanation: 
In 2015-01-02, the temperature was higher than the previous day (10 -> 25).
In 2015-01-04, the temperature was higher than the previous day (20 -> 30).

SQL에서는 날짜가 숫자로 인지된다고 알고 있어서 아래와 같이 recordDate에 +1 하는 방법으로 진행 했었는데

Example 1에서는 정답인데 13번 예제에서 오류가 나서 이유를 찾아야 했다. 

생각해보니 30일이나 31일의 경우 그 다음 달로 넘어가야했고 이 경우에는 카운팅이 어려운 상황이 발생하게 된다.

이를 극복하기 위해서 날짜 간 차를 구할 수 있는 DATEDIFF 함수를 사용하기로 했고, 성공했다 ! 

SELECT A.id AS Id
FROM Weather A
JOIN Weather B ON datediff(a.recordDate, b.recordDate) = 1
WHERE A.temperature > B.temperature

테이블이 하나일 때 날짜간 비교를 통해 정답을 구하는 문제에서는 DATEDIFF를 애용해보자 💗

난이도

Medium 

문제

Table Activities:
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| sell_date   | date  |
| product     | varchar |
+-------------+---------+
There is no primary key for this table, it may contain duplicates.
Each row of this table contains the product name and the date it was sold in a market.
 
Write an SQL query to find for each date the number of different products sold and their names.
The sold products names for each date should be sorted lexicographically.
Return the result table ordered by sell_date.
The query result format is in the following example.

Example 1:
Input: 
Activities table:
+------------+------------+
| sell_date  | product     |
+------------+------------+
| 2020-05-30 | Headphone  |
| 2020-06-01 | Pencil     |
| 2020-06-02 | Mask       |
| 2020-05-30 | Basketball |
| 2020-06-01 | Bible      |
| 2020-06-02 | Mask       |
| 2020-05-30 | T-Shirt    |
+------------+------------+

Output: 
+------------+----------+------------------------------+
| sell_date  | num_sold | products                     |
+------------+----------+------------------------------+
| 2020-05-30 | 3        | Basketball,Headphone,T-shirt |
| 2020-06-01 | 2        | Bible,Pencil                 |
| 2020-06-02 | 1        | Mask                         |
+------------+----------+------------------------------+

Explanation: 
For 2020-05-30, Sold items were (Headphone, Basketball, T-shirt), we sort them lexicographically and separate them by a comma.
For 2020-06-01, Sold items were (Pencil, Bible), we sort them lexicographically and separate them by a comma.
For 2020-06-02, the Sold item is (Mask), we just return it.

나의 답 

SELECT sell_date, COUNT(DISTINCT(product)) AS num_sold, GROUP_CONCAT(DISTINCT(product) SEPARATOR ',') as products
FROM Activities
GROUP BY sell_date
ORDER BY sell_date

핵심

💡 GROUP_CONCAT 함수는 그룹 내의 값을 결합하여 문자열로 반환하는 함수이다.
주로 GROUP BY와 함께 사용해 그룹별로 데이터를 요약하고, 그룹 내의 값들을 결합하여 하나의 문자열로 만든다.

GROUP_CONCAT 함수의 기본적인 사용법
GROUP_CONCAT(expression [ORDER BY clause] [SEPARATOR separator])

✔️ expression: 결합할 열이나 식
✔️ ORDER BY clause (선택적): 결합된 값들의 순서를 지정할 수 있습니다. 기본적으로는 정렬되지 않은 순서로 결합됩니다.
✔️ SEPARATOR separator (선택적): 결합된 값들 사이에 삽입할 구분자(separator)를 지정합니다. 기본값은 쉼표(,)입니다.
✔️ GROUP_CONCAT 함수는 SELECT 문에서 사용되며, 일반적으로 GROUP BY 절과 함께 사용됩니다.