Find out the bank teller's account opening records within half an hour. See if there are 3 or more accounts opened within half an hour.

A table is given, in which is the operation record of a teller. The structure of the table records is as follows:

Method 1: use window function (need MySQL 8.0 or above);
Method 2: conventional method.
Method two is adopted in this case. Due to the tortuous installation process of MySQL 8.0, the installation was not successful.

1. Creating a database bank in MySQL

CREATE DATABASE bank;

2. Create table records

CREATE TABLE records(
Cid varchar(10) NOT NULL,
Ctime datetime NOT NULL
);

3. Insert data
(20 records to be inserted)

INSERT INTO records VALUES('adsf', '2017-5-27 10:55:00');
INSERT INTO records VALUES('ngfd', '2017-5-27 10:56:00');
INSERT INTO records VALUES('brsd', '2017-5-27 11:12:33');
INSERT INTO records VALUES('afdg', '2017-5-27 11:30:00');
INSERT INTO records VALUES('4045', '2017-5-27 11:33:23');
INSERT INTO records VALUES('sdsa', '2017-5-27 11:41:21');
INSERT INTO records VALUES('bvrt', '2017-5-27 11:52:55');
INSERT INTO records VALUES('4275', '2017-5-27 12:00:03');
INSERT INTO records VALUES('2578', '2017-5-27 12:04:00');
INSERT INTO records VALUES('7869', '2017-5-27 12:15:00');
INSERT INTO records VALUES('2479', '2017-5-27 12:21:20');
INSERT INTO records VALUES('4537', '2017-5-27 12:28:00');
INSERT INTO records VALUES('wefi', '2017-5-27 12:37:24');
INSERT INTO records VALUES('jrty', '2017-5-27 12:44:00');
INSERT INTO records VALUES('sdfg', '2017-5-27 12:50:11');
INSERT INTO records VALUES('sdvf', '2017-5-27 12:55:25');
INSERT INTO records VALUES('afds', '2017-5-27 13:08:00');
INSERT INTO records VALUES('jhtg', '2017-5-27 13:12:00');
INSERT INTO records VALUES('7632', '2017-5-27 13:15:00');
INSERT INTO records VALUES('fghd', '2017-5-27 13:16:00');

4. Self connect
Connect each record in the table with 20 records to get 400 records (20 * 20).

SELECT a.*, b.*
FROM records a, records b
ORDER BY a.Ctime, b.Ctime;

5. Calculate the time difference. Add a new calculation field to record the time difference

SELECT	a.Cid, 
		a.Ctime AS t1, 
		b.Cid, 
		b.Ctime AS t2,
		(b.Ctime -a.Ctime) AS t1_t2
FROM records a, records b
ORDER BY a.Ctime, b.Ctime;

6. Filter out all records within half an hour from the original 20 records
There are 41 articles.

SELECT	a.Cid, 
		a.Ctime AS t1, 
		b.Cid, 
		b.Ctime AS t2,
		(b.Ctime -a.Ctime) AS t1_t2
FROM records a, records b
WHERE (b.Ctime -a.Ctime) > 0  AND (b.Ctime - a.Ctime) <= 3000
ORDER BY a.Ctime, b.Ctime;

7. For these 41 records, group count

SELECT * 
FROM (
		SELECT a.Cid, count(*) as cnt
		FROM (
				SELECT	a.Cid, 
						a.Ctime as t1, 
						b.Cid, 
						b.Ctime as t2,
						(b.Ctime -a.Ctime) AS t1_t2
				FROM records a, records b
				WHERE (b.Ctime -a.Ctime) > 0  AND (b.Ctime -a.Ctime) <= 3000
				ORDER BY a.Ctime, b.Ctime
              ) A
		GROUP BY a.Cid
      ) B
ORDER BY B.cnt DESC;

8, others
(1) Time difference.
The difference between "2017-5-27 12:44:00" and "2017-5-27 12:50:11" is 6 minutes and 11 seconds, and the calculation result here is 611. Therefore, the judgment condition for half an hour is: 0-3000, that is, where (b.ctime-a.ctime) > 0 and (b.ctime-a.ctime) < 3000

(2) Check whether there are 5 or more accounts opened within one hour

SELECT * 
FROM (
		SELECT a.Cid, count(*) as cnt
		FROM (
				SELECT	a.Cid, 
						a.Ctime as t1, 
						b.Cid, 
						b.Ctime as t2,
						(b.Ctime -a.Ctime) AS t1_t2
				FROM records a, records b
				WHERE (b.Ctime -a.Ctime) > 0  AND (b.Ctime -a.Ctime) <= 10000
				ORDER BY a.Ctime, b.Ctime
             ) A
		GROUP BY a.Cid
      ) B
ORDER BY B.cnt DESC;