From c9b6b9a5ca082fe7c1b6f58d7713f785a9eb6a5c Mon Sep 17 00:00:00 2001 From: Martial Simon Date: Mon, 15 Sep 2025 01:08:27 +0200 Subject: add: graphs et rushs --- graphs/sql/outsourcing/req04.sql | 47 ++++++++++++++++++++++++++++++++++++++++ 1 file changed, 47 insertions(+) create mode 100644 graphs/sql/outsourcing/req04.sql (limited to 'graphs/sql/outsourcing/req04.sql') 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 -- cgit v1.2.3