【Leecode-专项突破-SQL基础】day2

今日专项训练 4 题：3 简单 1 中等

1193.每月交易 I

输入：
Transactions table:
+------+---------+----------+--------+------------+
| id   | country | state    | amount | trans_date |
+------+---------+----------+--------+------------+
| 121  | US      | approved | 1000   | 2018-12-18 |
| 122  | US      | declined | 2000   | 2018-12-19 |
| 123  | US      | approved | 2000   | 2019-01-01 |
| 124  | DE      | approved | 2000   | 2019-01-07 |
+------+---------+----------+--------+------------+
输出：
+----------+---------+-------------+----------------+--------------------+-----------------------+
| month    | country | trans_count | approved_count | trans_total_amount | approved_total_amount |
+----------+---------+-------------+----------------+--------------------+-----------------------+
| 2018-12  | US      | 2           | 1              | 3000               | 1000                  |
| 2019-01  | US      | 1           | 1              | 2000               | 2000                  |
| 2019-01  | DE      | 1           | 1              | 2000               | 2000                  |
+----------+---------+-------------+----------------+--------------------+-----------------------+

编写一个 sql 查询来查找每个月和每个国家/地区的事务数及其总金额、已批准的事务数及其总金额。以任意顺序返回结果表。
直接上SQL：

select substr(trans_date,1,7) as 'month', country
  ,count(id) as trans_count
  ,sum(case when state = 'approved' then 1 else 0 end ) as approved_count
  ,sum(amount) as trans_total_amount
  ,sum(case when state = 'approved' then amount else 0 end ) as approved_total_amount
  from Transactions
  group by substr(trans_date,1,7),country ;

这里的substr(trans_date,1,7)也可以切换为DATE_FORMAT(trans_date, '%Y-%m')

1193.各赛事的用户注册率

输入：
Users 表：
+---------+-----------+
| user_id | user_name |
+---------+-----------+
| 6       | Alice     |
| 2       | Bob       |
| 7       | Alex      |
+---------+-----------+
#user_id 是该表的主键

Register 表：
+------------+---------+
| contest_id | user_id |
+------------+---------+
| 215        | 6       |
| 209        | 2       |
| 208        | 2       |
| 210        | 6       |
| 208        | 6       |
| 209        | 7       |
| 209        | 6       |
| 215        | 7       |
| 208        | 7       |
| 210        | 2       |
| 207        | 2       |
| 210        | 7       |
+------------+---------+
#(contest_id, user_id) 是该表的主键

输出：
+------------+------------+
| contest_id | percentage |
+------------+------------+
| 208        | 100.0      |
| 209        | 100.0      |
| 210        | 100.0      |
| 215        | 66.67      |
| 207        | 33.33      |
+------------+------------+

写一条 SQL 语句，查询各赛事的用户注册百分率，保留两位小数。返回的结果表按 percentage 的降序排序，若相同则按 contest_id 的升序排序

select contest_id
  ,round(count(contest_id)/(select count(1) from Users) * 100, 2) as percentage
  from Register
  group by contest_id
  order by percentage desc,contest_id ;

1173.即时食物配送 I

输入：
Delivery 表:
+-------------+-------------+------------+-----------------------------+
| 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                  |
+-------------+-------------+------------+-----------------------------+
#delivery_id 是表的主键。
#该表保存着顾客的食物配送信息，顾客在某个日期下了订单，并指定了一个期望的配送日期（和下单日期相同或者在那之后）
输出：
+----------------------+
| immediate_percentage |
+----------------------+
| 33.33                |
+----------------------+

如果顾客期望的配送日期和下单日期相同，则该订单称为 「即时订单」，否则称为「计划订单」。写一条 SQL 查询语句获取即时订单所占的百分比， 保留两位小数。

select round(sum(t.imm_flag)/count(t.imm_flag) * 100, 2) as immediate_percentage from(
select
  case when order_date = customer_pref_delivery_date then 1 else 0 end as imm_flag
  from Delivery ;
) t

也可以将 round()，sum() 与 case…when 整合到一起

select round(
    sum(case when order_date = customer_pref_delivery_date then 1 else 0 end)/count(delivery_id)* 100, 2
) as immediate_percentage
  from Delivery ;

1211.查询结果的质量和占比

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      |
+------------+-------------------+----------+--------+
#此表没有主键，并可能有重复的行。
#此表包含了一些从数据库中收集的查询信息。
#“位置”（position）列的值为 1 到 500 。
#“评分”（rating）列的值为 1 到 5 。评分小于 3 的查询被定义为质量很差的查询

Result table:
+------------+---------+-----------------------+
| query_name | quality | poor_query_percentage |
+------------+---------+-----------------------+
| Dog        | 2.50    | 33.33                 |
| Cat        | 0.66    | 33.33                 |
+------------+---------+-----------------------+

#Dog 查询结果的质量为 ((5 / 1) + (5 / 2) + (1 / 200)) / 3 = 2.50
#Dog 查询结果的劣质查询百分比为 (1 / 3) * 100 = 33.33

#Cat 查询结果的质量为 ((2 / 5) + (3 / 3) + (4 / 7)) / 3 = 0.66
#Cat 查询结果的劣质查询百分比为 (1 / 3) * 100 = 33.33

将查询结果的质量 quality 定义为：各查询结果的评分与其位置之间比率的平均值。

将劣质查询百分比 poor_query_percentage 为：评分小于 3 的查询结果占全部查询结果的百分比。

编写一组 SQL 来查找每次查询的名称(query_name)、质量(quality) 和 劣质查询百分比(poor_query_percentage)。质量(quality) 和劣质查询百分比(poor_query_percentage) 都应四舍五入到小数点后两位。
题目就是一个不说人话 - -，但是可以根据示例了解我们需要做什么。
每一步按照正常的思维来就行：

select query_name
  ,round(sum(q.rating/q.position)/count(q.result), 2) as quality
  ,round(sum(case when q.rating < 3 then 1 else 0 end)/count(q.result)* 100, 2) as poor_query_percentage
  from Queries q
  group by query_name ;

–要9月咯~