Leetcode SQL Solution Day 9

🪙 Capital Gain/Loss

Table: Stocks

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| stock_name    | varchar |
| operation     | enum    |
| operation_day | int     |
| price         | int     |
+---------------+---------+
(stock_name, operation_day) is the primary key for this table.
The operation column is an ENUM of type ('Sell', 'Buy')
Each row of this table indicates that the stock which has stock_name had an operation on the day operation_day with the price.
It is guaranteed that each 'Sell' operation for a stock has a corresponding 'Buy' operation in a previous day. 
It is also guaranteed that each 'Buy' operation for a stock has a corresponding 'Sell' operation in an upcoming day.

Write an SQL query to report the Capital gain/loss for each stock.

The Capital gain/loss of a stock is the total gain or loss after buying and selling the stock one or many times.
Return the result table in any order.

The query result format is in the following example.

Example 1:

Input:
Stocks table:
+---------------+-----------+---------------+--------+
| stock_name    | operation | operation_day | price  |
+---------------+-----------+---------------+--------+
| Leetcode      | Buy       | 1             | 1000   |
| Corona Masks  | Buy       | 2             | 10     |
| Leetcode      | Sell      | 5             | 9000   |
| Handbags      | Buy       | 17            | 30000  |
| Corona Masks  | Sell      | 3             | 1010   |
| Corona Masks  | Buy       | 4             | 1000   |
| Corona Masks  | Sell      | 5             | 500    |
| Corona Masks  | Buy       | 6             | 1000   |
| Handbags      | Sell      | 29            | 7000   |
| Corona Masks  | Sell      | 10            | 10000  |
+---------------+-----------+---------------+--------+
Output:
+---------------+-------------------+
| stock_name    | capital_gain_loss |
+---------------+-------------------+
| Corona Masks  | 9500              |
| Leetcode      | 8000              |
| Handbags      | -23000            |
+---------------+-------------------+
Explanation:
Leetcode stock was bought at day 1 for 1000$ and was sold at day 5 for 9000$. Capital gain = 9000 - 1000 = 8000$.
Handbags stock was bought at day 17 for 30000$ and was sold at day 29 for 7000$. Capital loss = 7000 - 30000 = -23000$.
Corona Masks stock was bought at day 1 for 10$ and was sold at day 3 for 1010$. It was bought again at day 4 for 1000$ and was sold at day 5 for 500$. 
At last, it was bought at day 6 for 1000$ and was sold at day 10 for 10000$. Capital gain/loss is the sum of capital gains/losses for each ('Buy' --> 'Sell') operation = (1010 - 10) + (500 - 1000) + (10000 - 1000) = 1000 - 500 + 9000 = 9500$.

✅ 해답

여기서는
말그래도 자본의 유입과 손실을 계산하면 되는데 stock_name으로 GROUP BY하고 operation의 Buy, Sell일 때 유입과 손실을 계산하고 이를 price별로 더해주거나 빼서 반환해주는 쿼리를 작성 해주면 된다.

그러기 위해서는 조건문을 작성해야하는데 MySQL에서 쓰는 CASE WHEN 구문을 이용하여 사용해주고 그것을 다 더하기만하면 요구하는 조건의 쿼리를 작성가능할 것이니
한번 시도해보겠다.

SELECT stock_name,
	   # SUM() 안에 CASE WHEN 함수를 사용하여 
	   # Buy는 사는 것이므로 Buy일 때 - price를 해주고 
	   # 나머지는 파는 것이므로 price를 그대로 사용한다.
       SUM(CASE WHEN operation = 'Buy' THEN - price
           ELSE price
           END) AS capital_gain_loss 
FROM Stocks
GROUP BY stock_name;

🛬 Top Travellers

Table: Users

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

Table: Rides

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| user_id       | int     |
| distance      | int     |
+---------------+---------+
id is the primary key for this table.
user_id is the id of the user who traveled the distance "distance".

Write an SQL query to report the distance traveled by each user.

Return the result table ordered by travelled_distance in descending order, if two or more users traveled the same distance, order them by their name in ascending order.

The query result format is in the following example.

Example 1:

Input:
Users table:
+------+-----------+
| id   | name      |
+------+-----------+
| 1    | Alice     |
| 2    | Bob       |
| 3    | Alex      |
| 4    | Donald    |
| 7    | Lee       |
| 13   | Jonathan  |
| 19   | Elvis     |
+------+-----------+
Rides table:
+------+----------+----------+
| id   | user_id  | distance |
+------+----------+----------+
| 1    | 1        | 120      |
| 2    | 2        | 317      |
| 3    | 3        | 222      |
| 4    | 7        | 100      |
| 5    | 13       | 312      |
| 6    | 19       | 50       |
| 7    | 7        | 120      |
| 8    | 19       | 400      |
| 9    | 7        | 230      |
+------+----------+----------+
Output:
+----------+--------------------+
| name     | travelled_distance |
+----------+--------------------+
| Elvis    | 450                |
| Lee      | 450                |
| Bob      | 317                |
| Jonathan | 312                |
| Alex     | 222                |
| Alice    | 120                |
| Donald   | 0                  |
+----------+--------------------+
Explanation:
Elvis and Lee traveled 450 miles, Elvis is the top traveler as his name is alphabetically smaller than Lee.
Bob, Jonathan, Alex, and Alice have only one ride and we just order them by the total distances of the ride.
Donald did not have any rides, the distance traveled by him is 0.

✅ 해답

여기서는
사람별로 여행한 거리가 얼마나 되는지 총계를 더하여 가장 많이 여행한 거리를 이동한 사람의 이름과 그 값을 내림차순으로 표현해주는 쿼리를 작성하면 된다.

일단 그것을 하기 위해서는 Users 테이블과 Rides 테이블을 JOIN 해주어야하는데 여기서 함정은 두 테이블의 id가 전혀 다른 것이다.
따라서 JOIN을 하려면 Users의 id와 Rides의 user_id를 JOIN 시켜줘야한다.

IFNULL을 사용하지 않고 쿼리를 실행했더니
Bob이 NULL 값으로 나와 이를 IFNULL을 사용해 0으로 치환해주었다.
그럼 여기서 간단하게 IFNULL에 대하여 알아보자

IFNULL

IFNULL(식, n)은 식의 값이 NULL일 때 n값으로 치환해주는 함수이다.

SELECT a.name, IFNULL(SUM(b.distance), 0) AS travelled_distance
FROM Users a
LEFT JOIN Rides b
ON a.id = b.user_id
GROUP BY user_id
ORDER BY travelled_distance DESC, name ASC;

🧐 Market Analysis I

Table: Users

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| user_id        | int     |
| join_date      | date    |
| favorite_brand | varchar |
+----------------+---------+
user_id is the primary key of this table.
This table has the info of the users of an online shopping website where users can sell and buy items.

Table: Orders

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| order_id      | int     |
| order_date    | date    |
| item_id       | int     |
| buyer_id      | int     |
| seller_id     | int     |
+---------------+---------+
order_id is the primary key of this table.
item_id is a foreign key to the Items table.
buyer_id and seller_id are foreign keys to the Users table.

Table: Items

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| item_id       | int     |
| item_brand    | varchar |
+---------------+---------+
item_id is the primary key of this table.

Write an SQL query to find for each user, the join date and the number of orders they made as a buyer in 2019.

Return the result table in any order.

The query result format is in the following example.

Example 1:

Input:
Users table:
+---------+------------+----------------+
| user_id | join_date  | favorite_brand |
+---------+------------+----------------+
| 1       | 2018-01-01 | Lenovo         |
| 2       | 2018-02-09 | Samsung        |
| 3       | 2018-01-19 | LG             |
| 4       | 2018-05-21 | HP             |
+---------+------------+----------------+
Orders table:
+----------+------------+---------+----------+-----------+
| order_id | order_date | item_id | buyer_id | seller_id |
+----------+------------+---------+----------+-----------+
| 1        | 2019-08-01 | 4       | 1        | 2         |
| 2        | 2018-08-02 | 2       | 1        | 3         |
| 3        | 2019-08-03 | 3       | 2        | 3         |
| 4        | 2018-08-04 | 1       | 4        | 2         |
| 5        | 2018-08-04 | 1       | 3        | 4         |
| 6        | 2019-08-05 | 2       | 2        | 4         |
+----------+------------+---------+----------+-----------+
Items table:
+---------+------------+
| item_id | item_brand |
+---------+------------+
| 1       | Samsung    |
| 2       | Lenovo     |
| 3       | LG         |
| 4       | HP         |
+---------+------------+
Output:
+-----------+------------+----------------+
| buyer_id  | join_date  | orders_in_2019 |
+-----------+------------+----------------+
| 1         | 2018-01-01 | 1              |
| 2         | 2018-02-09 | 2              |
| 3         | 2018-01-19 | 0              |
| 4         | 2018-05-21 | 0              |
+-----------+------------+----------------+

✅ 해답

여기서는
각 구매자의 id 별로 언제 가입했는지와 2019년에 몇 번 구매했는지의 값을 반환하는 쿼리를 작성해주면 된다.

이 문제도 함정들이 좀 있는데 일단 테이블은 3개가 있지만 JOIN이 필요한 테이블은 Users 와 Orders 테이블이며 Items 테이블은 그냥 버리는 것이다.
그리고 JOIN을 할 때도 user_id와 buyer_id를 기준으로 JOIN해주어야 한다.

더하여 여기서는 JOIN을 할 때에 조건을 하나 더 추가해줄건데
Orders의 order_date가 2019년인 것만을 조건으로 걸어 JOIN 해줄 것이다.
이는 ON절에 AND 조건절을 추가하여 구현할 수 있다.

마지막으로 user_id(buyer_id)별로 GROUP BY 해주면 ehlsek.

SELECT user_id AS buyer_id, join_date, 
       COUNT(order_date) AS orders_in_2019 
FROM Users a

LEFT JOIN Orders b
ON a.user_id = b.buyer_id
AND YEAR(order_date) = '2019'

GROUP BY user_id;

다음에는 마지막으로 Day 10의 문제들을 알아보자