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