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"

Click here for the Solution!

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
Labels:

4 comments:

  1. ImreJune 5, 2019 at 4:25 PM

    declare
    -- Local variables here
    v_alone integer;
    v_cnt INTEGER;
    v_time DATE;
    begin
    -- Test statements here
    FOR i IN (SELECT DISTINCT t.job_name, t.start_time, t.end_time FROM NIMISH_JOBS t) LOOP

    v_time := i.start_time;
    v_alone := 0;

    LOOP

    EXIT WHEN v_time > i.end_time;

    SELECT COUNT(*)
    INTO v_cnt
    FROM nimish_jobs x
    WHERE x.job_name <> i.job_name
    AND v_time BETWEEN x.start_time AND x.end_time;

    IF v_cnt = 0 THEN
    v_alone := v_alone + 1;
    END IF;

    v_time := v_time + interval '1' minute;

    END LOOP;

    IF v_alone >= 10 THEN
    dbms_output.put_line(i.job_name ||' ran '|| v_alone ||' minutes alone.');
    END IF;

    END LOOP;

    end;
    /

    ReplyDelete
    Replies
    1. Nimish GargJune 5, 2019 at 6:20 PM

      Thanks for sharing the plsql solution. I would like to encourage you to solve this in sql 👍

      Delete
    2. ImreJune 7, 2019 at 1:18 AM

      I have no idea, how to solve it in sql... :(

      Delete
    3. GucciJuly 17, 2019 at 3:55 PM

      This takes care of resolving use cases where we remove the effect of dependent jobs which start within the current job. How about if there is some other superset job which started before current job and ended after current job.. i think the code above wont take that into account and will count the current job as if its running alone.

      Delete

Subscribe to: Post Comments (Atom)