Add municipality level top-5 view and update muni assessment roll view

[wrridgeway]

After the conclusion of each assessment stage countywide, the CCAO publishes municipal reporting. We've finally landed on a standard format; let's institutionalize it on Athena and Open Data.

There will be two tables or views: one that summarizes the assessment roll per municipality, and the other that reports on the Top 5 PINs in that municipality.

Below are the columns I've used for Tableau reporting, but they may require some changes for Open Data.

Municipal Assessment Roll: unique per major class, per municipality, per stage, per tax year. Columns:

• Municipality

• Tax Year

• Stage

• Tax Year & Stage (concatenated)

• Major Class

• Class Group (if Major Class = 2, then Residential; else, Non-Residential)

• Stage

• Number of Parcels

• Total AV

• Delta pct AV

• Phase total AV

• Phase AV Share

Municipal Top 5: top 5 PINs per municipality, per tax year. Columns:

• Municipality

• Tax Year

• PIN (pretty format)

• Class

• Major Class

• Taxpayer Name

• Situs Address

• Prior BOR AV (i.e., from the previous Tax Year, BOR final stage)

• CCAO AV (i.e., in Tax Year, CCAO final stage)

• BOR AV (i.e., in Tax Year, BOR final stage)

• BOR Change ($)

Please clean:

• "Mc Cook" to "McCook"

• "Forestview" to "Forest View"

[wrridgeway]

@ccao-jardine Not sure which columns BOR Change should compare.

Also need some guidance on Delta pct AV, Phase total AV, Phase AV Share

[ccao-jardine]

Sorry all, I fat-fingered; this is not yet ready for review.

[ccao-jardine]

@ccao-jardine Not sure which columns BOR Change should compare.

Also need some guidance on Delta pct AV, Phase total AV, Phase AV Share

Good questions, I should have specified!

• Delta pct AV: that row's percent AV change, relative to the immediately prior stage.
• Phase total AV: this one I could be open to discussion for. It's been the total AV for the municipality, stage, and tax year (i.e., summed across major classes). E.g., let's say Chicago Heights has 6 rows for 6 major classes for tax year 2020, stage BOR final; this column is the sum of the major classes and will be the same value for all 6 of these Chicago Heights rows. Historically it's been useful to have as an attribute, and for constructing and explaining Phase AV share, but I wonder if it might be confusing, unless it's renamed to Municipal total AV. Thoughts?
• Phase AV share: that row's percent share of Phase total AV; e.g., for Class 1 in Chicago Heights, this row is Chicago Heights's Class 1 total AV divided by the Phase total AV.

[wrridgeway]

vw_assessment_roll_muni

We don't want to see any changes to this view other than the addition of 3 new columns and one column name change (municipality_name -> municipality). Median columns are excluded here since they are not deterministic. We see the expected result of 0 rows returned:

WITH old AS (

    SELECT
        year,
        stage,
        municipality_name as municipality,
        class,
        num_pin_w_value,
        num_pin_total_in_group,
        pct_pin_w_value_in_group,
        bldg_sum,
        land_sum,
        tot_sum
    FROM reporting.vw_assessment_roll_muni
),

new AS (

    SELECT
        year,
        stage,
        municipality,
        class,
        num_pin_w_value,
        num_pin_total_in_group,
        pct_pin_w_value_in_group,
        bldg_sum,
        land_sum,
        tot_sum
    FROM "z_ci_429_institutionalize_municipal_reporting_on_open_data_reporting".vw_assessment_roll_muni
)

SELECT * FROM old
EXCEPT
SELECT * FROM new

EDIT: this still holds after the most recent changes.

[ccao-jardine]

Thanks team! The "pizza tracker" column for muni assessment roll is a substantial value-add and I give @wrridgeway major props for it.

We are constantly asked about whether we can report on the complete assessment roll of a municipality that might overlap townships in various assessment stages. In the past, users would seek maps of municipalities and townships, then have to consult the assessment calendar to see which stage each township was in. Building this "pizza tracker" column for municipalities is an excellent milestone, makes life easier for us, and provides an opportunity to test user behavior around it conceptually.