A Live Developer Journal

Meal Plan analysis for F45 using SQL

No notes with this article, just a few queries I played with to get a macro calculator database up and running based on a meal plan I'm following with F45

CREATE database meal_plan_analysis; USE DATABASE meal_plan_analysis;

CREATE TABLE meal_types (
  id INT NOT NULL AUTO_INCREMENT,
  meal_type VARCHAR(100) NOT NULL,
  PRIMARY KEY (id)
);


INSERT INTO meal_types
(meal_type)
VALUES
('Breakfast'),
('Lunch'),
('Dinner'),
('Snack');

SELECT * FROM meal_types;

CREATE TABLE meal_plans (
  id INT NOT NULL AUTO_INCREMENT,
  meal_plan VARCHAR(150) NOT NULL,
  duration_in_days INT,
  PRIMARY KEY (id)
);


INSERT INTO meal_plans
(meal_plan, duration_in_days)
VALUES
('Spring Training', 14),
('Regular Season', 28),
('World Series', 14);

SELECT * FROM meal_plans;

CREATE TABLE nutrition_info (
  id INT NOT NULL AUTO_INCREMENT,
  meal_plan_id INT,
  meal_type_id INT,
  calories INT NOT NULL,
  protein DECIMAL(5,2) NOT NULL,
  fat DECIMAL(5,2) NOT NULL,
  carbohydrates DECIMAL(5,2) NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (meal_plan_id) REFERENCES meal_plans(id),
  FOREIGN KEY (meal_type_id) REFERENCES meal_types(id)
);

DESCRIBE nutrition_info

INSERT INTO nutrition_info
(
  meal_plan_id,
  meal_type_id,
  calories,
  protein,
  fat,
  carbohydrates
)
VALUES
(1, 1, 344, 12.24, 27.34, 11.84),
(1, 4, 125, 6.1, 8.6, 4.8),
(1, 2, 349, 24.14, 21.92, 12.43),
(1, 4, 124, 12.1, 5.3, 5.4),
(1, 3, 335, 26.73, 21.30, 7.18);


SELECT mt.meal_type AS meal,
       ROUND((AVG(calories) / 1349) * 100) AS "Calories (%)",
       ROUND(AVG(protein / (protein + fat + carbohydrates) * 100), 0) AS "Protein (%)",
       ROUND(AVG(fat / (protein + fat + carbohydrates) * 100),0) AS "Fat (%)",
       ROUND(AVG(carbohydrates / (protein + fat + carbohydrates) * 100),0) AS "Carbs (%)"
  FROM nutrition_info ni
 INNER JOIN meal_types mt
    ON mt.id = ni.meal_type_id
 WHERE meal_plan_id = 2
 GROUP BY meal;

results of query

How many calories should you be eating?


CREATE TABLE regular_season_macro_percentages (
  id INT NOT NULL AUTO_INCREMENT,
  meal VARCHAR(150) NOT NULL,
  calories INT NOT NULL,
  protein INT NOT NULL,
  fat INT NOT NULL,
  carbs INT NOT NULL,
  PRIMARY KEY (id)
);


INSERT INTO regular_season_macro_percentages
(meal, calories, protein, fat, carbs)
VALUES
('Breakfast', 25, 42, 41, 17),
('Snack 1', 11, 39, 21, 40),
('Lunch', 26, 45, 17, 37),
('Snack 2', 11, 39, 21, 40),
('Dinner', 27, 47, 20, 32);


SELECT
       meal,
       ROUND(1700 * (calories / 100)) AS calories,
       ROUND(1700 * (calories / 100) * (protein / 100) / 4) AS "Protein (g)",
       ROUND(1700 * (calories / 100) * (fat / 100) / 9) AS "Fat (g)",
       ROUND(1700 * (calories / 100) * (carbs / 100) / 4) AS "Carbs (g)"
  FROM regular_season_macro_percentages;


SELECT
       ROUND(SUM(1700 * (calories / 100))) AS calories,
       ROUND(SUM(1700 * (calories / 100) * (protein / 100) / 4)) AS "Protein (g)",
       ROUND(SUM(1700 * (calories / 100) * (fat / 100) / 9)) AS "Fat (g)",
       ROUND(SUM(1700 * (calories / 100) * (carbs / 100) / 4)) AS "Carbs (g)"
  FROM regular_season_macro_percentages;

meal plan query results