WITH snap_gap AS (
    SELECT
        state,
        county,
        COUNT(*) AS total_tracts,
        SUM(lilatracts_1and10) AS desert_tracts,
        ROUND(100.0 * SUM(lilatracts_1and10) / 
            NULLIF(COUNT(*), 0), 1) AS pct_desert,
        ROUND(AVG(povertyrate), 1) AS avg_poverty_rate,
        SUM(tractsnap) AS total_snap_recipients,
        SUM(lasnap1) AS snap_in_food_deserts,
        ROUND(100.0 * SUM(lasnap1) / 
            NULLIF(SUM(tractsnap), 0), 1) AS pct_snap_stranded
    FROM food_access_raw
    WHERE tractsnap > 0
    GROUP BY state, county
    HAVING COUNT(*) >= 10
    AND SUM(lilatracts_1and10) > 0
)
SELECT
    state,
    county,
    total_tracts,
    pct_desert,
    avg_poverty_rate,
    total_snap_recipients,
    snap_in_food_deserts,
    pct_snap_stranded,
    CASE
        WHEN pct_snap_stranded >= 75 THEN 'Critical — SNAP Failing'
        WHEN pct_snap_stranded >= 50 THEN 'Severe — SNAP Partially Failing'
        WHEN pct_snap_stranded >= 25 THEN 'Moderate — SNAP Partially Reaching'
        ELSE 'Adequate — SNAP Mostly Reaching'
    END AS snap_effectiveness
FROM snap_gap
ORDER BY pct_snap_stranded DESC
