SQL_QUERY_nyc_lead_water_uhf_yr.sql

select
  Year_Coll,
  count(distinct(Kit_ID)) as num_samples,
  Borough_broad,
  UHF_code as neighb_code,
  Neighb_Name,
  0.015 as epa_pb_lim,
  /*lead first draw calcs*/
  100 * sum(
    case
      when Lead_First_Draw_mg_L > 0.015 then 1
      else 0
    end
  ) / count(distinct(Kit_ID)) as pct_1d_pb_over_epa,
  sum(
    case
      when Lead_First_Draw_mg_L > 0.015 then 1
      else 0
    end
  ) as num_1d_pb_over_epa,
  MAX(Lead_First_Draw_mg_L) as max_1d_pb,
  MIN(Lead_First_Draw_mg_L) as min_1d_pb,
  AVG(Lead_First_Draw_mg_L) as avg_1d_pb,
  statistics_median(cast(Lead_First_Draw_mg_L as float)) as med_1d_pb,
  MAX(Lead_First_Draw_mg_L) - MIN(Lead_First_Draw_mg_L) as range_1d_pb,
  statistics_median(cast(Lead_First_Draw_mg_L as float)) - 0.015 as med_1d_pb_diff_epa,
  AVG(Lead_First_Draw_mg_L) - 0.015 as avg_1d_pb_diff_epa,
  /*lead 1-2 min flush calcs*/
  100 * sum(
    case
      when Lead_1_2_Min_Flush_mg_L > 0.015 then 1
      else 0
    end
  ) / count(distinct(Kit_ID)) as pct_2d_pb_over_epa,
  MAX(Lead_1_2_Min_Flush_mg_L) as max_2d_pb,
  MIN(Lead_1_2_Min_Flush_mg_L) as min_2d_pb,
  AVG(Lead_1_2_Min_Flush_mg_L) as avg_2d_pb,
  statistics_median(cast(Lead_1_2_Min_Flush_mg_L as float)) as med_2d_pb,
  MAX(Lead_1_2_Min_Flush_mg_L) - MIN(Lead_1_2_Min_Flush_mg_L) as range_2d_pb,
  statistics_median(cast(Lead_1_2_Min_Flush_mg_L as float)) - 0.015 as med_2d_pb_diff_epa,
  AVG(Lead_1_2_Min_Flush_mg_L) - 0.015 as avg_2d_pb_diff_epa
from
  SOURCE_GS_NYC_lead_levels
where
  UHF_code NOT IN ("#N/A", "999")
group by
  Neighb_Name,
  Year_Coll
having
  count(distinct(Kit_ID)) >= 10
order by
  Neighb_Name,
  Year_Coll,
  AVG(Lead_First_Draw_mg_L)