sustainability_analysis.sql

/*
Project: Intel Sustainability Impact Analysis
Author: Brandon Tuchtenhagen
Date: 10/14/2025
Description: SQL queries used to evaluate the Cost-Effectiveness Index of 
repurposing devices vs. recycling them.
*/

-- TASK 1: ORGANIZING AND UNDERSTANDING THE DATA
-- Objective: Inspect the dataset to understand device distribution by type and age.

-- 1. Count total devices by category (Laptop, Desktop, Tablet)
SELECT 
    device_type, 
    COUNT(*) AS total_count
FROM 
    repurposed_devices_2024
GROUP BY 
    device_type
ORDER BY 
    total_count DESC;

-- 2. Identify average age of devices to assess refurbishment viability
SELECT 
    device_type,
    AVG(device_age_years) AS avg_age
FROM 
    repurposed_devices_2024
GROUP BY 
    device_type;


-- TASK 2: KEY INSIGHTS & COSTS
-- Objective: Calculate the average refurbishment cost per device to set a baseline.

SELECT 
    device_type,
    AVG(refurbishment_cost) AS avg_refurb_cost,
    MAX(refurbishment_cost) AS max_refurb_cost
FROM 
    repurposed_devices_2024
WHERE 
    device_age_years < 6 -- Focusing on viable devices under 6 years
GROUP BY 
    device_type;


-- TASK 3: IDENTIFYING TRENDS & MAXIMIZING SUSTAINABILITY
-- Objective: Calculate CO2 Savings based on deployment region (Grid Intensity).
-- Note: Asia has higher grid intensity, meaning power savings there prevent more CO2.

SELECT 
    deployment_region,
    COUNT(device_id) AS units_deployed,
    SUM(estimated_co2_savings) AS total_co2_saved,
    AVG(refurbishment_cost) AS avg_cost
FROM 
    repurposed_devices_2024
GROUP BY 
    deployment_region
ORDER BY 
    total_co2_saved DESC;


-- TASK 4: COST-EFFECTIVENESS INDEX (ADVANCED)
-- Objective: Identify which "Old" devices (>6 years) are actually worth saving.
-- Logic: We only want to save old devices if they are going to High-Intensity regions.

SELECT 
    device_id,
    device_type,
    device_age_years,
    deployment_region,
    (estimated_co2_savings / NULLIF(refurbishment_cost, 0)) AS cost_effectiveness_index
FROM 
    repurposed_devices_2024
WHERE 
    device_age_years > 6 
    AND deployment_region = 'Asia' -- Target high-impact grid
ORDER BY 
    cost_effectiveness_index DESC;