586. Customer Placing the Largest Number of 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.

這一題的意思是要我們找出下最多訂單的用戶，
寫法就是降序(DESC)列出出現最多次的customer_number即可，LIMIT 1是列出第1筆的意思。

SELECT customer_number FROM orders
GROUP BY customer_number
ORDER BY COUNT(*) DESC
LIMIT 1

題目的最後另有延伸，
意思是說，如果有多位下訂最多訂單的情況該怎麼辦呢?
Follow up: What if more than one customer has the largest number of orders, can you find all the customer_number in this case?
這邊在GOOGLE上學到了CTE(Common Table Expression)的用法，
用法就像我們定義一個FUNCTION去使用，網路是說讓你有一個臨時結果集可以去調用，
就不用一直重複寫一樣的東西。

用法如下：

WITH 資料1 AS(SELECT...運算過程),
    資料2 AS (SELECT...運算過程)
    要再多宣告幾個都行，下面就能直接調用。

SELECT * FROM 資料1
WHERE 篩選條件

這邊附上針對Follow up內容做解析的影片，增加對CTE的印象。

WITH cte AS
	(SELECT customer_number,COUNT(*) AS C FROM Orders
    GROUP BY customer_number),
    cte2 AS (SELECT MAX(c) AS m FROM cte)

SELECT customer_number FROM cte
WHERE c=(SELECT m FROM cte2);

511. Game Play Analysis I

+--------------+---------+
| 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  |
+-----------+-------------+

這一題要找出玩家首次登入的那一天，最早的那天就想到能用MIN()去解，直接通過。

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

1890. The Latest Login in 2020

+----------------+----------+
| 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進行登入的玩家，且輸出最後登入的日期，
要找2020的話直接用LIKE去做就行，這邊要注意常常會忘記用GROUP BY，
只要有用數學函式(MAX.MIN.COUNT...)這種就打上GROUP BY，避免多卡那一會兒。

SELECT user_id,MAX(time_stamp) AS last_stamp
FROM Logins 
WHERE time_stamp LIKE '2020%'
GROUP BY user_id

1741. Find Total Time Spent by Each Employee

+-------------+------+
| 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.

這一題要計算員工出入辦公室的時間，同一天出入的會要加總起來，
如果直接(out_time - in_time)的話，會漏掉11/28(200-55)這一筆，
我想了一下才找到要用SUM的問題，那看到SUM自然要先補上GROUP BY，

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

下面就當作是CTE小練習，感覺這個用法會很常看到。

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

SELECT * FROM total_time