indexscoretrees.sql

WITH
  all_and_stscaler AS (
  SELECT
    *,
    ML.STANDARD_SCALER(trees_per_1000squaremeters) OVER() AS trees_per_1000squaremeters_scale,
    ML.STANDARD_SCALER(avg_tree_diameter) OVER() AS avg_tree_diameter_scale
  FROM
    `cartodb-gcp-solutions-eng-team.kyle_data.nyc_blockgroups_withtreedatacalc` ),
  sum_to_create_score AS (
  SELECT
    *,
    ML.STANDARD_SCALER(trees_per_1000squaremeters_scale * .7 + avg_tree_diameter_scale * .3) OVER() AS sumcap
  FROM
    all_and_stscaler ),
  capping AS (
  SELECT
    CASE
      WHEN sumcap>=3 THEN 3
      WHEN sumcap<=-3 THEN -3
    ELSE
    sumcap
  END
    AS sumcap_capped,
    *,
  FROM
    sum_to_create_score )
SELECT
  (1+(sumcap_capped/ (
      SELECT
        MAX(sumcap_capped)
      FROM
        capping))) * 50 AS index_score,
  *
FROM
  capping
ORDER BY
  index_score DESC