def get_view_builder(
view_id: str,
description: str,
facility_type: state_specific_query_strings.SpotlightFacilityType,
) -> MetricBigQueryViewBuilder:
"""Retrieves an incarceration population view builder filtered by facility type"""
return MetricBigQueryViewBuilder(
view_id=view_id,
description=description,
dataset_id=dataset_config.PUBLIC_DASHBOARD_VIEWS_DATASET,
view_query_template=
POPULATION_BY_FACILITY_BY_DEMOGRAPHICS_VIEW_QUERY_TEMPLATE,
dimensions=(
"state_code",
"date_of_stay",
"facility",
"race_or_ethnicity",
"gender",
"age_bucket",
),
static_reference_dataset=dataset_config.
STATIC_REFERENCE_TABLES_DATASET,
reference_views_dataset=dataset_config.REFERENCE_VIEWS_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"),
facility_dimension=bq_utils.unnest_column("facility", "facility"),
state_specific_race_or_ethnicity_groupings=state_specific_query_strings
.state_specific_race_or_ethnicity_groupings(
race_or_ethnicity_column="prioritized_race_or_ethnicity"),
state_specific_facility_mapping=state_specific_query_strings.
spotlight_state_specific_facility(),
facility_type_filter=state_specific_query_strings.
spotlight_state_specific_facility_filter(facility_type=facility_type),
)
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(
)
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()
WHERE ranking = 1
AND supervision_type IN ('ALL', 'DUAL', 'PAROLE', 'PROBATION')
AND district IS NOT NULL
"""
SUPERVISION_TERMINATION_MATRIX_BY_PERSON_VIEW_BUILDER = SimpleBigQueryViewBuilder(
dataset_id=dataset_config.REFERENCE_VIEWS_DATASET,
view_id=SUPERVISION_TERMINATION_MATRIX_BY_PERSON_VIEW_NAME,
view_query_template=SUPERVISION_TERMINATION_MATRIX_BY_PERSON_VIEW_QUERY_TEMPLATE,
description=SUPERVISION_TERMINATION_MATRIX_BY_PERSON_VIEW_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_assessment_bucket=
state_specific_query_strings.state_specific_assessment_bucket(output_column_name='risk_level'),
state_specific_supervision_level=state_specific_query_strings.state_specific_supervision_level(),
district_dimension=bq_utils.unnest_district('district'),
supervision_type_dimension=bq_utils.unnest_supervision_type(),
supervision_level_dimension=bq_utils.unnest_column('supervision_level', 'supervision_level'),
charge_category_dimension=bq_utils.unnest_charge_category(),
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):
SUPERVISION_TERMINATION_MATRIX_BY_PERSON_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()
"""
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",
"level_1_supervision_location",
"level_2_supervision_location",
"supervision_type",
"supervision_level",
"violation_type",
"reported_violations",
"admission_type",
"charge_category",
"gender",
),
description=REVOCATIONS_MATRIX_DISTRIBUTION_BY_GENDER_DESCRIPTION,
reference_views_dataset=dataset_config.REFERENCE_VIEWS_DATASET,
gender_dimension=bq_utils.unnest_column("gender", "gender"),
supported_gender_values=SUPPORTED_GENDER_VALUES,
)
if __name__ == "__main__":
with local_project_id_override(GCP_PROJECT_STAGING):
REVOCATIONS_MATRIX_DISTRIBUTION_BY_GENDER_VIEW_BUILDER.build_and_print(
)
{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 (facility != 'ALL' AND race_or_ethnicity = 'ALL' AND gender = 'ALL' AND age_bucket = 'ALL') -- Facility breakdown
OR (facility = 'ALL' AND race_or_ethnicity = 'ALL' AND gender = 'ALL' AND age_bucket = 'ALL') -- State-wide count
GROUP BY state_code, date_of_stay, facility, race_or_ethnicity, gender, age_bucket
ORDER BY state_code, date_of_stay, facility, race_or_ethnicity, gender, age_bucket
"""
INCARCERATION_POPULATION_BY_FACILITY_BY_DEMOGRAPHICS_VIEW_BUILDER = SimpleBigQueryViewBuilder(
dataset_id=dataset_config.PUBLIC_DASHBOARD_VIEWS_DATASET,
view_id=INCARCERATION_POPULATION_BY_FACILITY_BY_DEMOGRAPHICS_VIEW_NAME,
view_query_template=INCARCERATION_POPULATION_BY_FACILITY_BY_DEMOGRAPHICS_VIEW_QUERY_TEMPLATE,
description=INCARCERATION_POPULATION_BY_FACILITY_BY_DEMOGRAPHICS_VIEW_DESCRIPTION,
metrics_dataset=dataset_config.DATAFLOW_METRICS_DATASET,
reference_dataset=dataset_config.REFERENCE_TABLES_DATASET,
race_or_ethnicity_dimension=bq_utils.unnest_race_and_ethnicity(),
metric_period_dimension=bq_utils.unnest_metric_period_months(),
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'),
facility_dimension=bq_utils.unnest_column('facility', 'facility'),
state_specific_race_or_ethnicity_groupings=bq_utils.state_specific_race_or_ethnicity_groupings()
)
if __name__ == '__main__':
with local_project_id_override(GAE_PROJECT_STAGING):
INCARCERATION_POPULATION_BY_FACILITY_BY_DEMOGRAPHICS_VIEW_BUILDER.build_and_print()
WHERE supervision_type IN ('ALL', 'DUAL', 'PAROLE', 'PROBATION')
AND {state_specific_supervision_location_optimization_filter}
AND {state_specific_dimension_filter}
AND (reported_violations = 'ALL' OR violation_type != 'ALL')
"""
REVOCATIONS_MATRIX_BY_PERSON_VIEW_BUILDER = SimpleBigQueryViewBuilder(
dataset_id=dataset_config.REFERENCE_VIEWS_DATASET,
view_id=REVOCATIONS_MATRIX_BY_PERSON_VIEW_NAME,
should_materialize=True,
view_query_template=REVOCATIONS_MATRIX_BY_PERSON_QUERY_TEMPLATE,
description=REVOCATIONS_MATRIX_BY_PERSON_DESCRIPTION,
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(),
level_1_supervision_location_dimension=bq_utils.unnest_column(
"level_1_supervision_location", "level_1_supervision_location"),
level_2_supervision_location_dimension=bq_utils.unnest_column(
"level_2_supervision_location", "level_2_supervision_location"),
supervision_type_dimension=bq_utils.unnest_supervision_type(),
supervision_level_dimension=bq_utils.unnest_column("supervision_level",
"supervision_level"),
charge_category_dimension=bq_utils.unnest_charge_category(),
violation_type_dimension=bq_utils.unnest_column("violation_type",
"violation_type"),
reported_violations_dimension=bq_utils.unnest_reported_violations(),
metric_period_dimension=bq_utils.unnest_metric_period_months(),
metric_period_condition=bq_utils.metric_period_condition(),
state_specific_assessment_bucket=state_specific_query_strings.
state_specific_assessment_bucket(output_column_name="risk_level"),
state_specific_supervision_level=state_specific_query_strings.
state_specific_supervision_level(),
"""
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,
base_dataset=dataset_config.STATE_BASE_DATASET,
static_reference_dataset=dataset_config.STATIC_REFERENCE_TABLES_DATASET,
reference_views_dataset=dataset_config.REFERENCE_VIEWS_DATASET,
metrics_dataset=dataset_config.DATAFLOW_METRICS_DATASET,
current_month_condition=bq_utils.current_month_condition(),
state_specific_race_or_ethnicity_groupings=state_specific_query_strings.
state_specific_race_or_ethnicity_groupings(),
race_or_ethnicity_dimension=bq_utils.unnest_race_and_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(),
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):
ACTIVE_PROGRAM_PARTICIPATION_BY_REGION_VIEW_BUILDER.build_and_print()
-- Filter out any rows that don't have a specified violation_type
WHERE violation_type != 'NO_VIOLATION_TYPE'
"""
REVOCATIONS_MATRIX_DISTRIBUTION_BY_RACE_VIEW_BUILDER = MetricBigQueryViewBuilder(
dataset_id=dataset_config.DASHBOARD_VIEWS_DATASET,
view_id=REVOCATIONS_MATRIX_DISTRIBUTION_BY_RACE_VIEW_NAME,
view_query_template=REVOCATIONS_MATRIX_DISTRIBUTION_BY_RACE_QUERY_TEMPLATE,
dimensions=(
"state_code",
"metric_period_months",
"level_1_supervision_location",
"level_2_supervision_location",
"supervision_type",
"supervision_level",
"violation_type",
"reported_violations",
"admission_type",
"charge_category",
"race",
),
description=REVOCATIONS_MATRIX_DISTRIBUTION_BY_RACE_DESCRIPTION,
reference_views_dataset=dataset_config.REFERENCE_VIEWS_DATASET,
race_dimension=bq_utils.unnest_column("prioritized_race_or_ethnicity",
"race"),
)
if __name__ == "__main__":
with local_project_id_override(GCP_PROJECT_STAGING):
REVOCATIONS_MATRIX_DISTRIBUTION_BY_RACE_VIEW_BUILDER.build_and_print()
LEFT JOIN overlapping_open_period USING (supervision_period_id)
LEFT JOIN `{project_id}.{reference_dataset}.supervision_period_to_agent_association` agent
USING (state_code, supervision_period_id)
WHERE EXTRACT(YEAR FROM termination_date) >= EXTRACT(YEAR FROM DATE_SUB(CURRENT_DATE(), INTERVAL 3 YEAR))
-- Do not count any discharges that are overlapping with another open supervision period
AND overlapping_open_period.supervision_period_id IS NULL
GROUP BY state_code, year, month, district, officer_external_id
),
{district_dimension},
{officer_dimension}
GROUP BY state_code, year, month, district, officer_external_id
ORDER BY state_code, year, month, district, officer_external_id
"""
SUPERVISION_DISCHARGES_BY_OFFICER_BY_MONTH_VIEW = BigQueryView(
dataset_id=dataset_config.DASHBOARD_VIEWS_DATASET,
view_id=SUPERVISION_DISCHARGES_BY_OFFICER_BY_MONTH_VIEW_NAME,
view_query_template=
SUPERVISION_DISCHARGES_BY_OFFICER_BY_MONTH_QUERY_TEMPLATE,
description=SUPERVISION_DISCHARGES_BY_OFFICER_BY_MONTH_DESCRIPTION,
reference_dataset=dataset_config.REFERENCE_TABLES_DATASET,
district_dimension=bq_utils.unnest_district(district_column='district'),
officer_dimension=bq_utils.unnest_column(
input_column_name='officer_external_id',
output_column_name='officer_external_id'),
)
if __name__ == '__main__':
print(SUPERVISION_DISCHARGES_BY_OFFICER_BY_MONTH_VIEW.view_id)
print(SUPERVISION_DISCHARGES_BY_OFFICER_BY_MONTH_VIEW.view_query)
AND methodology = 'PERSON'
-- Revisit these exclusions when #3657 and #3723 are complete --
AND (state_code != 'US_ND' OR facility not in ('OOS', 'CPP'))
AND EXTRACT(YEAR FROM date_of_stay) > EXTRACT(YEAR FROM DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR))),
{facility_dimension}
LEFT JOIN
`{project_id}.{base_dataset}.state_person`
USING (person_id))
GROUP BY state_code, facility, date_of_stay)
LEFT JOIN
`{project_id}.{reference_dataset}.state_incarceration_facility_capacity`
USING (state_code, facility)
ORDER BY state_code, facility, date_of_stay
"""
FACILITY_POPULATION_BY_AGE_WITH_CAPACITY_BY_DAY_VIEW_BUILDER = SimpleBigQueryViewBuilder(
dataset_id=dataset_config.COVID_REPORT_DATASET,
view_id=FACILITY_POPULATION_BY_AGE_WITH_CAPACITY_BY_DAY_VIEW_NAME,
view_query_template=
FACILITY_POPULATION_BY_AGE_WITH_CAPACITY_BY_DAY_QUERY_TEMPLATE,
description=FACILITY_POPULATION_BY_AGE_WITH_CAPACITY_BY_DAY_DESCRIPTION,
base_dataset=STATE_BASE_DATASET,
metrics_dataset=DATAFLOW_METRICS_DATASET,
reference_dataset=REFERENCE_TABLES_DATASET,
facility_dimension=bq_utils.unnest_column('facility', 'facility'))
if __name__ == '__main__':
with local_project_id_override(GCP_PROJECT_STAGING):
FACILITY_POPULATION_BY_AGE_WITH_CAPACITY_BY_DAY_VIEW_BUILDER.build_and_print(
)
date_of_stay,
COUNT(DISTINCT IF(specialized_purpose_for_incarceration = 'PAROLE_BOARD_HOLD', person_id, NULL)) AS
parole_board_hold_count,
COUNT(DISTINCT(person_id)) as total_population
FROM
daily_population,
{facility_dimension}
GROUP BY state_code, facility, date_of_stay
ORDER BY state_code, facility, date_of_stay
"""
INCARCERATION_POPULATION_BY_PURPOSE_BY_DAY_VIEW_BUILDER = SimpleBigQueryViewBuilder(
dataset_id=dataset_config.COVID_REPORT_DATASET,
view_id=INCARCERATION_POPULATION_BY_PURPOSE_BY_DAY_VIEW_NAME,
view_query_template=
INCARCERATION_POPULATION_BY_PURPOSE_BY_DAY_QUERY_TEMPLATE,
description=INCARCERATION_POPULATION_BY_PURPOSE_BY_DAY_DESCRIPTION,
base_dataset=STATE_BASE_DATASET,
materialized_metrics_dataset=DATAFLOW_METRICS_MATERIALIZED_DATASET,
static_reference_dataset=STATIC_REFERENCE_TABLES_DATASET,
reference_views_dataset=dataset_config.REFERENCE_VIEWS_DATASET,
facility_dimension=bq_utils.unnest_column("facility", "facility"),
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_PURPOSE_BY_DAY_VIEW_BUILDER.build_and_print(
)
