Closed wrridgeway closed 5 months ago
with old as (
select
year,
class,
sum(case when bldg_is_mixed_use then 1 else 0 end) as bldg_is_mixed_use_old,
avg(case when cdu = 'GR' then 1 else 0 end) as cdu_old,
count(*) as count_old
from default.vw_pin_condo_char
group by year, class, bldg_is_mixed_use
),
new as (
select
year,
class,
sum(case when bldg_is_mixed_use then 1 else 0 end) as bldg_is_mixed_use_new,
avg(case when cdu = 'GR' then 1 else 0 end) as cdu_new,
count(*) as count_new
from "z_ci_315-fix-bldg-is-mixed-use-indicator-in-vw-pin-condo-chars_default".vw_pin_condo_char
group by year, class
)
select
old.year,
old.class,
count_old,
count_new,
cdu_old = cdu_new as cdu_parity,
bldg_is_mixed_use_old,
bldg_is_mixed_use_new
from old
left join new on old.year = new.year
and old.class = new.class
year | class | count_old | count_new | cdu_parity | bldg_is_mixed_use_old | bldg_is_mixed_use_new |
---|---|---|---|---|---|---|
2022 | 399 | 963 | 963 | TRUE | 0.00E+00 | 0 |
2004 | 299 | 319479 | 319479 | TRUE | 0 | 61 |
2001 | 299 | 264926 | 264926 | TRUE | 0 | 0 |
2008 | 299 | 427258 | 427258 | TRUE | 0 | 444 |
2024 | 299 | 457638 | 457638 | TRUE | 0 | 434 |
2009 | 399 | 2416 | 2416 | TRUE | 0 | 0 |
2018 | 399 | 7848 | 7848 | TRUE | 0 | 0 |
2010 | 299 | 452528 | 452528 | TRUE | 0 | 433 |
2000 | 299 | 248540 | 248540 | TRUE | 0 | 0 |
1999 | 399 | 2022 | 2022 | TRUE | 0 | 1 |
2005 | 399 | 1515 | 1515 | TRUE | 0 | 0 |
2023 | 399 | 2 | 2 | TRUE | 0 | 0 |
2009 | 299 | 443374 | 443374 | TRUE | 0 | 846 |
2020 | 399 | 6776 | 6776 | TRUE | 0 | 0 |
2017 | 299 | 453251 | 453251 | TRUE | 0 | 434 |
2007 | 299 | 400630 | 400630 | TRUE | 0.00E+00 | 442 |
2014 | 299 | 452633 | 452633 | TRUE | 0.00E+00 | 433 |
2017 | 399 | 6024 | 6024 | TRUE | 0 | 0 |
2003 | 299 | 299284 | 299284 | TRUE | 0.00E+00 | 0 |
2012 | 299 | 453751 | 453751 | TRUE | 0.00E+00 | 435 |
2011 | 399 | 2982 | 2982 | TRUE | 0.00E+00 | 0 |
2023 | 299 | 457638 | 457638 | TRUE | 0 | 434 |
2002 | 399 | 1657 | 1657 | TRUE | 0.00E+00 | 0 |
2022 | 299 | 458163 | 458163 | TRUE | 0.00E+00 | 657 |
2013 | 299 | 452824 | 452824 | TRUE | 0 | 433 |
2021 | 399 | 2034 | 2034 | TRUE | 0 | 0 |
2006 | 299 | 369019 | 369019 | TRUE | 0 | 77 |
2007 | 399 | 1878 | 1878 | TRUE | 0 | 0 |
2000 | 399 | 1762 | 1762 | TRUE | 0.00E+00 | 0 |
2013 | 399 | 4988 | 4988 | TRUE | 0 | 0 |
2018 | 299 | 451625 | 451625 | TRUE | 0.00E+00 | 434 |
2015 | 299 | 452626 | 452626 | TRUE | 0 | 433 |
2005 | 299 | 341481 | 341481 | TRUE | 0.00E+00 | 61 |
2014 | 399 | 5416 | 5416 | TRUE | 0.00E+00 | 0 |
2019 | 399 | 7076 | 7076 | TRUE | 0.00E+00 | 0 |
2011 | 299 | 454184 | 454184 | TRUE | 0 | 433 |
2006 | 399 | 1715 | 1715 | TRUE | 0 | 0 |
2004 | 399 | 1603 | 1603 | TRUE | 0 | 0 |
1999 | 299 | 233773 | 233773 | TRUE | 0 | 0 |
2008 | 399 | 1927 | 1927 | TRUE | 0 | 0 |
2024 | 399 | 2 | 2 | TRUE | 0.00E+00 | 0 |
2020 | 299 | 451673 | 451673 | TRUE | 0 | 434 |
2001 | 399 | 1904 | 1904 | TRUE | 0 | 0 |
2021 | 299 | 456946 | 456946 | TRUE | 0.00E+00 | 434 |
2019 | 299 | 451597 | 451597 | TRUE | 0 | 434 |
2016 | 299 | 452951 | 452951 | TRUE | 0 | 433 |
2003 | 399 | 1600 | 1600 | TRUE | 0.00E+00 | 0 |
2010 | 399 | 2411 | 2411 | TRUE | 0.00E+00 | 0 |
2016 | 399 | 5896 | 5896 | TRUE | 0 | 0 |
2015 | 399 | 5729 | 5729 | TRUE | 0 | 0 |
2012 | 399 | 4443 | 4443 | TRUE | 0 | 0 |
2002 | 299 | 281793 | 281793 | TRUE | 0.00E+00 | 0 |
The
bldg_is_mixed_use
indicator indefault.vw_pin_condo_char
isn't currently detecting any mixed use buildings. This is due to its design and a conditional in thechars
section of thedefault.vw_pin_condo_char
sql script:and
WHERE class IN ('299', '2-99', '399')
We need to allow non-299/399 class parcels into this part of the pull in order to be able to detect mixed-use buildings. We can filter them out later. I've included output below to show that these buildings are now being detected and that the new syntax has not altered parcel counts or other features such as CDU.