CREATE OR REPLACE VIEW llnw_bad_runs AS
    SELECT
        *
    FROM
        Job j
    WHERE
        j.JobStatus IN ('E' , 'e', 'f', 'I', 'D')
            AND j.JobId IN (SELECT
                JobId
            FROM
                Job s1
            WHERE
                EndTime = (SELECT
                        MAX(s2.EndTime)
                    FROM
                        Job s2
                    WHERE
                        s1.Name = s2.Name));

CREATE OR REPLACE VIEW llnw_good_runs AS
SELECT
    *
FROM
    Job j2
WHERE
    j2.JobId IN (SELECT
            JobId
        FROM
            Job s1
        WHERE
            s1.EndTime = (SELECT
                    MAX(s2.EndTime)
                FROM
                    Job s2
                WHERE
                    NOT s2.JobStatus IN ('E' , 'e', 'f', 'I', 'D')
                        and s1.Name = s2.Name));

CREATE OR REPLACE VIEW llnw_success AS
    SELECT
        COUNT(*) AS successes, DATE(StartTime) AS RunDay
    FROM
        Job
    WHERE
        NOT JobStatus IN ('E' , 'e', 'f', 'I', 'D')
    GROUP BY RunDay;

CREATE OR REPLACE VIEW llnw_failure AS
    SELECT
        COUNT(*) AS failures, DATE(StartTime) AS RunDay
    FROM
        Job
    WHERE
        JobStatus IN ('E' , 'e', 'f', 'I', 'D')
    GROUP BY RunDay;