Photo by Maxim Tolchinskiy on Unsplash
When factoring heat generation required for the manufacturing and transportation of products, Greenhouse gas emissions attributable to products, from food to sneakers to appliances, make up more than 75% of global emissions. -The Carbon Catalogue
Our data, which is publicly available on nature.com, contains product carbon footprints (PCFs) for various companies. PCFs are the greenhouse gas emissions attributable to a given product, measured in CO2 (carbon dioxide equivalent).
This data is stored in a PostgreSQL database containing one table, prouduct_emissions, which looks at PCFs by product as well as the stage of production that these emissions occurred. Here's a snapshot of what product_emissions contains in each column:
product_emissions
product_emissions| field | data type |
|---|---|
id | VARCHAR |
year | INT |
product_name | VARCHAR |
company | VARCHAR |
country | VARCHAR |
industry_group | VARCHAR |
weight_kg | NUMERIC |
carbon_footprint_pcf | NUMERIC |
upstream_percent_total_pcf | VARCHAR |
operations_percent_total_pcf | VARCHAR |
downstream_percent_total_pcf | VARCHAR |
You'll use this data to examine the carbon footprint of each industry in the dataset!
-- Total Industry Footprint by Industry Group
SELECT industry_group, COUNT(DISTINCT(company)) as num_companies, ROUND(SUM(carbon_footprint_pcf), 1) as total_industry_footprint
FROM product_emissions
WHERE year in (select max(year) from product_emissions)
GROUP BY industry_group
ORDER BY total_industry_footprint DESC;
-- What countries have the highest footprint per companies
SELECT country, round(sum(carbon_footprint_pcf)/count(distinct company),2) as footprint_per_capita
FROM public.product_emissions
GROUP BY country
ORDER BY footprint_per_capita DESC;
-- What industry leads to the highest emission
SELECT industry_group, sum(carbon_footprint_pcf) as total_carbon_footprint
FROM public.product_emissions
GROUP BY industry_group
ORDER BY total_carbon_footprint DESC;
-- Create a running total footprint of the Electrical Equipment and Machinery through the year
SELECT distinct year, sum(carbon_footprint_pcf) OVER (ORDER BY year) as running_total
FROM product_emissions
WHERE industry_group LIKE '%Electrical Equipment and Machinery%'
GROUP BY year, carbon_footprint_pcf;
-- Find the % increase in carboon footprint YoY
WITH running_totals AS (
SELECT DISTINCT year, sum(carbon_footprint_pcf) OVER (ORDER BY year) AS running_total
FROM product_emissions
GROUP BY year, carbon_footprint_pcf
ORDER BY year ASC
)
SELECT distinct a.year, round((a.running_total-coalesce(b.running_total,0))/a.running_total *100,2) AS YOY_change
FROM running_totals AS a
INNER JOIN running_totals AS b
ON a.year = b.year +1
ORDER BY a.year ASC