16.FavouriteCategories

WITH CTE_TotalReportsPerDepartments ([Department Name], ReportsPerDepartment) AS (
SELECT d.[Name] AS [Department Name],
       COUNT(r.Id) AS ReportsPerDepartmentsCount
FROM Departments AS d
JOIN Categories AS c
ON c.DepartmentId = d.Id
JOIN Reports AS r
ON r.CategoryId = c.Id
JOIN Users AS u
ON u.Id = r.UserId
GROUP BY d.[Name])

SELECT e.[Department Name],
       e.[Category Name],
       CAST((CAST(e.ReportsPerCategoryCount AS DECIMAL) / dtr.ReportsPerDepartment) * 100 AS INT) AS [Percentage]
FROM (
    SELECT d.[Name] AS [Department Name],
           c.[Name] AS [Category Name],
           COUNT(r.Id) AS ReportsPerCategoryCount
    FROM Departments AS d
    JOIN Categories AS c
    ON c.DepartmentId = d.Id
    JOIN Reports AS r
    ON r.CategoryId = c.Id
    JOIN Users AS u
    ON u.Id = r.UserId
    GROUP BY c.[Name], d.[Name]) AS e
JOIN CTE_TotalReportsPerDepartments AS dtr
ON dtr.[Department Name] = e.[Department Name]
ORDER BY [Department Name], [Category Name], [Percentage]