4 files changed, 64 insertions, 0 deletions
diff --git a/graphs/sql/outsourcing/req01.sql b/graphs/sql/outsourcing/req01.sql
new file mode 100644
index 0000000..caf3c1f
--- /dev/null
+++ b/graphs/sql/outsourcing/req01.sql
@@ -0,0 +1,6 @@
+SELECT T.id AS trial_id,case_id,classification,description
+INTO public.definitive_trials
+FROM justice.trials AS T, justice.cases AS C
+WHERE T.case_id = C.id
+AND T.status = 'FINISHED'
+ORDER BY classification, case_id
diff --git a/graphs/sql/outsourcing/req02.sql b/graphs/sql/outsourcing/req02.sql
new file mode 100644
index 0000000..1c2dd3d
--- /dev/null
+++ b/graphs/sql/outsourcing/req02.sql
@@ -0,0 +1,6 @@
+SELECT person_id,first_name,last_name,birth_date,O.created_at::date AS conviction_date,serving_time,(date '2059-12-03' <= T.created_at::date - concat(serving_time, ' month')::interval) AS could_be_released
+INTO public.outcome_status
+FROM justice.defendants AS D, public.people AS P, justice.trials as T, justice.outcomes AS O, justice.cases AS C
+WHERE D.person_id = P.id AND D.trial_id = T.id AND T.id = O.trial_id AND T.case_id = C.id
+AND O.outcome = 'GUILTY'
+AND C.classification = 'CRIME'
diff --git a/graphs/sql/outsourcing/req03.sql b/graphs/sql/outsourcing/req03.sql
new file mode 100644
index 0000000..226a7e1
--- /dev/null
+++ b/graphs/sql/outsourcing/req03.sql
@@ -0,0 +1,5 @@
+SELECT person_id, first_name, last_name, birth_date, classification, description
+INTO TEMPORARY TABLE pg_temp.not_guilty
+FROM justice.trials AS T, justice.cases AS C, justice.outcomes AS O, public.people AS P, justice.defendants AS D
+WHERE T.case_id = C.id AND O.trial_id = T.id AND D.trial_id = T.id AND D.person_id = P.id
+AND O.outcome = 'NOT_GUILTY'
diff --git a/graphs/sql/outsourcing/req04.sql b/graphs/sql/outsourcing/req04.sql
new file mode 100644
index 0000000..badca1a
--- /dev/null
+++ b/graphs/sql/outsourcing/req04.sql
@@ -0,0 +1,47 @@
+        SELECT
+            person_id,
+            first_name,
+            last_name,
+            o.created_at,
+            serving_time,
+            o.created_at::timestamp
+            + concat(serving_time, ' month')::interval AS release_date
+            INTO TEMPORARY TABLE a
+        FROM justice.trials AS t, justice.cases AS c, justice.defendants AS d,
+            public.people AS p, justice.outcomes AS o,
+            justice.sentence_reductions AS r
+        WHERE
+            t.case_id = c.id
+            AND t.id = d.trial_id
+            AND d.person_id = p.id
+            AND o.trial_id = t.id
+            AND r.outcome_id != o.id
+            AND o.outcome = 'GUILTY';
+
+        SELECT
+            person_id,
+            first_name,
+            last_name,
+            o.created_at,
+            serving_time,
+            o.created_at + concat(serving_time - r.amount, ' month')::interval AS release_date
+            INTO TEMPORARY TABLE b
+        FROM justice.trials AS t, justice.cases AS c, justice.defendants AS d,
+            public.people AS p, justice.outcomes AS o,
+            justice.sentence_reductions AS r
+        WHERE
+            t.case_id = c.id
+            AND t.id = d.trial_id
+            AND d.person_id = p.id
+            AND o.trial_id = t.id
+            AND r.outcome_id = o.id
+            AND o.outcome = 'GUILTY';
+
+INSERT INTO b
+SELECT DISTINCT * FROM a
+WHERE NOT EXISTS (SELECT * FROM b WHERE a.person_id = b.person_id);
+
+SELECT DISTINCT *
+INTO public.release_dates
+FROM b
+ORDER BY b.release_date DESC, serving_time DESC