test

material_transport   transport_emission_factors

select material_name,sum(transport_distance) as total_distance
from material_transport group by material_name
order by total_distance desc limit 3;

select mt.material_name,mt.transport_mode,
sum(mt.quantity * mt.transport_distance * tmf.emission_factor) as max_emission
from material_transport as mt inner join
transport_emission_factors as tmf on mt.transport_mode = tmf.transport_mode
group by mt.material_name,mt.transport_mode
order by max_emission desc limit 3;


equipment_consumption   equipment_work

select equipment,sum(shifts) as total_shifts
from equipment_work where DATE(work_date)  = '2023-04-06'
group by equipment
order by total_shifts desc limit 1;

select ew.equipment,
sum(ew.shifts * ec.consumption_per_shift) as total_consumption
from equipment_work as ew  inner join
equipment_consumption as ec on ew.equipment = ec.equipment
where DATE(ew.work_date)  = '2023-04-06'
group by ew.equipment
order by total_consumption desc limit 1;