def _get_query_prep_statement(reference_views_dataset: str) -> str: """Return the Common Table Expression used to gather the termination case data""" return """ -- Gather supervision period case termination data WITH case_terminations AS ( SELECT supervision_period.state_code, EXTRACT(YEAR FROM termination_date) AS year, EXTRACT(MONTH FROM termination_date) AS month, supervision_period.termination_reason, supervision_period.person_id, supervision_type, district, agent.agent_external_id AS officer_external_id FROM `{{project_id}}.state.state_supervision_period` supervision_period LEFT JOIN `{{project_id}}.{reference_views_dataset}.supervision_period_to_agent_association` agent USING (supervision_period_id), {district_dimension}, {supervision_type_dimension} WHERE termination_date IS NOT NULL ) """.format( reference_views_dataset=reference_views_dataset, district_dimension=bq_utils.unnest_district( district_column='agent.district_external_id'), supervision_type_dimension=bq_utils.unnest_supervision_type( supervision_type_column='supervision_period.supervision_type'), )
violation_type, reported_violations, officer_recommendation, violation_record, supervision_type, charge_category, district, officer, person_id, person_external_id, gender, age_bucket, -- TODO(3135): remove this aggregation once the dashboard supports LOW_MEDIUM CASE WHEN risk_level = 'LOW_MEDIUM' THEN 'LOW' ELSE risk_level END AS risk_level, race, ethnicity, (year = EXTRACT(YEAR FROM CURRENT_DATE('US/Pacific')) AND month = EXTRACT(MONTH FROM CURRENT_DATE('US/Pacific'))) AS current_month FROM revocations WHERE supervision_type IN ('ALL', 'DUAL', 'PAROLE', 'PROBATION') AND district IS NOT NULL """ REVOCATIONS_MATRIX_BY_PERSON_VIEW_BUILDER = SimpleBigQueryViewBuilder( dataset_id=dataset_config.REFERENCE_TABLES_DATASET, view_id=REVOCATIONS_MATRIX_BY_PERSON_VIEW_NAME, view_query_template=REVOCATIONS_MATRIX_BY_PERSON_QUERY_TEMPLATE, description=REVOCATIONS_MATRIX_BY_PERSON_DESCRIPTION, metrics_dataset=dataset_config.DATAFLOW_METRICS_DATASET, reference_dataset=dataset_config.REFERENCE_TABLES_DATASET, district_dimension=bq_utils.unnest_district(), supervision_dimension=bq_utils.unnest_supervision_type(), charge_category_dimension=bq_utils.unnest_charge_category(), ) if __name__ == '__main__': with local_project_id_override(GAE_PROJECT_STAGING): REVOCATIONS_MATRIX_BY_PERSON_VIEW_BUILDER.build_and_print()
WHERE methodology = 'PERSON' AND person_id IS NOT NULL AND m.metric_period_months = 1 AND {metric_period_condition} GROUP BY state_code, metric_period_months, district ) ret USING (state_code, metric_period_months, district) WHERE district IS NOT NULL ORDER BY state_code, metric_period_months, district """ REINCARCERATIONS_BY_PERIOD_VIEW_BUILDER = MetricBigQueryViewBuilder( dataset_id=dataset_config.DASHBOARD_VIEWS_DATASET, view_id=REINCARCERATIONS_BY_PERIOD_VIEW_NAME, view_query_template=REINCARCERATIONS_BY_PERIOD_QUERY_TEMPLATE, dimensions=['state_code', 'metric_period_months', 'district'], description=REINCARCERATIONS_BY_PERIOD_DESCRIPTION, metrics_dataset=dataset_config.DATAFLOW_METRICS_DATASET, reference_views_dataset=dataset_config.REFERENCE_VIEWS_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(), filter_to_most_recent_job_id_for_metric=bq_utils. filter_to_most_recent_job_id_for_metric( reference_dataset=dataset_config.REFERENCE_VIEWS_DATASET)) if __name__ == '__main__': with local_project_id_override(GCP_PROJECT_STAGING): REINCARCERATIONS_BY_PERIOD_VIEW_BUILDER.build_and_print()
GROUP BY state_code, district, race_or_ethnicity, gender, age_bucket ORDER BY state_code, district, race_or_ethnicity, gender, age_bucket """ SENTENCE_TYPE_BY_DISTRICT_BY_DEMOGRAPHICS_VIEW_BUILDER = MetricBigQueryViewBuilder( dataset_id=dataset_config.PUBLIC_DASHBOARD_VIEWS_DATASET, view_id=SENTENCE_TYPE_BY_DISTRICT_BY_DEMOGRAPHICS_VIEW_NAME, view_query_template= SENTENCE_TYPE_BY_DISTRICT_BY_DEMOGRAPHICS_VIEW_QUERY_TEMPLATE, dimensions=("state_code", "district", "race_or_ethnicity", "gender", "age_bucket"), description=SENTENCE_TYPE_BY_DISTRICT_BY_DEMOGRAPHICS_VIEW_DESCRIPTION, materialized_metrics_dataset=dataset_config. DATAFLOW_METRICS_MATERIALIZED_DATASET, state_specific_race_or_ethnicity_groupings=state_specific_query_strings. state_specific_race_or_ethnicity_groupings( "prioritized_race_or_ethnicity"), 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"), district_dimension=bq_utils.unnest_district( state_specific_query_strings. state_specific_judicial_district_groupings("judicial_district_code")), ) if __name__ == "__main__": with local_project_id_override(GCP_PROJECT_STAGING): SENTENCE_TYPE_BY_DISTRICT_BY_DEMOGRAPHICS_VIEW_BUILDER.build_and_print( )
USING (state_code, year, month, officer_external_id, district) LEFT JOIN ( SELECT * FROM avg_requests_by_district_state WHERE district != 'ALL' ) district_avg USING (state_code, year, month, district) LEFT JOIN ( SELECT * EXCEPT (district) FROM avg_requests_by_district_state WHERE district = 'ALL' ) state_avg USING (state_code, year, month) ORDER BY state_code, year, month, district, officer_external_id """ SUPERVISION_EARLY_DISCHARGE_REQUESTS_BY_OFFICER_BY_MONTH_VIEW_BUILDER = SimpleBigQueryViewBuilder( dataset_id=dataset_config.PO_REPORT_DATASET, view_id=SUPERVISION_EARLY_DISCHARGE_REQUESTS_BY_OFFICER_BY_MONTH_VIEW_NAME, view_query_template= SUPERVISION_EARLY_DISCHARGE_REQUESTS_BY_OFFICER_BY_MONTH_QUERY_TEMPLATE, description= SUPERVISION_EARLY_DISCHARGE_REQUESTS_BY_OFFICER_BY_MONTH_DESCRIPTION, reference_dataset=dataset_config.REFERENCE_TABLES_DATASET, state_dataset=dataset_config.STATE_BASE_DATASET, district_dimension=bq_utils.unnest_district(district_column='district'), ) if __name__ == '__main__': with local_project_id_override(GCP_PROJECT_STAGING): SUPERVISION_EARLY_DISCHARGE_REQUESTS_BY_OFFICER_BY_MONTH_VIEW_BUILDER.build_and_print( )
dimensions=( "state_code", "supervision_type", "district", "race_or_ethnicity", "gender", "age_bucket", ), description= SUPERVISION_POPULATION_BY_DISTRICT_BY_DEMOGRAPHICS_VIEW_DESCRIPTION, materialized_metrics_dataset=dataset_config. DATAFLOW_METRICS_MATERIALIZED_DATASET, 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"), district_dimension=bq_utils.unnest_district( state_specific_query_strings. state_supervision_specific_district_groupings( "supervising_district_external_id", "judicial_district_code")), supervision_type_dimension=bq_utils.unnest_supervision_type(), state_specific_race_or_ethnicity_groupings=state_specific_query_strings. state_specific_race_or_ethnicity_groupings( "prioritized_race_or_ethnicity"), ) if __name__ == "__main__": with local_project_id_override(GCP_PROJECT_STAGING): SUPERVISION_POPULATION_BY_DISTRICT_BY_DEMOGRAPHICS_VIEW_BUILDER.build_and_print( )
state_code, year, month, person_id, /* TODO(#7437): Remove specific US_PA handling */ IF(state_code = 'US_PA', SPLIT(SPLIT(officer_external_id, "|")[SAFE_OFFSET(2)], "#")[SAFE_OFFSET(1)] ,officer_external_id) AS officer_external_id, violation_type, response_date AS revocation_report_date FROM revocation_recommendations_ranking WHERE revocation_violation_type_rank = 1 """ REVOCATION_REPORTS_BY_PERSON_BY_MONTH_VIEW_BUILDER = SimpleBigQueryViewBuilder( dataset_id=dataset_config.PO_REPORT_DATASET, view_id=REVOCATION_REPORTS_BY_PERSON_BY_MONTH_VIEW_NAME, should_materialize=True, view_query_template=REVOCATION_REPORTS_BY_PERSON_BY_MONTH_QUERY_TEMPLATE, description=REVOCATION_REPORTS_BY_PERSON_BY_MONTH_DESCRIPTION, reference_views_dataset=dataset_config.REFERENCE_VIEWS_DATASET, state_dataset=dataset_config.STATE_BASE_DATASET, district_dimension=bq_utils.unnest_district(district_column="district"), po_report_dataset=dataset_config.PO_REPORT_DATASET, violation_reports_query=violation_reports_query( state_dataset=dataset_config.STATE_BASE_DATASET, reference_views_dataset=dataset_config.REFERENCE_VIEWS_DATASET, ), ) if __name__ == "__main__": with local_project_id_override(GCP_PROJECT_STAGING): REVOCATION_REPORTS_BY_PERSON_BY_MONTH_VIEW_BUILDER.build_and_print()
WHERE ((race_or_ethnicity != 'ALL' AND gender = 'ALL' AND age_bucket = 'ALL') -- Race breakdown OR (race_or_ethnicity = 'ALL' AND gender != 'ALL' AND age_bucket = 'ALL') -- Gender breakdown OR (race_or_ethnicity = 'ALL' AND gender = 'ALL' AND age_bucket != 'ALL') -- Age breakdown OR (race_or_ethnicity = 'ALL' AND gender = 'ALL' AND age_bucket = 'ALL')) -- State-wide count GROUP BY state_code, district, race_or_ethnicity, gender, age_bucket ORDER BY state_code, district, race_or_ethnicity, gender, age_bucket """ SENTENCE_TYPE_BY_DISTRICT_BY_DEMOGRAPHICS_VIEW_BUILDER = SimpleBigQueryViewBuilder( dataset_id=dataset_config.PUBLIC_DASHBOARD_VIEWS_DATASET, view_id=SENTENCE_TYPE_BY_DISTRICT_BY_DEMOGRAPHICS_VIEW_NAME, view_query_template= SENTENCE_TYPE_BY_DISTRICT_BY_DEMOGRAPHICS_VIEW_QUERY_TEMPLATE, description=SENTENCE_TYPE_BY_DISTRICT_BY_DEMOGRAPHICS_VIEW_DESCRIPTION, reference_dataset=dataset_config.REFERENCE_TABLES_DATASET, state_specific_race_or_ethnicity_groupings=bq_utils. state_specific_race_or_ethnicity_groupings(), unnested_race_or_ethnicity_dimension=bq_utils.unnest_column( 'prioritized_race_or_ethnicity', 'race_or_ethnicity'), gender_dimension=bq_utils.unnest_column('gender', 'gender'), age_dimension=bq_utils.unnest_column('age_bucket', 'age_bucket'), district_dimension=bq_utils.unnest_district( bq_utils.state_specific_judicial_district_groupings( 'judicial_district_code')), ) if __name__ == '__main__': with local_project_id_override(GCP_PROJECT_STAGING): SENTENCE_TYPE_BY_DISTRICT_BY_DEMOGRAPHICS_VIEW_BUILDER.build_and_print( )
OR (race_or_ethnicity = 'ALL' AND gender = 'ALL' AND age_bucket != 'ALL') -- Age breakdown OR (district != 'ALL' AND race_or_ethnicity = 'ALL' AND gender = 'ALL' AND age_bucket = 'ALL') -- District breakdown OR (district = 'ALL' AND race_or_ethnicity = 'ALL' AND gender = 'ALL' AND age_bucket = 'ALL')) -- State-wide count GROUP BY state_code, supervision_type, district, race_or_ethnicity, gender, age_bucket ORDER BY state_code, supervision_type, district, race_or_ethnicity, gender, age_bucket """ SUPERVISION_POPULATION_BY_DISTRICT_BY_DEMOGRAPHICS_VIEW_BUILDER = SimpleBigQueryViewBuilder( dataset_id=dataset_config.PUBLIC_DASHBOARD_VIEWS_DATASET, view_id=SUPERVISION_POPULATION_BY_DISTRICT_BY_DEMOGRAPHICS_VIEW_NAME, view_query_template= SUPERVISION_POPULATION_BY_DISTRICT_BY_DEMOGRAPHICS_VIEW_QUERY_TEMPLATE, description= SUPERVISION_POPULATION_BY_DISTRICT_BY_DEMOGRAPHICS_VIEW_DESCRIPTION, reference_dataset=dataset_config.REFERENCE_TABLES_DATASET, unnested_race_or_ethnicity_dimension=bq_utils.unnest_column( 'prioritized_race_or_ethnicity', 'race_or_ethnicity'), gender_dimension=bq_utils.unnest_column('gender', 'gender'), age_dimension=bq_utils.unnest_column('age_bucket', 'age_bucket'), district_dimension=bq_utils.unnest_district( bq_utils.supervision_specific_district_groupings( 'supervising_district_external_id', 'judicial_district_code')), supervision_dimension=bq_utils.unnest_supervision_type(), state_specific_race_or_ethnicity_groupings=bq_utils. state_specific_race_or_ethnicity_groupings()) if __name__ == '__main__': with local_project_id_override(GCP_PROJECT_STAGING): SUPERVISION_POPULATION_BY_DISTRICT_BY_DEMOGRAPHICS_VIEW_BUILDER.build_and_print( )