0. Contexto del análisis

Después de cargar los archivos CCRS de California desde 2019 hasta 2025, el objetivo dejó de ser técnico y pasó a ser analítico: preguntar mejor, validar hipótesis y convertir registros operacionales en inteligencia de negocio.

Fuente: California Crash Reporting System (CCRS), California Open Data Portal.

1. Validación histórica de carga

¿La carga de 2019 a 2025 quedó correctamente distribuida por año?

SELECT
    Source_Year,
    COUNT(*) AS TotalRows
FROM dbo.Crashes
GROUP BY Source_Year
ORDER BY Source_Year;

• Los siete años esperados aparecen en la tabla final.
• El total validado por SQL Server es 2,906,173 registros.
• La tabla dbo.Crashes quedó lista para análisis histórico, no solo para pruebas de carga.

La carga quedó validada directamente en SQL Server. Esto confirma que el ETL no solo ejecutó, sino que integró correctamente los años esperados en una tabla histórica única.

2. KPI histórico: accidentes, fallecidos y lesionados

La cantidad de accidentes puede bajar, pero la severidad puede subir. Por eso no basta con contar eventos; hay que medir muertos, lesionados y tasas.

SELECT
    Source_Year,
    COUNT(*) AS TotalCrashes,
    SUM(TRY_CAST(NumberKilled AS INT)) AS TotalKilled,
    SUM(TRY_CAST(NumberInjured AS INT)) AS TotalInjured
FROM dbo.Crashes
GROUP BY Source_Year
ORDER BY Source_Year;

• 2019 tuvo el mayor volumen de accidentes: 478,189.
• 2020 bajó a 374,757 accidentes, pero subió a 4,081 fallecidos.
• 2022 tuvo el mayor número absoluto de fallecidos: 4,661.
• 2025 redujo fallecidos a 3,104, el valor más bajo del periodo mostrado.

El 2020 muestra una caída fuerte en accidentes frente a 2019, pero las fatalidades suben. Esta diferencia confirma que volumen y severidad son dimensiones distintas. El dato sugiere un cambio de comportamiento vial durante y después del periodo COVID: menos eventos, pero eventos más letales.

3. Fatality Rate por año

¿Los accidentes fueron menos frecuentes pero más letales durante 2020–2022?

SELECT
    Source_Year,
    COUNT(*) AS TotalCrashes,
    SUM(TRY_CAST(NumberKilled AS INT)) AS TotalKilled,
    CAST(
        SUM(TRY_CAST(NumberKilled AS INT)) * 100.0 /
        COUNT(*)
        AS DECIMAL(10,4)
    ) AS FatalityRatePct
FROM dbo.Crashes
GROUP BY Source_Year
ORDER BY Source_Year;

• 2022 fue el peor año por tasa de fatalidad: 1.1502%.
• 2020 tuvo menos accidentes que 2019, pero una tasa mucho más alta.
• 2025 muestra la mejor tasa de la serie: 0.7794%, incluso ligeramente mejor que 2019.

Este query cambia la narrativa. No se trata de decir “hubo menos accidentes”; se trata de entender que algunos años tuvieron menos frecuencia, pero mayor severidad. La tasa normalizada es la métrica que permite ver el riesgo real.

4. Frecuencia por tipo de colisión

¿Cómo evolucionan los tipos de accidente por año?

SELECT
    Source_Year,
    Collision_Type_Code,
    COUNT(*) AS TotalCrashes
FROM dbo.Crashes
GROUP BY
    Source_Year,
    Collision_Type_Code
ORDER BY
    Source_Year,
    Collision_Type_Code;

El código C = REAR END se mantiene como el tipo más frecuente en todos los años revisados.

• El código C = REAR END domina consistentemente por volumen.
• 2019 tuvo 158,474 REAR END crashes, el mayor volumen observado en la muestra.
• La caída de 2020 también se observa en REAR END, bajando a 104,981.

REAR END domina por frecuencia, pero eso no significa que sea el principal problema de mortalidad. Esta diferencia llevó al siguiente análisis: separar frecuencia de letalidad.

5. Letalidad por tipo de accidente

El accidente más común no necesariamente es el más peligroso.

SELECT
    Collision_Type_Code,
    COUNT(*) AS TotalCrashes,
    SUM(TRY_CAST(NumberKilled AS INT)) AS TotalKilled,
    CAST(
        SUM(TRY_CAST(NumberKilled AS INT))*100.0 /
        COUNT(*)
        AS DECIMAL(10,4)
    ) AS FatalityRatePct
FROM dbo.Crashes
WHERE Collision_Type_Code IS NOT NULL
GROUP BY Collision_Type_Code
ORDER BY FatalityRatePct DESC;

• G = Vehicle/Pedestrian es el tipo más letal: 8.5299%.
• F = Overturned y A = Head-On superan el 2.6% de letalidad.
• C = Rear End tiene el mayor volumen, pero una tasa mucho menor: 0.3051%.
• B = Side Swipe tiene la menor tasa relativa: 0.1855%.

El código G, accidentes vehículo/peatón, es el más letal por gran diferencia. Aunque REAR END es el más frecuente, su letalidad relativa es baja. Esto obliga a separar dashboards de volumen y dashboards de severidad.

6. Evolución de peatones fallecidos

¿Los peatones mejoraron o empeoraron entre 2019 y 2025?

SELECT
    Source_Year,
    SUM(
        CASE
            WHEN Collision_Type_Code='G'
            THEN TRY_CAST(NumberKilled AS INT)
            ELSE 0
        END
    ) AS PedestrianKilled
FROM dbo.Crashes
GROUP BY Source_Year
ORDER BY Source_Year;

• Las muertes de peatones suben desde 960 en 2019 hasta 1,167 en 2022.
• 2022 es el pico absoluto de peatones fallecidos.
• 2025 baja a 812, una reducción fuerte frente a 2024.

El pico de muertes de peatones ocurre en 2022. La reducción en 2025 es fuerte y abre una pregunta de política pública: ¿qué cambió entre 2022 y 2025?

7. Accidentes de peatones y tasa de fatalidad por año

La mejora de 2025 puede venir por menos accidentes de peatones, menor severidad o ambas cosas.

SELECT
    Source_Year,
    COUNT(*) AS PedestrianCrashes,
    SUM(TRY_CAST(NumberKilled AS INT)) AS PedestrianKilled,
    CAST(
        SUM(TRY_CAST(NumberKilled AS INT))*100.0 /
        COUNT(*)
        AS DECIMAL(10,2)
    ) AS PedestrianFatalityRate
FROM dbo.Crashes
WHERE Collision_Type_Code = 'G'
GROUP BY Source_Year
ORDER BY Source_Year;

• 2021 fue el peor año por tasa de fatalidad peatonal: 10.10%.
• En 2020 hubo menos accidentes peatonales que en 2019, pero mayor tasa de fatalidad.
• 2025 presenta una mejora notable y cae por debajo del nivel 2019.

La recuperación de 2025 no solo reduce muertes absolutas; también reduce la tasa. Esto sugiere una mejora real de severidad, no solamente un cambio de volumen.

8. Ciudades con mayor tasa de fatalidad peatonal

¿Qué ciudades presentan mayor riesgo relativo en accidentes con peatones, usando un umbral mínimo para evitar ruido extremo?

SELECT TOP 25
    City_Name,
    COUNT(*) AS PedestrianCrashes,
    SUM(TRY_CAST(NumberKilled AS INT)) AS PedestrianKilled,
    CAST(
        SUM(TRY_CAST(NumberKilled AS INT))*100.0 /
        COUNT(*)
        AS DECIMAL(10,2)
    ) AS PedestrianFatalityRate
FROM dbo.Crashes
WHERE Collision_Type_Code = 'G'
GROUP BY City_Name
HAVING COUNT(*) >= 100
ORDER BY PedestrianFatalityRate DESC;

• Apple Valley lidera por tasa: 24.76%.
• Stanton, Colton y Fountain Valley superan el 21%.
• Unincorporated combina volumen extremo con riesgo alto: 11,564 accidentes peatonales y 1,713 fallecidos.

Algunas ciudades pequeñas muestran tasas muy altas. Unincorporated no lidera por tasa, pero por volumen absoluto es el gran monstruo. Esto requiere dos lentes: tasa para riesgo relativo y volumen para impacto total.

9. Ciudad + iluminación en accidentes peatonales

Las ciudades de mayor riesgo podrían estar asociadas a oscuridad, falta de iluminación o luces no funcionales.

SELECT TOP 30
    City_Name,
    LightingDescription,
    COUNT(*) AS PedestrianCrashes,
    SUM(TRY_CAST(NumberKilled AS INT)) AS PedestrianKilled,
    CAST(
        SUM(TRY_CAST(NumberKilled AS INT)) * 100.0 / COUNT(*)
        AS DECIMAL(10,2)
    ) AS FatalityRate
FROM dbo.Crashes
WHERE Collision_Type_Code = 'G'
GROUP BY City_Name, LightingDescription
HAVING COUNT(*) >= 50
ORDER BY FatalityRate DESC;

• Los primeros registros están dominados por DARK-NO STREET LIGHTS y DARK-STREET LIGHTS NOT FUNCTIONING.
• San Jose / DARK-NO STREET LIGHTS muestra 38.36%.
• Los Angeles / DARK-STREET LIGHTS NOT FUNCTIONING muestra 36.25%.
• Unincorporated / DARK-NO STREET LIGHTS combina gran volumen: 2,747 casos y 870 fallecidos.

Los primeros lugares están dominados por condiciones oscuras: sin alumbrado o con alumbrado no funcional. Esto transforma el análisis en una hipótesis operacional clara: la iluminación puede estar asociada de manera fuerte con la severidad peatonal.

10. Iluminación y fatalidad peatonal en todo California

Quitando el ruido de ciudad, ¿qué relación aparece entre iluminación y tasa de fatalidad peatonal a nivel estatal?

SELECT
    LightingDescription,
    COUNT(*) AS PedestrianCrashes,
    SUM(TRY_CAST(NumberKilled AS INT)) AS PedestrianKilled,
    CAST(
        SUM(TRY_CAST(NumberKilled AS INT))*100.0 /
        COUNT(*)
        AS DECIMAL(10,2)
    ) AS FatalityRate
FROM dbo.Crashes
WHERE Collision_Type_Code='G'
GROUP BY LightingDescription
ORDER BY FatalityRate DESC;

La oscuridad mata. En la muestra histórica 2019–2025, los accidentes peatonales en condiciones DARK-NO STREET LIGHTS tienen una tasa de fatalidad de 29.52%, frente a 2.60% en DAYLIGHT.

Este es el hallazgo más fuerte del viaje analítico. La disponibilidad y funcionamiento de la iluminación no aparece como un simple atributo descriptivo; aparece como una variable crítica asociada a la severidad de accidentes con peatones.

11. Lecciones analíticas del proceso

• Volumen no es riesgo: REAR END domina por frecuencia, pero no por letalidad.
• Normalizar cambia la historia: Fatality Rate revela lo que el conteo bruto oculta.
• Los códigos importan: el texto PEDESTRAIN tenía error ortográfico; el código G fue más confiable.
• La granularidad importa: Año → tipo → peatón → ciudad → iluminación.
• La IA como partner acelera: cada respuesta del dato generó una pregunta mejor.

12. Narrativa ejecutiva final

El análisis histórico de California Crashes 2019–2025 muestra una reducción fuerte de accidentes en 2020, pero con aumento de letalidad. La severidad alcanza su punto más alto alrededor de 2021–2022 y mejora de forma sostenida hacia 2025.

Los accidentes con peatones representan el tipo más letal por tasa relativa. Dentro de ellos, la iluminación emerge como un factor crítico: los accidentes en oscuridad, especialmente sin alumbrado o con luces no funcionales, presentan tasas de fatalidad dramáticamente superiores a los accidentes en daylight.

Conclusión: este no fue solamente un ejercicio de Python o SQL Server. Fue un caso completo de transformación de datos abiertos en inteligencia operacional, usando preguntas progresivas, business rules, normalización de métricas y análisis valorativo.

PythonSQL ServerOpen DataTraffic SafetyPedestrian RiskLighting AnalysisBusiness Intelligence