add: graphs et rushs

1 files changed, 47 insertions, 0 deletions

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