SELECT
state_code, metric_period_months,
COUNT(IF(source_violation_type = 'NEW_ADMISSION', person_id, NULL)) AS new_admissions,
COUNT(IF(source_violation_type = 'TECHNICAL', person_id, NULL)) AS technicals,
COUNT(IF(source_violation_type IN ('ABSCONDED', 'ESCAPED', 'FELONY', 'MISDEMEANOR', 'LAW'), person_id, NULL)) AS non_technicals,
COUNT(person_id) AS all_violation_types_count,
supervision_type,
district
FROM most_recent_admission
WHERE admission_rank = 1
GROUP BY state_code, metric_period_months, supervision_type, district
)
ORDER BY state_code, supervision_type, district, metric_period_months
"""
ADMISSIONS_BY_TYPE_BY_PERIOD_VIEW_BUILDER = MetricBigQueryViewBuilder(
dataset_id=dataset_config.DASHBOARD_VIEWS_DATASET,
view_id=ADMISSIONS_BY_TYPE_BY_PERIOD_VIEW_NAME,
view_query_template=ADMISSIONS_BY_TYPE_BY_PERIOD_QUERY_TEMPLATE,
dimensions=("state_code", "metric_period_months", "supervision_type",
"district"),
description=ADMISSIONS_BY_TYPE_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):
ADMISSIONS_BY_TYPE_BY_PERIOD_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()
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()
district,
metric_period_months,
race_or_ethnicity
FROM `{project_id}.{reference_views_dataset}.event_based_revocations`,
{metric_period_dimension},
{race_ethnicity_dimension}
WHERE {metric_period_condition}
GROUP BY state_code, supervision_type, district, metric_period_months, race_or_ethnicity
) rev
USING (state_code, supervision_type, district, metric_period_months, race_or_ethnicity)
WHERE supervision_type in ('ALL', 'PAROLE', 'PROBATION')
AND race_or_ethnicity != 'EXTERNAL_UNKNOWN'
ORDER BY state_code, race_or_ethnicity, district, supervision_type, metric_period_months
"""
REVOCATIONS_BY_RACE_AND_ETHNICITY_BY_PERIOD_VIEW_BUILDER = MetricBigQueryViewBuilder(
dataset_id=dataset_config.DASHBOARD_VIEWS_DATASET,
view_id=REVOCATIONS_BY_RACE_AND_ETHNICITY_BY_PERIOD_VIEW_NAME,
view_query_template=REVOCATIONS_BY_RACE_AND_ETHNICITY_BY_PERIOD_QUERY_TEMPLATE,
dimensions=['state_code', 'metric_period_months', 'supervision_type', 'district', 'race_or_ethnicity'],
description=REVOCATIONS_BY_RACE_AND_ETHNICITY_BY_PERIOD_DESCRIPTION,
reference_views_dataset=dataset_config.REFERENCE_VIEWS_DATASET,
metric_period_dimension=bq_utils.unnest_metric_period_months(),
race_ethnicity_dimension=bq_utils.unnest_race_and_ethnicity(),
metric_period_condition=bq_utils.metric_period_condition(),
)
if __name__ == '__main__':
with local_project_id_override(GCP_PROJECT_STAGING):
REVOCATIONS_BY_RACE_AND_ETHNICITY_BY_PERIOD_VIEW_BUILDER.build_and_print()
AND year = EXTRACT(YEAR FROM CURRENT_DATE('US/Pacific'))
AND month = EXTRACT(MONTH FROM CURRENT_DATE('US/Pacific'))
ORDER BY state_code, metric_period_months, violation_record
"""
REVOCATIONS_MATRIX_FILTERED_CASELOAD_VIEW_BUILDER = MetricBigQueryViewBuilder(
dataset_id=dataset_config.DASHBOARD_VIEWS_DATASET,
view_id=REVOCATIONS_MATRIX_FILTERED_CASELOAD_VIEW_NAME,
view_query_template=REVOCATIONS_MATRIX_FILTERED_CASELOAD_QUERY_TEMPLATE,
dimensions=[
'state_code', 'metric_period_months', 'district', 'supervision_type',
'supervision_level', 'charge_category', 'risk_level', 'violation_type',
'reported_violations'
],
description=REVOCATIONS_MATRIX_FILTERED_CASELOAD_DESCRIPTION,
metrics_dataset=dataset_config.DATAFLOW_METRICS_DATASET,
reference_views_dataset=dataset_config.REFERENCE_VIEWS_DATASET,
most_severe_violation_type_subtype_grouping=state_specific_query_strings.
state_specific_most_severe_violation_type_subtype_grouping(),
state_specific_officer_recommendation=state_specific_query_strings.
state_specific_officer_recommendation(),
state_specific_supervision_level=state_specific_query_strings.
state_specific_supervision_level(),
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):
REVOCATIONS_MATRIX_FILTERED_CASELOAD_VIEW_BUILDER.build_and_print()
{unnested_race_or_ethnicity_dimension},
{region_dimension},
{supervision_type_dimension}
GROUP BY state_code, supervision_type, race_or_ethnicity, region_id
ORDER BY state_code, supervision_type, race_or_ethnicity, region_id
"""
ACTIVE_PROGRAM_PARTICIPATION_BY_REGION_VIEW_BUILDER = MetricBigQueryViewBuilder(
dataset_id=dataset_config.PUBLIC_DASHBOARD_VIEWS_DATASET,
view_id=ACTIVE_PROGRAM_PARTICIPATION_BY_REGION_VIEW_NAME,
view_query_template=
ACTIVE_PROGRAM_PARTICIPATION_BY_REGION_VIEW_QUERY_TEMPLATE,
dimensions=("state_code", "supervision_type", "race_or_ethnicity",
"region_id"),
description=ACTIVE_PROGRAM_PARTICIPATION_BY_REGION_VIEW_DESCRIPTION,
static_reference_dataset=dataset_config.STATIC_REFERENCE_TABLES_DATASET,
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"),
region_dimension=bq_utils.unnest_column("region_id", "region_id"),
supervision_type_dimension=bq_utils.unnest_supervision_type(),
)
if __name__ == "__main__":
with local_project_id_override(GCP_PROJECT_STAGING):
ACTIVE_PROGRAM_PARTICIPATION_BY_REGION_VIEW_BUILDER.build_and_print()
supervision_type, district,
-- Only use most recent revocation per person/supervision_type/metric_period_months
ROW_NUMBER() OVER (PARTITION BY state_code, person_id, supervision_type, metric_period_months, district
ORDER BY admission_date DESC) AS revocation_rank
FROM `{project_id}.{reference_views_dataset}.event_based_commitments_from_supervision_materialized`,
{metric_period_dimension}
WHERE {metric_period_condition}
)
WHERE revocation_rank = 1
GROUP BY state_code, supervision_type, district, metric_period_months
) rev
USING (state_code, supervision_type, district, metric_period_months)
WHERE supervision_type in ('ALL', 'PAROLE', 'PROBATION')
ORDER BY state_code, district, supervision_type, metric_period_months
"""
REVOCATIONS_BY_SITE_ID_BY_PERIOD_VIEW_BUILDER = MetricBigQueryViewBuilder(
dataset_id=dataset_config.DASHBOARD_VIEWS_DATASET,
view_id=REVOCATIONS_BY_SITE_ID_BY_PERIOD_VIEW_NAME,
view_query_template=REVOCATIONS_BY_SITE_ID_BY_PERIOD_QUERY_TEMPLATE,
dimensions=("state_code", "metric_period_months", "supervision_type", "district"),
description=REVOCATIONS_BY_SITE_ID_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):
REVOCATIONS_BY_SITE_ID_BY_PERIOD_VIEW_BUILDER.build_and_print()
GROUP BY state_code, year, month, district
)
SELECT
state_code, year, month, district,
IFNULL(returns, 0) as returns,
IFNULL(total_admissions, 0) as total_admissions
FROM admissions
LEFT JOIN person_based_reincarcerations
USING (state_code, year, month, district)
WHERE district IS NOT NULL
ORDER BY state_code, year, month, district
"""
REINCARCERATIONS_BY_MONTH_VIEW_BUILDER = MetricBigQueryViewBuilder(
dataset_id=dataset_config.DASHBOARD_VIEWS_DATASET,
view_id=REINCARCERATIONS_BY_MONTH_VIEW_NAME,
view_query_template=REINCARCERATIONS_BY_MONTH_QUERY_TEMPLATE,
dimensions=("state_code", "year", "month", "district"),
description=REINCARCERATIONS_BY_MONTH_DESCRIPTION,
materialized_metrics_dataset=dataset_config.
DATAFLOW_METRICS_MATERIALIZED_DATASET,
reference_views_dataset=dataset_config.REFERENCE_VIEWS_DATASET,
district_dimension=bq_utils.unnest_district(
district_column="county_of_residence"),
)
if __name__ == "__main__":
with local_project_id_override(GCP_PROJECT_STAGING):
REINCARCERATIONS_BY_MONTH_VIEW_BUILDER.build_and_print()
"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(
)
FTR_REFERRALS_BY_PARTICIPATION_STATUS_QUERY_TEMPLATE = \
"""
/*{description}*/
SELECT
state_code, year, month,
supervision_type, district,
COUNT(DISTINCT IF(participation_status = 'IN_PROGRESS', person_id, NULL)) AS in_progress,
COUNT(DISTINCT IF(participation_status = 'DISCHARGED', person_id, NULL)) AS discharged,
COUNT(DISTINCT IF(participation_status = 'DENIED', person_id, NULL)) AS denied,
COUNT(DISTINCT IF(participation_status = 'PENDING', person_id, NULL)) AS pending
FROM `{project_id}.{reference_views_dataset}.event_based_program_referrals`
WHERE supervision_type in ('ALL', 'PAROLE', 'PROBATION')
AND state_code = 'US_ND'
GROUP BY state_code, year, month, district, supervision_type
ORDER BY state_code, year, month, district, supervision_type
"""
FTR_REFERRALS_BY_PARTICIPATION_STATUS_VIEW_BUILDER = MetricBigQueryViewBuilder(
dataset_id=dataset_config.DASHBOARD_VIEWS_DATASET,
view_id=FTR_REFERRALS_BY_PARTICIPATION_STATUS_VIEW_NAME,
view_query_template=FTR_REFERRALS_BY_PARTICIPATION_STATUS_QUERY_TEMPLATE,
dimensions=['state_code', 'year', 'month', 'district', 'supervision_type'],
description=FTR_REFERRALS_BY_PARTICIPATION_STATUS_DESCRIPTION,
reference_views_dataset=dataset_config.REFERENCE_VIEWS_DATASET,
)
if __name__ == '__main__':
with local_project_id_override(GCP_PROJECT_STAGING):
FTR_REFERRALS_BY_PARTICIPATION_STATUS_VIEW_BUILDER.build_and_print()
AND person_id IS NOT NULL
AND month IS NOT NULL
AND year >= EXTRACT(YEAR FROM DATE_SUB(CURRENT_DATE(), INTERVAL 3 YEAR))
)
WHERE supervision_type IN ('ALL', 'PAROLE', 'PROBATION')
AND supervision_rank = 1
GROUP BY state_code, termination_year, termination_month, supervision_type, district
ORDER BY state_code, termination_year, termination_month, district, supervision_type
"""
AVERAGE_CHANGE_LSIR_SCORE_MONTH_VIEW_BUILDER = MetricBigQueryViewBuilder(
dataset_id=dataset_config.DASHBOARD_VIEWS_DATASET,
view_id=AVERAGE_CHANGE_LSIR_SCORE_MONTH_VIEW_NAME,
view_query_template=AVERAGE_CHANGE_LSIR_SCORE_MONTH_QUERY_TEMPLATE,
dimensions=[
'state_code', 'termination_year', 'termination_month',
'supervision_type', 'district'
],
description=AVERAGE_CHANGE_LSIR_SCORE_MONTH_DESCRIPTION,
metrics_dataset=dataset_config.DATAFLOW_METRICS_DATASET,
reference_views_dataset=dataset_config.REFERENCE_VIEWS_DATASET,
district_dimension=bq_utils.unnest_district(),
supervision_type_dimension=bq_utils.unnest_supervision_type(),
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):
AVERAGE_CHANGE_LSIR_SCORE_MONTH_VIEW_BUILDER.build_and_print()
ON c.state = f.state AND c.canonical_facility_name = f.facility_name
)
SELECT
facility_id,
date,
pop_tested,
pop_tested_negative,
pop_tested_positive,
pop_deaths,
staff_tested,
staff_tested_negative,
staff_tested_positive,
staff_deaths,
FROM cases
ORDER BY date, facility_id
"""
FACILITY_CASE_DATA_VIEW_BUILDER = MetricBigQueryViewBuilder(
dataset_id=dataset_config.COVID_DASHBOARD_DATASET,
view_id=FACILITY_CASE_DATA_VIEW_NAME,
view_query_template=FACILITY_CASE_DATA_VIEW_QUERY_TEMPLATE,
description=FACILITY_CASE_DATA_VIEW_DESCRIPTION,
dimensions=("facility_id", "date"),
covid_dashboard_reference_dataset=dataset_config.COVID_DASHBOARD_REFERENCE_DATASET,
)
if __name__ == "__main__":
with local_project_id_override(GCP_PROJECT_STAGING):
FACILITY_CASE_DATA_VIEW_BUILDER.build_and_print()
SELECT
state_code,
race_or_ethnicity,
population_count,
total_state_population_count
FROM
state_specific_group_sums
LEFT JOIN
total_state_population
USING (state_code)
"""
STATE_RACE_ETHNICITY_POPULATION_VIEW_BUILDER = MetricBigQueryViewBuilder(
dataset_id=dataset_config.DASHBOARD_VIEWS_DATASET,
view_id=STATE_RACE_ETHNICITY_POPULATION_VIEW_NAME,
view_query_template=STATE_RACE_ETHNICITY_POPULATION_VIEW_QUERY_TEMPLATE,
dimensions=("state_code", "race_or_ethnicity"),
description=STATE_RACE_ETHNICITY_POPULATION_VIEW_DESCRIPTION,
static_reference_dataset=dataset_config.STATIC_REFERENCE_TABLES_DATASET,
state_specific_race_or_ethnicity_groupings=state_specific_query_strings.
state_specific_race_or_ethnicity_groupings(
supported_race_overrides={
StateCode.US_PA: US_PA_SUPPORTED_RACE_VALUES
}),
)
if __name__ == "__main__":
with local_project_id_override(GCP_PROJECT_STAGING):
STATE_RACE_ETHNICITY_POPULATION_VIEW_BUILDER.build_and_print()
END as entity_name,
parent_entity_id,
timely_discharge,
timely_risk_assessment,
timely_contact,
timely_downgrade,
overall,
overall_30d,
overall_90d
FROM vitals_metrics
LEFT JOIN `{{project_id}}.{{reference_views_dataset}}.agent_external_id_to_full_name` agent
ON vitals_metrics.state_code = agent.state_code
AND vitals_metrics.supervising_officer_external_id = agent.agent_external_id
"""
VITALS_SUMMARIES_VIEW_BUILDER = MetricBigQueryViewBuilder(
dataset_id=dataset_config.DASHBOARD_VIEWS_DATASET,
view_id=VITALS_SUMMARIES_VIEW_NAME,
view_query_template=VITALS_SUMMARIES_QUERY_TEMPLATE,
dimensions=("entity_id", "entity_name", "parent_entity_id"),
description=VITALS_SUMMARIES_DESCRIPTION,
vitals_report_dataset=dataset_config.VITALS_REPORT_DATASET,
materialized_metrics_dataset=dataset_config.
DATAFLOW_METRICS_MATERIALIZED_DATASET,
reference_views_dataset=dataset_config.REFERENCE_VIEWS_DATASET,
)
if __name__ == "__main__":
with local_project_id_override(GCP_PROJECT_STAGING):
VITALS_SUMMARIES_VIEW_BUILDER.build_and_print()
COUNT(DISTINCT(person_id)) as total_population
FROM
`{project_id}.{reference_views_dataset}.most_recent_daily_incarceration_population_materialized`,
{unnested_race_or_ethnicity_dimension},
{gender_dimension},
{age_dimension}
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, date_of_stay, race_or_ethnicity, gender, age_bucket
ORDER BY state_code, date_of_stay, race_or_ethnicity, gender, age_bucket
"""
INCARCERATION_POPULATION_BY_ADMISSION_REASON_VIEW_BUILDER = MetricBigQueryViewBuilder(
dataset_id=dataset_config.PUBLIC_DASHBOARD_VIEWS_DATASET,
view_id=INCARCERATION_POPULATION_BY_ADMISSION_REASON_VIEW_NAME,
view_query_template=INCARCERATION_POPULATION_BY_ADMISSION_REASON_VIEW_QUERY_TEMPLATE,
dimensions=['state_code', 'date_of_stay', 'race_or_ethnicity', 'gender', 'age_bucket'],
description=INCARCERATION_POPULATION_BY_ADMISSION_REASON_VIEW_DESCRIPTION,
reference_views_dataset=dataset_config.REFERENCE_VIEWS_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'),
state_specific_race_or_ethnicity_groupings=state_specific_query_strings.state_specific_race_or_ethnicity_groupings()
)
if __name__ == '__main__':
with local_project_id_override(GCP_PROJECT_STAGING):
INCARCERATION_POPULATION_BY_ADMISSION_REASON_VIEW_BUILDER.build_and_print()
SELECT
state_code, year, month,
CASE WHEN termination_reason = 'ABSCONSION' THEN person_id ELSE NULL END AS absconsion,
CASE WHEN termination_reason = 'DEATH' THEN person_id ELSE NULL END AS death,
CASE WHEN termination_reason = 'DISCHARGE' THEN person_id ELSE NULL END AS discharge,
CASE WHEN termination_reason = 'EXPIRATION' THEN person_id ELSE NULL END AS expiration,
CASE WHEN termination_reason = 'REVOCATION' THEN person_id ELSE NULL END AS revocation,
CASE WHEN termination_reason = 'SUSPENSION' THEN person_id ELSE NULL END AS suspension,
CASE WHEN termination_reason = 'EXTERNAL_UNKNOWN' THEN person_id ELSE NULL END AS other,
supervision_type,
district
FROM case_terminations
)
WHERE supervision_type IN ('ALL', 'PROBATION', 'PAROLE')
AND year >= EXTRACT(YEAR FROM DATE_SUB(CURRENT_DATE('US/Pacific'), INTERVAL 3 YEAR))
GROUP BY state_code, year, month, supervision_type, district
ORDER BY state_code, year, month, supervision_type, district
"""
CASE_TERMINATIONS_BY_TYPE_BY_MONTH_VIEW_BUILDER = MetricBigQueryViewBuilder(
dataset_id=dataset_config.DASHBOARD_VIEWS_DATASET,
view_id=CASE_TERMINATIONS_BY_TYPE_BY_MONTH_VIEW_NAME,
view_query_template=CASE_TERMINATIONS_BY_TYPE_BY_MONTH_QUERY_TEMPLATE,
dimensions=['state_code', 'year', 'month', 'supervision_type', 'district'],
description=CASE_TERMINATIONS_BY_TYPE_BY_MONTH_DESCRIPTION,
)
if __name__ == '__main__':
with local_project_id_override(GCP_PROJECT_STAGING):
CASE_TERMINATIONS_BY_TYPE_BY_MONTH_VIEW_BUILDER.build_and_print()
LEFT JOIN
revocation_counts
USING (state_code, violation_type, reported_violations, gender, risk_level, supervision_type, supervision_level, charge_category,
district, metric_period_months)
LEFT JOIN
termination_counts
USING (state_code, violation_type, reported_violations, gender, risk_level, supervision_type, supervision_level, charge_category,
district, metric_period_months)
ORDER BY state_code, metric_period_months, district, supervision_type, supervision_level, gender, risk_level, violation_type,
reported_violations, charge_category
"""
REVOCATIONS_MATRIX_DISTRIBUTION_BY_GENDER_VIEW_BUILDER = MetricBigQueryViewBuilder(
dataset_id=dataset_config.DASHBOARD_VIEWS_DATASET,
view_id=REVOCATIONS_MATRIX_DISTRIBUTION_BY_GENDER_VIEW_NAME,
view_query_template=
REVOCATIONS_MATRIX_DISTRIBUTION_BY_GENDER_QUERY_TEMPLATE,
dimensions=[
'state_code', 'metric_period_months', 'district', 'supervision_type',
'supervision_level', 'violation_type', 'reported_violations',
'charge_category', 'gender', 'risk_level'
],
description=REVOCATIONS_MATRIX_DISTRIBUTION_BY_GENDER_DESCRIPTION,
reference_views_dataset=dataset_config.REFERENCE_VIEWS_DATASET,
)
if __name__ == '__main__':
with local_project_id_override(GCP_PROJECT_STAGING):
REVOCATIONS_MATRIX_DISTRIBUTION_BY_GENDER_VIEW_BUILDER.build_and_print(
)
GROUP BY state_code, supervision_type, district, metric_period_months, race_or_ethnicity
) ref
USING (state_code, supervision_type, district, metric_period_months, race_or_ethnicity)
WHERE supervision_type in ('ALL', 'PAROLE', 'PROBATION')
AND district IS NOT NULL
AND race_or_ethnicity != 'EXTERNAL_UNKNOWN'
AND state_code = 'US_ND'
ORDER BY state_code, race_or_ethnicity, district, supervision_type, metric_period_months
"""
FTR_REFERRALS_BY_RACE_AND_ETHNICITY_BY_PERIOD_VIEW_BUILDER = MetricBigQueryViewBuilder(
dataset_id=dataset_config.DASHBOARD_VIEWS_DATASET,
view_id=FTR_REFERRALS_BY_RACE_AND_ETHNICITY_BY_PERIOD_VIEW_NAME,
view_query_template=
FTR_REFERRALS_BY_RACE_AND_ETHNICITY_BY_PERIOD_QUERY_TEMPLATE,
dimensions=[
'state_code', 'metric_period_months', 'district', 'supervision_type',
'race_or_ethnicity'
],
description=FTR_REFERRALS_BY_RACE_AND_ETHNICITY_BY_PERIOD_DESCRIPTION,
reference_views_dataset=dataset_config.REFERENCE_VIEWS_DATASET,
metric_period_dimension=bq_utils.unnest_metric_period_months(),
race_or_ethnicity_dimension=bq_utils.unnest_race_and_ethnicity(),
metric_period_condition=bq_utils.metric_period_condition(),
)
if __name__ == '__main__':
with local_project_id_override(GCP_PROJECT_STAGING):
FTR_REFERRALS_BY_RACE_AND_ETHNICITY_BY_PERIOD_VIEW_BUILDER.build_and_print(
)
state_code, year, month,
SUM(IF(supervision_type = 'PROBATION', revocation_count, 0)) AS probation_count,
SUM(IF(supervision_type = 'PAROLE', revocation_count, 0)) AS parole_count,
district
FROM (
SELECT
state_code, year, month,
COUNT(DISTINCT person_id) AS revocation_count,
supervision_type,
district
FROM `{project_id}.{reference_views_dataset}.event_based_revocations`
WHERE supervision_type in ('PAROLE', 'PROBATION')
GROUP BY state_code, year, month, supervision_type, district
) rev
GROUP BY state_code, year, month, district
ORDER BY state_code, year, month, district
"""
REVOCATIONS_BY_SUPERVISION_TYPE_BY_MONTH_VIEW_BUILDER = MetricBigQueryViewBuilder(
dataset_id=dataset_config.DASHBOARD_VIEWS_DATASET,
view_id=REVOCATIONS_BY_SUPERVISION_TYPE_BY_MONTH_VIEW_NAME,
view_query_template=REVOCATIONS_BY_SUPERVISION_TYPE_BY_MONTH_QUERY_TEMPLATE,
dimensions=['state_code', 'year', 'month', 'district'],
description=REVOCATIONS_BY_SUPERVISION_TYPE_BY_MONTH_DESCRIPTION,
reference_views_dataset=dataset_config.REFERENCE_VIEWS_DATASET,
)
if __name__ == '__main__':
with local_project_id_override(GCP_PROJECT_STAGING):
REVOCATIONS_BY_SUPERVISION_TYPE_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(
)
WHERE {metric_period_condition}
)
WHERE revocation_rank = 1
GROUP BY state_code, metric_period_months, supervision_type, district, officer_external_id
) rev
USING (state_code, supervision_type, district, officer_external_id, metric_period_months)
WHERE supervision_type in ('ALL', 'PAROLE', 'PROBATION')
ORDER BY state_code, officer_external_id, district, supervision_type, metric_period_months
"""
REVOCATIONS_BY_OFFICER_BY_PERIOD_VIEW_BUILDER = MetricBigQueryViewBuilder(
dataset_id=dataset_config.DASHBOARD_VIEWS_DATASET,
view_id=REVOCATIONS_BY_OFFICER_BY_PERIOD_VIEW_NAME,
view_query_template=REVOCATIONS_BY_OFFICER_BY_PERIOD_QUERY_TEMPLATE,
dimensions=(
"state_code",
"metric_period_months",
"supervision_type",
"district",
"officer_external_id",
),
description=REVOCATIONS_BY_OFFICER_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):
REVOCATIONS_BY_OFFICER_BY_PERIOD_VIEW_BUILDER.build_and_print()
dataset_id=dataset_config.PUBLIC_DASHBOARD_VIEWS_DATASET,
view_id=
SUPERVISION_POPULATION_BY_PRIORITIZED_RACE_AND_ETHNICITY_BY_PERIOD_VIEW_NAME,
view_query_template=
SUPERVISION_POPULATION_BY_PRIORITIZED_RACE_AND_ETHNICITY_BY_PERIOD_VIEW_QUERY_TEMPLATE,
dimensions=(
"state_code",
"supervision_type",
"metric_period_months",
"race_or_ethnicity",
),
description=
SUPERVISION_POPULATION_BY_PRIORITIZED_RACE_AND_ETHNICITY_BY_PERIOD_VIEW_DESCRIPTION,
reference_views_dataset=dataset_config.REFERENCE_VIEWS_DATASET,
static_reference_dataset=dataset_config.STATIC_REFERENCE_TABLES_DATASET,
metric_period_condition=bq_utils.metric_period_condition(),
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(),
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_POPULATION_BY_PRIORITIZED_RACE_AND_ETHNICITY_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(
)
supervision_type,
district
FROM (
SELECT
state_code, year, month,
COUNT(IF(most_severe_violation_type = 'NEW_ADMISSION', person_id, NULL)) AS new_admissions,
COUNT(IF(most_severe_violation_type = 'TECHNICAL', person_id, NULL)) AS technicals,
COUNT(IF(most_severe_violation_type IN ('ABSCONDED', 'ESCAPED', 'FELONY', 'MISDEMEANOR', 'LAW'), person_id, NULL)) AS non_technicals,
COUNT(person_id) AS all_violation_types_count,
supervision_type,
district
FROM most_recent_admission
WHERE admission_rank = 1
GROUP BY state_code, year, month, supervision_type, district
)
ORDER BY state_code, year, month, district, supervision_type
"""
ADMISSIONS_BY_TYPE_BY_MONTH_VIEW_BUILDER = MetricBigQueryViewBuilder(
dataset_id=dataset_config.DASHBOARD_VIEWS_DATASET,
view_id=ADMISSIONS_BY_TYPE_BY_MONTH_VIEW_NAME,
view_query_template=ADMISSIONS_BY_TYPE_BY_MONTH_QUERY_TEMPLATE,
dimensions=("state_code", "year", "month", "supervision_type", "district"),
description=ADMISSIONS_BY_TYPE_BY_MONTH_DESCRIPTION,
reference_views_dataset=dataset_config.REFERENCE_VIEWS_DATASET,
)
if __name__ == "__main__":
with local_project_id_override(GCP_PROJECT_STAGING):
ADMISSIONS_BY_TYPE_BY_MONTH_VIEW_BUILDER.build_and_print()
SELECT
*,
IEEE_DIVIDE(recidivated_releases, releases) as recidivism_rate
FROM
recidivism_numbers
ORDER BY state_code, release_cohort, followup_years, gender, age_bucket, race_or_ethnicity
"""
RECIDIVISM_RATES_BY_COHORT_BY_YEAR_VIEW_BUILDER = MetricBigQueryViewBuilder(
dataset_id=dataset_config.PUBLIC_DASHBOARD_VIEWS_DATASET,
view_id=RECIDIVISM_RATES_BY_COHORT_BY_YEAR_VIEW_NAME,
view_query_template=RECIDIVISM_RATES_BY_COHORT_BY_YEAR_VIEW_QUERY_TEMPLATE,
dimensions=[
'state_code', 'release_cohort', 'followup_years', 'gender',
'age_bucket', 'race_or_ethnicity'
],
description=RECIDIVISM_RATES_BY_COHORT_BY_YEAR_VIEW_DESCRIPTION,
metrics_dataset=dataset_config.DATAFLOW_METRICS_DATASET,
reference_views_dataset=dataset_config.REFERENCE_VIEWS_DATASET,
state_specific_race_or_ethnicity_groupings=state_specific_query_strings.
state_specific_race_or_ethnicity_groupings(),
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'),
)
if __name__ == '__main__':
with local_project_id_override(GCP_PROJECT_STAGING):
RECIDIVISM_RATES_BY_COHORT_BY_YEAR_VIEW_BUILDER.build_and_print()
{metric_period_dimension}
WHERE methodology = 'EVENT'
AND person_id IS NOT NULL
AND DATE(year, month, 1) >= DATE_SUB(DATE_TRUNC(CURRENT_DATE('US/Pacific'), MONTH),
INTERVAL metric_period_months - 1 MONTH)
GROUP BY state_code, metric_period_months, supervision_type, district, person_id
)
WHERE supervision_type in ('ALL', 'PAROLE', 'PROBATION')
GROUP BY state_code, metric_period_months, supervision_type, district
ORDER BY state_code, metric_period_months, district, supervision_type
"""
SUPERVISION_TERMINATION_BY_TYPE_BY_PERIOD_VIEW_BUILDER = MetricBigQueryViewBuilder(
dataset_id=dataset_config.DASHBOARD_VIEWS_DATASET,
view_id=SUPERVISION_TERMINATION_BY_TYPE_BY_PERIOD_VIEW_NAME,
view_query_template=SUPERVISION_TERMINATION_BY_TYPE_BY_PERIOD_QUERY_TEMPLATE,
dimensions=['state_code', 'metric_period_months', 'supervision_type', 'district'],
description=SUPERVISION_TERMINATION_BY_TYPE_BY_PERIOD_DESCRIPTION,
reference_views_dataset=dataset_config.REFERENCE_VIEWS_DATASET,
metrics_dataset=dataset_config.DATAFLOW_METRICS_DATASET,
district_dimension=bq_utils.unnest_district(),
supervision_type_dimension=bq_utils.unnest_supervision_type(),
metric_period_dimension=bq_utils.unnest_metric_period_months(),
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):
SUPERVISION_TERMINATION_BY_TYPE_BY_PERIOD_VIEW_BUILDER.build_and_print()
view_query_template=
INCARCERATION_POPULATION_BY_MONTH_BY_DEMOGRAPHICS_VIEW_QUERY_TEMPLATE,
dimensions=(
"state_code",
"population_date",
"race_or_ethnicity",
"gender",
"age_bucket",
),
description=
INCARCERATION_POPULATION_BY_MONTH_BY_DEMOGRAPHICS_VIEW_DESCRIPTION,
materialized_metrics_dataset=dataset_config.
DATAFLOW_METRICS_MATERIALIZED_DATASET,
reference_views_dataset=dataset_config.REFERENCE_VIEWS_DATASET,
static_reference_dataset=dataset_config.STATIC_REFERENCE_TABLES_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"),
state_specific_race_or_ethnicity_groupings=state_specific_query_strings.
state_specific_race_or_ethnicity_groupings(
"prioritized_race_or_ethnicity"),
state_specific_facility_exclusion=state_specific_query_strings.
state_specific_facility_exclusion(),
)
if __name__ == "__main__":
with local_project_id_override(GCP_PROJECT_STAGING):
INCARCERATION_POPULATION_BY_MONTH_BY_DEMOGRAPHICS_VIEW_BUILDER.build_and_print(
)
ROUND(IEEE_DIVIDE(successful_termination_count, projected_completion_count), 2) as success_rate
FROM success_counts
ORDER BY state_code, projected_year, projected_month, supervision_type
"""
SUPERVISION_SUCCESS_BY_MONTH_VIEW_BUILDER = MetricBigQueryViewBuilder(
dataset_id=dataset_config.PUBLIC_DASHBOARD_VIEWS_DATASET,
view_id=SUPERVISION_SUCCESS_BY_MONTH_VIEW_NAME,
view_query_template=SUPERVISION_SUCCESS_BY_MONTH_VIEW_QUERY_TEMPLATE,
dimensions=(
"state_code",
"supervision_type",
"projected_year",
"projected_month",
"district",
),
description=SUPERVISION_SUCCESS_BY_MONTH_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"),
district_dimension=bq_utils.unnest_district(),
thirty_six_month_filter=bq_utils.thirty_six_month_filter(),
)
if __name__ == "__main__":
with local_project_id_override(GCP_PROJECT_STAGING):
SUPERVISION_SUCCESS_BY_MONTH_VIEW_BUILDER.build_and_print()
district
FROM `{project_id}.{reference_views_dataset}.event_based_supervision_populations`
GROUP BY state_code, year, month, supervision_type, district
) pop
LEFT JOIN (
SELECT
state_code, year, month,
COUNT(DISTINCT person_id) AS revocation_count,
supervision_type,
district
FROM `{project_id}.{reference_views_dataset}.event_based_revocations`
GROUP BY state_code, year, month, supervision_type, district
) rev
USING (state_code, year, month, supervision_type, district)
WHERE supervision_type in ('ALL', 'PAROLE', 'PROBATION')
ORDER BY state_code, year, month, supervision_type, district
"""
REVOCATIONS_BY_MONTH_VIEW_BUILDER = MetricBigQueryViewBuilder(
dataset_id=dataset_config.DASHBOARD_VIEWS_DATASET,
view_id=REVOCATIONS_BY_MONTH_VIEW_NAME,
view_query_template=REVOCATIONS_BY_MONTH_QUERY_TEMPLATE,
dimensions=['state_code', 'year', 'month', 'supervision_type', 'district'],
description=REVOCATIONS_BY_MONTH_DESCRIPTION,
reference_views_dataset=dataset_config.REFERENCE_VIEWS_DATASET,
)
if __name__ == '__main__':
with local_project_id_override(GCP_PROJECT_STAGING):
REVOCATIONS_BY_MONTH_VIEW_BUILDER.build_and_print()
USING (state_code, violation_type, reported_violations, risk_level, supervision_type, supervision_level,
charge_category, level_1_supervision_location, level_2_supervision_location, metric_period_months, admission_type)
-- Filter out any rows that don't have a specified violation_type
WHERE violation_type != 'NO_VIOLATION_TYPE'
"""
REVOCATIONS_MATRIX_DISTRIBUTION_BY_RISK_LEVEL_VIEW_BUILDER = MetricBigQueryViewBuilder(
dataset_id=dataset_config.DASHBOARD_VIEWS_DATASET,
view_id=REVOCATIONS_MATRIX_DISTRIBUTION_BY_RISK_LEVEL_VIEW_NAME,
view_query_template=REVOCATIONS_MATRIX_DISTRIBUTION_BY_RISK_LEVEL_QUERY_TEMPLATE,
dimensions=(
"state_code",
"metric_period_months",
"level_1_supervision_location",
"level_2_supervision_location",
"supervision_type",
"violation_type",
"reported_violations",
"charge_category",
"risk_level",
"supervision_level",
"admission_type",
),
description=REVOCATIONS_MATRIX_DISTRIBUTION_BY_RISK_LEVEL_DESCRIPTION,
reference_views_dataset=dataset_config.REFERENCE_VIEWS_DATASET,
)
if __name__ == "__main__":
with local_project_id_override(GCP_PROJECT_STAGING):
REVOCATIONS_MATRIX_DISTRIBUTION_BY_RISK_LEVEL_VIEW_BUILDER.build_and_print()
