CBG -> COVID hot zones

[SPTKL]

in recipe:

WITH cbg as (
select 
    (CASE 
        WHEN LEFT(bctcb2010, 1)='2' THEN '36005'
        WHEN LEFT(bctcb2010, 1)='3' THEN '36047'
        WHEN LEFT(bctcb2010, 1)='1' THEN '36061'
        WHEN LEFT(bctcb2010, 1)='4' THEN '36081'
        WHEN LEFT(bctcb2010, 1)='5' THEN '36085'
    END)||substr(bctcb2010, 2,7) as cbg,
    st_makevalid(st_union(wkb_geometry)) as wkb_geometry
FROM dcp_censusblocks."20c" b
GROUP BY (CASE 
        WHEN LEFT(bctcb2010, 1)='2' THEN '36005'
        WHEN LEFT(bctcb2010, 1)='3' THEN '36047'
        WHEN LEFT(bctcb2010, 1)='1' THEN '36061'
        WHEN LEFT(bctcb2010, 1)='4' THEN '36081'
        WHEN LEFT(bctcb2010, 1)='5' THEN '36085'
    END)||substr(bctcb2010, 2,7) 
), cbg_zones as (
SELECT 
    a.zonecolor, 
    a.zonename, 
    b.cbg, 
    (CASE 
        WHEN st_intersects(a.wkb_geometry, b.wkb_geometry)
        THEN  st_area(st_intersection(st_makevalid(a.wkb_geometry), st_makevalid(b.wkb_geometry)))
    ELSE NULL END) as area
FROM covid_zones."2020/10/21" a
RIGHT JOIN cbg b
ON st_intersects(a.wkb_geometry, b.wkb_geometry)
ORDER BY cbg, area
), ranked as (
select 
    zonecolor, zonename, cbg, area, 
    RANK () OVER ( 
        PARTITION by cbg
        ORDER BY area DESC
    ) rank_number 
from cbg_zones
) 
SELECT cbg, zonecolor, zonename
FROM ranked
where rank_number = 1
order by cbg, zonecolor, zonename

[SPTKL]

cbg_covid_zones.csv.zip

[SPTKL]

397617714c0b4de9a89a999be826df96aba7279a