Analyzing Industry Carbon Emissions
Our data, which is publicly availably 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 these emissions occured in. 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!
-- Start coding here...
SELECT *
FROM product_emissions;You are interested in the recent carbon emissions of each industry in product_emissions.
Write a query which returns a table of each industry_group along with the number of companies which are in that industry and a total of the PCFs for that industry, rounded to one decimal place. Please name the three columns industry_group, count_industry, and total_industry_footprint.
product_emissions contains data for several years, but your results should only include data for the most recent year that data was collected. Finally, your results should be presented in order from the highest emitting industries to the lowest emitting industries.
SELECT industry_group,COUNT(id) as count_industry ,ROUND(SUM(carbon_footprint_pcf),1) as total_industry_footprint
FROM product_emissions
GROUP BY industry_group,year
HAVING year = 2017
ORDER BY ROUND(SUM(carbon_footprint_pcf),1) DESC