[Warning] Review patterns/trends/distribution

[GregorSchroeder]

Describe the warning

@dlesdg noted in #274 the following anomalous year-over-year changes that require investigation. Please address each of the four following findings below in individual comments and refer to the original test plan in #274 for more details.

a. Census tract 83.76 (UTC mall) had a spike in retail jobs in 2021, following by a decrease the following year.

b Census tract 169.02 (Barona) likely had a reclassification shift from 722 (Food Services) to 721 (Accommodation).

c. Census tract 221.01 (Carlsbad) had a spike in 92 (Public Administration) in 2023 from 0 in prior years. This may be tied to changes in census tract 178.01.

d. Census tract 133.03 (Chula Vista) sees a spike in 62 (Health Care/Social Assistance) in 2023 from prior years.

[bryce-sandag]

a) This is seen and comes from the underlying LODES data set for retail jobs ([CNS07]) jobs.

The total for all jobs and the retail sector are:

[bryce-sandag]

c) This is seen and comes from the underlying LODES data set for retail jobs ([CNS20]) jobs.

The total for all jobs and the public administration sector in Census tract 221.01 are:

Do see change also in Census tract 178.01 in LODES data. This should not be geography issue and geography would not have changed but possible issue with siting by LODES.

[bryce-sandag]

d) This is seen and comes from the underlying LODES data set for retail jobs ([CNS16]) jobs.

The total for all jobs and the public administration sector in Census tract 133.03 are:

[bryce-sandag]

b) This is operating as it should.

In 2020 and 2021 the LODES jobs data for industry_code 72 is mainly based in block 060730169021010 and for 2022 and after the jobs data is mainly in block 060730169021047. To split to industry_code 721 and 722 then if there is EDD jobs data in that block for that year it will use that split between 721 and 722 and if there is no EDD jobs data in the block than it uses the region split between 721 and 722.

Block 060730169021010 does not have EDD data in it therefore will use the region split of 721 ~ 15% and 722 ~ 85% so majority of jobs assigned to industry_code 722. Block 060730169021047 does have EDD data in it and therefore uses the split which is 721 = 100% and 722 = 0%.

This difference ultimately comes from the LODES data and the change in what block the jobs are assigned to, but the underlying logic is working as it was written.

Will add more comments to get_naics72_split.sql to add more clarification about using region distribution when no EDD data exists and note about adding a where clause to last query will change the results returned because of the use of OVER(), which is what happened while trying to investigate this QA finding