Closed wrridgeway closed 5 months ago
with old as (
select
year, count(*) as old
from default.vw_pin_condo_char
group by year
),
new as (
select
year, count(*) as new
from "z_ci_309-explore-collected-characteristics-filling-for-condos_default".vw_pin_condo_char
group by year
)
select
old.*,
new.new
from old
left join new on old.year = new.year
order by year desc
year | old | new |
---|---|---|
2024 | 457640 | 457640 |
2023 | 457640 | 457640 |
2022 | 459126 | 459126 |
2021 | 458980 | 458980 |
2020 | 458449 | 458449 |
2019 | 458673 | 458673 |
2018 | 459473 | 459473 |
2017 | 459275 | 459275 |
2016 | 458847 | 458847 |
2015 | 458355 | 458355 |
2014 | 458049 | 458049 |
2013 | 457812 | 457812 |
2012 | 458194 | 458194 |
2011 | 457166 | 457166 |
2010 | 454939 | 454939 |
2009 | 445790 | 445790 |
2008 | 429185 | 429185 |
2007 | 402508 | 402508 |
2006 | 370734 | 370734 |
2005 | 342996 | 342996 |
2004 | 321082 | 321082 |
2003 | 300884 | 300884 |
2002 | 283450 | 283450 |
2001 | 266830 | 266830 |
2000 | 250302 | 250302 |
1999 | 235795 | 235795 |
Sure. My main motivation here was that it's more likely than not that this data will make its way into iasworld before we have multiple years worth of collected data for the same PINs (if this is even something that happens) in the current format and will need a complete refactor regardless.
See https://github.com/ccao-data/data-architecture/issues/309.
This seems like a better solution for the present then back/forward filling these chars since we only have one observation per pin. Once this data gets moved into iasworld we can start filling. Until then, this is probably more robust.