Dynamically populate (i.e. CREATE OR REPLACE) a table named derived.tax_year_assessment_bins. Imagine that the data in this table informs a distribution of the assessed values. The values should be divided into bins of some consistent widths either on a linear or logarithmic scale, and the number of properties with assessed values in each bin should be counted up. For example, it could look something like this:
The table should have the following columns:
tax_year -- The year for which the tax assessment value applies
lower_bound -- The minimum assessed value cutoff in the histogram bin
upper_bound -- The maximum assessed value cutoff in the histogram bin
property_count -- The number of properties that fall between that min and max value
Use the source.opa_assessments table to build this table.
Dynamically populate (i.e.
CREATE OR REPLACE
) a table namedderived.tax_year_assessment_bins
. Imagine that the data in this table informs a distribution of the assessed values. The values should be divided into bins of some consistent widths either on a linear or logarithmic scale, and the number of properties with assessed values in each bin should be counted up. For example, it could look something like this:The table should have the following columns:
tax_year
-- The year for which the tax assessment value applieslower_bound
-- The minimum assessed value cutoff in the histogram binupper_bound
-- The maximum assessed value cutoff in the histogram binproperty_count
-- The number of properties that fall between that min and max valueUse the
source.opa_assessments
table to build this table.Acceptance criteria:
CREATE TABLE
SQL to generate thederived.tax_year_assessment_bins
table. The actual SQL statement should be in its own .sql file (e.g.create_derived_tax_year_assessment_bins.sql
). See therun_sql
task from the course_info as an example (https://github.com/musa-5090-spring-2024/course-info/tree/main/week08/explore_phila_data/run_sql)