刷题记录帖子

1241. Number of Comments per Post
Easy
Topics
SQL Schema
Pandas Schema
Table: Submissions

+---------------+----------+
| Column Name   | Type     |
+---------------+----------+
| sub_id        | int      |
| parent_id     | int      |
+---------------+----------+
This table may have duplicate rows.
Each row can be a post or comment on the post.
parent_id is null for posts.
parent_id for comments is sub_id for another post in the table.


Write a solution to find the number of comments per post. The result table should contain post_id and its corresponding number_of_comments.

The Submissions table may contain duplicate comments. You should count the number of unique comments per post.

The Submissions table may contain duplicate posts. You should treat them as one post.

The result table should be ordered by post_id in ascending order.

The result format is in the following example.



Example 1:

Input:
Submissions table:
+---------+------------+
| sub_id  | parent_id  |
+---------+------------+
| 1       | Null       |
| 2       | Null       |
| 1       | Null       |
| 12      | Null       |
| 3       | 1          |
| 5       | 2          |
| 3       | 1          |
| 4       | 1          |
| 9       | 1          |
| 10      | 2          |
| 6       | 7          |
+---------+------------+
Output:
+---------+--------------------+
| post_id | number_of_comments |
+---------+--------------------+
| 1       | 3                  |
| 2       | 2                  |
| 12      | 0                  |
+---------+--------------------+
Explanation:
The post with id 1 has three comments in the table with id 3, 4, and 9. The comment with id 3 is repeated in the table, we counted it only once.
The post with id 2 has two comments in the table with id 5 and 10.
The post with id 12 has no comments in the table.
The comment with id 6 is a comment on a deleted post with id 7 so we ignored it.

思路

这题要做三件事：

去重的帖子：parent_id IS NULL 的行是帖子，重复的帖子只算一个。
去重的评论：parent_id 指向某个帖子，重复的评论只算一次。
没评论的帖子也要保留：如帖子 12，评论数为 0。

所以先取出去重后的帖子作为主表，再 LEFT JOIN 评论，统计每个帖子下不同评论的数量。

另外，评论 6 的 parent_id = 7，但帖子 7 不在表里（已被删除）。因为我们是从帖子出发去连评论，这条评论自然不会出现在结果里。

1. # Write your MySQL query statement below
   
2. SELECT
   
3.     p.sub_id AS post_id,
   
4.     COUNT(DISTINCT c.sub_id) AS number_of_comments
   
5. FROM (
   
6.     SELECT DISTINCT sub_id
   
7.     FROM Submissions
   
8.     WHERE parent_id IS NULL
   
9. ) p
   
10. LEFT JOIN Submissions c
    
11.     ON c.parent_id = p.sub_id
    
12. GROUP BY p.sub_id
    
13. ORDER BY p.sub_id;

复制代码

1251. Average Selling Price
Easy
Topics
conpanies icon
Companies
SQL Schema
Pandas Schema
Table: Prices

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| product_id    | int     |
| start_date    | date    |
| end_date      | date    |
| price         | int     |
+---------------+---------+
(product_id, start_date, end_date) is the primary key (combination of columns with unique values) for this table.
Each row of this table indicates the price of the product_id in the period from start_date to end_date.
For each product_id there will be no two overlapping periods. That means there will be no two intersecting periods for the same product_id.


Table: UnitsSold

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| product_id    | int     |
| purchase_date | date    |
| units         | int     |
+---------------+---------+
This table may contain duplicate rows.
Each row of this table indicates the date, units, and product_id of each product sold.


Write a solution to find the average selling price for each product. average_price should be rounded to 2 decimal places. If a product does not have any sold units, its average selling price is assumed to be 0.

Return the result table in any order.

The result format is in the following example.



Example 1:

Input:
Prices table:
+------------+------------+------------+--------+
| product_id | start_date | end_date   | price  |
+------------+------------+------------+--------+
| 1          | 2019-02-17 | 2019-02-28 | 5      |
| 1          | 2019-03-01 | 2019-03-22 | 20     |
| 2          | 2019-02-01 | 2019-02-20 | 15     |
| 2          | 2019-02-21 | 2019-03-31 | 30     |
+------------+------------+------------+--------+
UnitsSold table:
+------------+---------------+-------+
| product_id | purchase_date | units |
+------------+---------------+-------+
| 1          | 2019-02-25    | 100   |
| 1          | 2019-03-01    | 15    |
| 2          | 2019-02-10    | 200   |
| 2          | 2019-03-22    | 30    |
+------------+---------------+-------+
Output:
+------------+---------------+
| product_id | average_price |
+------------+---------------+
| 1          | 6.96          |
| 2          | 16.96         |
+------------+---------------+
Explanation:
Average selling price = Total Price of Product / Number of products sold.
Average selling price for product 1 = ((100 * 5) + (15 * 20)) / 115 = 6.96
Average selling price for product 2 = ((200 * 15) + (30 * 30)) / 230 = 16.96

要算每个商品的加权平均售价：

平均售价 = Σ(单价 × 销量) / Σ销量

难点在于：同一个商品在不同时间段有不同价格，所以每笔销售要匹配到购买日期所在的那个价格区间，即 purchase_date BETWEEN start_date AND end_date。

另外，没有任何销量的商品平均价要输出 0，所以要以 Prices 为主表做 LEFT JOIN，不能用 INNER JOIN，否则这类商品会消失。

1. # Write your MySQL query statement below
   
2. SELECT
   
3.     p.product_id,
   
4.     ROUND(IFNULL(SUM(p.price * u.units) / SUM(u.units), 0), 2) AS average_price
   
5. FROM Prices p
   
6. LEFT JOIN UnitsSold u
   
7.     ON p.product_id = u.product_id
   
8.    AND u.purchase_date BETWEEN p.start_date AND p.end_date
   
9. GROUP BY p.product_id;

复制代码

1264. Page Recommendations
Medium
Topics
conpanies icon
Companies
SQL Schema
Pandas Schema
Table: Friendship

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| user1_id      | int     |
| user2_id      | int     |
+---------------+---------+
(user1_id, user2_id) is the primary key (combination of columns with unique values) for this table.
Each row of this table indicates that there is a friendship relation between user1_id and user2_id.


Table: Likes

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| user_id     | int     |
| page_id     | int     |
+-------------+---------+
(user_id, page_id) is the primary key (combination of columns with unique values) for this table.
Each row of this table indicates that user_id likes page_id.


Write a solution to recommend pages to the user with user_id = 1 using the pages that your friends liked. It should not recommend pages you already liked.

Return result table in any order without duplicates.

The result format is in the following example.



Example 1:

Input:
Friendship table:
+----------+----------+
| user1_id | user2_id |
+----------+----------+
| 1        | 2        |
| 1        | 3        |
| 1        | 4        |
| 2        | 3        |
| 2        | 4        |
| 2        | 5        |
| 6        | 1        |
+----------+----------+
Likes table:
+---------+---------+
| user_id | page_id |
+---------+---------+
| 1       | 88      |
| 2       | 23      |
| 3       | 24      |
| 4       | 56      |
| 5       | 11      |
| 6       | 33      |
| 2       | 77      |
| 3       | 77      |
| 6       | 88      |
+---------+---------+
Output:
+------------------+
| recommended_page |
+------------------+
| 23               |
| 24               |
| 56               |
| 33               |
| 77               |
+------------------+
Explanation:
User one is friend with users 2, 3, 4 and 6.
Suggested pages are 23 from user 2, 24 from user 3, 56 from user 3 and 33 from user 6.
Page 77 is suggested from both user 2 and user 3.
Page 88 is not suggested because user 1 already likes it.

要推荐的页面 = 用户 1 的朋友点赞过的页面，减去 用户 1 自己已经点赞的页面，并且去重。

关键点有三个：

友谊是双向的：Friendship 表里一行 (a, b) 表示 a 和 b 互为朋友。用户 1 可能出现在 user1_id，也可能出现在 user2_id（如示例里的 (6, 1)），两种情况都要考虑。
排除已点赞的页面：如页面 88，用户 1 自己点过赞，不能推荐。
去重：页面 77 被用户 2 和 3 同时点赞，只输出一次。

1. # Write your MySQL query statement below
   
2. WITH friends AS (
   
3.     SELECT user2_id AS friend_id FROM Friendship WHERE user1_id = 1
   
4.     UNION
   
5.     SELECT user1_id AS friend_id FROM Friendship WHERE user2_id = 1
   
6. )
   
7. SELECT DISTINCT l.page_id AS recommended_page
   
8. FROM Likes l
   
9. JOIN friends f
   
10.     ON l.user_id = f.friend_id
    
11. WHERE l.page_id NOT IN (
    
12.     SELECT page_id FROM Likes WHERE user_id = 1
    
13. );

复制代码

1270. All People Report to the Given Manager
Medium
Topics
conpanies icon
Companies
SQL Schema
Pandas Schema
Table: Employees

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| employee_id   | int     |
| employee_name | varchar |
| manager_id    | int     |
+---------------+---------+
employee_id is the column of unique values for this table.
Each row of this table indicates that the employee with ID employee_id and name employee_name reports his work to his/her direct manager with manager_id
The head of the company is the employee with employee_id = 1.


Write a solution to find employee_id of all employees that directly or indirectly report their work to the head of the company.

The indirect relation between managers will not exceed three managers as the company is small.

Return the result table in any order.

The result format is in the following example.



Example 1:

Input:
Employees table:
+-------------+---------------+------------+
| employee_id | employee_name | manager_id |
+-------------+---------------+------------+
| 1           | Boss          | 1          |
| 3           | Alice         | 3          |
| 2           | Bob           | 1          |
| 4           | Daniel        | 2          |
| 7           | Luis          | 4          |
| 8           | Jhon          | 3          |
| 9           | Angela        | 8          |
| 77          | Robert        | 1          |
+-------------+---------------+------------+
Output:
+-------------+
| employee_id |
+-------------+
| 2           |
| 77          |
| 4           |
| 7           |
+-------------+
Explanation:
The head of the company is the employee with employee_id 1.
The employees with employee_id 2 and 77 report their work directly to the head of the company.
The employee with employee_id 4 reports their work indirectly to the head of the company 4 --> 2 --> 1.
The employee with employee_id 7 reports their work indirectly to the head of the company 7 --> 4 --> 2 --> 1.
The employees with employee_id 3, 8, and 9 do not report their work to the head of the company directly or indirectly.


思路

要找出所有直接或间接向总经理（employee_id = 1）汇报的员工。题目保证层级不超过 3 级，所以可以用自连接，沿着汇报链最多往上走 3 步，看最终是否到达 1。

有两个细节：

总经理的 manager_id 是他自己（示例里 1 | Boss | 1）。这个"自环"很有用：链条较短时，多出来的几次连接会一直停在 1 上，不需要分别写"1 级、2 级、3 级"三种情况。
总经理自己要排除，因为他的汇报链也能走到 1，但他不是"向总经理汇报"的人。

1. # Write your MySQL query statement below
   
2. SELECT e1.employee_id
   
3. FROM Employees e1
   
4. JOIN Employees e2 ON e1.manager_id = e2.employee_id
   
5. JOIN Employees e3 ON e2.manager_id = e3.employee_id
   
6. WHERE e3.manager_id = 1
   
7.   AND e1.employee_id != 1;

复制代码

1280. Students and Examinations
Easy
Topics
conpanies icon
Companies
SQL Schema
Pandas Schema
Table: Students

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| student_id    | int     |
| student_name  | varchar |
+---------------+---------+
student_id is the primary key (column with unique values) for this table.
Each row of this table contains the ID and the name of one student in the school.


Table: Subjects

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| subject_name | varchar |
+--------------+---------+
subject_name is the primary key (column with unique values) for this table.
Each row of this table contains the name of one subject in the school.


Table: Examinations

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| student_id   | int     |
| subject_name | varchar |
+--------------+---------+
There is no primary key (column with unique values) for this table. It may contain duplicates.
Each student from the Students table takes every course from the Subjects table.
Each row of this table indicates that a student with ID student_id attended the exam of subject_name.


Write a solution to find the number of times each student attended each exam.

Return the result table ordered by student_id and subject_name.

The result format is in the following example.



Example 1:

Input:
Students table:
+------------+--------------+
| student_id | student_name |
+------------+--------------+
| 1          | Alice        |
| 2          | Bob          |
| 13         | John         |
| 6          | Alex         |
+------------+--------------+
Subjects table:
+--------------+
| subject_name |
+--------------+
| Math         |
| Physics      |
| Programming  |
+--------------+
Examinations table:
+------------+--------------+
| student_id | subject_name |
+------------+--------------+
| 1          | Math         |
| 1          | Physics      |
| 1          | Programming  |
| 2          | Programming  |
| 1          | Physics      |
| 1          | Math         |
| 13         | Math         |
| 13         | Programming  |
| 13         | Physics      |
| 2          | Math         |
| 1          | Math         |
+------------+--------------+
Output:
+------------+--------------+--------------+----------------+
| student_id | student_name | subject_name | attended_exams |
+------------+--------------+--------------+----------------+
| 1          | Alice        | Math         | 3              |
| 1          | Alice        | Physics      | 2              |
| 1          | Alice        | Programming  | 1              |
| 2          | Bob          | Math         | 1              |
| 2          | Bob          | Physics      | 0              |
| 2          | Bob          | Programming  | 1              |
| 6          | Alex         | Math         | 0              |
| 6          | Alex         | Physics      | 0              |
| 6          | Alex         | Programming  | 0              |
| 13         | John         | Math         | 1              |
| 13         | John         | Physics      | 1              |
| 13         | John         | Programming  | 1              |
+------------+--------------+--------------+----------------+
Explanation:
The result table should contain all students and all subjects.
Alice attended the Math exam 3 times, the Physics exam 2 times, and the Programming exam 1 time.
Bob attended the Math exam 1 time, the Programming exam 1 time, and did not attend the Physics exam.
Alex did not attend any exams.
John attended the Math exam 1 time, the Physics exam 1 time, and the Programming exam 1 time.

思路

题目要求每个学生 × 每门科目的组合都要出现，即使这个学生一次都没考过（如 Alex），次数也要显示为 0。

用 Students 和 Subjects 做笛卡尔积（CROSS JOIN），生成所有"学生 × 科目"组合。
再 LEFT JOIN 到 Examinations，按学生和科目匹配考试记录。
按组合分组，数出考试次数。

1. # Write your MySQL query statement below
   
2. SELECT
   
3.     s.student_id,
   
4.     s.student_name,
   
5.     sub.subject_name,
   
6.     COUNT(e.subject_name) AS attended_exams
   
7. FROM Students s
   
8. CROSS JOIN Subjects sub
   
9. LEFT JOIN Examinations e
   
10.     ON s.student_id = e.student_id
    
11.    AND sub.subject_name = e.subject_name
    
12. GROUP BY s.student_id, s.student_name, sub.subject_name
    
13. ORDER BY s.student_id, sub.subject_name;

复制代码

1327. List the Products Ordered in a Period
Easy
Topics
conpanies icon
Companies
SQL Schema
Pandas Schema
Table: Products

+------------------+---------+
| Column Name      | Type    |
+------------------+---------+
| product_id       | int     |
| product_name     | varchar |
| product_category | varchar |
+------------------+---------+
product_id is the primary key (column with unique values) for this table.
This table contains data about the company's products.


Table: Orders

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| product_id    | int     |
| order_date    | date    |
| unit          | int     |
+---------------+---------+
This table may have duplicate rows.
product_id is a foreign key (reference column) to the Products table.
unit is the number of products ordered in order_date.


Write a solution to get the names of products that have at least 100 units ordered in February 2020 and their amount.

Return the result table in any order.

The result format is in the following example.



Example 1:

Input:
Products table:
+-------------+-----------------------+------------------+
| product_id  | product_name          | product_category |
+-------------+-----------------------+------------------+
| 1           | Leetcode Solutions    | Book             |
| 2           | Jewels of Stringology | Book             |
| 3           | HP                    | Laptop           |
| 4           | Lenovo                | Laptop           |
| 5           | Leetcode Kit          | T-shirt          |
+-------------+-----------------------+------------------+
Orders table:
+--------------+--------------+----------+
| product_id   | order_date   | unit     |
+--------------+--------------+----------+
| 1            | 2020-02-05   | 60       |
| 1            | 2020-02-10   | 70       |
| 2            | 2020-01-18   | 30       |
| 2            | 2020-02-11   | 80       |
| 3            | 2020-02-17   | 2        |
| 3            | 2020-02-24   | 3        |
| 4            | 2020-03-01   | 20       |
| 4            | 2020-03-04   | 30       |
| 4            | 2020-03-04   | 60       |
| 5            | 2020-02-25   | 50       |
| 5            | 2020-02-27   | 50       |
| 5            | 2020-03-01   | 50       |
+--------------+--------------+----------+
Output:
+--------------------+---------+
| product_name       | unit    |
+--------------------+---------+
| Leetcode Solutions | 130     |
| Leetcode Kit       | 100     |
+--------------------+---------+
Explanation:
Products with product_id = 1 is ordered in February a total of (60 + 70) = 130.
Products with product_id = 2 is ordered in February a total of 80.
Products with product_id = 3 is ordered in February a total of (2 + 3) = 5.
Products with product_id = 4 was not ordered in February 2020.
Products with product_id = 5 is ordered in February a total of (50 + 50) = 100.

思路

这题分三步：

筛选日期：只保留 2020 年 2 月的订单。
汇总：按商品分组，把 2 月内的 unit 加起来。
筛选总量：只保留总量 ≥ 100 的商品，输出商品名和总量。

最容易错的是先后顺序：日期条件要在汇总之前过滤（WHERE），总量条件要在汇总之后过滤（HAVING）。

1. SELECT
   
2.     p.product_name,
   
3.     SUM(o.unit) AS unit
   
4. FROM Products p
   
5. JOIN Orders o
   
6.     ON p.product_id = o.product_id
   
7. WHERE o.order_date >= '2020-02-01'
   
8.   AND o.order_date <  '2020-03-01'
   
9. GROUP BY p.product_id, p.product_name
   
10. HAVING SUM(o.unit) >= 100;

复制代码

1322. Ads Performance
Easy
Topics
SQL Schema
Pandas Schema
Table: Ads

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| ad_id         | int     |
| user_id       | int     |
| action        | enum    |
+---------------+---------+
(ad_id, user_id) is the primary key (combination of columns with unique values) for this table.
Each row of this table contains the ID of an Ad, the ID of a user, and the action taken by this user regarding this Ad.
The action column is an ENUM (category) type of ('Clicked', 'Viewed', 'Ignored').


A company is running Ads and wants to calculate the performance of each Ad.

Performance of the Ad is measured using Click-Through Rate (CTR) where:


Write a solution to find the ctr of each Ad. Round ctr to two decimal points.

Return the result table ordered by ctr in descending order and by ad_id in ascending order in case of a tie.

The result format is in the following example.



Example 1:

Input:
Ads table:
+-------+---------+---------+
| ad_id | user_id | action  |
+-------+---------+---------+
| 1     | 1       | Clicked |
| 2     | 2       | Clicked |
| 3     | 3       | Viewed  |
| 5     | 5       | Ignored |
| 1     | 7       | Ignored |
| 2     | 7       | Viewed  |
| 3     | 5       | Clicked |
| 1     | 4       | Viewed  |
| 2     | 11      | Viewed  |
| 1     | 2       | Clicked |
+-------+---------+---------+
Output:
+-------+-------+
| ad_id | ctr   |
+-------+-------+
| 1     | 66.67 |
| 3     | 50.00 |
| 2     | 33.33 |
| 5     | 0.00  |
+-------+-------+
Explanation:
for ad_id = 1, ctr = (2/(2+1)) * 100 = 66.67
for ad_id = 2, ctr = (1/(1+2)) * 100 = 33.33
for ad_id = 3, ctr = (1/(1+1)) * 100 = 50.00
for ad_id = 5, ctr = 0.00, Note that ad_id = 5 has no clicks or views.
Note that we do not care about Ignored Ads.

1. # Write your MySQL query statement below
   
2. SELECT
   
3.     ad_id,
   
4.     ROUND(
   
5.         IFNULL(
   
6.             SUM(action = 'Clicked') / SUM(action IN ('Clicked', 'Viewed')) * 100,
   
7.             0
   
8.         ),
   
9.         2
   
10.     ) AS ctr
    
11. FROM Ads
    
12. GROUP BY ad_id
    
13. ORDER BY ctr DESC, ad_id ASC;

复制代码

1321. Restaurant Growth
Medium
Topics
conpanies icon
Companies
SQL Schema
Pandas Schema
Table: Customer

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| customer_id   | int     |
| name          | varchar |
| visited_on    | date    |
| amount        | int     |
+---------------+---------+
In SQL,(customer_id, visited_on) is the primary key for this table.
This table contains data about customer transactions in a restaurant.
visited_on is the date on which the customer with ID (customer_id) has visited the restaurant.
amount is the total paid by a customer.


You are the restaurant owner and you want to analyze a possible expansion (there will be at least one customer every day).

Compute the moving average of how much the customer paid in a seven days window (i.e., current day + 6 days before). average_amount should be rounded to two decimal places.

Return the result table ordered by visited_on in ascending order.

The result format is in the following example.



Example 1:

Input:
Customer table:
+-------------+--------------+--------------+-------------+
| customer_id | name         | visited_on   | amount      |
+-------------+--------------+--------------+-------------+
| 1           | Jhon         | 2019-01-01   | 100         |
| 2           | Daniel       | 2019-01-02   | 110         |
| 3           | Jade         | 2019-01-03   | 120         |
| 4           | Khaled       | 2019-01-04   | 130         |
| 5           | Winston      | 2019-01-05   | 110         |
| 6           | Elvis        | 2019-01-06   | 140         |
| 7           | Anna         | 2019-01-07   | 150         |
| 8           | Maria        | 2019-01-08   | 80          |
| 9           | Jaze         | 2019-01-09   | 110         |
| 1           | Jhon         | 2019-01-10   | 130         |
| 3           | Jade         | 2019-01-10   | 150         |
+-------------+--------------+--------------+-------------+
Output:
+--------------+--------------+----------------+
| visited_on   | amount       | average_amount |
+--------------+--------------+----------------+
| 2019-01-07   | 860          | 122.86         |
| 2019-01-08   | 840          | 120            |
| 2019-01-09   | 840          | 120            |
| 2019-01-10   | 1000         | 142.86         |
+--------------+--------------+----------------+
Explanation:
1st moving average from 2019-01-01 to 2019-01-07 has an average_amount of (100 + 110 + 120 + 130 + 110 + 140 + 150)/7 = 122.86
2nd moving average from 2019-01-02 to 2019-01-08 has an average_amount of (110 + 120 + 130 + 110 + 140 + 150 + 80)/7 = 120
3rd moving average from 2019-01-03 to 2019-01-09 has an average_amount of (120 + 130 + 110 + 140 + 150 + 80 + 110)/7 = 120
4th moving average from 2019-01-04 to 2019-01-10 has an average_amount of (130 + 110 + 140 + 150 + 80 + 110 + 130 + 150)/7 = 142.86
思路

要算的是7 天滑动窗口（当天加前 6 天）的总金额和平均金额，只输出从第 7 天开始的结果，因为前 6 天凑不够 7 天。

有两个细节：

同一天可能有多个顾客，如 01-10 有两条记录（130 和 150）。窗口是按"天"算的，所以要先按 visited_on 汇总成"每天一行"，再做滑动窗口。
题目保证每天至少有一位顾客，日期是连续的，所以"往前 6 行"就等于"往前 6 天"。

1. # Write your MySQL query statement below
   
2. WITH daily AS (
   
3.     SELECT visited_on, SUM(amount) AS day_amount
   
4.     FROM Customer
   
5.     GROUP BY visited_on
   
6. )
   
7. SELECT
   
8.     a.visited_on,
   
9.     SUM(b.day_amount)           AS amount,
   
10.     ROUND(SUM(b.day_amount) / 7, 2) AS average_amount
    
11. FROM daily a
    
12. JOIN daily b
    
13.     ON b.visited_on BETWEEN DATE_SUB(a.visited_on, INTERVAL 6 DAY) AND a.visited_on
    
14. WHERE a.visited_on >= (SELECT DATE_ADD(MIN(visited_on), INTERVAL 6 DAY) FROM daily)
    
15. GROUP BY a.visited_on
    
16. ORDER BY a.visited_on;

复制代码

对 daily 的每一天 a，用日期范围找出它前 6 天到当天的所有记录 b。
对 b 求和就是 7 天总额，除以 7 就是平均值。
WHERE 里限定 a 的日期不早于"最早日期 + 6 天"，也就是第 7 天，这样每个窗口都完整。
这种写法按日期范围匹配，不依赖"日期连续"这个前提。

1308. Running Total for Different Genders
Medium
Topics
SQL Schema
Pandas Schema
Table: Scores

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| player_name   | varchar |
| gender        | varchar |
| day           | date    |
| score_points  | int     |
+---------------+---------+
(gender, day) is the primary key (combination of columns with unique values) for this table.
A competition is held between the female team and the male team.
Each row of this table indicates that a player_name and with gender has scored score_point in someday.
Gender is 'F' if the player is in the female team and 'M' if the player is in the male team.


Write a solution to find the total score for each gender on each day.

Return the result table ordered by gender and day in ascending order.

The result format is in the following example.



Example 1:

Input:
Scores table:
+-------------+--------+------------+--------------+
| player_name | gender | day        | score_points |
+-------------+--------+------------+--------------+
| Aron        | F      | 2020-01-01 | 17           |
| Alice       | F      | 2020-01-07 | 23           |
| Bajrang     | M      | 2020-01-07 | 7            |
| Khali       | M      | 2019-12-25 | 11           |
| Slaman      | M      | 2019-12-30 | 13           |
| Joe         | M      | 2019-12-31 | 3            |
| Jose        | M      | 2019-12-18 | 2            |
| Priya       | F      | 2019-12-31 | 23           |
| Priyanka    | F      | 2019-12-30 | 17           |
+-------------+--------+------------+--------------+
Output:
+--------+------------+-------+
| gender | day        | total |
+--------+------------+-------+
| F      | 2019-12-30 | 17    |
| F      | 2019-12-31 | 40    |
| F      | 2020-01-01 | 57    |
| F      | 2020-01-07 | 80    |
| M      | 2019-12-18 | 2     |
| M      | 2019-12-25 | 13    |
| M      | 2019-12-30 | 26    |
| M      | 2019-12-31 | 29    |
| M      | 2020-01-07 | 36    |
+--------+------------+-------+
Explanation:
For the female team:
The first day is 2019-12-30, Priyanka scored 17 points and the total score for the team is 17.
The second day is 2019-12-31, Priya scored 23 points and the total score for the team is 40.
The third day is 2020-01-01, Aron scored 17 points and the total score for the team is 57.
The fourth day is 2020-01-07, Alice scored 23 points and the total score for the team is 80.

For the male team:
The first day is 2019-12-18, Jose scored 2 points and the total score for the team is 2.
The second day is 2019-12-25, Khali scored 11 points and the total score for the team is 13.
The third day is 2019-12-30, Slaman scored 13 points and the total score for the team is 26.
The fourth day is 2019-12-31, Joe scored 3 points and the total score for the team is 29.
The fifth day is 2020-01-07, Bajrang scored 7 points and the total score for the team is 36.


思路

这是经典的**累计求和（running total）**问题：每个性别各自按日期排序，每一天的 total 是该性别从最早一天到当天的得分之和。

有两点要抓住：

按性别分开累计：男队和女队互不影响，所以要对 gender 分区。
不需要先分组汇总：(gender, day) 是主键，同一性别同一天只有一行，所以每行的 score_points 就是当天的得分，直接累计即可。

1. # Write your MySQL query statement below
   
2. SELECT
   
3.     gender,
   
4.     day,
   
5.     SUM(score_points) OVER (
   
6.         PARTITION BY gender
   
7.         ORDER BY day
   
8.         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
   
9.     ) AS total
   
10. FROM Scores
    
11. ORDER BY gender, day;

复制代码

逐步解释
PARTITION BY gender：男女各算各的，累计到另一个性别时重新从 0 开始。
ORDER BY day：在每个分区内按日期从早到晚排序，决定"累计到哪一天"。
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW：窗口从分区第一行到当前行。这其实就是加了 ORDER BY 之后的默认行为，显式写出来是为了意图更清楚。因为 (gender, day) 唯一，用 ROWS 和默认的 RANGE 结果一致。
外层 ORDER BY gender, day：窗口函数里的 ORDER BY 只决定累计顺序，不保证输出顺序，所以最后仍要显式排序。


SQL 解法二：自连接（不依赖窗口函数）

1. SELECT
   
2.     a.gender,
   
3.     a.day,
   
4.     SUM(b.score_points) AS total
   
5. FROM Scores a
   
6. JOIN Scores b
   
7.     ON a.gender = b.gender
   
8.    AND b.day <= a.day
   
9. GROUP BY a.gender, a.day
   
10. ORDER BY a.gender, a.day;

复制代码

对每一行 a，找出同性别且日期不晚于它的所有行 b，也就是"从最早到今天"的所有记录。
对这些行的得分求和，就是累计总分。
自连接会产生大约 N² / 2 级别的中间行，数据量大时比窗口函数慢，只适合不支持窗口函数的环境。

1303. Find the Team Size
Easy
Topics
SQL Schema
Pandas Schema
Table: Employee

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| employee_id   | int     |
| team_id       | int     |
+---------------+---------+
employee_id is the primary key (column with unique values) for this table.
Each row of this table contains the ID of each employee and their respective team.


Write a solution to find the team size of each of the employees.

Return the result table in any order.

The result format is in the following example.



Example 1:

Input:
Employee Table:
+-------------+------------+
| employee_id | team_id    |
+-------------+------------+
|     1       |     8      |
|     2       |     8      |
|     3       |     8      |
|     4       |     7      |
|     5       |     9      |
|     6       |     9      |
+-------------+------------+
Output:
+-------------+------------+
| employee_id | team_size  |
+-------------+------------+
|     1       |     3      |
|     2       |     3      |
|     3       |     3      |
|     4       |     1      |
|     5       |     2      |
|     6       |     2      |
+-------------+------------+
Explanation:
Employees with Id 1,2,3 are part of a team with team_id = 8.
Employee with Id 4 is part of a team with team_id = 7.
Employees with Id 5,6 are part of a team with team_id = 9.

思路

每个员工都要输出一行，并带上他所在团队的人数。所以不能直接 GROUP BY team_id，那样每个团队只剩一行，员工信息就丢了。需要的是"保留每一行，同时附上所在组的人数"。

有两种做法：

窗口函数：COUNT(*) OVER (PARTITION BY team_id)，每行保留，同时给出组内人数。
先分组再连接：先算出每个团队的人数，再连回员工表。

1. # Write your MySQL query statement below
   
2. SELECT
   
3.     employee_id,
   
4.     COUNT(*) OVER (PARTITION BY team_id) AS team_size
   
5. FROM Employee;

复制代码

逐步解释
PARTITION BY team_id：按团队分区，同一团队的员工在同一个分区里。
COUNT(*) OVER (...)：在每个分区内数行数，并把结果附在分区内的每一行上。与普通 GROUP BY 不同，窗口函数不会合并行，所以每个员工仍然各占一行。
不需要 ORDER BY：这里要的是整个分区的总人数，不是累计值，所以窗口里不写排序，窗口范围就是整个分区。
employee_id 是主键：没有重复员工，直接数行数即可。

1. # Write your MySQL query statement below
   
2. SELECT
   
3.     e.employee_id,
   
4.     t.team_size
   
5. FROM Employee e
   
6. JOIN (
   
7.     SELECT team_id, COUNT(*) AS team_size
   
8.     FROM Employee
   
9.     GROUP BY team_id
   
10. ) t
    
11.     ON e.team_id = t.team_id;

复制代码

子查询先按 team_id 分组，数出每个团队的人数。
再用 team_id 连回 Employee，每个员工就拿到了自己团队的人数。
这种写法不依赖窗口函数，在 MySQL 5.7 等旧版本里也能用。