1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
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;
|
