• All these queries are fetching tasks and instances where benchmarks where exceeded after October, 16th 2020

-- TaskRuns Assignee's that tracked more than 10 minutes over benchmark
SELECT * FROM (
	SELECT
		"timeEntries"."instanceId",
	    "taskRuns".id AS "taskRunId",
		"taskRuns".benchmark,
		SUM("timeEntries".duration) AS "totalTimeTracked",
		profiles.name AS "userName"
	FROM "taskRuns"
	JOIN "timeEntries" ON "timeEntries"."taskRunId" = "taskRuns".id AND "taskRuns"."assigneeId" = "timeEntries"."userId"
	JOIN users ON "taskRuns"."assigneeId" = users.id
	JOIN profiles ON users."profileId" = profiles.id
	WHERE "taskRuns"."createdAt" > '2020-10-16'
	GROUP BY "taskRuns".id, "taskRuns".benchmark, "timeEntries"."instanceId", profiles.name
) a WHERE (a."totalTimeTracked" - a.benchmark) > INTERVAL '10 minutes';

-- TaskRuns QA Assignee's that tracked more than 10 minutes over QA benchmark
SELECT * FROM (
	SELECT
		"timeEntries"."instanceId",
	    "taskRuns".id AS "taskRunId",
		"taskRuns"."qaBenchmark" AS "QA benchmark",
		SUM("timeEntries".duration) AS "totalTimeTracked",
		profiles.name AS "userName"
	FROM "taskRuns"
	JOIN "timeEntries" ON "timeEntries"."taskRunId" = "taskRuns".id AND "taskRuns"."qaAssigneeId" = "timeEntries"."userId"
	JOIN users ON "taskRuns"."qaAssigneeId" = users.id
	JOIN profiles ON users."profileId" = profiles.id
	WHERE "taskRuns"."createdAt" > '2020-10-16'
	GROUP BY "taskRuns".id, "taskRuns"."qaBenchmark", "timeEntries"."instanceId", profiles.name
) a WHERE (a."totalTimeTracked" - a."QA benchmark") > INTERVAL '10 minutes';

-- Crossed more than 10 minutes over Scoping Benchmark
SELECT * FROM (
	SELECT
		instances.id,
		instances."scopingBenchmark",
		SUM("timeEntries".duration) AS "totalTimeTracked",
		string_agg(profiles.name, ', ') AS "userNames"
	FROM "instances"
	LEFT JOIN "timeEntries" ON "timeEntries"."instanceId" = instances.id
	LEFT JOIN users ON "timeEntries"."userId" = users.id
	LEFT JOIN profiles ON users."profileId" = profiles.id
	WHERE "timeEntries".type = 'scoping' AND "instances"."createdAt" > '2020-10-16'
	GROUP BY "instances".id, "timeEntries"."instanceId"
) a WHERE (a."totalTimeTracked" - a."scopingBenchmark") > INTERVAL '10 minutes';

-- Crossed more than 10 minutes over Management Benchmark
SELECT * FROM (
	SELECT
		instances.id,
		instances."managementBenchmark",
		SUM("timeEntries".duration) AS "totalTimeTracked",
		string_agg(profiles.name, ', ') AS "userNames"
	FROM "instances"
	LEFT JOIN "timeEntries" ON "timeEntries"."instanceId" = instances.id
	LEFT JOIN users ON "timeEntries"."userId" = users.id
	LEFT JOIN profiles ON users."profileId" = profiles.id
	WHERE "timeEntries".type = 'management' AND "instances"."createdAt" > '2020-10-16'
	GROUP BY "instances".id, "timeEntries"."instanceId"
) a WHERE (a."totalTimeTracked" - a."managementBenchmark") > INTERVAL '10 minutes';