Leetcode SQL Solution Day 8

💁🏻‍♀️ Customer Placing the Largest Number of Orders

Table: Orders

+-----------------+----------+
| Column Name     | Type     |
+-----------------+----------+
| order_number    | int      |
| customer_number | int      |
+-----------------+----------+
order_number is the primary key for this table.
This table contains information about the order ID and the customer ID.

Write an SQL query to find the customer_number for the customer who has placed the largest number of orders.

The test cases are generated so that exactly one customer will have placed more orders than any other customer.

The query result format is in the following example.

Example 1:

Input:
Orders table:
+--------------+-----------------+
| order_number | customer_number |
+--------------+-----------------+
| 1            | 1               |
| 2            | 2               |
| 3            | 3               |
| 4            | 3               |
+--------------+-----------------+
Output:
+-----------------+
| customer_number |
+-----------------+
| 3               |
+-----------------+
Explanation:
The customer with number 3 has two orders, which is greater than either customer 1 or 2 
because each of them only has one order.
So the result is customer_number 3.

✅ 해답

여기서는
가장 많이 주문한 사람의 customer_number의 값을 불러오는 쿼리를 작성하면 된다.
GROUP BY로 아주 간단하게 구현할 수 있다.
또한 ORDER BY 에도 집계함수를 넣어 어떻게 정렬할 것인지 선택할 수 있다.
그리고 이번에는 HAVING을 사용한 답안도 작성해 보도록 하겠다.

1. ORDER BY에 COUNT 사용

SELECT customer_number FROM orders
GROUP BY customer_number
# 여기서는 전체에서 count가 가장 많은 것 순으로 정렬하고 그 중 한개만 보여주기로 한다.
ORDER BY COUNT(*) DESC 
LIMIT 1;

2. GROUP BY에 HAVING절 사용
   HAVING은 간단히 말하자면 GROUP BY의 WHERE절 같은 것이라고 생각하면 된다.
   아래에서 볼 수 있듯이 WHERE절과 같이 서브쿼리를 활용할 수 있다.

SELECT customer_number
FROM orders
GROUP BY customer_number
# order_number의 갯수를 세는데 
# 위의 ORDER BY 자리에 HAVING절의 서브쿼리를 활용한 것이다.
HAVING COUNT(order_number) = (
	SELECT COUNT(order_number) cnt
	FROM orders
	GROUP BY customer_number
	ORDER BY cnt DESC
	LIMIT 1
);

위의 서브쿼리는 1번 답안과 동일하게 customer_number별로 개수가 가장 많은 것 하나만 선택하여 반환하는 것이고
그것이 조건으로 있으니 결국 본래 쿼리문에 적용됨으로써 동일한 값이 나올 수 밖에 없는 것이다.

🕹️ Game Play Analysis I

Table: Activity

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| player_id    | int     |
| device_id    | int     |
| event_date   | date    |
| games_played | int     |
+--------------+---------+
(player_id, event_date) is the primary key of this table.
This table shows the activity of players of some games.
Each row is a record of a player who logged in and played a number of games (possibly 0) before logging out on someday using some device.

Write an SQL query to report the first login date for each player.

Return the result table in any order.

The query result format is in the following example.

Example 1:

Input:
Activity table:
+-----------+-----------+------------+--------------+
| player_id | device_id | event_date | games_played |
+-----------+-----------+------------+--------------+
| 1         | 2         | 2016-03-01 | 5            |
| 1         | 2         | 2016-05-02 | 6            |
| 2         | 3         | 2017-06-25 | 1            |
| 3         | 1         | 2016-03-02 | 0            |
| 3         | 4         | 2018-07-03 | 5            |
+-----------+-----------+------------+--------------+
Output:
+-----------+-------------+
| player_id | first_login |
+-----------+-------------+
| 1         | 2016-03-01  |
| 2         | 2017-06-25  |
| 3         | 2016-03-02  |
+-----------+-------------+

✅ 해답

여기서는
player_id별로 게임에 가장 먼저 접속한 날짜를 반환하는 쿼리를 작성하면 된다.
가장 나중에 접속한 날짜는 MIN(날짜)를 활용하여 편하게 구할 수 있다.
결국 날짜 중에서 가장 낮은 값이 가장 이른 날짜이기 때문이다.

어렵지 않으니 바로 작성해보도록 하자.

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

📈 The Lastest Login in 2020

Table: Logins

+----------------+----------+
| Column Name    | Type     |
+----------------+----------+
| user_id        | int      |
| time_stamp     | datetime |
+----------------+----------+
(user_id, time_stamp) is the primary key for this table.
Each row contains information about the login time for the user with ID user_id.

Write an SQL query to report the latest login for all users in the year 2020. Do not include the users who did not login in 2020.

Return the result table in any order.

The query result format is in the following example.

Example 1:

Input:
Logins table:
+---------+---------------------+
| user_id | time_stamp          |
+---------+---------------------+
| 6       | 2020-06-30 15:06:07 |
| 6       | 2021-04-21 14:06:06 |
| 6       | 2019-03-07 00:18:15 |
| 8       | 2020-02-01 05:10:53 |
| 8       | 2020-12-30 00:46:50 |
| 2       | 2020-01-16 02:49:50 |
| 2       | 2019-08-25 07:59:08 |
| 14      | 2019-07-14 09:00:00 |
| 14      | 2021-01-06 11:59:59 |
+---------+---------------------+
Output:
+---------+---------------------+
| user_id | last_stamp          |
+---------+---------------------+
| 6       | 2020-06-30 15:06:07 |
| 8       | 2020-12-30 00:46:50 |
| 2       | 2020-01-16 02:49:50 |
+---------+---------------------+
Explanation:
User 6 logged into their account 3 times but only once in 2020, so we include this login in the result table.
User 8 logged into their account 2 times in 2020, once in February and once in December. We include only the latest one (December) in the result table.
User 2 logged into their account 2 times but only once in 2020, so we include this login in the result table.
User 14 did not login in 2020, so we do not include them in the result table.

✅ 해답

여기서는
2020년에 마지막에 로그인한 user_id와 그 일자와 시간을 last_stamp라는 column이름으로 반환하는 쿼리를 작성하면 된다.
이번에는 날짜의 범위를 조건으로 해야하기때문에 관련 함수를 쓰기 보다는
부등호로 범위를 표현하거나 BETWEEN을 사용하는 것이 좋을 것 같다.

또한 연도만 2020년이기만 하면 접속한 time_stamp 안에서 가장 나중 값을 가져오면 되기 때문에 MAX함수를 사용하여 값을 구할 수 있는데
여기서도 두 개 다 알아보도록 하겠다.

1. 내 답안

SELECT user_id, MAX(time_stamp) AS last_stamp FROM Logins
WHERE time_stamp BETWEEN '2020-01-01 00:00:00' AND '2020-12-31 23:59:59'
# 단순 부등호 사용
# WHERE time_stamp >= '2020-01-01 00:00:00' AND  time_stamp <= '2020-12-31 23:59:59'
GROUP BY user_id

리트코드에서는 날짜로만 지정해주면 정확하지않기 때문에 시간, 분, 초까지 정확히 지정해주어야 정답처리가 되는 듯 하였다.

2. MAX 함수를 이용한 답안

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

🕵🏻 Find Total Time Spent by Each Employee

Table: Employees

+-------------+------+
| Column Name | Type |
+-------------+------+
| emp_id      | int  |
| event_day   | date |
| in_time     | int  |
| out_time    | int  |
+-------------+------+
(emp_id, event_day, in_time) is the primary key of this table.
The table shows the employees' entries and exits in an office.
event_day is the day at which this event happened, in_time is the minute at which the employee entered the office, and out_time is the minute at which they left the office.
in_time and out_time are between 1 and 1440.
It is guaranteed that no two events on the same day intersect in time, and in_time < out_time.

Write an SQL query to calculate the total time in minutes spent by each employee on each day at the office. Note that within one day, an employee can enter and leave more than once. The time spent in the office for a single entry is out_time - in_time.

Return the result table in any order.

The query result format is in the following example.

Example 1:

Input:
Employees table:
+--------+------------+---------+----------+
| emp_id | event_day  | in_time | out_time |
+--------+------------+---------+----------+
| 1      | 2020-11-28 | 4       | 32       |
| 1      | 2020-11-28 | 55      | 200      |
| 1      | 2020-12-03 | 1       | 42       |
| 2      | 2020-11-28 | 3       | 33       |
| 2      | 2020-12-09 | 47      | 74       |
+--------+------------+---------+----------+
Output:
+------------+--------+------------+
| day        | emp_id | total_time |
+------------+--------+------------+
| 2020-11-28 | 1      | 173        |
| 2020-11-28 | 2      | 30         |
| 2020-12-03 | 1      | 41         |
| 2020-12-09 | 2      | 27         |
+------------+--------+------------+
Explanation:
Employee 1 has three events: two on day 2020-11-28 with a total of (32 - 4) + (200 - 55) = 173, and one on day 2020-12-03 with a total of (42 - 1) = 41.
Employee 2 has two events: one on day 2020-11-28 with a total of (33 - 3) = 30, and one on day 2020-12-09 with a total of (74 - 47) = 27.

해답

여기서는
각 day와 emp_id별로 사무실에서 얼마나 시간을 보냈는지 계산해주는 쿼리를 작성해주면 된다.
또한 문제에 사무실에서 지낸 시간 계산은 out_time - in_time이라고 알려주었으므로 이것을 활용하면 된다.

MySQL에서는 SELECT문을 계산기처럼 활용할 수 있는데 사칙연산도 가능하다.
그래서 아래와 같은 쿼리를 작성할 수 있다.

SELECT event_day AS day, emp_id, SUM(out_time - in_time) AS total_time 
FROM Employees
GROUP BY day, emp_id;

다음에는 Day 9의 문제들을 알아보도록 하자