【leetcode-sql】1173、1174、1193、1205

最新推荐文章于 2024-06-07 21:15:30 发布

MiSeRyⅢ

最新推荐文章于 2024-06-07 21:15:30 发布

阅读量228

点赞数

分类专栏： Sql练习文章标签： sql leetcode 数据库

本文链接：https://blog.csdn.net/weixin_37875391/article/details/120853977

版权

Sql练习专栏收录该内容

12 篇文章 0 订阅

订阅专栏

1173. 即时食物配送 I

配送表: Delivery

+-----------------------------+---------+
| Column Name                 | Type    |
+-----------------------------+---------+
| delivery_id                 | int     |
| customer_id                 | int     |
| order_date                  | date    |
| customer_pref_delivery_date | date    |
+-----------------------------+---------+
delivery_id 是表的主键。
该表保存着顾客的食物配送信息，顾客在某个日期下了订单，并指定了一个期望的配送日期（和下单日期相同或者在那之后）。

如果顾客期望的配送日期和下单日期相同，则该订单称为「即时订单」，否则称为「计划订单」。

写一条 SQL 查询语句获取即时订单所占的百分比， 保留两位小数。

查询结果如下所示：

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

Result 表:
+----------------------+
| immediate_percentage |
+----------------------+
| 33.33                |
+----------------------+
2 和 3 号订单为即时订单，其他的为计划订单。

提交答案：

# Write your MySQL query statement below

SELECT ROUND(SUM(CASE 
		WHEN order_date = customer_pref_delivery_date THEN 1
		ELSE 0
	END) * 100 / COUNT(*), 2) AS immediate_percentage
FROM Delivery

解析：

使用CASE···WHEN···进行判断，日期相同取1，不同取0，然后用sum()函数来累加；
ROUND()函数保留两位小数。

1174. 即时食物配送 II

配送表: Delivery

+-----------------------------+---------+
| Column Name                 | Type    |
+-----------------------------+---------+
| delivery_id                 | int     |
| customer_id                 | int     |
| order_date                  | date    |
| customer_pref_delivery_date | date    |
+-----------------------------+---------+
delivery_id 是表的主键。
该表保存着顾客的食物配送信息，顾客在某个日期下了订单，并指定了一个期望的配送日期（和下单日期相同或者在那之后）。

如果顾客期望的配送日期和下单日期相同，则该订单称为「即时订单」，否则称为「计划订单」。

「首次订单」是顾客最早创建的订单。我们保证一个顾客只会有一个「首次订单」。

写一条 SQL 查询语句获取即时订单在所有用户的首次订单中的比例。保留两位小数。

查询结果如下所示：

Delivery 表：
+-------------+-------------+------------+-----------------------------+
| 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                  |
+-------------+-------------+------------+-----------------------------+

Result 表：
+----------------------+
| immediate_percentage |
+----------------------+
| 50.00                |
+----------------------+
1 号顾客的 1 号订单是首次订单，并且是计划订单。
2 号顾客的 2 号订单是首次订单，并且是即时订单。
3 号顾客的 5 号订单是首次订单，并且是计划订单。
4 号顾客的 7 号订单是首次订单，并且是即时订单。
因此，一半顾客的首次订单是即时的。

提交答案：

# Write your MySQL query statement below
SELECT ROUND(SUM(IF(order_date = customer_pref_delivery_date, 1, 0)) * 100 / COUNT(*), 2) AS immediate_percentage
FROM (
	SELECT *
	FROM (
		SELECT *, RANK() OVER (PARTITION BY customer_id ORDER BY order_date) AS rk
		FROM Delivery
	) t
	WHERE rk = 1
) t1

解析：

首先在over(PARTITION BY customer_id ORDER BY order_date)根据顾客id分组后按照订单日期排序；
取rk排名为1的作为临时表，确保当前都为首次订单；

1193. 每月交易 I

Table: Transactions

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| country       | varchar |
| state         | enum    |
| amount        | int     |
| trans_date    | date    |
+---------------+---------+
id 是这个表的主键。
该表包含有关传入事务的信息。
state 列类型为 “[”批准“，”拒绝“] 之一。

编写一个 sql 查询来查找每个月和每个国家/地区的事务数及其总金额、已批准的事务数及其总金额。

查询结果格式如下所示：

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

Result table:
+----------+---------+-------------+----------------+--------------------+-----------------------+
| 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                  |
+----------+---------+-------------+----------------+--------------------+-----------------------+

提交答案：

# Write your MySQL query statement below
SELECT DATE_FORMAT(trans_date, '%Y-%m') AS month, country
	, COUNT(*) AS trans_count
	, SUM(IF(state = 'approved', 1, 0)) AS approved_count
	, SUM(amount) AS trans_total_amount
	, SUM(IF(state = 'approved', amount, 0)) AS approved_total_amount
FROM Transactions
GROUP BY DATE_FORMAT(trans_date, '%Y-%m'), country

1205. 每月交易II

Transactions 记录表

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| id             | int     |
| country        | varchar |
| state          | enum    |
| amount         | int     |
| trans_date     | date    |
+----------------+---------+
id 是这个表的主键。
该表包含有关传入事务的信息。
状态列是类型为 [approved（已批准）、declined（已拒绝）] 的枚举。

Chargebacks 表

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| trans_id       | int     |
| trans_date     | date    |
+----------------+---------+
退单包含有关放置在事务表中的某些事务的传入退单的基本信息。
trans_id 是 transactions 表的 id 列的外键。
每项退单都对应于之前进行的交易，即使未经批准。

编写一个 SQL 查询，以查找每个月和每个国家/地区的信息：已批准交易的数量及其总金额、退单的数量及其总金额。

注意：在您的查询中，只需显示给定月份和国家，忽略所有为零的行。

查询结果格式如下所示：

Transactions 表：
+-----+---------+----------+--------+------------+
| 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 表：
+----------+------------+
| trans_id | trans_date |
+----------+------------+
| 102      | 2019-05-29 |
| 101      | 2019-06-30 |
| 105      | 2019-09-18 |
+----------+------------+

Result 表：
+---------+---------+----------------+-----------------+------------------+-------------------+
| 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              |
+---------+---------+----------------+-----------------+------------------+-------------------+

提交答案：

# Write your MySQL query statement below
select month, country, sum(IF(tag=1, 1, 0)) as approved_count,
    sum(IF(tag=1, amount, 0)) as approved_amount,
    sum(IF(tag=0, 1, 0)) as chargeback_count,
    sum(IF(tag=0, amount, 0)) as chargeback_amount
from
(select country, state, amount, DATE_FORMAT(trans_date, '%Y-%m') as month, 1 as tag
from Transactions
where state='approved'
UNION ALL
select country, state, amount, DATE_FORMAT(c.trans_date, '%Y-%m') as month, 0 as tag
from Chargebacks c JOIN Transactions t
on c.trans_id=t.id) t1
group by month, country

解析：

按题目中的要求，result中的答案的月份可以包括所有approved状态的Transactions表中的trans_date，也包括Chargebacks 表中的trans_date;
有这么几种情况：
- 当前月只包括批准记录；
- 当前月只包括退单记录；
- 当前月两种都包括。
使用1和0作为tag作为标签区分两种情况（学到了！）
使用UNION ALL对结果集进行并集（可能存在除id不同外其他均相同的记录），最后统计即可。

以上是leetcode四道sql的解法，如有考虑错误或者解法问题，请指正。🙂

MiSeRyⅢ

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
【leetcode-sql】1173、1174、1193、1205

1173. 即时食物配送 I配送表: Delivery+-----------------------------+---------+| Column Name | Type |+-----------------------------+---------+| delivery_id | int || customer_id | int || order_date
复制链接

扫一扫