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 --- rushs/data-clash/step-3/napoleon_connections.sql | 29 ++++++++++++++++++++++++ 1 file changed, 29 insertions(+) create mode 100644 rushs/data-clash/step-3/napoleon_connections.sql (limited to 'rushs/data-clash/step-3/napoleon_connections.sql') diff --git a/rushs/data-clash/step-3/napoleon_connections.sql b/rushs/data-clash/step-3/napoleon_connections.sql new file mode 100644 index 0000000..170f635 --- /dev/null +++ b/rushs/data-clash/step-3/napoleon_connections.sql @@ -0,0 +1,29 @@ +SELECT + t.id AS individual_id, + t.name, + t.role, + t.affiliation AS current_affiliation, + p.affiliation AS historical_affiliation +FROM surveillance_data.current_targets AS t +INNER JOIN napoleon_data.historical_people AS p + ON + t.name = p.name + OR t.affiliation != 'N/A' +WHERE + EXISTS ( + SELECT 1 FROM (SELECT + a1.group_name_1, + a2.group_name_2 + FROM surveillance_data.group_affinities AS a1, + surveillance_data.group_affinities AS a2 + WHERE a1.group_name_2 = a2.group_name_1 + UNION + SELECT + group_name_1, + group_name_2 + FROM surveillance_data.group_affinities) AS test + WHERE + (group_name_1 = p.affiliation AND t.affiliation = group_name_2) + OR (group_name_1 = t.affiliation AND group_name_2 = p.affiliation) + ) + OR t.affiliation = p.affiliation -- cgit v1.2.3