Skip to content

detailed-view-reporting

ARCHIVED: This plan was not delivered. Preserved for context.

CREATE TABLE emission_type_mapping ( id INT PRIMARY KEY, name TEXT NOT NULL );

INSERT INTO emission_type_mapping (id, name) VALUES -- food (10000, 'food'), (10001, 'foodvegetarian'), (10002, 'foodnon_vegetarian'),

-- waste (20000, 'waste'), (20001, 'wasteincineration'), (20002, 'wastecomposting'), (20003, 'wastebiogas'), (2000301, 'wastebiogasorganic_waste_food_leftovers'), (2000302, 'wastebiogascooking_vegetable_oil'), (20004, 'wasterecycling'), (2000401, 'wasterecyclingpaper'), (2000402, 'wasterecyclingcardboard'), (2000403, 'wasterecyclingplastics'),q (2000404, 'wasterecyclingglass'), (2000405, 'wasterecyclingferrous_metals'), (2000406, 'wasterecyclingnon_ferrous_metals'), (2000407, 'wasterecyclingelectronics'), (2000408, 'wasterecyclingwood'), (2000409, 'wasterecyclingpet'), (2000410, 'wasterecyclingaluminum'), (2000411, 'wasterecyclingtextile'), (2000412, 'wasterecyclingtoner_and_ink_cartridges'), (2000413, 'wasterecyclinginert_waste'),

-- commuting (30000, 'commuting'), (30001, 'commutingwalking'), (30002, 'commutingcycling'), (30003, 'commutingpowered_two_wheeler'), (30004, 'commutingpublic_transport'), (30005, 'commuting__car'),

-- professional travel (50000, 'professional_travel'), (50100, 'professional_traveltrain'), (50101, 'professional_traveltrainclass_1'), (50102, 'professional_traveltrainclass_2'), (50200, 'professional_travelplane'), (50201, 'professional_travelplanefirst'), (50202, 'professional_travelplanebusiness'), (50203, 'professional_travelplaneeco'),

-- buildings (60000, 'buildings'), (60100, 'buildings__rooms'),

(60101, 'buildingsroomslighting'), (6010101, 'buildingsroomslightingoffice'), (6010102, 'buildingsroomslightinglaboratories'), (6010103, 'buildingsroomslightingarchives'), (6010104, 'buildingsroomslightinglibraries'), (6010105, 'buildingsroomslightingauditoriums'), (6010106, 'buildingsroomslightingmiscellaneous'),

(60102, 'buildingsroomscooling'), (6010201, 'buildingsroomscoolingoffice'), (6010202, 'buildingsroomscoolinglaboratories'), (6010203, 'buildingsroomscoolingarchives'), (6010204, 'buildingsroomscoolinglibraries'), (6010205, 'buildingsroomscoolingauditoriums'), (6010206, 'buildingsroomscoolingmiscellaneous'),

(60103, 'buildingsroomsventilation'), (6010301, 'buildingsroomsventilationoffice'), (6010302, 'buildingsroomsventilationlaboratories'), (6010303, 'buildingsroomsventilationarchives'), (6010304, 'buildingsroomsventilationlibraries'), (6010305, 'buildingsroomsventilationauditoriums'), (6010306, 'buildingsroomsventilationmiscellaneous'),

(60104, 'buildingsroomsheating_elec'), (6010401, 'buildingsroomsheating_elecoffice'), (6010402, 'buildingsroomsheating_eleclaboratories'), (6010403, 'buildingsroomsheating_elecarchives'), (6010404, 'buildingsroomsheating_eleclibraries'), (6010405, 'buildingsroomsheating_elecauditoriums'), (6010406, 'buildingsroomsheating_elecmiscellaneous'),

(60105, 'buildingsroomsheating_thermal'), (6010501, 'buildingsroomsheating_thermaloffice'), (6010502, 'buildingsroomsheating_thermallaboratories'), (6010503, 'buildingsroomsheating_thermalarchives'), (6010504, 'buildingsroomsheating_thermallibraries'), (6010505, 'buildingsroomsheating_thermalauditoriums'), (6010506, 'buildingsroomsheating_thermalmiscellaneous'),

(60200, 'buildingscombustion'), (60201, 'buildingscombustionnatural_gas'), (60202, 'buildingscombustionheating_oil'), (60203, 'buildingscombustionbiomethane'), (60204, 'buildingscombustionpellets'), (60205, 'buildingscombustionforest_chips'), (60206, 'buildingscombustionwood_logs'), (60300, 'buildingsembodied_energy'),

-- process emissions (70000, 'process_emissions'), (70100, 'process_emissionsch4'), (70200, 'process_emissionsco2'), (70300, 'process_emissionsn2o'), (70400, 'process_emissionsrefrigerants'),

-- equipment (80000, 'equipment'), (80100, 'equipmentscientific'), (80200, 'equipmentit'), (80300, 'equipment__other'),

-- purchases (90000, 'purchases'), (90100, 'purchasesgoods_and_services'), (90200, 'purchasesscientific_equipment'), (90300, 'purchasesit_equipment'), (90400, 'purchasesconsumable_accessories'), (90500, 'purchasesbiological_chemical_gaseous'), (90600, 'purchasesservices'), (90700, 'purchasesvehicles'), (90800, 'purchasesother'), (90900, 'purchasesadditional'), (90901, 'purchasesadditional__ln2'),

-- research facilities (100000, 'research_facilities'), (100100, 'research_facilitiesfacilities'), (100200, 'research_facilitiesanimal'),

-- external (110000, 'external'), (110100, 'externalclouds'), (110101, 'externalcloudsvirtualisation'), (110102, 'externalcloudscalcul'), (110103, 'externalcloudsstockage'), (110200, 'externalai'), (110201, 'externalaiprovider_google'), (110202, 'externalaiprovider_mistral_ai'), (110203, 'externalaiprovider_anthropic'), (110204, 'externalaiprovider_openai'), (110205, 'externalaiprovider_cohere'), (110206, 'externalaiprovider_others');

pivot that generate the sql

WITH keys AS ( SELECT DISTINCT (jsonb_object_keys(cr.stats::jsonb -> 'by_emission_type'))::int AS id FROM carbon_reports cr ), cols AS ( SELECT k.id, m.name FROM keys k LEFT JOIN emission_type_mapping m ON m.id = k.id ) SELECT 'SELECT cr.year, u.institutional_id,

    (cr.stats->>''scope1'')::float AS scope1,
    (cr.stats->>''scope2'')::float AS scope2,
    (cr.stats->>''scope3'')::float AS scope3,
    (cr.stats->>''total'')::float AS total,' ||

string_agg(
    format(
        'COALESCE((cr.stats->''by_emission_type''->>''%s'')::float, 0) AS "%s"',
        id,
        COALESCE(name, id::text)
    ),
    ',\n        '
    ORDER BY id
) ||

'

FROM carbon_reports cr
JOIN units u ON u.id = cr.unit_id

ORDER BY cr.year, u.institutional_id;
'

FROM cols;

--> here it is

SELECT cr.year, u.institutional_id,

    (cr.stats->>'scope1')::float AS scope1,
    (cr.stats->>'scope2')::float AS scope2,
    (cr.stats->>'scope3')::float AS scope3,
    (cr.stats->>'total')::float AS total,COALESCE((cr.stats->'by_emission_type'->>'10000')::float, 0) AS "food",
    COALESCE((cr.stats->'by_emission_type'->>'20000')::float, 0) AS "waste",
    COALESCE((cr.stats->'by_emission_type'->>'30000')::float, 0) AS "commuting"

FROM carbon_reports cr
JOIN units u ON u.id = cr.unit_id

ORDER BY cr.year, u.institutional_id;