Assist: properties that most impact each school district's property taxes

wrridgeway commented 4 days ago

Goal:

Produce a flat file of a list that identifies, per school taxing district in 2023, the top 10 most impactful PINs (i.e., the PINs with the largest AV).

Context:

There are two related Strategic Plan initiatives we can assist with: "Build support amongst taxing bodies to defend quality of assessments and intervene in appeals processes" and "Evaluate opportunities for the Assessor's Office to defend our values in the Board of Review appeals process". A list of top 10 most impactful properties might help these initiatives.

Universe:

Please identify school districts using the tax database; specifically, use all agencies in tax.agency_info, where major_type = SCHOOL. Each agency has an agency_num, which is associated with multiple taxcodes in tax.tax_code. Taxcode is how PINs are associated with agencies; specifically, each PIN in tax.pin has one taxcode per year, so you can get the PINs for each agency by associating all PINs with the taxcodes for that agency.

For this first attempt, please identify the top PINs by the ones with the highest 2023 CCAO Mailed AV.

Data requested:

I'd like one row per PIN school district in 2023, such that each school district has 10 rows for the top 10 PINs. Columns:

[x] agency name
[x] agency_num
[x] PIN rank (1 through 10)
[x] PIN number
[x] PIN class
[x] PIN AV: CCAO mailed
[x] PIN AV: CCAO final
[x] PIN AV: BOR final
[x] nice to have: EAV
[x] 2023 tax bill
[x] taxcode cumulative rate
[x] TIF district name, to know whether the PIN is in a TIF
[x] SSA name, to know whether the PIN is in an SSA

Other ideas?

wrridgeway commented 4 days ago

QUESTION: @ccao-jardine do we want to define impactful as the largest AV, or the largest bill

agency_num	year	pin	class	av_board	av_rank	tax_bill_total	bill_rank
42140000	2008	12033020240000	529	32319400	1	5642183.37	1
42140000	2008	12092160020000	522	16867726	2	2944695.87	2
42140000	2008	12031000190000	591	14503114	3	2531891.92	3
42140000	2008	12311000030000	593	7566157	13	2005302.66	4
42140000	2008	12031030100000	591	11442662	4	1997611.25	5
42140000	2008	12031021010000	529	9819922	5	1714320.23	6
42140000	2008	12101000510000	591	9062182	6	1582037.17	7
42140000	2008	12101001050000	529	9003991	7	1571878.47	8
42140000	2008	12312000190000	593	5869871	21	1555726.14	9
42140000	2008	12031000290000	591	8854138	8	1545717.72	10
42140000	2008	12101001208002	529	8518983	9	1487207.83	11
42140000	2008	12031030150000	529	8200175	10	1431551.65	12
42140000	2008	12101000460000	529	7999999	11	1396605.79	13
42140000	2008	12033090120000	591	7846157	12	1369748.7	14

ccao-jardine commented 3 days ago

Ooh, great question. Let's go with AV

ccao-data / data-architecture