search

ccao-data / data-architecture

Codebase for CCAO data infrastructure construction and management
https://ccao-data.github.io/data-architecture/
5 stars 3 forks source link

Add market values to `default.vw_pin_value` #493

Closed dfsnow closed 3 weeks ago

dfsnow commented 3 weeks ago

This PR adds the iasWorld market value columns from the asmt tab to default.vw_pin_value. These columns are populated only for stages after the 2020 Board stage.

dfsnow commented 3 weeks ago

Identical row counts:

SELECT new.year, old.cnt_old, new.cnt_new
FROM (
    SELECT year, COUNT(*) as cnt_old
    FROM "default"."vw_pin_value"
    GROUP BY year
) old
LEFT JOIN (
    SELECT year, COUNT(*) as cnt_new
    FROM "z_ci_dfsnow_add_mv_to_pin_values_default"."vw_pin_value"
    GROUP BY year
) new
ON old.year = new.year
ORDER BY new.year DESC
year cnt_old cnt_new
2024 446066 446066
2023 1864162 1864162
2022 1865013 1865013
2021 1864602 1864602
2020 1864035 1864035
2019 1864102 1864102
2018 1864588 1864588
2017 1864621 1864621
2016 1863996 1863996
2015 1862756 1862756
2014 1862170 1862170
2013 1861929 1861929
2012 1862181 1862181
2011 1861008 1861008
2010 1859244 1859244
2009 1849650 1849650
2008 1832617 1832617
2007 1804217 1804217
2006 1771313 1771313
2005 1740963 1740963
2004 1716214 1716214
2003 1693139 1693139
2002 1673530 1673530
2001 1655168 1655168
2000 1637079 1637079
1999 1620798 1620798