SQL实战训练之，力扣：1843. 可疑银行账户

ops/2024/11/1 12:41:59/

目录

        一、力扣原题链接

        二、题目描述

        三、建表语句

        四、题目分析        

        五、SQL解答

        六、最终答案

        七、验证

        八、知识点

一、力扣原题链接

1843. 可疑银行账户

二、题目描述

表: Accounts

+----------------+------+
| Column Name    | Type |
+----------------+------+
| account_id     | int  |
| max_income     | int  |
+----------------+------+
account_id 是这张表具有唯一值的列。
每行包含一个银行账户每月最大收入的信息。

表: Transactions

+----------------+----------+
| Column Name    | Type     |
+----------------+----------+
| transaction_id | int      |
| account_id     | int      |
| type           | ENUM     |
| amount         | int      |
| day            | datetime |
+----------------+----------+
transaction_id 是这张表具有唯一值的列。
每行包含一条转账信息。
type 是枚举类型（包含'Creditor','Debtor'），其中 'Creditor' 表示用户向其账户存入资金，'Debtor' 表示用户从其账户取出资金。
amount 是交易过程中的存入/取出的金额。

如果一个账户在 连续两个及以上 月份的 总收入 超过最大收入（max_income），那么认为这个账户 可疑。  账户当月 总收入 是当月存入资金总数（即 transactions 表中 type 字段的 'Creditor'）。

编写一个解决方案，报告所有的 可疑 账户。

以 任意顺序 返回结果表

返回结果格式如下示例所示。

示例 1:

输入：
Accounts 表:
+------------+------------+
| account_id | max_income |
+------------+------------+
| 3          | 21000      |
| 4          | 10400      |
+------------+------------+
Transactions 表:
+----------------+------------+----------+--------+---------------------+
| transaction_id | account_id | type     | amount | day                 |
+----------------+------------+----------+--------+---------------------+
| 2              | 3          | Creditor | 107100 | 2021-06-02 11:38:14 |
| 4              | 4          | Creditor | 10400  | 2021-06-20 12:39:18 |
| 11             | 4          | Debtor   | 58800  | 2021-07-23 12:41:55 |
| 1              | 4          | Creditor | 49300  | 2021-05-03 16:11:04 |
| 15             | 3          | Debtor   | 75500  | 2021-05-23 14:40:20 |
| 10             | 3          | Creditor | 102100 | 2021-06-15 10:37:16 |
| 14             | 4          | Creditor | 56300  | 2021-07-21 12:12:25 |
| 19             | 4          | Debtor   | 101100 | 2021-05-09 15:21:49 |
| 8              | 3          | Creditor | 64900  | 2021-07-26 15:09:56 |
| 7              | 3          | Creditor | 90900  | 2021-06-14 11:23:07 |
+----------------+------------+----------+--------+---------------------+
输出：
+------------+
| account_id |
+------------+
| 3          |
+------------+
解释：
对于账户 3：
- 在 2021年6月，用户收入为 107100 + 102100 + 90900 = 300100。
- 在 2021年7月，用户收入为 64900。
可见收入连续两月超过21000的最大收入，因此账户3列入结果表中。对于账户 4：
- 在 2021年5月，用户收入为 49300。
- 在 2021年6月，用户收入为 10400。
- 在 2021年7月，用户收入为 56300。
可见收入在5月与7月超过了最大收入，但6月没有。因为账户没有没有连续两月超过最大收入，账户4不列入结果表中。

三、建表语句

sql">drop table if exists Accounts;
drop table if exists Transactions;
Create table If Not Exists Accounts (account_id int, max_income int);
Create table If Not Exists Transactions (transaction_id int, account_id int, type ENUM('creditor', 'debtor'), amount int, day datetime);
Truncate table Accounts;
insert into Accounts (account_id, max_income) values ('3', '21000');
insert into Accounts (account_id, max_income) values ('4', '10400');
Truncate table Transactions;
insert into Transactions (transaction_id, account_id, type, amount, day) values ('2', '3', 'Creditor', '107100', '2021-06-02 11:38:14');
insert into Transactions (transaction_id, account_id, type, amount, day) values ('4', '4', 'Creditor', '10400', '2021-06-20 12:39:18');
insert into Transactions (transaction_id, account_id, type, amount, day) values ('11', '4', 'Debtor', '58800', '2021-07-23 12:41:55');
insert into Transactions (transaction_id, account_id, type, amount, day) values ('1', '4', 'Creditor', '49300', '2021-05-03 16:11:04');
insert into Transactions (transaction_id, account_id, type, amount, day) values ('15', '3', 'Debtor', '75500', '2021-05-23 14:40:20');
insert into Transactions (transaction_id, account_id, type, amount, day) values ('10', '3', 'Creditor', '102100', '2021-06-15 10:37:16');
insert into Transactions (transaction_id, account_id, type, amount, day) values ('14', '4', 'Creditor', '56300', '2021-07-21 12:12:25');
insert into Transactions (transaction_id, account_id, type, amount, day) values ('19', '4', 'Debtor', '101100', '2021-05-09 15:21:49');
insert into Transactions (transaction_id, account_id, type, amount, day) values ('8', '3', 'Creditor', '64900', '2021-07-26 15:09:56');
insert into Transactions (transaction_id, account_id, type, amount, day) values ('7', '3', 'Creditor', '90900', '2021-06-14 11:23:07');

四、题目分析

需求：

        连续两个及以上 月份的 总收入 超过最大收入（max_income），那么认为这个账户 可疑

解答：

        1、要求每月总收入需要先按月统计所有的收入总和

        2、筛选每月大于最大收入的用户

        3、用等差求连续月

        4、筛选连续大于2月的用户

五、SQL解答

sql">with t1 as (selectDATE_FORMAT(day,'%Y-%m') ny,substring(day,1,7) ny1,account_id,-- 1、每月收入总和sum(amount) sum_amount,dense_rank() over (partition by account_id order by DATE_FORMAT(day,'%Y-%m')) drfrom transactions twhere type = 'Creditor'group by ny,ny1,account_id-- 2、筛选每月大于最大收入having sum_amount > (select max_income from accounts where account_id = t.account_id)
),t2 as (
selectaccount_id,-- 3、日期等差求连续date_add(str_to_date(concat(ny,'-01'),'%Y-%m-%d'),interval -dr month) as day2
from t1
group by day2,account_id
-- 4、最少连续2天
having count(date_add(str_to_date(concat(ny,'-01'),'%Y-%m-%d'),interval -dr month)) >= 2
)
select distinct account_id from t2
;

六、最终答案

sql">with t1 as (selectDATE_FORMAT(day,'%Y-%m') ny,substring(day,1,7) ny1,account_id,-- 1、每月收入总和sum(amount) sum_amount,dense_rank() over (partition by account_id order by DATE_FORMAT(day,'%Y-%m')) drfrom transactions twhere type = 'Creditor'group by ny,ny1,account_id-- 2、筛选每月大于最大收入having sum_amount > (select max_income from accounts where account_id = t.account_id)
),t2 as (
selectaccount_id,-- 3、日期等差求连续date_add(str_to_date(concat(ny,'-01'),'%Y-%m-%d'),interval -dr month) as day2
from t1
group by day2,account_id
-- 4、最少连续2天
having count(date_add(str_to_date(concat(ny,'-01'),'%Y-%m-%d'),interval -dr month)) >= 2
)
select distinct account_id from t2
;

七、验证


http://www.ppmy.cn/ops/130138.html

相关文章

工作笔记【六】

工作笔记【六】

任务1.对数据表进行CRUD操作 第一个任务首先是最基本的CRUD操作，很简单，直接放代码 package com.example.demo.demos.web.pojo;import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor;Data NoArgsConstructor AllAr…
阅读更多...
Ubuntu-22.04 虚拟机安装

Ubuntu-22.04 虚拟机安装

1. Ubuntu安装方式 1.1. 基于物理介质安装 光盘安装：通过将 Ubuntu 镜像刻录到光盘，在计算机 BIOS/UEFI 中设置光盘为第一启动项，然后按照安装程序的提示进行语言选择、分区、用户信息设置等操作来完成安装。这种方式需要有光盘刻录设备和空…
阅读更多...
vscode | 开发神器vscode快捷键删除和恢复

vscode | 开发神器vscode快捷键删除和恢复

目录 快捷键不好使了删除快捷键恢复删除的快捷键 在vscode使用的过程中，随着我们自身需求的不断变化，安装的插件将会持续增长，那么随之而来的就会带来一个问题：插件的快捷键重复。快捷键重复导致的问题就是快捷键不好使了&#xf…
阅读更多...
QT国际化，语言翻译

QT国际化，语言翻译

文章目录 1.lupdate更新翻译2.生成*.ts文件3.翻译4.lrelease发布翻译5.在程序中使用翻译文件6.运行 1.lupdate更新翻译 lupdate就是用于扫描pro文件中指定的代码或UI文件中被tr包装起来的文本。 lupdate的使用 lupdate的使用可以使用lupdate --help来查看。 粗略的说一下这个…
阅读更多...
ISAAC-SIM跨机器复现

ISAAC-SIM跨机器复现

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档 文章目录 基础环境安装下载 Omniverse安装 Isaac Sim方法一：方法二： NUCLEUS 配置 代码运行Python Env问题解决问题1 基础环境安装 下载 Omniverse …
阅读更多...
Idea常用插件

Idea常用插件

1、RestfulTool 接口路径->方法 快捷键 ctrlalt/ 2、TONGYI Lingma 3、MyBatisCodeHelperPro Mapper.java<->Mapper.xml 4、Mybatis Log Plus
阅读更多...
巨好看的登录注册界面源码

巨好看的登录注册界面源码

展示效果 源码 <!DOCTYPE html> <html lang"en"><head><meta charset"UTF-8" /><meta http-equiv"X-UA-Compatible" content"IEedge" /><meta name"viewport" content"widthdevic…
阅读更多...
web文件包含include

web文件包含include

php伪协议 在 PHP 中&#xff0c;伪协议&#xff08;Pseudo Protocols&#xff09; 也被称为 流包装器&#xff0c;这些伪协议以 php:// 开头&#xff0c;后面跟着一些参数&#xff0c;用于指定 要执行的操作 或 需要访问的资源。 伪协议表明这些协议并不是一个 真实的外部协议…
阅读更多...
最新文章

Copyright @ 2022~2023