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_synthesis.sql | 43 ++++++++++++++++++++++++++ 1 file changed, 43 insertions(+) create mode 100644 rushs/data-clash/step-3/napoleon_synthesis.sql (limited to 'rushs/data-clash/step-3/napoleon_synthesis.sql') diff --git a/rushs/data-clash/step-3/napoleon_synthesis.sql b/rushs/data-clash/step-3/napoleon_synthesis.sql new file mode 100644 index 0000000..f71dd09 --- /dev/null +++ b/rushs/data-clash/step-3/napoleon_synthesis.sql @@ -0,0 +1,43 @@ +CREATE VIEW napoleon_affair_view AS +WITH count_report AS ( + SELECT + location, + COUNT(*) AS total_reports + FROM napoleon_data.public_reports + GROUP BY location +), +persons_mention AS ( + SELECT + location, + name AS person, + count(*) AS occ + FROM napoleon_data.public_reports + INNER JOIN napoleon_data.historical_people + ON details LIKE concat('%', name, '%') + GROUP BY location, name +), +most_mentioned AS ( + SELECT + location, + person AS most_mentioned_person, + occ AS occurrences_most_mentioned_person, + row_number() OVER (PARTITION BY location ORDER BY occ DESC) AS rn + FROM persons_mention +), +unique_people_count AS ( + SELECT + location, + COUNT(DISTINCT person) AS total_people_mentioned + FROM persons_mention + GROUP BY location +) + +SELECT + rc.location, + total_reports, + total_people_mentioned, + most_mentioned_person, + occurrences_most_mentioned_person +FROM count_report AS rc +INNER JOIN unique_people_count AS uc ON rc.location = uc.location +INNER JOIN most_mentioned AS mmmm ON mmmm.location = rc.location AND mmmm.rn = 1; -- cgit v1.2.3