Code:food2.sql

Like Code:food1.sql, this analyzes food production data from FAO, and nutrition data from USDA. But while food1 is focused on plants (crops primary), food2 is focused on animal products (livestock primary).

Dependencies:

• Code:fao2usda.csv
• File:sr28-collated.csv
• File:fao-livestock-primary.csv

Code

-- Analysis of the production of animal products --
---------------------------------------------------


-- Load data from various sources
.mode csv
--  cross-reference table 2: livestock products (FAO data) with foods (USDA nutrition data):
.import fao2usda.csv                     fao2usda
--  nutrition (USDA):
.import data/sr28-collated.csv           usda
--  crop production (FAO):
.import data/fao-livestock-primary.csv   animal
.mode list




--## Animal products, worldwide production per capita

CREATE TABLE summary AS
SELECT `Item`,
       food_grams_per_day_per_capita,
       food_grams_per_day_per_capita * 0.01*`Energy (kcal)`     AS      kalories_per_day_per_capita,
       food_grams_per_day_per_capita * 0.01*`Protein (g)`       AS protein_grams_per_day_per_capita,
       food_grams_per_day_per_capita * 0.01*`Total lipid (fat) (g)` AS fat_grams_per_day_per_capita
FROM (
 SELECT *,
  AVG(`Value`)                       -- tonnes per year
  * 2737.9093                        -- convert to grams per day
  / 7.9e9                            -- per capita, world population
--* 0.01*(100-`Refuse`)*`Multiplier` -- edible portion, but this is commented out because actually FAO already specifies that the data is for edible portion, and so does USDA
  AS food_grams_per_day_per_capita
 FROM animal
 JOIN fao2usda USING (`Item Code (FAO)`)
 JOIN usda     USING (`NDB_No`)
 WHERE `Area`="World" AND `Element`="Production" AND `Unit`="tonnes"
 GROUP BY `Item`
);



-- Total animal protein produced:
SELECT SUM(protein_grams_per_day_per_capita) FROM summary;
SELECT SUM(kalories_per_day_per_capita) FROM summary;



-- Meat production from all ruminants:
SELECT SUM(food_grams_per_day_per_capita),
    SUM(protein_grams_per_day_per_capita) FROM summary
 WHERE Item="Meat, buffalo"
    OR Item="Meat, cattle"
    OR Item="Meat, goat"
    OR Item="Meat, sheep";

-- Milk production from all livestock species:
SELECT SUM(food_grams_per_day_per_capita)*0.9776 AS milk_mL_per_day_per_capita, -- Milk is slightly denser than water. To convert "grams" to "milliliters milk", multiply by 97.76% (according to GNU Units)
    SUM(protein_grams_per_day_per_capita) FROM summary WHERE Item LIKE "Milk%";







-------------------------------
----- DATA & CODE TESTING -----

-- Test that all items have "Production" in tonnes/year (both queries should return the same number)
SELECT COUNT(DISTINCT `Item`) FROM animal;
SELECT COUNT(DISTINCT `Item`) FROM animal WHERE `Area`="World" AND `Element`="Production" AND `Unit`="tonnes";

-- Test that "World" is the only aggregate
SELECT SUM(`Value`) FROM animal WHERE `Area` ="World" AND `Element`="Production" AND `Unit`="tonnes";
SELECT SUM(`Value`) FROM animal WHERE `Area`<>"World" AND `Element`="Production" AND `Unit`="tonnes";