Links below to show SQL queries / data definitions

• Condata - this table is the raw data that we extracted which contains the construction permit data for New York City.
• Taxdata - this table is the raw data that we extracted for income tax returns across the state of New York. We can join on the zip codes in the NYC permit data to get just the entries for the boroughs.
• Wavgsalary (Materialized View) - We use this materialized view to pre-calculate the weighted average salary (based on income tax data) for each zip code. This gives us an approximation of the potential purchasing power of the residents of each zip.
• Wagranks (Materialized View) - We use this materialized view to pre-calculate the (percentile) rankings of each zip code based on weighted average salary. That is, zip codes with a higher weighted average salary are have a higher score, which gives us one data point to evaluate them.
• Countranks (Materialized View) - We use this materialized view to pre-calculate the (percentile) rankings of each zipe code based on count of construction permits pulled. Zip codes with more permits pulled show a potentially greater demand for construction services overall. This is the other data point we can use to evaluate each zipe code, along with the 'Wagranks' view above. The reason to have both, is that some zip codes may have a high weighted average salary, but few permits issued, indicating low demand. Others may have a high number of permits, but lower average salary, potentially indicating lower purchasing power, and thus, may represent less opportunity for total revenue captured.
• Targetzip (Materialized View) - This is the materialized view that is the final output for the data. It outputs the columns that we would potentially want to see in a report. By zip code, we can see the calculated values, such as weighted average salary, number of permits, the percentile ranks of salaries, the percentile ranks of number of permits, and, crucially, a combined score of those percentile ranks. This combined score gives us a more complete picture of how to prioritize and evaluate each zip code, by both their capability and propensity to seek out construction services.