刷题记录帖子

1164. Product Price at a Given Date
Medium
Topics
conpanies icon
Companies
SQL Schema
Pandas Schema
Table: Products

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| product_id    | int     |
| new_price     | int     |
| change_date   | date    |
+---------------+---------+
(product_id, change_date) is the primary key (combination of columns with unique values) of this table.
Each row of this table indicates that the price of some product was changed to a new price at some date.
Initially, all products have price 10.

Write a solution to find the prices of all products on the date 2019-08-16.

Return the result table in any order.

The result format is in the following example.



Example 1:

Input:
Products table:
+------------+-----------+-------------+
| product_id | new_price | change_date |
+------------+-----------+-------------+
| 1          | 20        | 2019-08-14  |
| 2          | 50        | 2019-08-14  |
| 1          | 30        | 2019-08-15  |
| 1          | 35        | 2019-08-16  |
| 2          | 65        | 2019-08-17  |
| 3          | 20        | 2019-08-18  |
+------------+-----------+-------------+
Output:
+------------+-------+
| product_id | price |
+------------+-------+
| 2          | 50    |
| 1          | 35    |
| 3          | 10    |
+------------+-------+

1. 
   
2. SELECT
   
3.     product_id,
   
4.     10 AS price
   
5. FROM Products
   
6. GROUP BY product_id
   
7. HAVING MIN(change_date) > '2019-08-16'
   
8. 
   
9. UNION
   
10. 
    
11. SELECT
    
12.     product_id,
    
13.     new_price AS price
    
14. FROM Products
    
15. WHERE (product_id, change_date) IN (
    
16.     SELECT
    
17.         product_id,
    
18.         MAX(change_date)
    
19.     FROM Products
    
20.     WHERE change_date <= '2019-08-16'
    
21.     GROUP BY product_id
    
22. );
    

复制代码

1173. Immediate Food Delivery I
Easy
Topics
SQL Schema
Pandas Schema
Table: Delivery

+-----------------------------+---------+
| Column Name                 | Type    |
+-----------------------------+---------+
| delivery_id                 | int     |
| customer_id                 | int     |
| order_date                  | date    |
| customer_pref_delivery_date | date    |
+-----------------------------+---------+
delivery_id is the primary key (column with unique values) of this table.
The table holds information about food delivery to customers that make orders at some date and specify a preferred delivery date (on the same order date or after it).


If the customer's preferred delivery date is the same as the order date, then the order is called immediate; otherwise, it is called scheduled.

Write a solution to find the percentage of immediate orders in the table, rounded to 2 decimal places.

The result format is in the following example.



Example 1:

Input:
Delivery table:
+-------------+-------------+------------+-----------------------------+
| delivery_id | customer_id | order_date | customer_pref_delivery_date |
+-------------+-------------+------------+-----------------------------+
| 1           | 1           | 2019-08-01 | 2019-08-02                  |
| 2           | 5           | 2019-08-02 | 2019-08-02                  |
| 3           | 1           | 2019-08-11 | 2019-08-11                  |
| 4           | 3           | 2019-08-24 | 2019-08-26                  |
| 5           | 4           | 2019-08-21 | 2019-08-22                  |
| 6           | 2           | 2019-08-11 | 2019-08-13                  |
+-------------+-------------+------------+-----------------------------+
Output:
+----------------------+
| immediate_percentage |
+----------------------+
| 33.33                |
+----------------------+
Explanation: The orders with delivery id 2 and 3 are immediate while the others are scheduled.

1. # Write your MySQL query statement below
   
2. 
   
3. SELECT
   
4.     ROUND(
   
5.         SUM(order_date = customer_pref_delivery_date) * 100.0 / COUNT(*),
   
6.         2
   
7.     ) AS immediate_percentage
   
8. FROM Delivery;
   

复制代码

1174. Immediate Food Delivery II
Medium
Topics
conpanies icon
Companies
SQL Schema
Pandas Schema
Table: Delivery

+-----------------------------+---------+
| Column Name                 | Type    |
+-----------------------------+---------+
| delivery_id                 | int     |
| customer_id                 | int     |
| order_date                  | date    |
| customer_pref_delivery_date | date    |
+-----------------------------+---------+
delivery_id is the column of unique values of this table.
The table holds information about food delivery to customers that make orders at some date and specify a preferred delivery date (on the same order date or after it).


If the customer's preferred delivery date is the same as the order date, then the order is called immediate; otherwise, it is called scheduled.

The first order of a customer is the order with the earliest order date that the customer made. It is guaranteed that a customer has precisely one first order.

Write a solution to find the percentage of immediate orders in the first orders of all customers, rounded to 2 decimal places.

The result format is in the following example.



Example 1:

Input:
Delivery table:
+-------------+-------------+------------+-----------------------------+
| delivery_id | customer_id | order_date | customer_pref_delivery_date |
+-------------+-------------+------------+-----------------------------+
| 1           | 1           | 2019-08-01 | 2019-08-02                  |
| 2           | 2           | 2019-08-02 | 2019-08-02                  |
| 3           | 1           | 2019-08-11 | 2019-08-12                  |
| 4           | 3           | 2019-08-24 | 2019-08-24                  |
| 5           | 3           | 2019-08-21 | 2019-08-22                  |
| 6           | 2           | 2019-08-11 | 2019-08-13                  |
| 7           | 4           | 2019-08-09 | 2019-08-09                  |
+-------------+-------------+------------+-----------------------------+
Output:
+----------------------+
| immediate_percentage |
+----------------------+
| 50.00                |
+----------------------+
Explanation:
The customer id 1 has a first order with delivery id 1 and it is scheduled.
The customer id 2 has a first order with delivery id 2 and it is immediate.
The customer id 3 has a first order with delivery id 5 and it is scheduled.
The customer id 4 has a first order with delivery id 7 and it is immediate.
Hence, half the customers have immediate first orders.

1. # Write your MySQL query statement below
   
2. 
   
3. WITH FirstOrder AS (
   
4.     SELECT
   
5.         *,
   
6.         ROW_NUMBER() OVER (
   
7.             PARTITION BY customer_id
   
8.             ORDER BY order_date
   
9.         ) AS rn
   
10.     FROM Delivery
    
11. )
    
12. SELECT
    
13.     ROUND(
    
14.         SUM(order_date = customer_pref_delivery_date) * 100.0 / COUNT(*),
    
15.         2
    
16.     ) AS immediate_percentage
    
17. FROM FirstOrder
    
18. WHERE rn = 1;
    

复制代码

1193. Monthly Transactions I
Medium
Topics
conpanies icon
Companies
SQL Schema
Pandas Schema
Table: Transactions

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| country       | varchar |
| state         | enum    |
| amount        | int     |
| trans_date    | date    |
+---------------+---------+
id is the primary key of this table.
The table has information about incoming transactions.
The state column is an enum of type ["approved", "declined"].


Write an SQL query to find for each month and country, the number of transactions and their total amount, the number of approved transactions and their total amount.

Return the result table in any order.

The query result format is in the following example.

1. 
   
2. SELECT
   
3.     DATE_FORMAT(trans_date, '%Y-%m') AS month,
   
4.     country,
   
5.     COUNT(*) AS trans_count,
   
6.     SUM(amount) AS trans_total_amount,
   
7.     SUM(CASE WHEN state = 'approved' THEN 1 ELSE 0 END) AS approved_count,
   
8.     SUM(CASE WHEN state = 'approved' THEN amount ELSE 0 END) AS approved_total_amount
   
9. FROM Transactions
   
10. GROUP BY
    
11.     DATE_FORMAT(trans_date, '%Y-%m'),
    
12.     country;
    

复制代码

1194. Tournament Winners
Hard
Topics
SQL Schema
Pandas Schema
Table: Players

+-------------+-------+
| Column Name | Type  |
+-------------+-------+
| player_id   | int   |
| group_id    | int   |
+-------------+-------+
player_id is the primary key (column with unique values) of this table.
Each row of this table indicates the group of each player.
Table: Matches

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| match_id      | int     |
| first_player  | int     |
| second_player | int     |
| first_score   | int     |
| second_score  | int     |
+---------------+---------+
match_id is the primary key (column with unique values) of this table.
Each row is a record of a match, first_player and second_player contain the player_id of each match.
first_score and second_score contain the number of points of the first_player and second_player respectively.
You may assume that, in each match, players belong to the same group.


The winner in each group is the player who scored the maximum total points within the group. In the case of a tie, the lowest player_id wins.

Write a solution to find the winner in each group.

Return the result table in any order.

The result format is in the following example.



Example 1:

Input:
Players table:
+-----------+------------+
| player_id | group_id   |
+-----------+------------+
| 15        | 1          |
| 25        | 1          |
| 30        | 1          |
| 45        | 1          |
| 10        | 2          |
| 35        | 2          |
| 50        | 2          |
| 20        | 3          |
| 40        | 3          |
+-----------+------------+
Matches table:
+------------+--------------+---------------+-------------+--------------+
| match_id   | first_player | second_player | first_score | second_score |
+------------+--------------+---------------+-------------+--------------+
| 1          | 15           | 45            | 3           | 0            |
| 2          | 30           | 25            | 1           | 2            |
| 3          | 30           | 15            | 2           | 0            |
| 4          | 40           | 20            | 5           | 2            |
| 5          | 35           | 50            | 1           | 1            |
+------------+--------------+---------------+-------------+--------------+
Output:
+-----------+------------+
| group_id  | player_id  |
+-----------+------------+
| 1         | 15         |
| 2         | 35         |
| 3         | 40         |
+-----------+------------+

1. # Write your MySQL query statement below
   
2. WITH scores AS (
   
3.     SELECT
   
4.         player_id,
   
5.         SUM(score) AS total_score
   
6.     FROM (
   
7.         SELECT
   
8.             first_player AS player_id,
   
9.             first_score AS score
   
10.         FROM Matches
    
11. 
    
12.         UNION ALL
    
13. 
    
14.         SELECT
    
15.             second_player AS player_id,
    
16.             second_score AS score
    
17.         FROM Matches
    
18.     ) AS all_scores
    
19.     GROUP BY player_id
    
20. ),
    
21. ranked AS (
    
22.     SELECT
    
23.         p.group_id,
    
24.         p.player_id,
    
25.         ROW_NUMBER() OVER (
    
26.             PARTITION BY p.group_id
    
27.             ORDER BY COALESCE(s.total_score, 0) DESC, p.player_id ASC
    
28.         ) AS rn
    
29.     FROM Players p
    
30.     LEFT JOIN scores s
    
31.         ON p.player_id = s.player_id
    
32. )
    
33. SELECT
    
34.     group_id,
    
35.     player_id
    
36. FROM ranked
    
37. WHERE rn = 1;

复制代码

1204. Last Person to Fit in the Bus
Medium
Topics
conpanies icon
Companies
SQL Schema
Pandas Schema
Table: Queue

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| person_id   | int     |
| person_name | varchar |
| weight      | int     |
| turn        | int     |
+-------------+---------+
person_id column contains unique values.
This table has the information about all people waiting for a bus.
The person_id and turn columns will contain all numbers from 1 to n, where n is the number of rows in the table.
turn determines the order of which the people will board the bus, where turn=1 denotes the first person to board and turn=n denotes the last person to board.
weight is the weight of the person in kilograms.


There is a queue of people waiting to board a bus. However, the bus has a weight limit of 1000 kilograms, so there may be some people who cannot board.

Write a solution to find the person_name of the last person that can fit on the bus without exceeding the weight limit. The test cases are generated such that the first person does not exceed the weight limit.

Note that only one person can board the bus at any given turn.

The result format is in the following example.



Example 1:

Input:
Queue table:
+-----------+-------------+--------+------+
| person_id | person_name | weight | turn |
+-----------+-------------+--------+------+
| 5         | Alice       | 250    | 1    |
| 4         | Bob         | 175    | 5    |
| 3         | Alex        | 350    | 2    |
| 6         | John Cena   | 400    | 3    |
| 1         | Winston     | 500    | 6    |
| 2         | Marie       | 200    | 4    |
+-----------+-------------+--------+------+
Output:
+-------------+
| person_name |
+-------------+
| John Cena   |
+-------------+
Explanation: The folowing table is ordered by the turn for simplicity.
+------+----+-----------+--------+--------------+
| Turn | ID | Name      | Weight | Total Weight |
+------+----+-----------+--------+--------------+
| 1    | 5  | Alice     | 250    | 250          |
| 2    | 3  | Alex      | 350    | 600          |
| 3    | 6  | John Cena | 400    | 1000         | (last person to board)
| 4    | 2  | Marie     | 200    | 1200         | (cannot board)
| 5    | 4  | Bob       | 175    | ___          |
| 6    | 1  | Winston   | 500    | ___          |
+------+----+-----------+--------+--------------+

1. # Write your MySQL query statement below
   
2. SELECT person_name
   
3. FROM (
   
4.     SELECT
   
5.         person_name,
   
6.         turn,
   
7.         SUM(weight) OVER (ORDER BY turn) AS total_weight
   
8.     FROM Queue
   
9. ) AS q
   
10. WHERE total_weight <= 1000
    
11. ORDER BY turn DESC
    
12. LIMIT 1;

复制代码

解题思路
第一步：计算每个人上车后的累计体重。
SUM(weight) OVER (ORDER BY turn) AS total_weight


ORDER BY turn 保证按照上车顺序累计体重。

例如题目中的数据：
turn        person_name        weight        total_weight
1        Alice        250        250
2        Alex        350        600
3        John Cena        400        1000
4        Marie        200        1200
5        Bob        175        1375
6        Winston        500        1875

第二步：过滤掉累计体重超过 1000 kg 的人。
WHERE total_weight <= 1000


剩下 Alice、Alex 和 John Cena。

第三步：找到最后上车的人。
ORDER BY turn DESC
LIMIT 1;


按 turn 降序排列，取第一条记录，结果就是 John Cena。
关键知识点
- SUM(weight) OVER (ORDER BY turn)：计算累计和，不会像 GROUP BY 一样把多行合并。
- WHERE：不能直接在同一层查询中引用窗口函数的计算结果，因此需要使用子查询。
- ORDER BY turn DESC LIMIT 1：取符合体重限制条件的人中，上车顺序最后的一位。
记忆技巧： 遇到 SQL 题目中需要计算累计金额、累计体重、累计销售额等问题，优先考虑窗口函数 SUM() OVER (ORDER BY ...)。

1212. Team Scores in Football Tournament
Medium
Topics
SQL Schema
Pandas Schema
Table: Teams

+---------------+----------+
| Column Name   | Type     |
+---------------+----------+
| team_id       | int      |
| team_name     | varchar  |
+---------------+----------+
team_id is the column with unique values of this table.
Each row of this table represents a single football team.


Table: Matches

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| match_id      | int     |
| host_team     | int     |
| guest_team    | int     |
| host_goals    | int     |
| guest_goals   | int     |
+---------------+---------+
match_id is the column of unique values of this table.
Each row is a record of a finished match between two different teams.
Teams host_team and guest_team are represented by their IDs in the Teams table (team_id), and they scored host_goals and guest_goals goals, respectively.


You would like to compute the scores of all teams after all matches. Points are awarded as follows:
A team receives three points if they win a match (i.e., Scored more goals than the opponent team).
A team receives one point if they draw a match (i.e., Scored the same number of goals as the opponent team).
A team receives no points if they lose a match (i.e., Scored fewer goals than the opponent team).
Write a solution that selects the team_id, team_name and num_points of each team in the tournament after all described matches.

Return the result table ordered by num_points in decreasing order. In case of a tie, order the records by team_id in increasing order.

The result format is in the following example.



Example 1:

Input:
Teams table:
+-----------+--------------+
| team_id   | team_name    |
+-----------+--------------+
| 10        | Leetcode FC  |
| 20        | NewYork FC   |
| 30        | Atlanta FC   |
| 40        | Chicago FC   |
| 50        | Toronto FC   |
+-----------+--------------+
Matches table:
+------------+--------------+---------------+-------------+--------------+
| match_id   | host_team    | guest_team    | host_goals  | guest_goals  |
+------------+--------------+---------------+-------------+--------------+
| 1          | 10           | 20            | 3           | 0            |
| 2          | 30           | 10            | 2           | 2            |
| 3          | 10           | 50            | 5           | 1            |
| 4          | 20           | 30            | 1           | 0            |
| 5          | 50           | 30            | 1           | 0            |
+------------+--------------+---------------+-------------+--------------+
Output:
+------------+--------------+---------------+
| team_id    | team_name    | num_points    |
+------------+--------------+---------------+
| 10         | Leetcode FC  | 7             |
| 20         | NewYork FC   | 3             |
| 50         | Toronto FC   | 3             |
| 30         | Atlanta FC   | 1             |
| 40         | Chicago FC   | 0             |
+------------+--------------+---------------+

1. SELECT
   
2.     t.team_id,
   
3.     t.team_name,
   
4.     SUM(
   
5.         CASE
   
6.             WHEN t.team_id = m.host_team  AND m.host_goals  > m.guest_goals THEN 3
   
7.             WHEN t.team_id = m.guest_team AND m.guest_goals > m.host_goals  THEN 3
   
8.             WHEN m.host_goals = m.guest_goals THEN 1
   
9.             ELSE 0
   
10.         END
    
11.     ) AS num_points
    
12. FROM Teams t
    
13. LEFT JOIN Matches m
    
14.     ON t.team_id = m.host_team OR t.team_id = m.guest_team
    
15. GROUP BY t.team_id, t.team_name
    
16. ORDER BY num_points DESC, t.team_id ASC;

复制代码

思路

要算每支球队的总积分，难点有两个：

一支球队可能是主队，也可能是客队，两种身份都要统计。
有些球队一场比赛都没踢（如 Chicago FC），也要出现在结果里，积分为 0。

所以用 Teams 表做主表，LEFT JOIN 到 Matches 表。

逐步解释

1. 连接条件用 OR

ON t.team_id = m.host_team OR t.team_id = m.guest_team 的意思是：只要这支球队出现在某场比赛里（不管是主队还是客队），就和这场比赛连成一行。所以一支球队踢了几场，就会产生几行。

2. 用 CASE WHEN 从这支球队的视角算分

第一个分支：这支球队是主队，且主队进球多，则赢球，得 3 分。
第二个分支：这支球队是客队，且客队进球多，则赢球，得 3 分。
第三个分支：双方进球数相同，则平局，得 1 分。平局对双方是对称的，所以不用判断主客队。
其余情况：输球，得 0 分。

3. 没踢过比赛的球队

LEFT JOIN 保证 Teams 里的每支球队都保留。没比赛的球队，m 表的所有列都是 NULL，前面几个条件的结果都是 NULL（相当于 false），于是走到 ELSE 0，SUM 之后就是 0。所以不需要 COALESCE。

如果用 INNER JOIN，Chicago FC 这种球队会直接消失，这是最容易错的地方。

4. 排序

先按 num_points 降序，积分相同再按 team_id 升序。

用示例验证
球队        比赛情况        积分
10        3:0 赢，2:2 平，5:1 赢        3+1+3 = 7
20        0:3 输，1:0 赢        0+3 = 3
50        1:5 输，1:0 赢        0+3 = 3
30        2:2 平，0:1 输，0:1 输        1+0+0 = 1
40        没有比赛        0

20 和 50 都是 3 分，按 team_id 升序，20 排在 50 前面，与预期输出一致。

1225. Report Contiguous Dates
Hard
Topics
SQL Schema
Pandas Schema
Table: Failed

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| fail_date    | date    |
+--------------+---------+
fail_date is the primary key (column with unique values) for this table.
This table contains the days of failed tasks.


Table: Succeeded

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| success_date | date    |
+--------------+---------+
success_date is the primary key (column with unique values) for this table.
This table contains the days of succeeded tasks.


A system is running one task every day. Every task is independent of the previous tasks. The tasks can fail or succeed.

Write a solution to report the period_state for each continuous interval of days in the period from 2019-01-01 to 2019-12-31.

period_state is 'failed' if tasks in this interval failed or 'succeeded' if tasks in this interval succeeded. Interval of days are retrieved as start_date and end_date.

Return the result table ordered by start_date.

The result format is in the following example.



Example 1:

Input:
Failed table:
+-------------------+
| fail_date         |
+-------------------+
| 2018-12-28        |
| 2018-12-29        |
| 2019-01-04        |
| 2019-01-05        |
+-------------------+
Succeeded table:
+-------------------+
| success_date      |
+-------------------+
| 2018-12-30        |
| 2018-12-31        |
| 2019-01-01        |
| 2019-01-02        |
| 2019-01-03        |
| 2019-01-06        |
+-------------------+
Output:
+--------------+--------------+--------------+
| period_state | start_date   | end_date     |
+--------------+--------------+--------------+
| succeeded    | 2019-01-01   | 2019-01-03   |
| failed       | 2019-01-04   | 2019-01-05   |
| succeeded    | 2019-01-06   | 2019-01-06   |
+--------------+--------------+--------------+
Explanation:
The report ignored the system state in 2018 as we care about the system in the period 2019-01-01 to 2019-12-31.
From 2019-01-01 to 2019-01-03 all tasks succeeded and the system state was "succeeded".
From 2019-01-04 to 2019-01-05 all tasks failed and the system state was "failed".
From 2019-01-06 to 2019-01-06 all tasks succeeded and the system state was "succeeded".

思路

这是经典的**连续区间（gaps and islands）**问题。

先把两张表合并成一张"每天一行"的表，每行带上状态（failed / succeeded），再把连续的、状态相同的日期分成一组，每组取最小和最大日期。

核心技巧：日期减去行号

在同一种状态内，按日期排序并编号 rn = 1, 2, 3, ...。

如果日期连续，每往后一天，日期加 1，行号也加 1，所以 日期 - rn 保持不变。
一旦出现间断（中间夹着另一种状态的日子），日期会多跳，日期 - rn 就变了。

所以 日期 - rn 相同的行就属于同一个连续区间。

1. # Write your MySQL query statement below
   
2. WITH all_days AS (
   
3.     SELECT fail_date AS dt, 'failed' AS period_state
   
4.     FROM Failed
   
5.     WHERE fail_date BETWEEN '2019-01-01' AND '2019-12-31'
   
6.     UNION ALL
   
7.     SELECT success_date, 'succeeded'
   
8.     FROM Succeeded
   
9.     WHERE success_date BETWEEN '2019-01-01' AND '2019-12-31'
   
10. ),
    
11. grouped AS (
    
12.     SELECT
    
13.         dt,
    
14.         period_state,
    
15.         DATE_SUB(
    
16.             dt,
    
17.             INTERVAL ROW_NUMBER() OVER (PARTITION BY period_state ORDER BY dt) DAY
    
18.         ) AS grp
    
19.     FROM all_days
    
20. )
    
21. SELECT
    
22.     period_state,
    
23.     MIN(dt) AS start_date,
    
24.     MAX(dt) AS end_date
    
25. FROM grouped
    
26. GROUP BY period_state, grp
    
27. ORDER BY start_date;

复制代码

1205. Monthly Transactions II
Medium
Topics
SQL Schema
Pandas Schema
Table: Transactions

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| id             | int     |
| country        | varchar |
| state          | enum    |
| amount         | int     |
| trans_date     | date    |
+----------------+---------+
id is the column of unique values of this table.
The table has information about incoming transactions.
The state column is an ENUM (category) of type ["approved", "declined"].
Table: Chargebacks

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| trans_id       | int     |
| trans_date     | date    |
+----------------+---------+
Chargebacks contains basic information regarding incoming chargebacks from some transactions placed in Transactions table.
trans_id is a foreign key (reference column) to the id column of Transactions table.
Each chargeback corresponds to a transaction made previously even if they were not approved.


Write a solution to find for each month and country: the number of approved transactions and their total amount, the number of chargebacks, and their total amount.

Note: In your solution, given the month and country, ignore rows with all zeros.

Return the result table in any order.

The result format is in the following example.



Example 1:

Input:
Transactions table:
+-----+---------+----------+--------+------------+
| id  | country | state    | amount | trans_date |
+-----+---------+----------+--------+------------+
| 101 | US      | approved | 1000   | 2019-05-18 |
| 102 | US      | declined | 2000   | 2019-05-19 |
| 103 | US      | approved | 3000   | 2019-06-10 |
| 104 | US      | declined | 4000   | 2019-06-13 |
| 105 | US      | approved | 5000   | 2019-06-15 |
+-----+---------+----------+--------+------------+
Chargebacks table:
+----------+------------+
| trans_id | trans_date |
+----------+------------+
| 102      | 2019-05-29 |
| 101      | 2019-06-30 |
| 105      | 2019-09-18 |
+----------+------------+
Output:
+---------+---------+----------------+-----------------+------------------+-------------------+
| month   | country | approved_count | approved_amount | chargeback_count | chargeback_amount |
+---------+---------+----------------+-----------------+------------------+-------------------+
| 2019-05 | US      | 1              | 1000            | 1                | 2000              |
| 2019-06 | US      | 2              | 8000            | 1                | 1000              |
| 2019-09 | US      | 0              | 0               | 1                | 5000              |
+---------+---------+----------------+-----------------+------------------+-------------------+


思路

这题的难点是：批准交易和退单属于不同的月份，而且可能出现在不同的月份、国家组合里。比如示例里 2019-09 只有退单，没有批准交易，但仍然要输出。

最简单的做法是把两种事件都变成统一格式的行，再一起汇总：

批准交易：按交易自己的日期取月份，approved_count = 1，approved_amount = amount，退单两列为 0。
退单：按退单日期取月份，国家和金额要回到 Transactions 表里查，chargeback_count = 1，chargeback_amount = amount，批准两列为 0。

两部分用 UNION ALL 合并，再按 (month, country) 分组求和。

这样做的好处是，只有真正发生过事件的 (month, country) 才会有行，所以"忽略全 0 的行"自然满足，也不需要 FULL OUTER JOIN（MySQL 不支持）。

1. # Write your MySQL query statement below
   
2. WITH events AS (
   
3.     SELECT
   
4.         DATE_FORMAT(trans_date, '%Y-%m') AS month,
   
5.         country,
   
6.         1 AS approved_count,
   
7.         amount AS approved_amount,
   
8.         0 AS chargeback_count,
   
9.         0 AS chargeback_amount
   
10.     FROM Transactions
    
11.     WHERE state = 'approved'
    
12. 
    
13.     UNION ALL
    
14. 
    
15.     SELECT
    
16.         DATE_FORMAT(c.trans_date, '%Y-%m') AS month,
    
17.         t.country,
    
18.         0, 0, 1, t.amount
    
19.     FROM Chargebacks c
    
20.     JOIN Transactions t ON c.trans_id = t.id
    
21. )
    
22. SELECT
    
23.     month,
    
24.     country,
    
25.     SUM(approved_count)    AS approved_count,
    
26.     SUM(approved_amount)   AS approved_amount,
    
27.     SUM(chargeback_count)  AS chargeback_count,
    
28.     SUM(chargeback_amount) AS chargeback_amount
    
29. FROM events
    
30. GROUP BY month, country;

复制代码

1211. Queries Quality and Percentage
Easy
Topics
conpanies icon
Companies
SQL Schema
Pandas Schema
Table: Queries

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| query_name  | varchar |
| result      | varchar |
| position    | int     |
| rating      | int     |
+-------------+---------+
This table may have duplicate rows.
This table contains information collected from some queries on a database.
The position column has a value from 1 to 500.
The rating column has a value from 1 to 5. Query with rating less than 3 is a poor query.


We define query quality as:

The average of the ratio between query rating and its position.

We also define poor query percentage as:

The percentage of all queries with rating less than 3.

Write a solution to find each query_name, the quality and poor_query_percentage.

Both quality and poor_query_percentage should be rounded to 2 decimal places.

Return the result table in any order.

The result format is in the following example.



Example 1:

Input:
Queries table:
+------------+-------------------+----------+--------+
| query_name | result            | position | rating |
+------------+-------------------+----------+--------+
| Dog        | Golden Retriever  | 1        | 5      |
| Dog        | German Shepherd   | 2        | 5      |
| Dog        | Mule              | 200      | 1      |
| Cat        | Shirazi           | 5        | 2      |
| Cat        | Siamese           | 3        | 3      |
| Cat        | Sphynx            | 7        | 4      |
+------------+-------------------+----------+--------+
Output:
+------------+---------+-----------------------+
| query_name | quality | poor_query_percentage |
+------------+---------+-----------------------+
| Dog        | 2.50    | 33.33                 |
| Cat        | 0.66    | 33.33                 |
+------------+---------+-----------------------+
Explanation:
Dog queries quality is ((5 / 1) + (5 / 2) + (1 / 200)) / 3 = 2.50
Dog queries poor_ query_percentage is (1 / 3) * 100 = 33.33

Cat queries quality equals ((2 / 5) + (3 / 3) + (4 / 7)) / 3 = 0.66
Cat queries poor_ query_percentage is (1 / 3) * 100 = 33.33


这题不需要连接或子查询，一次 GROUP BY query_name 就够了。两个指标都是"平均值"：

quality：每行的 rating / position 的平均值。
poor_query_percentage：rating < 3 的行占总行数的百分比。

第二个指标的技巧是：把"是否为差评"看成 0/1，这个 0/1 的平均值就是差评占比，乘以 100 即为百分比。

1. # Write your MySQL query statement below
   
2. SELECT
   
3.     query_name,
   
4.     ROUND(AVG(rating / position), 2)    AS quality,
   
5.     ROUND(AVG(rating < 3) * 100, 2)     AS poor_query_percentage
   
6. FROM Queries
   
7. GROUP BY query_name;

复制代码