"state_code", "supervision_type", "metric_period_months", "district", "race_or_ethnicity", "gender", "age_bucket", ), description=SUPERVISION_SUCCESS_BY_PERIOD_BY_DEMOGRAPHICS_VIEW_DESCRIPTION, materialized_metrics_dataset=dataset_config. DATAFLOW_METRICS_MATERIALIZED_DATASET, reference_views_dataset=dataset_config.REFERENCE_VIEWS_DATASET, grouped_districts=state_specific_query_strings. state_supervision_specific_district_groupings( "supervising_district_external_id", "judicial_district_code"), metric_period_condition=bq_utils.metric_period_condition(month_offset=1), unnested_race_or_ethnicity_dimension=bq_utils.unnest_column( "race_or_ethnicity", "race_or_ethnicity"), gender_dimension=bq_utils.unnest_column("gender", "gender"), age_dimension=bq_utils.unnest_column("age_bucket", "age_bucket"), state_specific_race_or_ethnicity_groupings=state_specific_query_strings. state_specific_race_or_ethnicity_groupings( "prioritized_race_or_ethnicity"), state_specific_supervision_type_inclusion_filter=state_specific_query_strings .state_specific_supervision_type_inclusion_filter(), ) if __name__ == "__main__": with local_project_id_override(GCP_PROJECT_STAGING): SUPERVISION_SUCCESS_BY_PERIOD_BY_DEMOGRAPHICS_VIEW_BUILDER.build_and_print( )
metric_period_months FROM supervision FULL OUTER JOIN referrals USING (state_code, supervision_type, district, metric_period_months, gender) WHERE supervision_type in ('ALL', 'PAROLE', 'PROBATION') AND district IS NOT NULL AND state_code = 'US_ND' ORDER BY state_code, gender, district, supervision_type, metric_period_months """ FTR_REFERRALS_BY_GENDER_BY_PERIOD_VIEW_BUILDER = MetricBigQueryViewBuilder( dataset_id=dataset_config.DASHBOARD_VIEWS_DATASET, view_id=FTR_REFERRALS_BY_GENDER_BY_PERIOD_VIEW_NAME, view_query_template=FTR_REFERRALS_BY_GENDER_BY_PERIOD_QUERY_TEMPLATE, dimensions=( "state_code", "metric_period_months", "district", "supervision_type", "gender", ), description=FTR_REFERRALS_BY_GENDER_BY_PERIOD_DESCRIPTION, reference_views_dataset=dataset_config.REFERENCE_VIEWS_DATASET, metric_period_dimension=bq_utils.unnest_metric_period_months(), metric_period_condition=bq_utils.metric_period_condition(), ) if __name__ == "__main__": with local_project_id_override(GCP_PROJECT_STAGING): FTR_REFERRALS_BY_GENDER_BY_PERIOD_VIEW_BUILDER.build_and_print()
AND m.metric_period_months = 0 AND {prior_month_metric_period_dimension} -- Get population count for the last day of the month AND date_of_stay = DATE_SUB(DATE_ADD(DATE(year, month, 1), INTERVAL 1 MONTH), INTERVAL 1 DAY) AND job.metric_type = 'INCARCERATION_POPULATION' GROUP BY state_code, district, metric_period_months ) inc_pop USING (state_code, district, metric_period_months) WHERE district IS NOT NULL ORDER BY state_code, metric_period_months, district """ ADMISSIONS_VERSUS_RELEASES_BY_PERIOD_VIEW_BUILDER = SimpleBigQueryViewBuilder( dataset_id=dataset_config.DASHBOARD_VIEWS_DATASET, view_id=ADMISSIONS_VERSUS_RELEASES_BY_PERIOD_VIEW_NAME, view_query_template=ADMISSIONS_VERSUS_RELEASES_BY_PERIOD_QUERY_TEMPLATE, description=ADMISSIONS_VERSUS_RELEASES_BY_PERIOD_DESCRIPTION, metrics_dataset=dataset_config.DATAFLOW_METRICS_DATASET, reference_dataset=dataset_config.REFERENCE_TABLES_DATASET, district_dimension=bq_utils.unnest_district( district_column='county_of_residence'), metric_period_dimension=bq_utils.unnest_metric_period_months(), metric_period_condition=bq_utils.metric_period_condition(month_offset=1), prior_month_metric_period_dimension=bq_utils.metric_period_condition( month_offset=0), ) if __name__ == '__main__': with local_project_id_override(GAE_PROJECT_STAGING): ADMISSIONS_VERSUS_RELEASES_BY_MONTH_VIEW_BUILDER.build_and_print()