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