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