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()