WITH IngredientCounts AS (
SELECT
cr.Continent,
i.Ingredient AS Continent_Ingredient,
COUNT(*) AS Top_Ingredient_count,
DENSE_RANK() OVER (PARTITION BY cr.Continent ORDER BY COUNT(*) DESC) AS rnk
FROM
country_region cr
INNER JOIN
country_national_dish cnd ON cr.Country_ID = cnd.Country_ID
INNER JOIN
main_contents mc ON cnd.Dishes_ID = mc.Dishes_ID
INNER JOIN
ingredients i ON mc.Ingredient_ID = i.Ingredient_ID
GROUP BY
cr.Continent, i.Ingredient
),
TotalCounts AS (
SELECT
Continent,
SUM(Top_Ingredient_count) AS Total_Count
FROM
IngredientCounts
GROUP BY
Continent
)
SELECT
ic.Continent,
ic.Continent_Ingredient,
ic.Top_Ingredient_count,
ROUND((ic.Top_Ingredient_count / tc.Total_Count) * 100, 2) AS Percent_Continent_Total
FROM
IngredientCounts ic
JOIN
TotalCounts tc ON ic.Continent = tc.Continent
WHERE
ic.rnk = 1
ORDER BY
ic.Continent, ic.Continent_Ingredient;