mysql - list timeline and count overlap

Question

I have the following sample data set...

CREATE TABLE timeline (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30) NOT NULL,
s_t DATETIME,
e_t DATETIME,
quantity INT(3)
);

INSERT INTO timeline
(`id`,`name`, `s_t`, `e_t`, `quantity`)
VALUES
(NULL, "joe", "2021-01-15 10:00:00", "2021-01-15 14:00:00","1"),
(NULL, "joe", "2021-01-15 15:00:00", "2021-01-15 18:00:00","1"),
(NULL, "joe", "2021-01-15 19:00:00", "2021-01-15 21:00:00","1"),
(NULL, "frank", "2021-01-15 10:00:00", "2021-01-15 14:00:00","1"),
(NULL, "frank", "2021-01-15 13:00:00", "2021-01-15 18:00:00","1"),
(NULL, "frank", "2021-01-15 19:00:00", "2021-01-15 21:00:00","1"),
(NULL, "bob", "2021-01-15 10:00:00", "2021-01-15 14:00:00","1"),
(NULL, "bob", "2021-01-15 15:00:00", "2021-01-15 18:00:00","1"),
(NULL, "bob", "2021-01-15 19:00:00", "2021-01-15 21:00:00","1"),
(NULL, "harry", "2021-01-17 14:00:00", "2021-01-17 17:00:00","1"),
(NULL, "harry", "2021-01-17 10:00:00", "2021-01-17 15:00:00","1"),
(NULL, "harry", "2021-01-17 08:00:00", "2021-01-17 11:00:00","1");
  

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=a126a50d9a612bb1603e381d65a3a734

I am trying to get SUM of 1 PER NAME when there is no overlapping times and a SUM of 2 or more when there are overlapping times.
(There doesn't need to be a "note" column - that's just to illustrate my point.)

id	name	start	end	total	note
7	bob	2021-01-15 10:00:00	2021-01-15 14:00:00	1
9	bob	2021-01-15 19:00:00	2021-01-15 21:00:00	1
8	bob	2021-01-15 15:00:00	2021-01-15 18:00:00	1
4	frank	2021-01-15 10:00:00	2021-01-15 14:00:00	2	overlap
5	frank	2021-01-15 13:00:00	2021-01-15 18:00:00	2	overlap
6	frank	2021-01-15 19:00:00	2021-01-15 21:00:00	1
12	harry	2021-01-17 08:00:00	2021-01-17 11:00:00	2	overlap
11	harry	2021-01-17 10:00:00	2021-01-17 15:00:00	3	2 overlaps
10	harry	2021-01-17 14:00:00	2021-01-17 17:00:00	2	overlap
1	joe	2021-01-15 10:00:00	2021-01-15 14:00:00	1
2	joe	2021-01-15 15:00:00	2021-01-15 18:00:00	1
3	joe	2021-01-15 19:00:00	2021-01-15 21:00:00	1

question from:https://stackoverflow.com/questions/65872306/list-timeline-and-count-overlap

Answer 1

Consider the following...

SELECT x.*
     , COUNT(*) total 
     , GROUP_CONCAT(y.id) overlaps
  FROM timeline x  
  JOIN timeline y 
    ON y.s_t <= x.e_t 
   AND y.e_t >= x.s_t 
   AND y.name = x.name
 GROUP 
    BY x.id;
+----+-------+---------------------+---------------------+----------+-------+----------+
| id | name  | s_t                 | e_t                 | quantity | total | overlaps |
+----+-------+---------------------+---------------------+----------+-------+----------+
|  1 | joe   | 2021-01-15 10:00:00 | 2021-01-15 14:00:00 |        1 |     1 | 1        |
|  2 | joe   | 2021-01-15 15:00:00 | 2021-01-15 18:00:00 |        1 |     1 | 2        |
|  3 | joe   | 2021-01-15 19:00:00 | 2021-01-15 21:00:00 |        1 |     1 | 3        |
|  4 | frank | 2021-01-15 10:00:00 | 2021-01-15 14:00:00 |        1 |     2 | 4,5      |
|  5 | frank | 2021-01-15 13:00:00 | 2021-01-15 18:00:00 |        1 |     2 | 5,4      |
|  6 | frank | 2021-01-15 19:00:00 | 2021-01-15 21:00:00 |        1 |     1 | 6        |
|  7 | bob   | 2021-01-15 10:00:00 | 2021-01-15 14:00:00 |        1 |     1 | 7        |
|  8 | bob   | 2021-01-15 15:00:00 | 2021-01-15 18:00:00 |        1 |     1 | 8        |
|  9 | bob   | 2021-01-15 19:00:00 | 2021-01-15 21:00:00 |        1 |     1 | 9        |
| 10 | harry | 2021-01-17 14:00:00 | 2021-01-17 17:00:00 |        1 |     2 | 10,11    |
| 11 | harry | 2021-01-17 10:00:00 | 2021-01-17 15:00:00 |        1 |     3 | 11,12,10 |
| 12 | harry | 2021-01-17 08:00:00 | 2021-01-17 11:00:00 |        1 |     2 | 12,11    |
+----+-------+---------------------+---------------------+----------+-------+----------+