Visualize SQL | Overlap Time and CROSS JOIN (part 3)

# Beginning
This technique is based on SQL Puzzle # 3 anesthesia

In this puzzle, it is required to get overlap time from data that contains time fields.
How many jobs are overlapped for each job(proc_id)?

『part 3』is a little complicated. The whole image is hard to imagine because of using
 CROSS JOIN three times in a row

From a different point of view, it is not complicated
because **it will be similar to 『part 2』if you rewrite 『part 3』 SQL**

Let's see 『part 3』SQL first, then modify 『part 3』 to be like 『part 2』

# check 『part 3』 SQL
It looks P2.start_time is the main condition whether overlap time or not.
This means you see P1 time as overlap time and use P2 time to prove it.

Replace P2.start_time with P1.start_time like image below.

Looking at the output before and after the replacement
P1.proc_id and P2.proc_id are reversed but they have the same number of count.
So this replacement seems to be no problem

![rep_1.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/b1f285cb-ec2b-2e3f-56eb-652005539140.png)

why is P2.start_time replaced with P1.start_time?

back to see 『part 2』, **the condition of overlap time is defined using P1.start_time**
so I think this is easier to imagine.

# reshape SQL 『part 3』 like 『part 2』
```SQL:SQL
-- original SQL
SELECT P1.proc_id, P2.proc_id, COUNT(*)
FROM Procs AS P1, Procs AS P2, Procs AS P3
WHERE P2.anest_name = P1.anest_name 
	AND P3.anest_name = P1.anest_name
	AND P2.start_time <= P1.start_time AND P1.start_time < P2.end_time 
	AND P3.start_time <= P1.start_time AND P1.start_time < P3.end_time
GROUP BY P1.proc_id, P2.proc_id
```

you see "Procs AS P1, Procs AS P2" as the first process
then "the first process" CROSS JOIN "Procs AS P3" as the second process

![rep_2.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/64b13f77-3c68-9c7c-1d09-c48018858603.png)

Reshape SQL to be like 『part 2』. Its output is the same as 『part 3』
『part 3』uses CROSS JOIN but『part 2』uses INNER JOIN ・・・

we have already seen in『part 1』『part 2』that both SQL have the same combinations
 because of using  the same "annest_name" as condition

```SQL:SQL
-- SQL that matches the form of 『Part 2』
SELECT a1.proc_id_1, a1.proc_id_2, COUNT(*)
FROM
( -- the second process
   SELECT Con.*
   FROM
   ( -- the first process
      SELECT P1.proc_id AS proc_id_1, P2.proc_id AS proc_id_2, P1.anest_name,P1.start_time
      FROM Procs AS P1
        INNER JOIN Procs AS P2 ON P2.anest_name = P1.anest_name 
          AND P2.start_time <= P1.start_time AND P1.start_time < P2.end_time
   ) AS Con
   INNER JOIN Procs AS P3 ON P3.anest_name = Con.anest_name 
      AND P3.start_time <= Con.start_time AND Con.start_time < P3.end_time
) AS a1
GROUP BY a1.proc_id_1, a1.proc_id_2
```

# Parse the reshaped SQL
Although it is not exactly the same description as 『part 2』
it is performed the same process as 『part 2』does

① In the inner process(the first INNER JOIN)
　 the overlap time is taken for each job(proc_id)
　   ⇒ use P1.start_time to prove as the overlap time

② In the outer process(the second INNER JOIN)
　 Recreating all combinations again
　  ⇒ use Con.start_time(P1.start_time) to prove as overlap time

③ Process "GROUP BY" only once at the end

In 『part 2』, two INNER JOIN are used to combine the data in two stages.
In 『part 3』, use CROSS JOIN three times in a row at beginning and
add conditions on where clause at end

#answer：Number of overlap time
in the original SQL uses P1.proc_id for GROUP BY but using P2.proc_id here
because we have replaced P2.start_time with P1.start_time.

```SQL:SQL
-- Change the variable to be the same as the original answer
WITH Vprocs AS (
  SELECT a1.proc_id_1, a1.proc_id_2, COUNT(*) AS total
  FROM
  (
    SELECT Con.*
    FROM
    (
      SELECT P1.proc_id AS proc_id_1, P2.proc_id AS proc_id_2, P1.anest_name,P1.start_time
      FROM Procs AS P1
        INNER JOIN Procs AS P2 ON P2.anest_name = P1.anest_name 
          AND P2.start_time <= P1.start_time AND P1.start_time < P2.end_time
     ) AS Con
     INNER JOIN Procs AS P3 ON P3.anest_name = Con.anest_name 
       AND P3.start_time <= Con.start_time AND Con.start_time < P3.end_time
  ) AS a1
  GROUP BY a1.proc_id_1, a1.proc_id_2
)

SELECT proc_id_2 AS proc_id, MAX(total) AS max_inst_count
FROM Vprocs
GROUP BY proc_id_2 -- caution here
```

|proc_id |max_inst_count|
|---|---|
|10|2|
|20|2|
|30|3|
|40|3|
|50|3|
|60|3|
|70|2|
|80|1|

# References
[Joe Celko's SQL Puzzles and Answers]
(https://www.amazon.com/Puzzles-Answers-Kaufmann-Management-Systems/dp/0123735963)