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;