Refactor qc_*.sql scripts to run in EDM rather than pulling into BUILD

[mgraber]

For example, see qaqc/mismatch.sql

Scripts to refactor:

• [x] qc_bbldiffs.sql #45
• [x] qc_frequencycomparison.sql #44
• [x] qc_frequencynownullcomparison.sql #44
• [x] qc_versioncomparisonfields.sql #44

Scripts that aren't used and should be retired:

• [ ] qc_bblareachange.sql #44
• [ ] qc_versioncomparisonarea.sql
• [ ] qc_versioncomparisonbbls.sql
• [ ] qc_versioncomparisonvalues.sql
• [ ] qc_versioncomparisonzoningdistrictarea.sql
• [x] qc_bblsaddedandremoved.sql (same as qaqc_bbl)

[SPTKL]

The build process will be:

dataloading -> build -> archive -> qaqc -> export tables and upload to digitalocean spaces

on these tables are actually used (or get looked at) source_data_versions, bbldiff,qc_frequencychanges,qc_versioncomparison, qc_versioncomparisonnownullcount,qaqc_mismatch,qaqc_frequency, qaqc_bbl

we should prioritize above and retire the tables not used by the qaqc app

[SPTKL]

qc_frequencycomparison

WITH 
count_old as (
    SELECT b.key as field, b.value::numeric as count
    FROM( SELECT row_to_json(row) as _col 
    FROM(
    SELECT 
        sum(zoningdistrict1 is not null::int) as zoningdistrict1,
        sum(zoningdistrict2 is not null::int) as zoningdistrict2,
        sum(zoningdistrict3 is not null::int) as zoningdistrict3,
        sum(zoningdistrict4 is not null::int) as zoningdistrict4,
        sum(commercialoverlay1 is not null::int) as commercialoverlay1,
        sum(commercialoverlay2 is not null::int) as commercialoverlay2,
        sum(specialdistrict1 is not null::int) as specialdistrict1,
        sum(specialdistrict2 is not null::int) as specialdistrict2,
        sum(specialdistrict3 is not null::int) as specialdistrict3,
        sum(limitedheightdistrict is not null::int) as limitedheightdistrict,
        sum(zoningmapnumber is not null::int) as zoningmapnumber,
        sum(zoningmapcode is not null::int) as zoningmapcode
    FROM dcp_zoning_taxlot_prev) row) a, json_each_text(_col) as b
),
count_new as (
    SELECT b.key as field, b.value::numeric as count
    FROM( SELECT row_to_json(row) as _col 
    FROM(
    SELECT 
        sum(zoningdistrict1 is not null::int) as zoningdistrict1,
        sum(zoningdistrict2 is not null::int) as zoningdistrict2,
        sum(zoningdistrict3 is not null::int) as zoningdistrict3,
        sum(zoningdistrict4 is not null::int) as zoningdistrict4,
        sum(commercialoverlay1 is not null::int) as commercialoverlay1,
        sum(commercialoverlay2 is not null::int) as commercialoverlay2,
        sum(specialdistrict1 is not null::int) as specialdistrict1,
        sum(specialdistrict2 is not null::int) as specialdistrict2,
        sum(specialdistrict3 is not null::int) as specialdistrict3,
        sum(limitedheightdistrict is not null::int) as limitedheightdistrict,
        sum(zoningmapnumber is not null::int) as zoningmapnumber,
        sum(zoningmapcode is not null::int) as zoningmapcode
    FROM dcp_zoning_taxlot) row) a, json_each_text(_col) as b
) 
SELECT a.field, a.count as countold, b.count as countnew
FROM count_old a JOIN count_new b on a.field=b.field
ORDER BY b.count - a.count DESC;

[github-actions[bot]]

Stale issue message