SELECT
    `all_data`.`year`,
    `all_data`.`month`,
    `all_data`.`sessions`,
    `all_data`.`playTime`,
    `all_data`.`events`,
    `all_data`.`alerts`,
    `all_data`.`alertedVehicles`,
    `all_data`.`statusUpdates`,
    `all_data`.`callRequests`,
    `all_data`.`hospitalAssignments`,
    `all_data`.`forceFinishedEvents`,
    `all_data`.`resetedVehicles`,
    COALESCE(`morticians_data`.`dead`, 0) + all_data.morticians AS `dead`,
    COALESCE(`towed_data`.`towed`, 0) + all_data.towed AS `towed`,
    `ranked_data`.`longestSessionId`,
    `ranked_data`.`longestSessionHost`,
    `ranked_data`.`longestSessionPlayTime`,
    `ranked_data`.`mostEventsSessionId`,
    `ranked_data`.`mostEventsSessionHost`,
    `ranked_data`.`mostEventsSessionCount`,
    `real_ranked_data`.`mostEventsSessionId` AS "realMostEventsSessionId",
    `real_ranked_data`.`mostEventsSessionHost` AS "realMostEventsSessionHost",
    `real_ranked_data`.`mostEventsSessionCount` "realMostEventsSessionCount",
    `startup_data`.`total_startups`,
    `pairs_data`.`mostPlayTimePlayer1`,
    `pairs_data`.`mostPlayTimePlayer2`,
    `pairs_data`.`mostPlayTime`,
    `pairs_data`.`mostSessionsPlayer1`,
    `pairs_data`.`mostSessionsPlayer2`,
    `pairs_data`.`mostSessions`
FROM
    (
        SELECT
            YEAR(`s`.`startTime`) AS `year`,
            MONTH(`s`.`startTime`) AS `month`,
            COUNT(0) AS `sessions`,
            ROUND(
                ((SUM(`s`.`playTime`) / 60) / 60),
                0
            ) AS `playTime`,
            SUM(`ss`.`events`) AS `events`,
            SUM(`ss`.`alerts`) AS `alerts`,
            SUM(`ss`.`alertedVehicles`) AS `alertedVehicles`,
            SUM(`ss`.`statusUpdates`) AS `statusUpdates`,
            SUM(`ss`.`callRequests`) AS `callRequests`,
            SUM(`ss`.`hospitalAssignments`) AS `hospitalAssignments`,
            SUM(`ss`.`forceFinishedEvents`) AS `forceFinishedEvents`,
            SUM(`ss`.`resetedVehicles`) AS `resetedVehicles`,
            SUM(`ss`.`morticians`) AS `morticians`,
            SUM(`ss`.`towcars`) AS `towed`
        FROM
            `luedenscheid`.`sessions` `s`
        JOIN `luedenscheid`.`session_statistics` `ss` ON `s`.`id` = `ss`.`sessionId`
        GROUP BY
            YEAR(`s`.`startTime`),
            MONTH(`s`.`startTime`)
    ) AS `all_data`
LEFT JOIN
    (
        SELECT
            YEAR(`s`.`startTime`) AS `year`,
            MONTH(`s`.`startTime`) AS `month`,
            SUM(`svs`.`status8`) AS `dead`
        FROM
            `luedenscheid`.`sessions` `s`
        JOIN `luedenscheid`.`session_vehicle_statistics` `svs` ON `s`.`id` = `svs`.`sessionId`
        WHERE
            `svs`.`vehicleId` IN (1972, 1973, 1977)
        GROUP BY
            YEAR(`s`.`startTime`),
            MONTH(`s`.`startTime`)
    ) AS `morticians_data` ON `all_data`.`year` = `morticians_data`.`year` AND `all_data`.`month` = `morticians_data`.`month`
LEFT JOIN
    (
        SELECT
            YEAR(`s`.`startTime`) AS `year`,
            MONTH(`s`.`startTime`) AS `month`,
            SUM(`svs`.`status8`) AS `towed`
        FROM
            `luedenscheid`.`sessions` `s`
        JOIN `luedenscheid`.`session_vehicle_statistics` `svs` ON `s`.`id` = `svs`.`sessionId`
        WHERE
            `svs`.`vehicleId` IN (1967, 1968, 1969, 1970, 1971, 1974, 1975, 1976)
        GROUP BY
            YEAR(`s`.`startTime`),
            MONTH(`s`.`startTime`)
    ) AS `towed_data` ON `all_data`.`year` = `towed_data`.`year` AND `all_data`.`month` = `towed_data`.`month`
LEFT JOIN
    (
        WITH RankedSessions AS (
            SELECT
                `s`.`id` AS `sessionId`,
                YEAR(`s`.`startTime`) AS `year`,
                MONTH(`s`.`startTime`) AS `month`,
                s.name as host,
                `s`.`playTime`,
                `ss`.`events`,
                ROW_NUMBER() OVER (PARTITION BY YEAR(`s`.`startTime`), MONTH(`s`.`startTime`) ORDER BY `s`.`playTime` DESC) AS PlayTimeRank,
                ROW_NUMBER() OVER (PARTITION BY YEAR(`s`.`startTime`), MONTH(`s`.`startTime`) ORDER BY `ss`.`events` DESC) AS EventsRank
            FROM
                `luedenscheid`.`sessions` `s`
            JOIN
                `luedenscheid`.`session_statistics` `ss` ON `s`.`id` = `ss`.`sessionId`
        )
        SELECT
            `year`,
            `month`,
            MAX(CASE WHEN PlayTimeRank = 1 THEN `sessionId` END) AS `longestSessionId`,
            MAX(CASE WHEN PlayTimeRank = 1 THEN `host` END) AS `longestSessionHost`,
            MAX(CASE WHEN PlayTimeRank = 1 THEN `playTime` END) AS `longestSessionPlayTime`,
            MAX(CASE WHEN EventsRank = 1 THEN `sessionId` END) AS `mostEventsSessionId`,
            MAX(CASE WHEN EventsRank = 1 THEN `host` END) AS `mostEventsSessionHost`,
            MAX(CASE WHEN EventsRank = 1 THEN `events` END) AS `mostEventsSessionCount`
        FROM
            RankedSessions
        GROUP BY
            `year`, `month`
    ) AS `ranked_data` ON `all_data`.`year` = `ranked_data`.`year` AND `all_data`.`month` = `ranked_data`.`month`
LEFT JOIN
    (
        WITH RankedSessions AS (
            SELECT
                `s`.`id` AS `sessionId`,
                YEAR(`s`.`startTime`) AS `year`,
                MONTH(`s`.`startTime`) AS `month`,
                s.name as host,
                `s`.`playTime`,
                `ss`.`events`,
                ROW_NUMBER() OVER (PARTITION BY YEAR(`s`.`startTime`), MONTH(`s`.`startTime`) ORDER BY `ss`.`events` DESC) AS EventsRank
            FROM
                `luedenscheid`.`sessions` `s`
            JOIN
                `luedenscheid`.`session_statistics` `ss` ON `s`.`id` = `ss`.`sessionId`
            WHERE (`ss`.`events` <> 0)
            AND (((`ss`.`alerts` / `ss`.`events`) * 100 ) > 80)
            AND (`ss`.`alerts` > 10)
            AND (((`ss`.`forceFinishedEvents` / `ss`.`events`) * 100) < 20)
            AND (((`ss`.`lstGeneratedEvents` / `ss`.`events`) * 100) < 20)
        )
        SELECT
            `year`,
            `month`,
            MAX(CASE WHEN EventsRank = 1 THEN `sessionId` END) AS `mostEventsSessionId`,
            MAX(CASE WHEN EventsRank = 1 THEN `host` END) AS `mostEventsSessionHost`,
            MAX(CASE WHEN EventsRank = 1 THEN `events` END) AS `mostEventsSessionCount`
        FROM
            RankedSessions
        GROUP BY
            `year`, `month`
    ) AS `real_ranked_data` ON `all_data`.`year` = `real_ranked_data`.`year` AND `all_data`.`month` = `real_ranked_data`.`month`
LEFT JOIN
    (
        SELECT
            YEAR(`ps`.`time`) AS `year`,
            MONTH(`ps`.`time`) AS `month`,
            COUNT(0) AS `total_startups`
        FROM
            `luedenscheid`.`player_startups` `ps`
        GROUP BY
            `year`, `month`
    ) AS `startup_data` ON `all_data`.`year` = `startup_data`.`year` AND `all_data`.`month` = `startup_data`.`month`
LEFT JOIN
    (
        WITH RankedPairsPlayTime AS (
            SELECT
                `year`,
                `month`,
                `player1`,
                `player2`,
                `total_playtime`,
                `total_sessions`,
                ROW_NUMBER() OVER (PARTITION BY `year`, `month` ORDER BY `total_playtime` DESC) AS RowNum
            FROM
                `view_player_pairing_monthly`
        ),
        RankedPairsSessions AS (
            SELECT
                `year`,
                `month`,
                `player1`,
                `player2`,
                `total_playtime`,
                `total_sessions`,
                ROW_NUMBER() OVER (PARTITION BY `year`, `month` ORDER BY `total_sessions` DESC) AS RowNum
            FROM
                `view_player_pairing_monthly`
        )
    SELECT
        p1.`year`,
        p1.`month`,
        p1.`player1` AS `mostPlayTimePlayer1`,
        p1.`player2` AS `mostPlayTimePlayer2`,
        p1.`total_playtime` AS `mostPlayTime`,
        s1.`player1` AS `mostSessionsPlayer1`,
        s1.`player2` AS `mostSessionsPlayer2`,
        s1.`total_sessions` AS `mostSessions`
    FROM
        RankedPairsPlayTime p1
    JOIN
        RankedPairsSessions s1
    ON
        p1.`year` = s1.`year` AND p1.`month` = s1.`month` AND p1.RowNum = 1 AND s1.RowNum = 1
    ) AS `pairs_data` ON `all_data`.`year` = `pairs_data`.`year` AND `all_data`.`month` = `pairs_data`.`month`
ORDER BY
    `all_data`.`year` DESC,
    `all_data`.`month` DESC;