SQL Puzzle - Jobs taking more than 10 individual minutes in Parallel Jobs Environment

Recently I got a chance to work on an interesting problem, and I am sharing the same in my SQL Puzzle series. So without wasting any time, let me directly jump on the problem.

Problem Statement:
Find out all the jobs which took more than 10 non-parallel minutes individually to execute.

Little Explanation:
Ideally every job should run in parallel i.e. at any point of time multiple jobs should be running. We need to find out the job(s) which took more than 10 minutes individually. In other-words, during the job execution there were 10 minutes, in which only one job was executing, and there was no other running job.

For Example: Job A starts at 10:00 AM and ends at 1100 AM. Job B starts at 10:02 AM and ends at 10:27 AM, and Job C starts at 10:30 AM and ends at 10:54 AM. As you can see in above example Job A executed for 2 Minutes alone in starting, 3 minutes alone after Job B and 6 minutes alone after Job C, it is the candidate job for our requirement.

Let me provide you table and some data for our problem statement
which stores job_names, start_time and end_time of our data-warehouse jobs
create table nimish_jobs
(
job_name varchar2(30),
start_time date,
end_time date
);

Following are the insert scripts of data -
Insert into  NIMISH_JOBS (JOB_NAME, START_TIME, END_TIME)
Values ('Job A', TO_DATE('03/25/2019 00:10:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/25/2019 00:45:49', 'MM/DD/YYYY HH24:MI:SS'));

Insert into  NIMISH_JOBS (JOB_NAME, START_TIME, END_TIME)
Values ('Job B', TO_DATE('03/25/2019 00:53:02', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/25/2019 01:55:18', 'MM/DD/YYYY HH24:MI:SS'));

Insert into  NIMISH_JOBS (JOB_NAME, START_TIME, END_TIME)
Values ('Job C', TO_DATE('03/25/2019 00:23:39', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/25/2019 02:08:04', 'MM/DD/YYYY HH24:MI:SS'));

Insert into  NIMISH_JOBS (JOB_NAME, START_TIME, END_TIME)
Values ('Job D', TO_DATE('03/25/2019 00:53:26', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/25/2019 01:41:31', 'MM/DD/YYYY HH24:MI:SS'));

Insert into  NIMISH_JOBS (JOB_NAME, START_TIME, END_TIME)
Values ('Job E', TO_DATE('03/25/2019 00:54:30', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/25/2019 01:26:17', 'MM/DD/YYYY HH24:MI:SS'));

Insert into  NIMISH_JOBS (JOB_NAME, START_TIME, END_TIME)
Values ('Job F', TO_DATE('03/25/2019 00:58:47', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/25/2019 01:07:35', 'MM/DD/YYYY HH24:MI:SS'));

Insert into  NIMISH_JOBS (JOB_NAME, START_TIME, END_TIME)
values ('Job G', TO_DATE('03/25/2019 00:33:03', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/25/2019 02:49:17', 'MM/DD/YYYY HH24:MI:SS'));

Insert into  NIMISH_JOBS (JOB_NAME, START_TIME, END_TIME)
Values ('Job H', TO_DATE('03/25/2019 00:27:10', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/25/2019 02:17:15', 'MM/DD/YYYY HH24:MI:SS'));

Insert into  NIMISH_JOBS (JOB_NAME, START_TIME, END_TIME)
Values ('Job I', TO_DATE('03/25/2019 00:37:32', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/25/2019 01:31:00', 'MM/DD/YYYY HH24:MI:SS'));

Insert into  NIMISH_JOBS (JOB_NAME, START_TIME, END_TIME)
Values ('Job J', TO_DATE('03/25/2019 02:18:29', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/25/2019 02:42:19', 'MM/DD/YYYY HH24:MI:SS'));

Insert into  NIMISH_JOBS (JOB_NAME, START_TIME, END_TIME)
Values ('Job K', TO_DATE('03/25/2019 00:04:02', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/25/2019 02:58:56', 'MM/DD/YYYY HH24:MI:SS'));

Insert into  NIMISH_JOBS (JOB_NAME, START_TIME, END_TIME)
Values ('Job L', TO_DATE('03/25/2019 00:51:49', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/25/2019 01:11:22', 'MM/DD/YYYY HH24:MI:SS'));

Insert into  NIMISH_JOBS (JOB_NAME, START_TIME, END_TIME)
Values ('Job M', TO_DATE('03/25/2019 01:28:53', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/25/2019 02:52:39', 'MM/DD/YYYY HH24:MI:SS'));

Insert into  NIMISH_JOBS (JOB_NAME, START_TIME, END_TIME)
Values ('Job N', TO_DATE('03/25/2019 01:08:29', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/25/2019 01:48:34', 'MM/DD/YYYY HH24:MI:SS'));

Insert into  NIMISH_JOBS (JOB_NAME, START_TIME, END_TIME)
Values ('Job O', TO_DATE('03/25/2019 00:22:41', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/25/2019 00:29:25', 'MM/DD/YYYY HH24:MI:SS'));

Insert into  NIMISH_JOBS (JOB_NAME, START_TIME, END_TIME)
Values ('Job P', TO_DATE('03/25/2019 00:49:07', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/25/2019 02:25:21', 'MM/DD/YYYY HH24:MI:SS'));

Insert into  NIMISH_JOBS (JOB_NAME, START_TIME, END_TIME)
Values ('Job Q', TO_DATE('03/25/2019 01:17:57', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/25/2019 02:40:31', 'MM/DD/YYYY HH24:MI:SS'));

Insert into  NIMISH_JOBS (JOB_NAME, START_TIME, END_TIME)
Values ('Job R', TO_DATE('03/25/2019 01:32:09', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/25/2019 02:51:29', 'MM/DD/YYYY HH24:MI:SS'));

Insert into  NIMISH_JOBS (JOB_NAME, START_TIME, END_TIME)
Values ('Job S', TO_DATE('03/25/2019 00:29:21', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/25/2019 01:58:37', 'MM/DD/YYYY HH24:MI:SS'));

Insert into  NIMISH_JOBS (JOB_NAME, START_TIME, END_TIME)
Values ('Job T', TO_DATE('03/25/2019 00:17:49', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/25/2019 02:10:05', 'MM/DD/YYYY HH24:MI:SS'));

Insert into  NIMISH_JOBS (JOB_NAME, START_TIME, END_TIME)
Values ('Job U', TO_DATE('03/25/2019 00:56:07', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/25/2019 01:22:06', 'MM/DD/YYYY HH24:MI:SS'));

Insert into  NIMISH_JOBS (JOB_NAME, START_TIME, END_TIME)
Values ('Job V', TO_DATE('03/25/2019 01:44:12', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/25/2019 01:51:56', 'MM/DD/YYYY HH24:MI:SS'));

Insert into  NIMISH_JOBS (JOB_NAME, START_TIME, END_TIME)
Values ('Job W', TO_DATE('03/25/2019 01:59:36', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/25/2019 02:52:41', 'MM/DD/YYYY HH24:MI:SS'));

Insert into  NIMISH_JOBS (JOB_NAME, START_TIME, END_TIME)
Values ('Job X', TO_DATE('03/25/2019 02:55:49', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/25/2019 02:57:09', 'MM/DD/YYYY HH24:MI:SS'));

Insert into  NIMISH_JOBS (JOB_NAME, START_TIME, END_TIME)
Values ('Job Y', TO_DATE('03/25/2019 00:09:12', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/25/2019 01:13:18', 'MM/DD/YYYY HH24:MI:SS'));

COMMIT;


If you are able to solve it, please do share your approach in the comment box. Have fun with SQL, Enjoy :)

Pssst! Correct output is "Job K"


More SQL Puzzles:
- Transpose Rows and Shift Values among columns
- Graph Shortest Path Solution by SQL
- Diamond Shaped Star Pattern by SQL
- Sorting Versions stored in Varchar2 Column
- SQL Puzzle - Grouping Deals
- SQL Puzzle - Consecutive Wins
- SQL Puzzle - Issue Tracker
- SQL Interview Question Answers