Day06-LeetCode-SQL启航~

今天也是菜味十足，5题中有3题都要去爬文了解才能完成，不过晚上终于可以狂欢看球了~

197. Rising Temperature

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

这一题要找出比前一天温度还高的id，
比如id2:01-02是25度比id1:01-01的10度高，所以要输出id'2'
，这边学会了DATEDIFF的用法，-1的意思就是昨天，
可以到w3s去试试变化。

SELECT  W2.idFROM Weather W1 INNER JOIN Weather W2 #只用join也会过ON DATEDIFF(W1.recordDate, W2.recordDate)=-1AND W2.temperature > W1.temperature;

607. Sales Person

+-----------------+---------+| Column Name     | Type    |+-----------------+---------+| sales_id        | int     || name            | varchar || salary          | int     || commission_rate | int     || hire_date       | date    |+-----------------+---------+sales_id is the primary key column for this table.Each row of this table indicates the name and the ID of a salesperson alongside their salary, commission rate, and hire date. Table: Company+-------------+---------+| Column Name | Type    |+-------------+---------+| com_id      | int     || name        | varchar || city        | varchar |+-------------+---------+com_id is the primary key column for this table.Each row of this table indicates the name and the ID of a company and the city in which the company is located.Table: Orders+-------------+------+| Column Name | Type |+-------------+------+| order_id    | int  || order_date  | date || com_id      | int  || sales_id    | int  || amount      | int  |+-------------+------+order_id is the primary key column for this table.com_id is a foreign key to com_id from the Company table.sales_id is a foreign key to sales_id from the SalesPerson table.Each row of this table contains information about one order. This includes the ID of the company, the ID of the salesperson, the date of the order, and the amount paid.Write an SQL query to report the names of all the salespersons who did not have any orders related to the company with the name "RED".Return the result table in any order.The query result format is in the following example.Example 1:Input: SalesPerson table:+----------+------+--------+-----------------+------------+| sales_id | name | salary | commission_rate | hire_date  |+----------+------+--------+-----------------+------------+| 1        | John | 100000 | 6               | 4/1/2006   || 2        | Amy  | 12000  | 5               | 5/1/2010   || 3        | Mark | 65000  | 12              | 12/25/2008 || 4        | Pam  | 25000  | 25              | 1/1/2005   || 5        | Alex | 5000   | 10              | 2/3/2007   |+----------+------+--------+-----------------+------------+Company table:+--------+--------+----------+| com_id | name   | city     |+--------+--------+----------+| 1      | RED    | Boston   || 2      | ORANGE | New York || 3      | YELLOW | Boston   || 4      | GREEN  | Austin   |+--------+--------+----------+Orders table:+----------+------------+--------+----------+--------+| order_id | order_date | com_id | sales_id | amount |+----------+------------+--------+----------+--------+| 1        | 1/1/2014   | 3      | 4        | 10000  || 2        | 2/1/2014   | 4      | 5        | 5000   || 3        | 3/1/2014   | 1      | 1        | 50000  || 4        | 4/1/2014   | 1      | 4        | 25000  |+----------+------------+--------+----------+--------+Output: +------+| name |+------+| Amy  || Mark || Alex |+------+Explanation: According to orders 3 and 4 in the Orders table, it is easy to tell that only salesperson John and Pam have sales to company RED, so we report all the other names in the table salesperson.

这一题要列出不在RED公司工作过的员工姓名，我的思路如下：
确认员工姓名在SalesPerson表中，再开始找其他表的关联性。
1.首先找到RED公司，公司在Company表的name中，com_id为1
2.com_id为1的值出现在Orders表中(有两笔)，而sales_id能关联到SalesPerson表
3.sales_id(1、4)两位有在RED公司工作过
4.排除掉两位员工后，剩下的人就是答案

SELECT S.nameFROM SalesPerson SWHERE S.Name NOT IN    (SELECT S.name     FROM Orders O    INNER JOIN Company C    ON C.com_id = O.com_id     LEFT JOIN SalesPerson S ON S.sales_id =O.sales_id    WHERE C.Name LIKE "RED")

1141. User Activity for the Past 30 Days I

+---------------+---------+| Column Name   | Type    |+---------------+---------+| user_id       | int     || session_id    | int     || activity_date | date    || activity_type | enum    |+---------------+---------+There is no primary key for this table, it may have duplicate rows.The activity_type column is an ENUM of type ('open_session', 'end_session', 'scroll_down', 'send_message').The table shows the user activities for a social media website. Note that each session belongs to exactly one user. Write an SQL query to find the daily active user count for a period of 30 days ending 2019-07-27 inclusively. A user was active on someday if they made at least one activity on that day.Return the result table in any order.The query result format is in the following example.Example 1:Input: Activity table:+---------+------------+---------------+---------------+| user_id | session_id | activity_date | activity_type |+---------+------------+---------------+---------------+| 1       | 1          | 2019-07-20    | open_session  || 1       | 1          | 2019-07-20    | scroll_down   || 1       | 1          | 2019-07-20    | end_session   || 2       | 4          | 2019-07-20    | open_session  || 2       | 4          | 2019-07-21    | send_message  || 2       | 4          | 2019-07-21    | end_session   || 3       | 2          | 2019-07-21    | open_session  || 3       | 2          | 2019-07-21    | send_message  || 3       | 2          | 2019-07-21    | end_session   || 4       | 3          | 2019-06-25    | open_session  || 4       | 3          | 2019-06-25    | end_session   |+---------+------------+---------------+---------------+Output: +------------+--------------+ | day        | active_users |+------------+--------------+ | 2019-07-20 | 2            || 2019-07-21 | 2            |+------------+--------------+ Explanation: Note that we do not care about days with zero active users.

这一题希望我们列出一个月中的用户活跃数，06-25的用户就会被排除掉，
07-20有两个用户活动
07-21也有两个用户活动
要注意ID:2看起来是跨日活动，会分开算。

SELECT activity_date AS day , COUNT(DISTINCT(user_id)) AS active_usersFROM ActivityWHERE activity_date > "2019-06-27" AND         activity_date <= "2019-07-27"GROUP BY activity_dateORDER BY activity_date ASC

1693. Daily Leads and Partners

+-------------+---------+| Column Name | Type    |+-------------+---------+| date_id     | date    || make_name   | varchar || lead_id     | int     || partner_id  | int     |+-------------+---------+This table does not have a primary key.This table contains the date and the name of the product sold and the IDs of the lead and partner it was sold to.The name consists of only lowercase English letters.Write an SQL query that will, for each date_id and make_name, return the number of distinct lead_id's and distinct partner_id's.Return the result table in any order.The query result format is in the following example.Example 1:Input: DailySales table:+-----------+-----------+---------+------------+| date_id   | make_name | lead_id | partner_id |+-----------+-----------+---------+------------+| 2020-12-8 | toyota    | 0       | 1          || 2020-12-8 | toyota    | 1       | 0          || 2020-12-8 | toyota    | 1       | 2          || 2020-12-7 | toyota    | 0       | 2          || 2020-12-7 | toyota    | 0       | 1          || 2020-12-8 | honda     | 1       | 2          || 2020-12-8 | honda     | 2       | 1          || 2020-12-7 | honda     | 0       | 1          || 2020-12-7 | honda     | 1       | 2          || 2020-12-7 | honda     | 2       | 1          |+-----------+-----------+---------+------------+Output: +-----------+-----------+--------------+-----------------+| date_id   | make_name | unique_leads | unique_partners |+-----------+-----------+--------------+-----------------+| 2020-12-8 | toyota    | 2            | 3               || 2020-12-7 | toyota    | 1            | 2               || 2020-12-8 | honda     | 2            | 2               || 2020-12-7 | honda     | 3            | 2               |+-----------+-----------+--------------+-----------------+Explanation: For 2020-12-8, toyota gets leads = [0, 1] and partners = [0, 1, 2] while honda gets leads = [1, 2] and partners = [1, 2].For 2020-12-7, toyota gets leads = [0] and partners = [1, 2] while honda gets leads = [0, 1, 2] and partners = [1, 2].

这一题是列出客户总数，比如toyota 12-08的客户有lead_id(0、1)、partner_id(0、1、2)
结果就会是unique_leads 2位，unique_partners 3位，依此类推，
因为有用COUNT函式，所以要用GROUP BY排序，
我去GOOGLE找看有没有跟题目要求的Output完全一样那种写法，但没找到，总之先求PASS了。

SELECT date_id, make_name,     COUNT(distinct lead_id) unique_leads,    COUNT(distinct partner_id) unique_partnersFROM DailySalesGROUP BY date_id,make_name#OUTPUT会是下面这样，但也能过关。/*| date_id    | make_name | unique_leads | unique_partners || ---------- | --------- | ------------ | --------------- || 2020-12-07 | honda     | 3            | 2               || 2020-12-07 | toyota    | 1            | 2               || 2020-12-08 | honda     | 2            | 2               || 2020-12-08 | toyota    | 2            | 3               |*/

1729. Find Followers Count

+-------------+------+| Column Name | Type |+-------------+------+| user_id     | int  || follower_id | int  |+-------------+------+(user_id, follower_id) is the primary key for this table.This table contains the IDs of a user and a follower in a social media app where the follower follows the user. Write an SQL query that will, for each user, return the number of followers.Return the result table ordered by user_id in ascending order.The query result format is in the following example.Example 1:Input: Followers table:+---------+-------------+| user_id | follower_id |+---------+-------------+| 0       | 1           || 1       | 0           || 2       | 0           || 2       | 1           |+---------+-------------+Output: +---------+----------------+| user_id | followers_count|+---------+----------------+| 0       | 1              || 1       | 1              || 2       | 2              |+---------+----------------+Explanation: The followers of 0 are {1}The followers of 1 are {0}The followers of 2 are {0,1}

统计追蹤者人数，COUNT(DISTINCT(follower_id))的意思是总计不重複的值，
记得要用GROUP BY排序

SELECT user_id , COUNT(DISTINCT(follower_id)) AS followers_countFROM FollowersGROUP BY user_id