Christmas Dinner Ingredient Optimization

You’re a data scientist working for a smart grocery platform that helps families automatically plan their Christmas dinner based on their dietary preferences and pantry availability.

Each Christmas recipe consists of several ingredients, and each ingredient has different brands with different prices. Families want the cheapest possible ingredient combination but only using brands they have marked as acceptable and only those ingredients they do not already have in their pantry.

Write a SQL query to determine the cheapest valid shopping cost for each recipe for family_id = 1, while also identifying whether the recipe is feasible to make.

For each recipe, compute:

1. missing_ingredients: number of ingredients the family does not already have in their pantry
2. min_total_cost: total minimum cost required to buy missing ingredients
3. feasible:
   • 'Yes' if every missing ingredient has at least one acceptable brand available
   • 'No' otherwise (and min_total_cost should be NULL)

Note: Sort results by missing_ingredients ASC and min_total_cost ASC (NULLS LAST)

Schema

Input

recipes Table

ingredient_brands Table

pantry Table

acceptable_brands Table

Output

Example

Input

recipes Table

pantry Table

ingredient_brands Table

acceptable_brands Table

Output

Explanation

For Recipe 1, the required ingredients are 100, 200, and 300. The pantry already contains ingredient 100, so only 200 and 300 are missing. The cheapest acceptable option for ingredient 200 is brand 10 at $3.50, since brand 11 is not allowed. For ingredient 300, the cheapest acceptable option is brand 20 at $8.50. This makes the total cost $12.00, and the recipe is feasible.

For Recipe 2, the required ingredients are 200 and 400. The cheapest acceptable choice for ingredient 200 is again $3.50, and for ingredient 400 it is $6.75. The total comes to $10.25, and this recipe is also feasible.