USING (state_code, job_id, year, month, metric_period_months, metric_type),
{district_dimension},
{metric_period_dimension}
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 = SimpleBigQueryViewBuilder(
dataset_id=dataset_config.DASHBOARD_VIEWS_DATASET,
view_id=REINCARCERATIONS_BY_PERIOD_VIEW_NAME,
view_query_template=REINCARCERATIONS_BY_PERIOD_QUERY_TEMPLATE,
description=REINCARCERATIONS_BY_PERIOD_DESCRIPTION,
metrics_dataset=dataset_config.DATAFLOW_METRICS_DATASET,
reference_dataset=dataset_config.REFERENCE_TABLES_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(),
)
if __name__ == '__main__':
with local_project_id_override(GCP_PROJECT_STAGING):
REINCARCERATIONS_BY_PERIOD_VIEW_BUILDER.build_and_print()
start_reason
WITH OFFSET AS priority
"""
ADMISSION_START_REASON_DEDUP_PRIORITY_VIEW_BUILDER = SimpleBigQueryViewBuilder(
dataset_id=ANALYST_VIEWS_DATASET,
view_id=ADMISSION_START_REASON_DEDUP_PRIORITY_VIEW_NAME,
view_query_template=ADMISSION_START_REASON_DEDUP_PRIORITY_QUERY_TEMPLATE,
description=ADMISSION_START_REASON_DEDUP_PRIORITY_VIEW_DESCRIPTION,
should_materialize=False,
prioritized_supervision_start_reasons=(
"\n,".join(
[
f"'{start_reason.value}'"
for start_reason in SUPERVISION_START_REASON_ORDERED_PRIORITY
]
)
),
prioritized_incarceration_start_reasons=(
"\n,".join(
[
f"'{start_reason.value}'"
for start_reason in INCARCERATION_START_REASON_ORDERED_PRIORITY
]
)
),
)
if __name__ == "__main__":
with local_project_id_override(GCP_PROJECT_STAGING):
ADMISSION_START_REASON_DEDUP_PRIORITY_VIEW_BUILDER.build_and_print()
from recidiviz.utils.environment import GCP_PROJECT_STAGING
from recidiviz.utils.metadata import local_project_id_override
from recidiviz.validation.views import dataset_config
INCARCERATION_RELEASE_REASON_NO_DATE_VIEW_NAME = "incarceration_release_reason_no_date"
INCARCERATION_RELEASE_REASON_NO_DATE_DESCRIPTION = (
"""Incarceration periods with release reasons but no release date."""
)
INCARCERATION_RELEASE_REASON_NO_DATE_QUERY_TEMPLATE = """
/*{description}*/
SELECT *, state_code as region_code
FROM `{project_id}.{state_dataset}.state_incarceration_period`
WHERE release_date IS NULL
AND release_reason IS NOT NULL
ORDER BY region_code, release_reason, external_id
"""
INCARCERATION_RELEASE_REASON_NO_DATE_VIEW_BUILDER = SimpleBigQueryViewBuilder(
dataset_id=dataset_config.VIEWS_DATASET,
view_id=INCARCERATION_RELEASE_REASON_NO_DATE_VIEW_NAME,
view_query_template=INCARCERATION_RELEASE_REASON_NO_DATE_QUERY_TEMPLATE,
description=INCARCERATION_RELEASE_REASON_NO_DATE_DESCRIPTION,
state_dataset=state_dataset_config.STATE_BASE_DATASET,
)
if __name__ == "__main__":
with local_project_id_override(GCP_PROJECT_STAGING):
INCARCERATION_RELEASE_REASON_NO_DATE_VIEW_BUILDER.build_and_print()
base_dataset=base_dataset, table_name=table.name)
query_str = query_str.replace(table.name, bq_table_name)
return query_str
with SessionFactory.using_database(
database_key=SQLAlchemyDatabaseKey.for_schema(SchemaType.JAILS),
autocommit=False,
) as session:
_QUERIES = [m.to_query(session) for m in state_aggregate_mappings.MAPPINGS]
_UNIONED_STATEMENT = sqlalchemy.union_all(*_QUERIES)
_BQ_UNIONED_STATEMENT_QUERY_TEMPLATE = _to_bq_table(
str(_UNIONED_STATEMENT.compile()))
COMBINED_STATE_AGGREGATE_DESCRIPTION = """
The concatenation of all 'state-reported aggregate reports' after mapping each column
to a shared column_name."""
# This view is the concatenation of all "state-reported aggregate reports" after
# mapping each column to a shared column_name. The next logical derivation from
# this view is to combine it with a view of our "scraped website data" that maps
# to the same shared column_names. Both of these views should be aggregated to
# the same level (eg. jurisdiction level via jurisdiction_id).
COMBINED_STATE_AGGREGATE_VIEW_BUILDER: SimpleBigQueryViewBuilder = (
SimpleBigQueryViewBuilder(
dataset_id=dataset_config.UNMANAGED_VIEWS_DATASET,
view_id="combined_state_aggregates",
description=COMBINED_STATE_AGGREGATE_DESCRIPTION,
view_query_template=_BQ_UNIONED_STATEMENT_QUERY_TEMPLATE,
))
(SELECT DISTINCT job_id, year, month, metric_period_months, state_code, 'SUPERVISION_SUCCESS' as metric_type
FROM `{project_id}.{metrics_dataset}.supervision_success_metrics`)
UNION ALL
(SELECT DISTINCT job_id, year, month, metric_period_months, state_code, 'SUCCESSFUL_SENTENCE_DAYS_SERVED' as metric_type
FROM `{project_id}.{metrics_dataset}.successful_supervision_sentence_days_served_metrics`)
UNION ALL
(SELECT DISTINCT job_id, year, month, metric_period_months, state_code, 'PROGRAM_REFERRAL' as metric_type
FROM `{project_id}.{metrics_dataset}.program_referral_metrics`)
UNION ALL
(SELECT DISTINCT job_id, year, month, metric_period_months, state_code, 'PROGRAM_PARTICIPATION' as metric_type
FROM `recidiviz-staging.dataflow_metrics.program_participation_metrics`)
)
)
WHERE recency_rank = 1
ORDER BY metric_type, state_code, year, month, metric_period_months
"""
MOST_RECENT_JOB_ID_BY_METRIC_AND_STATE_CODE_VIEW_BUILDER = SimpleBigQueryViewBuilder(
dataset_id=dataset_config.REFERENCE_TABLES_DATASET,
view_id=MOST_RECENT_JOB_ID_BY_METRIC_AND_STATE_CODE_VIEW_NAME,
materialized_view_table_id=MATERIALIZED_VIEW_TABLE_NAME,
view_query_template=
MOST_RECENT_JOB_ID_BY_METRIC_AND_STATE_CODE_QUERY_TEMPLATE,
description=MOST_RECENT_JOB_ID_BY_METRIC_AND_STATE_CODE_DESCRIPTION,
metrics_dataset=dataset_config.DATAFLOW_METRICS_DATASET)
if __name__ == '__main__':
with local_project_id_override(GAE_PROJECT_STAGING):
MOST_RECENT_JOB_ID_BY_METRIC_AND_STATE_CODE_VIEW_BUILDER.build_and_print(
)
USING (SCD)
WHERE
DOC IS NOT NULL AND
CYC IS NOT NULL AND
SEO IS NOT NULL AND
SSO IS NOT NULL
) all_statuses
LEFT OUTER JOIN
`{project_id}.{base_dataset}.state_supervision_sentence` supervision_sentences
ON
supervision_sentences.external_id = sentence_external_id
LEFT OUTER JOIN
`{project_id}.{base_dataset}.state_incarceration_sentence` incarceration_sentences
ON
incarceration_sentences.external_id = sentence_external_id
WHERE (incarceration_sentences.person_id IS NOT NULL OR supervision_sentences.person_id IS NOT NULL);
"""
US_MO_SENTENCE_STATUSES_VIEW_BUILDER = SimpleBigQueryViewBuilder(
dataset_id=dataset_config.REFERENCE_TABLES_DATASET,
view_id=US_MO_SENTENCE_STATUSES_VIEW_NAME,
view_query_template=US_MO_SENTENCE_STATUSES_QUERY_TEMPLATE,
description=US_MO_SENTENCE_STATUSES_DESCRIPTION,
base_dataset=dataset_config.STATE_BASE_DATASET,
reference_tables_dataset=dataset_config.REFERENCE_TABLES_DATASET,
)
if __name__ == '__main__':
with local_project_id_override(GAE_PROJECT_STAGING):
US_MO_SENTENCE_STATUSES_VIEW_BUILDER.build_and_print()
SELECT
'US_ID' as state_code,
(row_number() OVER ()) - 1 as week_num,
start_date,
DATE_ADD(start_date, INTERVAL 13 DAY) as end_date
FROM
(SELECT * FROM UNNEST(GENERATE_DATE_ARRAY('2020-05-02', CURRENT_DATE('America/Los_Angeles'), INTERVAL 2 WEEK)) as start_date)
UNION ALL
-- US_ND report starting 2020-03-12 --
SELECT
'US_ND' as state_code,
(row_number() OVER ()) - 1 as week_num,
start_date,
DATE_ADD(start_date, INTERVAL 13 DAY) as end_date
FROM
(SELECT * FROM UNNEST(GENERATE_DATE_ARRAY('2020-03-12', CURRENT_DATE('America/Los_Angeles'), INTERVAL 2 WEEK)) as start_date)
"""
COVID_REPORT_WEEKS_VIEW_BUILDER = SimpleBigQueryViewBuilder(
dataset_id=dataset_config.REFERENCE_VIEWS_DATASET,
view_id=COVID_REPORT_WEEKS_VIEW_NAME,
view_query_template=COVID_REPORT_WEEKS_QUERY_TEMPLATE,
description=COVID_REPORT_WEEKS_DESCRIPTION,
)
if __name__ == "__main__":
with local_project_id_override(GCP_PROJECT_STAGING):
COVID_REPORT_WEEKS_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()
{metric_period_dimension}
WHERE methodology = 'EVENT'
AND m.metric_period_months = 1
AND assessment_type = 'LSIR'
AND assessment_score_change IS NOT NULL
AND person_id IS NOT NULL
AND {metric_period_condition}
)
WHERE supervision_type IN ('ALL', 'PAROLE', 'PROBATION')
AND supervision_rank = 1
GROUP BY state_code, metric_period_months, supervision_type, district
ORDER BY state_code, district, supervision_type, metric_period_months
"""
AVERAGE_CHANGE_LSIR_SCORE_BY_PERIOD_VIEW_BUILDER = SimpleBigQueryViewBuilder(
dataset_id=dataset_config.DASHBOARD_VIEWS_DATASET,
view_id=AVERAGE_CHANGE_LSIR_SCORE_BY_PERIOD_VIEW_NAME,
view_query_template=AVERAGE_CHANGE_LSIR_SCORE_BY_PERIOD_QUERY_TEMPLATE,
description=AVERAGE_CHANGE_LSIR_SCORE_BY_PERIOD_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(),
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):
AVERAGE_CHANGE_LSIR_SCORE_BY_PERIOD_VIEW_BUILDER.build_and_print()
total_population
FROM `{project_id}.{population_projection_dataset}.incarceration_remaining_sentences`
UNION ALL
SELECT
state_code,
run_date,
compartment,
outflow_to,
compartment_duration,
gender,
CAST(ROUND(SUM(total_population)) AS INT64) AS total_population
FROM `{project_id}.{population_projection_dataset}.us_id_rider_pbh_remaining_sentences`
GROUP BY state_code, run_date, compartment, outflow_to, compartment_duration, gender
"""
REMAINING_SENTENCES_VIEW_BUILDER = SimpleBigQueryViewBuilder(
dataset_id=POPULATION_PROJECTION_DATASET,
view_id=REMAINING_SENTENCES_VIEW_NAME,
view_query_template=REMAINING_SENTENCES_QUERY_TEMPLATE,
description=REMAINING_SENTENCES_VIEW_DESCRIPTION,
analyst_dataset=ANALYST_VIEWS_DATASET,
population_projection_dataset=POPULATION_PROJECTION_DATASET,
should_materialize=True,
)
if __name__ == "__main__":
with local_project_id_override(GCP_PROJECT_STAGING):
REMAINING_SENTENCES_VIEW_BUILDER.build_and_print()
DATE_SUB(start_date, INTERVAL 1 YEAR) as start_date,
DATE_SUB(end_date, INTERVAL 1 YEAR) as end_date,
facility
FROM
(SELECT * FROM
UNNEST([{us_nd_report_facilities}]) as facility,
incident_report_weeks)) weeks
LEFT JOIN incidents
ON weeks.facility = incidents.facility AND INCIDENT_DATE BETWEEN start_date AND end_date)
GROUP BY week_num, start_date, end_date, facility)
USING (week_num, facility)
ORDER BY facility, week_num, start_date, end_date
"""
DATE_REGEX_MATCHER = r"'\d{{1,2}}/\d{{1,2}}/\d{{4}}'"
US_ND_REPORT_FACILITIES = "'NDSP','JRCC', 'DWCRC', 'MRCC'"
INCIDENTS_BY_FACILITY_BY_WEEK_VIEW_BUILDER = SimpleBigQueryViewBuilder(
dataset_id=dataset_config.COVID_REPORT_DATASET,
view_id=INCIDENTS_BY_FACILITY_BY_WEEK_VIEW_NAME,
view_query_template=INCIDENTS_BY_FACILITY_BY_WEEK_QUERY_TEMPLATE,
description=INCIDENTS_BY_FACILITY_BY_WEEK_DESCRIPTION,
covid_report_dataset=COVID_REPORT_DATASET,
date_regex=DATE_REGEX_MATCHER,
us_nd_report_facilities=US_ND_REPORT_FACILITIES,
static_reference_dataset=dataset_config.STATIC_REFERENCE_TABLES_DATASET)
if __name__ == '__main__':
with local_project_id_override(GCP_PROJECT_STAGING):
INCIDENTS_BY_FACILITY_BY_WEEK_VIEW_BUILDER.build_and_print()
parole_eligibility_date,
life_sentence,
offense_count,
most_severe_is_violent,
most_severe_classification_type,
classification_type,
description,
ncic_code,
offense_type,
CASE WHEN completion_date < last_day_of_data THEN sentence_length_days END AS sentence_length_days,
min_projected_sentence_length,
max_projected_sentence_length
FROM final_pre_deduped_date_proximity_cte
WHERE longest_projected_sentence = 1
ORDER by person_id ASC, session_id ASC, sentence_start_date ASC
"""
COMPARTMENT_SENTENCES_VIEW_BUILDER = SimpleBigQueryViewBuilder(
dataset_id=dataset_config.ANALYST_VIEWS_DATASET,
view_id=COMPARTMENT_SENTENCES_VIEW_NAME,
view_query_template=COMPARTMENT_SENTENCES_QUERY_TEMPLATE,
description=COMPARTMENT_SENTENCES_VIEW_DESCRIPTION,
base_dataset=STATE_BASE_DATASET,
analyst_dataset=ANALYST_VIEWS_DATASET,
should_materialize=True,
)
if __name__ == "__main__":
with local_project_id_override(GCP_PROJECT_STAGING):
COMPARTMENT_SENTENCES_VIEW_BUILDER.build_and_print()
'technical_revocations', 'technical_revocations_change', 'absconsions',
'absconsions_change', 'crime_revocations', 'crime_revocations_change'
]
PO_REPORT_COMPARISON_COLUMNS = PO_REPORT_REQUIRED_FIELDS + ['total_rows']
PO_REPORT_MISSING_FIELDS_QUERY_TEMPLATE = \
"""
/*{description}*/
SELECT
state_code, review_month,
COUNT(*) AS total_rows,
{non_null_column_count}
FROM `{project_id}.{po_report_dataset}.po_monthly_report_data` t1
GROUP BY state_code, review_month
"""
PO_REPORT_MISSING_FIELDS_VIEW_BUILDER = SimpleBigQueryViewBuilder(
dataset_id=dataset_config.VIEWS_DATASET,
view_id=PO_REPORT_MISSING_FIELDS_VIEW_NAME,
view_query_template=PO_REPORT_MISSING_FIELDS_QUERY_TEMPLATE,
description=PO_REPORT_MISSING_FIELDS_DESCRIPTION,
po_report_dataset=state_dataset_config.PO_REPORT_DATASET,
non_null_column_count=',\n'.join([
f'SUM(IF({col} IS NOT NULL, 1, 0)) AS {col}'
for col in PO_REPORT_REQUIRED_FIELDS
]))
if __name__ == '__main__':
with local_project_id_override(GAE_PROJECT_STAGING):
PO_REPORT_MISSING_FIELDS_VIEW_BUILDER.build_and_print()
staging.internal_person_external_id AS staging_person,
prod.external_district AS external_district,
prod.internal_district AS prod_district,
staging.internal_district AS staging_district,
prod.external_supervision_level AS external_supervision_level,
prod.internal_supervision_level AS prod_supervision_level,
staging.internal_supervision_level AS staging_supervision_level,
prod.external_supervising_officer AS external_supervising_officer,
prod.internal_supervising_officer AS prod_supervising_officer,
staging.internal_supervising_officer AS staging_supervising_officer,
FROM `{prod_project_id}.{validation_views_dataset}.supervision_population_person_level_external_comparison_materialized` prod
FULL JOIN `{staging_project_id}.{validation_views_dataset}.supervision_population_person_level_external_comparison_materialized` staging
USING (region_code, external_person_external_id, date_of_supervision)
"""
SUPERVISION_POPULATION_EXTERNAL_PROD_STAGING_COMPARISON_VIEW_BUILDER = SimpleBigQueryViewBuilder(
dataset_id=dataset_config.VIEWS_DATASET,
view_id=SUPERVISION_POPULATION_EXTERNAL_PROD_STAGING_COMPARISON_VIEW_NAME,
view_query_template=SUPERVISION_POPULATION_EXTERNAL_PROD_STAGING_COMPARISON_QUERY_TEMPLATE,
description=SUPERVISION_POPULATION_EXTERNAL_PROD_STAGING_COMPARISON_DESCRIPTION,
validation_views_dataset=dataset_config.VIEWS_DATASET,
materialized_metrics_dataset=state_dataset_config.DATAFLOW_METRICS_MATERIALIZED_DATASET,
prod_project_id=GCP_PROJECT_PRODUCTION,
staging_project_id=GCP_PROJECT_STAGING,
should_materialize=True,
)
if __name__ == "__main__":
with local_project_id_override(GCP_PROJECT_STAGING):
SUPERVISION_POPULATION_EXTERNAL_PROD_STAGING_COMPARISON_VIEW_BUILDER.build_and_print()
active_program_participation_by_region view."""
PARTITION_COLUMNS = ["state_code", "supervision_type", "region_id"]
CALCULATED_COLUMNS_TO_VALIDATE = ["participation_count"]
MUTUALLY_EXCLUSIVE_BREAKDOWN_COLUMNS = ["race_or_ethnicity"]
ACTIVE_PROGRAM_PARTICIPATION_BY_REGION_INTERNAL_CONSISTENCY_QUERY_TEMPLATE = f"""
/*{{description}}*/
{internal_consistency_query(partition_columns=PARTITION_COLUMNS,
mutually_exclusive_breakdown_columns=MUTUALLY_EXCLUSIVE_BREAKDOWN_COLUMNS,
calculated_columns_to_validate=CALCULATED_COLUMNS_TO_VALIDATE)}
"""
ACTIVE_PROGRAM_PARTICIPATION_BY_REGION_INTERNAL_CONSISTENCY_VIEW_BUILDER = SimpleBigQueryViewBuilder(
dataset_id=dataset_config.VIEWS_DATASET,
view_id=
ACTIVE_PROGRAM_PARTICIPATION_BY_REGION_INTERNAL_CONSISTENCY_VIEW_NAME,
view_query_template=
ACTIVE_PROGRAM_PARTICIPATION_BY_REGION_INTERNAL_CONSISTENCY_QUERY_TEMPLATE,
description=
ACTIVE_PROGRAM_PARTICIPATION_BY_REGION_INTERNAL_CONSISTENCY_DESCRIPTION,
validated_table_dataset_id=state_dataset_config.
PUBLIC_DASHBOARD_VIEWS_DATASET,
validated_table_id=ACTIVE_PROGRAM_PARTICIPATION_BY_REGION_VIEW_NAME,
)
if __name__ == "__main__":
with local_project_id_override(GCP_PROJECT_STAGING):
ACTIVE_PROGRAM_PARTICIPATION_BY_REGION_INTERNAL_CONSISTENCY_VIEW_BUILDER.build_and_print(
)
WHERE supervising_officer_external_id IS NOT NULL
AND date_of_evaluation = LAST_DAY(DATE(year, month, 1), MONTH)
AND year >= EXTRACT(YEAR FROM DATE_SUB(CURRENT_DATE('US/Pacific'), INTERVAL 3 YEAR))
)
SELECT
state_code, year, month, person_id,
officer_external_id,
assessment_count,
face_to_face_count,
next_recommended_assessment_date,
face_to_face_frequency_sufficient
FROM compliance
WHERE inclusion_order = 1
"""
SUPERVISION_COMPLIANCE_BY_PERSON_BY_MONTH_VIEW_BUILDER = SimpleBigQueryViewBuilder(
dataset_id=dataset_config.PO_REPORT_DATASET,
view_id=SUPERVISION_COMPLIANCE_BY_PERSON_BY_MONTH_VIEW_NAME,
should_materialize=True,
view_query_template=
SUPERVISION_COMPLIANCE_BY_PERSON_BY_MONTH_QUERY_TEMPLATE,
description=SUPERVISION_COMPLIANCE_BY_PERSON_BY_MONTH_DESCRIPTION,
materialized_metrics_dataset=dataset_config.
DATAFLOW_METRICS_MATERIALIZED_DATASET,
)
if __name__ == "__main__":
with local_project_id_override(GCP_PROJECT_STAGING):
SUPERVISION_COMPLIANCE_BY_PERSON_BY_MONTH_VIEW_BUILDER.build_and_print(
)
FROM RaceGender
GROUP BY year, fips
)
SELECT CombinedRaceGender.*, TotalPop.total_resident_pop FROM (
SELECT * FROM AllRace
UNION ALL
SELECT * FROM AllGender
UNION ALL
SELECT * FROM RaceGender
) CombinedRaceGender
LEFT JOIN
TotalPop
ON
CombinedRaceGender.year = TotalPop.year AND CombinedRaceGender.fips = TotalPop.fips
ORDER BY year DESC, fips, race, gender
"""
RESIDENT_POPULATION_COUNTS_VIEW_BUILDER = SimpleBigQueryViewBuilder(
dataset_id=dataset_config.VIEWS_DATASET,
view_id=RESIDENT_POPULATION_COUNTS_VIEW_NAME,
view_query_template=RESIDENT_POPULATION_COUNTS_QUERY_TEMPLATE,
description=RESIDENT_POPULATION_COUNTS_DESCRIPTION,
vera_dataset=vera_view_constants.VERA_DATASET,
iob_race_gender_pop_table=vera_view_constants.IOB_RACE_GENDER_POP_TABLE)
if __name__ == '__main__':
with local_project_id_override(GAE_PROJECT_STAGING):
RESIDENT_POPULATION_COUNTS_VIEW_BUILDER.build_and_print()
CALCULATED_COLUMNS_TO_VALIDATE = [
'new_crime_count', 'technical_count', 'absconsion_count', 'unknown_count',
'revocation_count'
]
MUTUALLY_EXCLUSIVE_BREAKDOWN_COLUMNS = [
'age_bucket', 'race_or_ethnicity', 'gender'
]
SUPERVISION_REVOCATIONS_BY_PERIOD_BY_TYPE_BY_DEMOGRAPHICS_INTERNAL_CONSISTENCY_QUERY_TEMPLATE = f"""
/*{{description}}*/
{internal_consistency_query(partition_columns=PARTITION_COLUMNS, mutually_exclusive_breakdown_columns=MUTUALLY_EXCLUSIVE_BREAKDOWN_COLUMNS, calculated_columns_to_validate=CALCULATED_COLUMNS_TO_VALIDATE)}
"""
SUPERVISION_REVOCATIONS_BY_PERIOD_BY_TYPE_BY_DEMOGRAPHICS_INTERNAL_CONSISTENCY_VIEW_BUILDER = SimpleBigQueryViewBuilder(
dataset_id=dataset_config.VIEWS_DATASET,
view_id=
SUPERVISION_REVOCATIONS_BY_PERIOD_BY_TYPE_BY_DEMOGRAPHICS_INTERNAL_CONSISTENCY_VIEW_NAME,
view_query_template=
SUPERVISION_REVOCATIONS_BY_PERIOD_BY_TYPE_BY_DEMOGRAPHICS_INTERNAL_CONSISTENCY_QUERY_TEMPLATE,
description=
SUPERVISION_REVOCATIONS_BY_PERIOD_BY_TYPE_BY_DEMOGRAPHICS_INTERNAL_CONSISTENCY_DESCRIPTION,
validated_table_dataset_id=state_dataset_config.
PUBLIC_DASHBOARD_VIEWS_DATASET,
validated_table_id=
SUPERVISION_REVOCATIONS_BY_PERIOD_BY_TYPE_BY_DEMOGRAPHICS_VIEW_VIEW_NAME)
if __name__ == '__main__':
with local_project_id_override(GCP_PROJECT_STAGING):
SUPERVISION_REVOCATIONS_BY_PERIOD_BY_TYPE_BY_DEMOGRAPHICS_INTERNAL_CONSISTENCY_VIEW_BUILDER.build_and_print(
)
sessions
"""
SESSION_INCARCERATION_RELEASES_TO_DATAFLOW_QUERY_TEMPLATE = """
/*{description}*/
SELECT
state_code AS region_code,
DATE_TRUNC(end_date, YEAR) AS end_year,
SUM(session_end) AS session_ends,
SUM(session_with_end_reason) AS sessions_with_end_reason,
SUM(dataflow_release) AS dataflow_releases,
SUM(session_release) AS session_releases,
FROM `{project_id}.{validation_views_dataset}.session_incarceration_releases_to_dataflow_disaggregated`
GROUP BY 1,2
ORDER BY 1,2
"""
SESSION_INCARCERATION_RELEASES_TO_DATAFLOW_VIEW_BUILDER = SimpleBigQueryViewBuilder(
dataset_id=dataset_config.VIEWS_DATASET,
view_id=SESSION_INCARCERATION_RELEASES_TO_DATAFLOW_VIEW_NAME,
view_query_template=
SESSION_INCARCERATION_RELEASES_TO_DATAFLOW_QUERY_TEMPLATE,
description=SESSION_INCARCERATION_RELEASES_TO_DATAFLOW_DESCRIPTION,
validation_views_dataset=dataset_config.VIEWS_DATASET,
)
if __name__ == "__main__":
with local_project_id_override(GCP_PROJECT_STAGING):
SESSION_INCARCERATION_RELEASES_TO_DATAFLOW_VIEW_BUILDER.build_and_print(
)
SELECT
cte.compartment,
cte.state_code,
cte.gender,
cte.run_date,
time_step,
SUM(cte.total_population) as total_population
FROM cte,
UNNEST(GENERATE_DATE_ARRAY('2000-01-01', DATE_TRUNC(CURRENT_DATE, MONTH), INTERVAL 1 MONTH)) AS time_step
WHERE
state_code = 'US_ID'
AND gender IN ('FEMALE', 'MALE')
AND time_step BETWEEN cte.start_date AND COALESCE(cte.end_date, '9999-01-01')
GROUP BY 1,2,3,4,5
ORDER BY 1,2,3,4,5
"""
TOTAL_POPULATION_VIEW_BUILDER = SimpleBigQueryViewBuilder(
dataset_id=dataset_config.POPULATION_PROJECTION_DATASET,
view_id=TOTAL_POPULATION_VIEW_NAME,
view_query_template=TOTAL_POPULATION_QUERY_TEMPLATE,
description=TOTAL_POPULATION_VIEW_DESCRIPTION,
analyst_dataset=dataset_config.ANALYST_VIEWS_DATASET,
population_projection_dataset=dataset_config.POPULATION_PROJECTION_DATASET,
should_materialize=False,
)
if __name__ == "__main__":
with local_project_id_override(GCP_PROJECT_STAGING):
TOTAL_POPULATION_VIEW_BUILDER.build_and_print()
ethnicity,
gender,
age_bucket
FROM `{project_id}.{metrics_dataset}.supervision_revocation_metrics` m
JOIN `{project_id}.{reference_dataset}.most_recent_job_id_by_metric_and_state_code` job
USING (state_code, job_id, year, month, metric_period_months, metric_type),
{district_dimension},
{supervision_dimension}
WHERE methodology = 'EVENT'
AND m.metric_period_months = 1
AND person_id IS NOT NULL
AND district IS NOT NULL
AND month IS NOT NULL
AND year >= EXTRACT(YEAR FROM DATE_SUB(CURRENT_DATE(), INTERVAL 3 YEAR))
"""
EVENT_BASED_REVOCATIONS_VIEW_BUILDER = SimpleBigQueryViewBuilder(
dataset_id=dataset_config.REFERENCE_TABLES_DATASET,
view_id=EVENT_BASED_REVOCATIONS_VIEW_NAME,
view_query_template=EVENT_BASED_REVOCATIONS_QUERY_TEMPLATE,
description=EVENT_BASED_REVOCATIONS_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(),
)
if __name__ == '__main__':
with local_project_id_override(GCP_PROJECT_STAGING):
EVENT_BASED_REVOCATIONS_VIEW_BUILDER.build_and_print()
)
REVOCATION_MATRIX_DISTRIBUTION_BY_GENDER_COMPARISON_DESCRIPTION = """
Revocation matrix comparison of summed counts across gender """
REVOCATION_MATRIX_DISTRIBUTION_BY_GENDER_COMPARISON_QUERY_TEMPLATE = f"""
/*{{description}}*/
{sums_and_totals_consistency_query(
view_builder=REVOCATIONS_MATRIX_DISTRIBUTION_BY_GENDER_VIEW_BUILDER,
breakdown_dimensions=['gender'],
columns_with_totals=['revocation_count_all', 'supervision_count_all','recommended_for_revocation_count_all'],
columns_with_breakdown_counts=
['revocation_count', 'supervision_population_count','recommended_for_revocation_count']
)}
"""
REVOCATION_MATRIX_DISTRIBUTION_BY_GENDER_COMPARISON_VIEW_BUILDER = SimpleBigQueryViewBuilder(
dataset_id=dataset_config.VIEWS_DATASET,
view_id=REVOCATION_MATRIX_DISTRIBUTION_BY_GENDER_COMPARISON_VIEW_NAME,
view_query_template=REVOCATION_MATRIX_DISTRIBUTION_BY_GENDER_COMPARISON_QUERY_TEMPLATE,
description=REVOCATION_MATRIX_DISTRIBUTION_BY_GENDER_COMPARISON_DESCRIPTION,
view_dataset=state_dataset_config.DASHBOARD_VIEWS_DATASET,
view=REVOCATIONS_MATRIX_DISTRIBUTION_BY_GENDER_VIEW_BUILDER.view_id,
)
if __name__ == "__main__":
with local_project_id_override(GCP_PROJECT_STAGING):
REVOCATION_MATRIX_DISTRIBUTION_BY_GENDER_COMPARISON_VIEW_BUILDER.build_and_print()
COALESCE(termination_reason, 'INTERNAL_UNKNOWN') AS end_reason,
'SUPERVISION' AS compartment_level_1,
metric_type AS metric_source,
ROW_NUMBER() OVER(PARTITION BY person_id, termination_date ORDER BY COALESCE(priority, 999)) AS rn
FROM `{project_id}.{materialized_metrics_dataset}.most_recent_supervision_termination_metrics_materialized` m
LEFT JOIN `{project_id}.{analyst_dataset}.release_termination_reason_dedup_priority` AS d
ON d.end_reason = m.termination_reason
AND d.metric_source = m.metric_type
WHERE end_reason IS NOT NULL
)
SELECT
* EXCEPT (rn)
FROM release_metric_cte
WHERE rn = 1
ORDER BY 1,2
"""
COMPARTMENT_SESSION_END_REASONS_VIEW_BUILDER = SimpleBigQueryViewBuilder(
dataset_id=ANALYST_VIEWS_DATASET,
view_id=COMPARTMENT_SESSION_END_REASONS_VIEW_NAME,
view_query_template=COMPARTMENT_SESSION_END_REASONS_QUERY_TEMPLATE,
description=COMPARTMENT_SESSION_END_REASONS_VIEW_DESCRIPTION,
materialized_metrics_dataset=DATAFLOW_METRICS_MATERIALIZED_DATASET,
analyst_dataset=ANALYST_VIEWS_DATASET,
should_materialize=True,
)
if __name__ == "__main__":
with local_project_id_override(GCP_PROJECT_STAGING):
COMPARTMENT_SESSION_END_REASONS_VIEW_BUILDER.build_and_print()
JOIN `{project_id}.{reference_dataset}.most_recent_job_id_by_metric_and_state_code` job
USING (state_code, job_id, year, month, metric_period_months, metric_type),
{district_dimension}
WHERE methodology = 'PERSON'
AND metric_period_months = 0
AND person_id IS NOT NULL
AND year >= EXTRACT(YEAR FROM DATE_SUB(CURRENT_DATE(), INTERVAL 4 YEAR))
-- Get population count for the last day of the month
AND date_of_stay = DATE_SUB(DATE_ADD(DATE(year, month, 1), INTERVAL 1 MONTH), INTERVAL 1 DAY)
GROUP BY state_code, year, month, district
) inc_pop
USING (state_code, year, month, district)
WHERE year >= EXTRACT(YEAR FROM DATE_SUB(CURRENT_DATE(), INTERVAL 3 YEAR))
AND district IS NOT NULL
ORDER BY state_code, district, year, month
"""
ADMISSIONS_VERSUS_RELEASES_BY_MONTH_VIEW_BUILDER = SimpleBigQueryViewBuilder(
dataset_id=dataset_config.DASHBOARD_VIEWS_DATASET,
view_id=ADMISSIONS_VERSUS_RELEASES_BY_MONTH_VIEW_NAME,
view_query_template=ADMISSIONS_VERSUS_RELEASES_BY_MONTH_QUERY_TEMPLATE,
description=ADMISSIONS_VERSUS_RELEASES_BY_MONTH_DESCRIPTION,
reference_dataset=dataset_config.REFERENCE_TABLES_DATASET,
metrics_dataset=dataset_config.DATAFLOW_METRICS_DATASET,
district_dimension=bq_utils.unnest_district(
district_column='county_of_residence'))
if __name__ == '__main__':
with local_project_id_override(GCP_PROJECT_STAGING):
ADMISSIONS_VERSUS_RELEASES_BY_MONTH_VIEW_BUILDER.build_and_print()
from recidiviz.calculator.query.state import dataset_config
from recidiviz.utils.environment import GCP_PROJECT_STAGING
from recidiviz.utils.metadata import local_project_id_override
AGENT_EXTERNAL_ID_TO_FULL_NAMES_VIEW_NAME = "agent_external_id_to_full_name"
AGENT_EXTERNAL_ID_TO_FULL_NAMES_VIEW_DESCRIPTION = """Agent information table that adds links distinct external ids for state agents back to their full names"""
AGENT_EXTERNAL_ID_TO_FULL_NAMES_QUERY_TEMPLATE = """
/*{description}*/
SELECT DISTINCT
state_code,
agent_external_id,
full_name,
given_names,
surname
FROM `{project_id}.{reference_views_dataset}.augmented_agent_info`
"""
AGENT_EXTERNAL_ID_TO_FULL_NAMES_VIEW_BUILDER = SimpleBigQueryViewBuilder(
dataset_id=dataset_config.REFERENCE_VIEWS_DATASET,
view_id=AGENT_EXTERNAL_ID_TO_FULL_NAMES_VIEW_NAME,
view_query_template=AGENT_EXTERNAL_ID_TO_FULL_NAMES_QUERY_TEMPLATE,
description=AGENT_EXTERNAL_ID_TO_FULL_NAMES_VIEW_DESCRIPTION,
reference_views_dataset=dataset_config.REFERENCE_VIEWS_DATASET,
)
if __name__ == "__main__":
with local_project_id_override(GCP_PROJECT_STAGING):
AGENT_EXTERNAL_ID_TO_FULL_NAMES_VIEW_BUILDER.build_and_print()
INCARCERATION_POPULATION_PERSON_LEVEL_EXTERNAL_COMPARISON_VIEW_NAME = (
"incarceration_population_person_level_external_comparison")
INCARCERATION_POPULATION_PERSON_LEVEL_EXTERNAL_COMPARISON_DESCRIPTION = """
Comparison of values between internal and external lists of end of month person-level incarceration
populations.
"""
INCARCERATION_POPULATION_PERSON_LEVEL_EXTERNAL_COMPARISON_QUERY_TEMPLATE = f"""
/*{{description}}*/
{incarceration_population_person_level_query(include_unmatched_people=True)}
"""
INCARCERATION_POPULATION_PERSON_LEVEL_EXTERNAL_COMPARISON_VIEW_BUILDER = SimpleBigQueryViewBuilder(
dataset_id=dataset_config.VIEWS_DATASET,
view_id=INCARCERATION_POPULATION_PERSON_LEVEL_EXTERNAL_COMPARISON_VIEW_NAME,
view_query_template=
INCARCERATION_POPULATION_PERSON_LEVEL_EXTERNAL_COMPARISON_QUERY_TEMPLATE,
description=
INCARCERATION_POPULATION_PERSON_LEVEL_EXTERNAL_COMPARISON_DESCRIPTION,
external_accuracy_dataset=dataset_config.EXTERNAL_ACCURACY_DATASET,
materialized_metrics_dataset=state_dataset_config.
DATAFLOW_METRICS_MATERIALIZED_DATASET,
)
if __name__ == "__main__":
with local_project_id_override(GCP_PROJECT_STAGING):
INCARCERATION_POPULATION_PERSON_LEVEL_EXTERNAL_COMPARISON_VIEW_BUILDER.build_and_print(
)
gender,
facility,
compartment_level_1,
compartment_level_2,
report_month,
FROM paid_status_on_compartment_sessions
-- Only use the pay_flag for the county jails
WHERE pay_flag OR (facility NOT IN ('COUNTY JAIL', '{disaggregated_county_jails}'))
ORDER BY state_code, person_id, report_month
"""
US_ID_MONTHLY_PAID_INCARCERATION_POPULATION_VIEW_BUILDER = SimpleBigQueryViewBuilder(
dataset_id=dataset_config.POPULATION_PROJECTION_DATASET,
view_id=US_ID_MONTHLY_PAID_INCARCERATION_POPULATION_VIEW_NAME,
view_query_template=
US_ID_MONTHLY_PAID_INCARCERATION_POPULATION_QUERY_TEMPLATE,
description=US_ID_MONTHLY_PAID_INCARCERATION_POPULATION_VIEW_DESCRIPTION,
analyst_dataset=dataset_config.ANALYST_VIEWS_DATASET,
population_projection_dataset=dataset_config.POPULATION_PROJECTION_DATASET,
state_base_dataset=dataset_config.STATE_BASE_DATASET,
static_reference_dataset=dataset_config.STATIC_REFERENCE_TABLES_DATASET,
disaggregated_county_jails="', '".join(
US_ID_INCARCERATION_DISAGGREGATED_COUNTY_JAILS),
should_materialize=False,
)
if __name__ == "__main__":
with local_project_id_override(GCP_PROJECT_STAGING):
US_ID_MONTHLY_PAID_INCARCERATION_POPULATION_VIEW_BUILDER.build_and_print(
)
FROM `{project_id}.{analyst_dataset}.compartment_sessions_materialized` release_session
LEFT JOIN `{project_id}.{analyst_dataset}.compartment_sessions_materialized` reincarceration_session
ON reincarceration_session.person_id = release_session.person_id
AND reincarceration_session.compartment_level_1 IN ('INCARCERATION', 'INCARCERATION_OUT_OF_STATE')
AND reincarceration_session.start_date > release_session.end_date
AND reincarceration_session.compartment_level_2 NOT IN ('PAROLE_BOARD_HOLD', 'TEMPORARY_CUSTODY','SHOCK_INCARCERATION','COMMUNITY_PLACEMENT_PROGRAM')
AND (reincarceration_session.inflow_from_level_1 NOT IN ('INCARCERATION', 'INCARCERATION_OUT_OF_STATE')
OR reincarceration_session.inflow_from_level_2 IN ('PAROLE_BOARD_HOLD', 'TEMPORARY_CUSTODY','SHOCK_INCARCERATION','COMMUNITY_PLACEMENT_PROGRAM'))
WHERE release_session.compartment_level_1 IN ('INCARCERATION', 'INCARCERATION_OUT_OF_STATE')
AND release_session.compartment_level_2 NOT IN ('PAROLE_BOARD_HOLD','TEMPORARY_CUSTODY','SHOCK_INCARCERATION','COMMUNITY_PLACEMENT_PROGRAM')
AND (release_session.outflow_to_level_1 IN ('SUPERVISION','SUPERVISION_OUT_OF_STATE','RELEASE', 'PENDING_SUPERVISION') OR
release_session.outflow_to_level_2 = 'COMMUNITY_PLACEMENT_PROGRAM')
)
SELECT * EXCEPT(rn)
FROM cte
WHERE rn = 1
"""
REINCARCERATION_SESSIONS_FROM_SESSIONS_VIEW_BUILDER = SimpleBigQueryViewBuilder(
dataset_id=ANALYST_VIEWS_DATASET,
view_id=REINCARCERATION_SESSIONS_FROM_SESSIONS_VIEW_NAME,
view_query_template=REINCARCERATION_SESSIONS_FROM_SESSIONS_QUERY_TEMPLATE,
description=REINCARCERATION_SESSIONS_FROM_SESSIONS_VIEW_DESCRIPTION,
analyst_dataset=ANALYST_VIEWS_DATASET,
should_materialize=True,
)
if __name__ == "__main__":
with local_project_id_override(GCP_PROJECT_STAGING):
REINCARCERATION_SESSIONS_FROM_SESSIONS_VIEW_BUILDER.build_and_print()
district
FROM (
SELECT
state_code, year, month,
person_id, source_violation_type,
supervision_type, district,
-- Only use most recent revocation per person/supervision_type/metric_period_months
ROW_NUMBER() OVER (PARTITION BY state_code, year, month, person_id, supervision_type, district
ORDER BY revocation_admission_date DESC) AS revocation_rank
FROM `{project_id}.{reference_dataset}.event_based_revocations`
)
WHERE revocation_rank = 1
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, district, supervision_type
"""
REVOCATIONS_BY_VIOLATION_TYPE_BY_MONTH_VIEW_BUILDER = SimpleBigQueryViewBuilder(
dataset_id=dataset_config.DASHBOARD_VIEWS_DATASET,
view_id=REVOCATIONS_BY_VIOLATION_TYPE_BY_MONTH_VIEW_NAME,
view_query_template=REVOCATIONS_BY_VIOLATION_TYPE_BY_MONTH_QUERY_TEMPLATE,
description=REVOCATIONS_BY_VIOLATION_TYPE_BY_MONTH_DESCRIPTION,
reference_dataset=dataset_config.REFERENCE_TABLES_DATASET,
)
if __name__ == '__main__':
with local_project_id_override(GCP_PROJECT_STAGING):
REVOCATIONS_BY_VIOLATION_TYPE_BY_MONTH_VIEW_BUILDER.build_and_print()
from recidiviz.utils.environment import GAE_PROJECT_STAGING
from recidiviz.utils.metadata import local_project_id_override
from recidiviz.validation.views import dataset_config
SUPERVISION_TERMINATION_PRIOR_TO_START_VIEW_NAME = 'supervision_termination_prior_to_start'
SUPERVISION_TERMINATION_PRIOR_TO_START_DESCRIPTION = """ Supervision termination dates prior to start dates """
SUPERVISION_TERMINATION_PRIOR_TO_START_QUERY_TEMPLATE = \
"""
/*{description}*/
SELECT *, state_code as region_code
FROM `{project_id}.{state_dataset}.state_supervision_period`
WHERE termination_date IS NOT NULL
AND termination_date < start_date
AND external_id IS NOT NULL
ORDER BY start_date, region_code, external_id
"""
SUPERVISION_TERMINATION_PRIOR_TO_START_VIEW_BUILDER = SimpleBigQueryViewBuilder(
dataset_id=dataset_config.VIEWS_DATASET,
view_id=SUPERVISION_TERMINATION_PRIOR_TO_START_VIEW_NAME,
view_query_template=SUPERVISION_TERMINATION_PRIOR_TO_START_QUERY_TEMPLATE,
description=SUPERVISION_TERMINATION_PRIOR_TO_START_DESCRIPTION,
state_dataset=state_dataset_config.STATE_BASE_DATASET,
)
if __name__ == '__main__':
with local_project_id_override(GAE_PROJECT_STAGING):
SUPERVISION_TERMINATION_PRIOR_TO_START_VIEW_BUILDER.build_and_print()
