Objective

Write a SQL query to list all possible 3-topping pizza combinations and calculate their total cost. Each combination must contain unique toppings listed in alphabetical order.

Table Schema:

• pizza_toppings

Task Requirements:

• Generate all unique combinations of exactly 3 toppings
• Within each combination, toppings must appear in alphabetical order
• No topping can appear more than once in a combination
• Concatenate the 3 toppings into a single string separated by ', '
• Calculate the total cost by summing the 3 ingredient costs
• Sort by total cost descending, then by the pizza string alphabetically
  
  

Output columns: pizza, total_cost

SELECT
    t1.topping_name || ', ' || t2.topping_name || ', ' || t3.topping_name AS pizza,
    t1.ingredient_cost + t2.ingredient_cost + t3.ingredient_cost AS total_cost
FROM pizza_toppings t1
CROSS JOIN pizza_toppings t2
CROSS JOIN pizza_toppings t3
WHERE t1.topping_name < t2.topping_name
    AND t2.topping_name < t3.topping_name
ORDER BY total_cost DESC, pizza;

Explanation

Step 1: Generate all possible triples

FROM pizza_toppings t1
CROSS JOIN pizza_toppings t2
CROSS JOIN pizza_toppings t3

The triple CROSS JOIN produces every possible combination of three rows from the table. For n toppings, this creates n x n x n rows before filtering.

Step 2: Filter to unique, ordered combinations

WHERE t1.topping_name < t2.topping_name
    AND t2.topping_name < t3.topping_name

The strict < comparisons on topping_name achieve three things at once: they guarantee all three toppings are distinct (no repeats), they enforce alphabetical ordering within each combination, and they eliminate duplicate combinations (e.g., Bacon-Mushrooms-Peppers only appears once, not in all 6 permutations). This reduces n^3 rows down to C(n,3) combinations.

Step 3: Build the output

SELECT
    t1.topping_name || ', ' || t2.topping_name || ', ' || t3.topping_name AS pizza,
    t1.ingredient_cost + t2.ingredient_cost + t3.ingredient_cost AS total_cost

The || operator concatenates the three topping names with ', ' separators. Since the WHERE clause already enforces alphabetical order, the resulting pizza string is automatically sorted. The total_cost is the sum of all three ingredient costs.

Step 4: Sort the results

ORDER BY total_cost DESC, pizza

Results are sorted by total_cost in descending order (most expensive first). Ties are broken alphabetically by the pizza string.