How can find the best source of protein whilst be the lowest cost?

Need to save money on my spending on meat.

Aggregating the data by type of meat.

Data is unique and was scraped manually from Target and Walmart.

-- View data

	SELECT *
	FROM protein_df
	WHERE protein IS NOT NULL;

-- View the count of each type of protein source

	SELECT type, COUNT(*) AS amount
	FROM protein_df
	WHERE protein IS NOT NULL
	GROUP BY type;
type amount
Chicken Breast 7
Chicken Thigh 3
Ground Beef 13

Calculating the cost per gram of protein.

-- View just three results 

SELECT name, ROUND(price / (protein * servings_per_container), 2) AS "$/pro", ROUND(price / oz, 2) AS "$/oz"
	FROM protein_df
	WHERE protein IS NOT NULL
	LIMIT 3;
name $/g_protein $/oz
96% Lean/4% Fat Extra Lean Ground Beef Roll, 1 lb 0.07 0.39
96% Lean/4% Fat Extra Lean Ground Beef Tray, 2.25 lb 0.06 0.38
93% Lean/7% Fat Lean Ground Beef Tray, 1 lb 0.07 0.38

My 4 priorities…

Meet with all these parameters in this priority:

  1. Highest protein
  2. Lowest calories
  3. Lowest cost per gram of protein
  4. Lowest fat
-- CTE of parameters

WITH best AS (
SELECT name, type, store, ROUND(price / (protein * servings_per_container), 2) AS "$/pro", protein AS pro, calories AS cal, fat_g AS fat, ROUND(price / oz, 2) AS "$/oz"
	FROM protein_df
	WHERE protein IS NOT NULL
	ORDER BY pro ASC, calories ASC, price_per_g_protein DESC, fat ASC)

-- Best options for each type of meat

Select *
FROM best
GROUP BY type;
name type store $/pro pro cal fat
Chicken Breast Tenderloins, 2.25 - 3.2 lb Chicken Breast Walmart 0.03 26 110 0.5
Boneless Chicken Thighs Family Pack, 4.7 - 5.6 lb Chicken Thigh Target 0.03 19 160 10
96% Lean/4% Fat Extra Lean Ground Beef Tray, 2.25 lb Ground Beef Walmart 0.06 24 140 4.5

Average amount of protein per type of meat.

Chicken breast is the best source of protein from the three.

SELECT type, ROUND(AVG(protein)) AS avg_pro_amount
	FROM protein_df
	WHERE protein IS NOT NULL
	GROUP BY type;
type avg_pro_amount
Chicken Breast 25
Chicken Thigh 19
Ground Beef 23

Best return on cost for amount of food.