I have this table in SQL:

CREATE TABLE food_orders (
    id VARCHAR(50),
    name VARCHAR(50),
    food_ordered VARCHAR(50)
);

INSERT INTO food_orders (id, name, food_ordered) VALUES
(1, 'Person1', 'burgers'),
(2, 'Person1', 'pizza'),
(3, 'Person1', 'pizza'),
(4, 'Person1', 'pizza'),
(5, 'Person1', 'pizza'),
(6, 'Person2', 'pizza'),
(7, 'Person2', 'burger'),
(8, 'Person2', 'burger'),
(9, 'Person2', 'burger'),
(10, 'Person2', 'burger'),
(11, 'Person3', 'burger'),
(12, 'Person3', 'burger'),
(13, 'Person3', 'burger'),
(14, 'Person3', 'burger'),
(15, 'Person3', 'burger');

For each type of food – I want to see how many people exclusively ordered that food compared to all people who ordered that food. For example:

• Suppose person1, person2, person3 ordered pizza
• But person1 only ordered pizza whereas person2 and person3 ordered pizza and tacos
• For pizza, the calculation would be 1/3
• I want to repeat this analysis for all food types

Here is what I tried:

WITH cte1 AS (
  SELECT name, food_ordered, COUNT(*) AS food_count
  FROM myt
  GROUP BY name, food_ordered
),
cte2 AS (
  SELECT name, COUNT(DISTINCT food_ordered) AS distinct_foods
  FROM myt
  GROUP BY name
),
food_stats AS (
  SELECT 
    c1.food_ordered,
    COUNT(DISTINCT c1.name) AS total_eaters,
    COUNT(DISTINCT CASE WHEN c2.distinct_foods = 1 THEN c1.name END) AS exclusive_eaters
  FROM cte1 c1
  JOIN cte2 c2 ON c1.name = c2.name
  GROUP BY c1.food_ordered
)
SELECT 
  food_ordered,
  total_eaters,
  exclusive_eaters,
  (exclusive_eaters * 100.0 / total_eaters) AS percentage_exclusive
FROM food_stats
ORDER BY percentage_exclusive DESC;

But this is giving me:

  food_ordered total_eaters exclusive_eaters percentage_exclusive
1       burger            0                0             0.00000
2        pizza            0                0              0.00000

I can see this is not correct. How can I fix this?