Volume-weighted, PG&E, Con Edison and small-mileage crude/ammonia lines top the PHMSA safety-rate rankings by pipeline type

SELECT MIN(activity_year) min_yr, MAX(activity_year) max_yr, COUNT(*) n FROM transport.phmsa_hazardous_liquid_incidents

SELECT MIN(report_year) min_yr, MAX(report_year) max_yr, COUNT(*) n FROM transport.phmsa_hazardous_liquid_mileage

SELECT MIN(activity_year) min_yr, MAX(activity_year) max_yr, COUNT(*) n FROM transport.phmsa_gas_transmission_incidents

SELECT MIN(report_year) min_yr, MAX(report_year) max_yr, COUNT(*) n FROM transport.phmsa_gas_transmission_mileage

WITH mileage_opyr AS (
  SELECT operator_id, report_year, SUM(total_miles) AS total_miles
  FROM transport.phmsa_hazardous_liquid_mileage
  WHERE report_year BETWEEN 2010 AND 2024
  GROUP BY operator_id, report_year
),
op_mileyears AS (
  SELECT operator_id, SUM(total_miles) AS mile_years, COUNT(DISTINCT report_year) AS years_reported,
         AVG(total_miles) AS avg_miles
  FROM mileage_opyr
  GROUP BY operator_id
),
op_names AS (
  SELECT operator_id, MAX(operator_name) AS operator_name
  FROM transport.phmsa_hazardous_liquid_mileage
  GROUP BY operator_id
),
inc AS (
  SELECT operator_id,
         COUNT(*) AS n_incidents,
         SUM(CASE WHEN fatalities_total>0 OR injuries_total>0 THEN 1 ELSE 0 END) AS n_serious,
         SUM(fatalities_total) AS fatalities,
         SUM(injuries_total) AS injuries,
         SUM(COALESCE(unintentional_release_bbls,0)) AS bbls_spilled
  FROM transport.phmsa_hazardous_liquid_incidents
  WHERE activity_year BETWEEN 2010 AND 2024
  GROUP BY operator_id
)
SELECT n.operator_name, m.operator_id, m.years_reported, ROUND(m.avg_miles,0) AS avg_miles,
       ROUND(m.mile_years,0) AS mile_years,
       COALESCE(i.n_incidents,0) AS n_incidents,
       COALESCE(i.n_serious,0) AS n_serious,
       COALESCE(i.fatalities,0) AS fatalities,
       COALESCE(i.injuries,0) AS injuries,
       ROUND(COALESCE(i.bbls_spilled,0),0) AS bbls_spilled,
       ROUND(COALESCE(i.n_incidents,0)*1000.0/NULLIF(m.mile_years,0),3) AS incidents_per_1000_mileyr,
       ROUND(COALESCE(i.bbls_spilled,0)/NULLIF(m.mile_years,0),2) AS bbls_per_mileyr
FROM op_mileyears m
JOIN op_names n ON n.operator_id = m.operator_id
LEFT JOIN inc i ON i.operator_id = m.operator_id
WHERE m.avg_miles >= 500 AND m.years_reported >= 8
ORDER BY incidents_per_1000_mileyr DESC
LIMIT 25

WITH mileage_opyr AS (
  SELECT operator_id, report_year, SUM(total_miles) AS total_miles
  FROM transport.phmsa_hazardous_liquid_mileage
  WHERE report_year BETWEEN 2010 AND 2024
  GROUP BY operator_id, report_year
),
op_mileyears AS (
  SELECT operator_id, SUM(total_miles) AS mile_years, COUNT(DISTINCT report_year) AS years_reported,
         AVG(total_miles) AS avg_miles
  FROM mileage_opyr
  GROUP BY operator_id
),
op_names AS (
  SELECT operator_id, MAX(operator_name) AS operator_name
  FROM transport.phmsa_hazardous_liquid_mileage
  GROUP BY operator_id
),
inc AS (
  SELECT operator_id,
         COUNT(*) AS n_incidents,
         SUM(fatalities_total) AS fatalities,
         SUM(injuries_total) AS injuries,
         SUM(CASE WHEN fatalities_total>0 OR injuries_total>0 THEN 1 ELSE 0 END) AS n_serious,
         SUM(COALESCE(unintentional_release_bbls,0)) AS bbls_spilled
  FROM transport.phmsa_hazardous_liquid_incidents
  WHERE activity_year BETWEEN 2010 AND 2024
  GROUP BY operator_id
)
SELECT n.operator_name, m.operator_id, m.years_reported, ROUND(m.avg_miles,0) AS avg_miles,
       ROUND(m.mile_years,0) AS mile_years,
       COALESCE(i.n_incidents,0) AS n_incidents,
       COALESCE(i.fatalities,0) AS fatalities,
       COALESCE(i.injuries,0) AS injuries,
       COALESCE(i.n_serious,0) AS n_serious,
       ROUND(COALESCE(i.n_serious,0)*1000.0/NULLIF(m.mile_years,0),4) AS serious_per_1000_mileyr,
       ROUND(COALESCE(i.bbls_spilled,0)/NULLIF(m.mile_years,0),2) AS bbls_per_mileyr
FROM op_mileyears m
JOIN op_names n ON n.operator_id = m.operator_id
LEFT JOIN inc i ON i.operator_id = m.operator_id
WHERE m.avg_miles >= 500 AND m.years_reported >= 8
ORDER BY serious_per_1000_mileyr DESC
FETCH FIRST 15 ROWS ONLY

WITH mileage_opyr AS (
  SELECT operator_id, report_year, SUM(total_miles) AS total_miles
  FROM transport.phmsa_gas_transmission_mileage
  WHERE report_year BETWEEN 2010 AND 2024
  GROUP BY operator_id, report_year
),
op_mileyears AS (
  SELECT operator_id, SUM(total_miles) AS mile_years, COUNT(DISTINCT report_year) AS years_reported,
         AVG(total_miles) AS avg_miles
  FROM mileage_opyr
  GROUP BY operator_id
),
op_names AS (
  SELECT operator_id, MAX(operator_name) AS operator_name
  FROM transport.phmsa_gas_transmission_mileage
  GROUP BY operator_id
),
inc AS (
  SELECT operator_id,
         COUNT(*) AS n_incidents,
         SUM(fatalities_total) AS fatalities,
         SUM(injuries_total) AS injuries,
         SUM(CASE WHEN fatalities_total>0 OR injuries_total>0 THEN 1 ELSE 0 END) AS n_serious,
         SUM(CASE WHEN explode_ind='YES' THEN 1 ELSE 0 END) AS n_explosions
  FROM transport.phmsa_gas_transmission_incidents
  WHERE activity_year BETWEEN 2010 AND 2024
  GROUP BY operator_id
)
SELECT n.operator_name, m.operator_id, m.years_reported, ROUND(m.avg_miles,0) AS avg_miles,
       ROUND(m.mile_years,0) AS mile_years,
       COALESCE(i.n_incidents,0) AS n_incidents,
       COALESCE(i.fatalities,0) AS fatalities,
       COALESCE(i.injuries,0) AS injuries,
       COALESCE(i.n_serious,0) AS n_serious,
       COALESCE(i.n_explosions,0) AS n_explosions,
       ROUND(COALESCE(i.n_incidents,0)*1000.0/NULLIF(m.mile_years,0),3) AS incidents_per_1000_mileyr,
       ROUND(COALESCE(i.n_serious,0)*1000.0/NULLIF(m.mile_years,0),4) AS serious_per_1000_mileyr
FROM op_mileyears m
JOIN op_names n ON n.operator_id = m.operator_id
LEFT JOIN inc i ON i.operator_id = m.operator_id
WHERE m.avg_miles >= 500 AND m.years_reported >= 8
ORDER BY serious_per_1000_mileyr DESC
FETCH FIRST 15 ROWS ONLY

SELECT activity_year, report_number, fatalities_total, injuries_total, explode_ind, cause, cause_details
FROM transport.phmsa_gas_transmission_incidents
WHERE operator_id='15007' AND (fatalities_total>0 OR injuries_total>0)
ORDER BY activity_year

SELECT MIN(activity_year) min_yr, MAX(activity_year) max_yr, COUNT(*) n FROM transport.phmsa_gas_distribution_incidents

SELECT MIN(report_year) min_yr, MAX(report_year) max_yr, COUNT(*) n FROM transport.phmsa_gas_distribution_mileage

WITH mileage_opyr AS (
  SELECT operator_id, report_year, SUM(total_miles) AS total_miles
  FROM transport.phmsa_gas_distribution_mileage
  WHERE report_year BETWEEN 2010 AND 2024
  GROUP BY operator_id, report_year
),
op_mileyears AS (
  SELECT operator_id, SUM(total_miles) AS mile_years, COUNT(DISTINCT report_year) AS years_reported,
         AVG(total_miles) AS avg_miles
  FROM mileage_opyr
  GROUP BY operator_id
),
op_names AS (
  SELECT operator_id, MAX(operator_name) AS operator_name
  FROM transport.phmsa_gas_distribution_mileage
  GROUP BY operator_id
),
inc AS (
  SELECT operator_id,
         COUNT(*) AS n_incidents,
         SUM(fatalities_total) AS fatalities,
         SUM(injuries_total) AS injuries,
         SUM(CASE WHEN fatalities_total>0 OR injuries_total>0 THEN 1 ELSE 0 END) AS n_serious
  FROM transport.phmsa_gas_distribution_incidents
  WHERE activity_year BETWEEN 2010 AND 2024
  GROUP BY operator_id
)
SELECT n.operator_name, m.operator_id, m.years_reported, ROUND(m.avg_miles,0) AS avg_miles,
       ROUND(m.mile_years,0) AS mile_years,
       COALESCE(i.n_incidents,0) AS n_incidents,
       COALESCE(i.fatalities,0) AS fatalities,
       COALESCE(i.injuries,0) AS injuries,
       COALESCE(i.n_serious,0) AS n_serious,
       ROUND(COALESCE(i.n_serious,0)*1000.0/NULLIF(m.mile_years,0),4) AS serious_per_1000_mileyr
FROM op_mileyears m
JOIN op_names n ON n.operator_id = m.operator_id
LEFT JOIN inc i ON i.operator_id = m.operator_id
WHERE m.avg_miles >= 3000 AND m.years_reported >= 8
ORDER BY serious_per_1000_mileyr DESC
FETCH FIRST 15 ROWS ONLY