def setUp(self): self.mock_project_id = 'fake-recidiviz-project' self.mock_dataset_name = 'base_dataset' self.mock_dataset = bigquery.dataset.DatasetReference( self.mock_project_id, self.mock_dataset_name) self.client_patcher = mock.patch( 'recidiviz.calculator.query.state.dashboard_export_manager.bq_utils.client') self.mock_client = self.client_patcher.start().return_value self.mock_view = bqview.BigQueryView( view_id='test_view', view_query='SELECT NULL LIMIT 0' ) views_to_export = [self.mock_view] dashboard_export_config_values = { 'STATES_TO_EXPORT': ['US_CA'], 'VIEWS_TO_EXPORT': views_to_export, } self.dashboard_export_config_patcher = mock.patch( 'recidiviz.calculator.query.state.dashboard_export_manager.dashboard_export_config', **dashboard_export_config_values) self.mock_export_config = self.dashboard_export_config_patcher.start() view_manager_config_values = { 'VIEWS_TO_UPDATE': views_to_export } self.view_manager_config_patcher = mock.patch( 'recidiviz.calculator.query.state.dashboard_export_manager.view_manager', **view_manager_config_values ) self.mock_view_manager = self.view_manager_config_patcher.start()
def setUp(self): fakes.use_in_memory_sqlite_database(JailsBase) sample_views = [ { 'view_id': 'my_fake_view', 'view_query': 'SELECT NULL LIMIT 0' }, { 'view_id': 'my_other_fake_view', 'view_query': 'SELECT NULL LIMIT 0' }, ] self.mock_views = [ bqview.BigQueryView(**view) for view in sample_views ] self.mock_project_id = 'fake-recidiviz-project' self.mock_view_dataset_name = 'my_views_dataset' self.mock_dataset = bigquery.dataset.DatasetReference( self.mock_project_id, self.mock_view_dataset_name) self.bq_utils_patcher = mock.patch( 'recidiviz.calculator.query.county.view_manager.bq_utils') self.mock_bq_utils = self.bq_utils_patcher.start() self.client_patcher = mock.patch( 'recidiviz.calculator.query.county.view_manager.bq_utils.client') self.mock_client = self.client_patcher.start().return_value
def setUp(self): self.mock_project_id = 'fake-recidiviz-project' self.mock_dataset_id = 'fake-dataset' self.mock_table_id = 'test_table' self.mock_dataset = bigquery.dataset.DatasetReference( self.mock_project_id, self.mock_dataset_id) self.mock_table = self.mock_dataset.table(self.mock_table_id) self.client_patcher = mock.patch( 'recidiviz.calculator.query.bq_utils.client') self.mock_client = self.client_patcher.start().return_value self.mock_view = bqview.BigQueryView(view_id='test_view', view_query='SELECT NULL LIMIT 0')
FROM `{project_id}.{metrics_dataset}.supervision_revocation_metrics`) UNION ALL (SELECT DISTINCT job_id, year, month, metric_period_months, state_code, 'SUPERVISION_REVOCATION_ANALYSIS' as metric_type FROM `{project_id}.{metrics_dataset}.supervision_revocation_analysis_metrics`) UNION ALL (SELECT DISTINCT job_id, year, month, metric_period_months, state_code, 'SUPERVISION_REVOCATION_VIOLATION' as metric_type FROM `{project_id}.{metrics_dataset}.supervision_revocation_violation_type_analysis_metrics`) UNION ALL (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, 'PROGRAM_REFERRAL' as metric_type FROM `{project_id}.{metrics_dataset}.program_referral_metrics`) ) ) WHERE recency_rank = 1 ORDER BY metric_type, state_code, year, month, metric_period_months """.format( description=MOST_RECENT_JOB_ID_BY_METRIC_AND_STATE_CODE_DESCRIPTION, project_id=PROJECT_ID, metrics_dataset=METRICS_DATASET, ) MOST_RECENT_JOB_ID_BY_METRIC_AND_STATE_CODE_VIEW = bqview.BigQueryView( view_id=MOST_RECENT_JOB_ID_BY_METRIC_AND_STATE_CODE_VIEW_NAME, view_query=MOST_RECENT_JOB_ID_BY_METRIC_AND_STATE_CODE_QUERY) if __name__ == '__main__': print(MOST_RECENT_JOB_ID_BY_METRIC_AND_STATE_CODE_VIEW.view_id) print(MOST_RECENT_JOB_ID_BY_METRIC_AND_STATE_CODE_VIEW.view_query)
AND revocation_type = 'REINCARCERATION' AND source_violation_type IS NULL AND supervising_officer_external_id IS NULL AND age_bucket IS NULL AND race IS NULL AND ethnicity IS NULL AND gender IS NULL AND person_id IS NULL AND person_external_id IS NULL AND year = EXTRACT(YEAR FROM CURRENT_DATE('US/Pacific')) AND month = EXTRACT(MONTH FROM CURRENT_DATE('US/Pacific')) AND job.metric_type = 'SUPERVISION_REVOCATION_ANALYSIS' ) WHERE supervision_type IN ('ALL', 'DUAL', 'PAROLE', 'PROBATION') GROUP BY state_code, violation_type, reported_violations, supervision_type, charge_category, district, metric_period_months ORDER BY state_code, district, metric_period_months, violation_type, reported_violations, supervision_type, charge_category """.format( description=REVOCATIONS_MATRIX_CELLS_DESCRIPTION, project_id=PROJECT_ID, metrics_dataset=METRICS_DATASET, views_dataset=VIEWS_DATASET, ) REVOCATIONS_MATRIX_CELLS_VIEW = bqview.BigQueryView( view_id=REVOCATIONS_MATRIX_CELLS_VIEW_NAME, view_query=REVOCATIONS_MATRIX_CELLS_QUERY) if __name__ == '__main__': print(REVOCATIONS_MATRIX_CELLS_VIEW.view_id) print(REVOCATIONS_MATRIX_CELLS_VIEW.view_query)
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, officer_external_id, district, metric_period_months FROM case_terminations_expanded, UNNEST([1, 3, 6, 12, 36]) AS metric_period_months WHERE termination_month_trunc >= DATE_SUB(DATE_TRUNC(CURRENT_DATE('US/Pacific'), MONTH), INTERVAL metric_period_months - 1 MONTH) ) WHERE supervision_type IN ('ALL', 'PROBATION', 'PAROLE') AND district != 'ALL' GROUP BY state_code, metric_period_months, supervision_type, officer_external_id, district ORDER BY state_code, supervision_type, district, officer_external_id, metric_period_months """.format( description=CASE_TERMINATIONS_BY_TYPE_BY_OFFICER_BY_PERIOD_DESCRIPTION, prep_expression=_get_query_prep_statement(project_id=PROJECT_ID, views_dataset=VIEWS_DATASET) ) CASE_TERMINATIONS_BY_TYPE_BY_OFFICER_BY_PERIOD_VIEW = bqview.BigQueryView( view_id=CASE_TERMINATIONS_BY_TYPE_BY_OFFICER_BY_PERIOD_VIEW_NAME, view_query=CASE_TERMINATIONS_BY_TYPE_BY_OFFICER_BY_PERIOD_QUERY) if __name__ == '__main__': print(CASE_TERMINATIONS_BY_TYPE_BY_OFFICER_BY_PERIOD_VIEW.view_id) print(CASE_TERMINATIONS_BY_TYPE_BY_OFFICER_BY_PERIOD_VIEW.view_query)
US_MO_SENTENCE_STATUSES_QUERY = \ """ /*{description}*/ SELECT CONCAT(DOC, '-', CYC, '-', SEO) as sentence_external_id, CONCAT(DOC, '-', CYC, '-', SEO, '-', SSO) as sentence_status_external_id, SCD AS status_code, SY AS status_date, SDE AS status_description FROM `{project_id}.{reference_tables_dataset}.us_mo_tak026_tak025_sentence_statuses` WHERE DOC IS NOT NULL AND CYC IS NOT NULL AND SEO IS NOT NULL AND SSO IS NOT NULL """.format( description=US_MO_SENTENCE_STATUSES_DESCRIPTION, project_id=PROJECT_ID, reference_tables_dataset=REFERENCE_TABLES_DATASET, ) US_MO_SENTENCE_STATUSES_VIEW = bqview.BigQueryView( view_id=US_MO_SENTENCE_STATUSES_VIEW_NAME, view_query=US_MO_SENTENCE_STATUSES_QUERY) if __name__ == '__main__': print(US_MO_SENTENCE_STATUSES_VIEW.view_id) print(US_MO_SENTENCE_STATUSES_VIEW.view_query)
FULL JOIN AdmittedTable ON PersonCountTable.day = AdmittedTable.day AND PersonCountTable.fips = AdmittedTable.fips FULL JOIN ReleasedTable ON PersonCountTable.day = ReleasedTable.day AND PersonCountTable.fips = ReleasedTable.fips LEFT JOIN PersonCountTableNoZero ON PersonCountTable.day = PersonCountTableNoZero.day AND PersonCountTable.bond_amount_category = PersonCountTableNoZero.bond_amount_category_no_zero AND PersonCountTable.fips = PersonCountTableNoZero.fips JOIN `{project_id}.{views_dataset}.{county_names_view}` CountyNames ON PersonCountTable.fips = CountyNames.fips ORDER BY day DESC, fips, bond_amount_category """.format( description=BOND_AMOUNTS_ALL_BOOKINGS_BINS_DESCRIPTION, project_id=PROJECT_ID, views_dataset=VIEWS_DATASET, bond_amounts_all_bookings_view=BOND_AMOUNTS_ALL_BOOKINGS_VIEW.view_id, county_names_view=COUNTY_NAMES_VIEW.view_id ) BOND_AMOUNTS_ALL_BOOKINGS_BINS_VIEW = bqview.BigQueryView( view_id=BOND_AMOUNTS_ALL_BOOKINGS_BINS_VIEW_NAME, view_query=BOND_AMOUNTS_ALL_BOOKINGS_BINS_QUERY) if __name__ == '__main__': print(BOND_AMOUNTS_ALL_BOOKINGS_BINS_VIEW.view_id) print(BOND_AMOUNTS_ALL_BOOKINGS_BINS_VIEW.view_query)
AND PersonCountTable.fips = AdmittedTable.fips AND PersonCountTable.race = AdmittedTable.race AND PersonCountTable.gender = AdmittedTable.gender FULL JOIN ReleasedTable ON PersonCountTable.day = ReleasedTable.day AND PersonCountTable.fips = ReleasedTable.fips AND PersonCountTable.race = ReleasedTable.race AND PersonCountTable.gender = ReleasedTable.gender JOIN `{project_id}.{views_dataset}.{county_names_view}` CountyNames ON PersonCountTable.fips = CountyNames.fips ORDER BY day DESC, fips, race, gender """.format( description=POPULATION_ADMISSIONS_RELEASES_RACE_GENDER_DESCRIPTION, project_id=PROJECT_ID, base_dataset=BASE_DATASET, views_dataset=VIEWS_DATASET, county_names_view=COUNTY_NAMES_VIEW.view_id, booking_table=Booking.__tablename__, person_table=Person.__tablename__ ) POPULATION_ADMISSIONS_RELEASES_RACE_GENDER_VIEW = bqview.BigQueryView( view_id=POPULATION_ADMISSIONS_RELEASES_RACE_GENDER_VIEW_NAME, view_query=POPULATION_ADMISSIONS_RELEASES_RACE_GENDER_QUERY) if __name__ == '__main__': print(POPULATION_ADMISSIONS_RELEASES_RACE_GENDER_VIEW.view_id) print(POPULATION_ADMISSIONS_RELEASES_RACE_GENDER_VIEW.view_query)
SELECT BookingCountTable.day, BookingCountTable.fips, BookingCountTable.most_severe_charge, booking_count, admitted, released, CountyNames.state, CountyNames.county_name FROM BookingCountTable FULL JOIN AdmittedTable ON BookingCountTable.day = AdmittedTable.day AND BookingCountTable.fips = AdmittedTable.fips AND BookingCountTable.most_severe_charge = AdmittedTable.most_severe_charge FULL JOIN ReleasedTable ON BookingCountTable.day = ReleasedTable.day AND BookingCountTable.fips = ReleasedTable.fips AND BookingCountTable.most_severe_charge = ReleasedTable.most_severe_charge JOIN `{project_id}.{views_dataset}.{county_names_view}` CountyNames ON BookingCountTable.fips = CountyNames.fips ORDER BY day DESC, fips """.format( description=CHARGE_SEVERITY_COUNTS_ALL_BOOKINGS_DESCRIPTION, project_id=PROJECT_ID, base_dataset=BASE_DATASET, views_dataset=VIEWS_DATASET, booking_table=Booking.__tablename__, person_table=Person.__tablename__, charge_severity_all_bookings_view=CHARGE_SEVERITY_ALL_BOOKINGS_VIEW.view_id, county_names_view=COUNTY_NAMES_VIEW.view_id ) CHARGE_SEVERITY_COUNTS_ALL_BOOKINGS_VIEW = bqview.BigQueryView( view_id=CHARGE_SEVERITY_COUNTS_ALL_BOOKINGS_VIEW_NAME, view_query=CHARGE_SEVERITY_COUNTS_ALL_BOOKINGS_QUERY) if __name__ == '__main__': print(CHARGE_SEVERITY_COUNTS_ALL_BOOKINGS_VIEW.view_id) print(CHARGE_SEVERITY_COUNTS_ALL_BOOKINGS_VIEW.view_query)
AND supervising_officer_external_id IS NULL AND age_bucket IS NULL AND race IS NULL AND ethnicity IS NULL AND gender IS NULL AND person_id IS NULL AND person_external_id IS NULL AND year >= EXTRACT(YEAR FROM DATE_ADD(CURRENT_DATE(), INTERVAL - 3 YEAR)) AND job.metric_type = 'SUPERVISION_REVOCATION_ANALYSIS' GROUP BY state_code, year, month, reported_violations, supervision_type, charge_category, district ) revocation_response_with_violation USING (state_code, year, month, reported_violations, supervision_type, charge_category, district) ) -- Only include rows that have revocations WHERE total_revocations > 0 ORDER BY state_code, year, month, district, supervision_type, violation_type, reported_violations """.format( description=REVOCATIONS_MATRIX_BY_MONTH_DESCRIPTION, project_id=PROJECT_ID, metrics_dataset=METRICS_DATASET, views_dataset=VIEWS_DATASET, ) REVOCATIONS_MATRIX_BY_MONTH_VIEW = bqview.BigQueryView( view_id=REVOCATIONS_MATRIX_BY_MONTH_VIEW_NAME, view_query=REVOCATIONS_MATRIX_BY_MONTH_QUERY) if __name__ == '__main__': print(REVOCATIONS_MATRIX_BY_MONTH_VIEW.view_id) print(REVOCATIONS_MATRIX_BY_MONTH_VIEW.view_query)
AND assessment_score_bucket IS NULL AND assessment_type = 'LSIR' AND age_bucket IS NULL AND race IS NULL AND ethnicity IS NULL AND gender IS NULL AND case_type IS NULL AND person_id IS NULL AND person_external_id IS NULL AND supervising_officer_external_id IS NULL AND termination_reason IS NULL AND year = EXTRACT(YEAR FROM CURRENT_DATE('US/Pacific')) AND month = EXTRACT(MONTH FROM CURRENT_DATE('US/Pacific')) AND IFNULL(supervision_type, 'ALL') in ('ALL', 'PAROLE', 'PROBATION') AND job.metric_type = 'SUPERVISION_ASSESSMENT_CHANGE' ORDER BY state_code, district, supervision_type, metric_period_months """.format( description=AVERAGE_CHANGE_LSIR_SCORE_BY_PERIOD_DESCRIPTION, project_id=PROJECT_ID, metrics_dataset=METRICS_DATASET, views_dataset=VIEWS_DATASET, ) AVERAGE_CHANGE_LSIR_SCORE_BY_PERIOD_VIEW = bqview.BigQueryView( view_id=AVERAGE_CHANGE_LSIR_SCORE_BY_PERIOD_VIEW_NAME, view_query=AVERAGE_CHANGE_LSIR_SCORE_BY_PERIOD_QUERY) if __name__ == '__main__': print(AVERAGE_CHANGE_LSIR_SCORE_BY_PERIOD_VIEW.view_id) print(AVERAGE_CHANGE_LSIR_SCORE_BY_PERIOD_VIEW.view_query)
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 """.format( description=RESIDENT_POPULATION_COUNTS_DESCRIPTION, project_id=PROJECT_ID, vera_dataset=vera_view_constants.VERA_DATASET, iob_race_gender_pop_table=vera_view_constants.IOB_RACE_GENDER_POP_TABLE ) RESIDENT_POPULATION_COUNTS_VIEW = bqview.BigQueryView( view_id=RESIDENT_POPULATION_COUNTS_VIEW_NAME, view_query=RESIDENT_POPULATION_COUNTS_QUERY) if __name__ == '__main__': print(RESIDENT_POPULATION_COUNTS_VIEW.view_id) print(RESIDENT_POPULATION_COUNTS_VIEW.view_query)
AND race IS NULL AND ethnicity IS NULL AND gender IS NULL AND case_type IS NULL AND person_id IS NULL AND person_external_id IS NULL AND metric_period_months = 1 AND year >= EXTRACT(YEAR FROM DATE_ADD(CURRENT_DATE(), INTERVAL -3 YEAR)) AND job.metric_type = 'SUPERVISION_REVOCATION' ) rev USING (state_code, year, month, supervision_type, supervising_district_external_id) WHERE supervision_type in ('PAROLE', 'PROBATION') ) GROUP BY state_code, year, month, district ORDER BY state_code, year, month, district """.format( description=REVOCATIONS_BY_SUPERVISION_TYPE_BY_MONTH_DESCRIPTION, project_id=PROJECT_ID, metrics_dataset=METRICS_DATASET, views_dataset=VIEWS_DATASET, ) REVOCATIONS_BY_SUPERVISION_TYPE_BY_MONTH_VIEW = bqview.BigQueryView( view_id=REVOCATIONS_BY_SUPERVISION_TYPE_BY_MONTH_VIEW_NAME, view_query=REVOCATIONS_BY_SUPERVISION_TYPE_BY_MONTH_QUERY ) if __name__ == '__main__': print(REVOCATIONS_BY_SUPERVISION_TYPE_BY_MONTH_VIEW.view_id) print(REVOCATIONS_BY_SUPERVISION_TYPE_BY_MONTH_VIEW.view_query)
AND current_address NOT LIKE '%311 S 4TH ST STE 101%ND%' AND current_address NOT LIKE '%222 WALNUT ST W%ND%' AND current_address NOT LIKE '%709 DAKOTA AVE STE D%ND%' AND current_address NOT LIKE '%113 MAIN AVE E STE B%ND%' AND current_address NOT LIKE '%712 5TH AVE%ND%' AND current_address NOT LIKE '%705 EAST HIGHLAND DR. SUITE B%ND%' AND current_address NOT LIKE '%705 E HIGHLAND DR STE B%ND%' AND current_address NOT LIKE '%135 SIMS ST STE 205%ND%' AND current_address NOT LIKE '%638 COOPER AVE%ND%' AND current_address NOT LIKE '%206 MAIN ST W%ND%' AND current_address NOT LIKE '%519 MAIN ST STE 8%ND%' AND current_address NOT LIKE '%115 S 5TH ST STE A%ND%' AND current_address NOT LIKE '%117 HWY 49%ND%' AND current_address NOT LIKE '%JAIL%ND%')) WHERE recency_rank = 1) people_with_last_known_address ON person.person_id = people_with_last_known_address.person_id ORDER BY person_id ASC """.format( description=PERSONS_WITH_LAST_KNOWN_ADDRESS_VIEW_DESCRIPTION, project_id=PROJECT_ID, base_dataset=BASE_DATASET, ) PERSONS_WITH_LAST_KNOWN_ADDRESS_VIEW = bqview.BigQueryView( view_id=PERSONS_WITH_LAST_KNOWN_ADDRESS_VIEW_NAME, view_query=PERSONS_WITH_LAST_KNOWN_ADDRESS_VIEW_QUERY) if __name__ == '__main__': print(PERSONS_WITH_LAST_KNOWN_ADDRESS_VIEW.view_id) print(PERSONS_WITH_LAST_KNOWN_ADDRESS_VIEW.view_query)
AND age_bucket IS NULL AND stay_length_bucket IS NULL AND race IS NULL AND ethnicity IS NULL AND gender IS NULL AND person_id IS NULL AND person_external_id IS NULL AND release_facility IS NULL AND return_type IS NULL AND from_supervision_type IS NULL AND source_violation_type IS NULL AND county_of_residence IS NULL AND EXTRACT(YEAR FROM start_date) >= EXTRACT(YEAR FROM DATE_ADD(CURRENT_DATE(), INTERVAL -3 YEAR)) AND EXTRACT(MONTH FROM start_date) = EXTRACT(MONTH FROM end_date) AND job.metric_type = 'RECIDIVISM_LIBERTY' ORDER BY state_code, year, month """.format( description=AVERAGE_DAYS_AT_LIBERTY_BY_MONTH_DESCRIPTION, project_id=PROJECT_ID, metrics_dataset=METRICS_DATASET, views_dataset=VIEWS_DATASET, ) AVERAGE_DAYS_AT_LIBERTY_BY_MONTH_VIEW = bqview.BigQueryView( view_id=AVERAGE_DAYS_AT_LIBERTY_BY_MONTH_VIEW_NAME, view_query=AVERAGE_DAYS_AT_LIBERTY_BY_MONTH_QUERY) if __name__ == '__main__': print(AVERAGE_DAYS_AT_LIBERTY_BY_MONTH_VIEW.view_id) print(AVERAGE_DAYS_AT_LIBERTY_BY_MONTH_VIEW.view_query)
WHERE methodology = 'PERSON' AND age_bucket IS NULL AND race IS NULL AND ethnicity IS NULL AND gender IS NULL AND person_id IS NULL AND person_external_id IS NULL AND return_type IS NULL AND from_supervision_type IS NULL AND source_violation_type IS NULL AND release_facility IS NULL AND stay_length_bucket IS NOT NULL AND release_cohort = EXTRACT(YEAR FROM DATE_ADD(CURRENT_DATE(), INTERVAL -2 YEAR)) AND follow_up_period = 1 AND job.metric_type = 'RECIDIVISM_RATE' ORDER BY state_code, release_cohort, follow_up_period, stay_length_bucket """.format( description=REINCARCERATION_RATE_BY_STAY_LENGTH_DESCRIPTION, project_id=PROJECT_ID, metrics_dataset=METRICS_DATASET, views_dataset=VIEWS_DATASET ) REINCARCERATION_RATE_BY_STAY_LENGTH_VIEW = bqview.BigQueryView( view_id=REINCARCERATION_RATE_BY_STAY_LENGTH_VIEW_NAME, view_query=REINCARCERATION_RATE_BY_STAY_LENGTH_QUERY) if __name__ == '__main__': print(REINCARCERATION_RATE_BY_STAY_LENGTH_VIEW.view_id) print(REINCARCERATION_RATE_BY_STAY_LENGTH_VIEW.view_query)
AND race IS NULL AND ethnicity IS NULL AND gender IS NULL AND person_id IS NULL AND person_external_id IS NULL AND specialized_purpose_for_incarceration IS NULL AND admission_reason_raw_text IS NULL AND admission_date IS NULL AND supervision_type_at_admission IS NULL AND year = EXTRACT(YEAR FROM CURRENT_DATE('US/Pacific')) AND month = EXTRACT(MONTH FROM CURRENT_DATE('US/Pacific')) AND job.metric_type = 'INCARCERATION_ADMISSION' GROUP BY state_code, metric_period_months ) adm USING (state_code, metric_period_months, supervision_type, district) ORDER BY state_code, supervision_type, district, metric_period_months """.format( description=ADMISSIONS_BY_TYPE_BY_PERIOD_DESCRIPTION, project_id=PROJECT_ID, views_dataset=VIEWS_DATASET, metrics_dataset=METRICS_DATASET, ) ADMISSIONS_BY_TYPE_BY_PERIOD_VIEW = bqview.BigQueryView( view_id=ADMISSIONS_BY_TYPE_BY_PERIOD_VIEW_NAME, view_query=ADMISSIONS_BY_TYPE_BY_PERIOD_QUERY) if __name__ == '__main__': print(ADMISSIONS_BY_TYPE_BY_PERIOD_VIEW.view_id) print(ADMISSIONS_BY_TYPE_BY_PERIOD_VIEW.view_query)
AND revocation_type IS NULL AND age_bucket IS NULL AND race IS NULL AND ethnicity IS NULL AND gender IS NULL AND case_type IS NULL AND person_id IS NULL AND person_external_id IS NULL AND year = EXTRACT(YEAR FROM CURRENT_DATE('US/Pacific')) AND month = EXTRACT(MONTH FROM CURRENT_DATE('US/Pacific')) AND job.metric_type = 'SUPERVISION_REVOCATION' GROUP BY state_code, year, month, supervision_type, supervising_district_external_id, supervising_officer_external_id, metric_period_months ) rev USING (state_code, supervision_type, supervising_district_external_id, supervising_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 """.format( description=REVOCATIONS_BY_OFFICER_BY_PERIOD_DESCRIPTION, project_id=PROJECT_ID, metrics_dataset=METRICS_DATASET, views_dataset=VIEWS_DATASET, ) REVOCATIONS_BY_OFFICER_BY_PERIOD_VIEW = bqview.BigQueryView( view_id=REVOCATIONS_BY_OFFICER_BY_PERIOD_VIEW_NAME, view_query=REVOCATIONS_BY_OFFICER_BY_PERIOD_QUERY) if __name__ == '__main__': print(REVOCATIONS_BY_OFFICER_BY_PERIOD_VIEW.view_id) print(REVOCATIONS_BY_OFFICER_BY_PERIOD_VIEW.view_query)
AND supervising_officer_external_id IS NULL AND age_bucket IS NULL AND race is NULL AND ethnicity IS NOT NULL AND gender IS NULL AND person_id IS NULL AND person_external_id IS NULL AND year = EXTRACT(YEAR FROM CURRENT_DATE('US/Pacific')) AND month = EXTRACT(MONTH FROM CURRENT_DATE('US/Pacific')) AND job.metric_type = 'PROGRAM_REFERRAL' ) ref USING (state_code, year, month, supervision_type, supervising_district_external_id, metric_period_months, race_or_ethnicity) WHERE supervision_type in ('ALL', 'PAROLE', 'PROBATION') AND state_code = 'US_ND' ORDER BY state_code, race_or_ethnicity, district, supervision_type, metric_period_months """.format( description= FTR_REFERRALS_BY_RACE_AND_ETHNICITY_BY_PERIOD_DESCRIPTION, project_id=PROJECT_ID, metrics_dataset=METRICS_DATASET, views_dataset=VIEWS_DATASET, ) FTR_REFERRALS_BY_RACE_AND_ETHNICITY_BY_PERIOD_VIEW = bqview.BigQueryView( view_id=FTR_REFERRALS_BY_RACE_AND_ETHNICITY_BY_PERIOD_VIEW_NAME, view_query=FTR_REFERRALS_BY_RACE_AND_ETHNICITY_BY_PERIOD_QUERY) if __name__ == '__main__': print(FTR_REFERRALS_BY_RACE_AND_ETHNICITY_BY_PERIOD_VIEW.view_id) print(FTR_REFERRALS_BY_RACE_AND_ETHNICITY_BY_PERIOD_VIEW.view_query)
AND ethnicity IS NULL AND gender IS NULL AND person_id IS NULL AND person_external_id IS NULL AND most_serious_offense_ncic_code IS NULL AND most_serious_offense_statute IS NULL AND admission_reason IS NULL AND admission_reason_raw_text IS NULL AND supervision_type_at_admission IS NULL AND job.metric_type = 'INCARCERATION_POPULATION' AND year = EXTRACT(YEAR FROM DATE_SUB(CURRENT_DATE('US/Pacific'), INTERVAL metric_period_months MONTH)) AND month = EXTRACT(MONTH FROM DATE_SUB(CURRENT_DATE('US/Pacific'), INTERVAL metric_period_months MONTH)) ) inc_pop USING (state_code, district, metric_period_months) ORDER BY state_code, metric_period_months, district """.format( description=ADMISSIONS_VERSUS_RELEASES_BY_PERIOD_DESCRIPTION, project_id=PROJECT_ID, metrics_dataset=METRICS_DATASET, views_dataset=VIEWS_DATASET, ) ADMISSIONS_VERSUS_RELEASES_BY_PERIOD_VIEW = bqview.BigQueryView( view_id=ADMISSIONS_VERSUS_RELEASES_BY_PERIOD_VIEW_NAME, view_query=ADMISSIONS_VERSUS_RELEASES_BY_PERIOD_QUERY ) if __name__ == '__main__': print(ADMISSIONS_VERSUS_RELEASES_BY_PERIOD_VIEW.view_id) print(ADMISSIONS_VERSUS_RELEASES_BY_PERIOD_VIEW.view_query)
SELECT person_id, SUBSTR(last_known_address, -9, 2) AS state_code, SUBSTR(county, 0, LENGTH(county) -7) AS county FROM `{project_id}.{views_dataset}.persons_with_last_known_address` as persons_with_address JOIN `{project_id}.{reference_tables_dataset}.zipcode_county_map` zipcode_county_map ON substr(persons_with_address.last_known_address, -5) = zipcode_county_map.zip_code WHERE persons_with_address.last_known_address IS NOT NULL ) WHERE state_code IN ('ND', 'MO') """.format( description=PERSONS_TO_RECENT_COUNTY_OF_RESIDENCE_DESCRIPTION, project_id=PROJECT_ID, views_dataset=VIEWS_DATASET, reference_tables_dataset=REFERENCE_TABLES_DATASET, ) PERSONS_TO_RECENT_COUNTY_OF_RESIDENCE_VIEW = bqview.BigQueryView( view_id=PERSONS_TO_RECENT_COUNTY_OF_RESIDENCE_VIEW_NAME, view_query=PERSONS_TO_RECENT_COUNTY_OF_RESIDENCE_QUERY) if __name__ == '__main__': print(PERSONS_TO_RECENT_COUNTY_OF_RESIDENCE_VIEW.view_id) print(PERSONS_TO_RECENT_COUNTY_OF_RESIDENCE_VIEW.view_query)
AND assessment_score_bucket IS NULL AND assessment_type IS NULL AND supervising_officer_external_id IS NULL AND age_bucket IS NULL AND race IS NULL AND ethnicity IS NULL AND gender IS NULL AND response_count IS NOT NULL AND most_severe_violation_type IS NOT NULL AND most_severe_violation_type_subtype IS NOT NULL AND year = EXTRACT(YEAR FROM CURRENT_DATE('US/Pacific')) AND month = EXTRACT(MONTH FROM CURRENT_DATE('US/Pacific')) AND job.metric_type = 'SUPERVISION_REVOCATION_VIOLATION' GROUP BY state_code, year, month, metric_period_months, supervision_type, case_type, district, response_count, violation_type ORDER BY year, month, metric_period_months, supervision_type, district, case_type, violation_type, response_count """.format( description=REVOCATIONS_MATRIX_DISTRIBUTION_BY_VIOLATION_DESCRIPTION, project_id=PROJECT_ID, metrics_dataset=METRICS_DATASET, views_dataset=VIEWS_DATASET, ) REVOCATIONS_MATRIX_DISTRIBUTION_BY_VIOLATION_VIEW = bqview.BigQueryView( view_id=REVOCATIONS_MATRIX_DISTRIBUTION_BY_VIOLATION_VIEW_NAME, view_query=REVOCATIONS_MATRIX_DISTRIBUTION_BY_VIOLATION_QUERY) if __name__ == '__main__': print(REVOCATIONS_MATRIX_DISTRIBUTION_BY_VIOLATION_VIEW.view_id) print(REVOCATIONS_MATRIX_DISTRIBUTION_BY_VIOLATION_VIEW.view_query)
AND supervising_officer_external_id IS NULL AND age_bucket IS NULL AND race is NULL AND ethnicity IS NOT NULL AND gender IS NULL AND case_type IS NULL AND person_id IS NULL AND person_external_id IS NULL AND year = EXTRACT(YEAR FROM CURRENT_DATE('US/Pacific')) AND month = EXTRACT(MONTH FROM CURRENT_DATE('US/Pacific')) AND job.metric_type = 'SUPERVISION_REVOCATION' ) rev USING (state_code, year, month, supervision_type, supervising_district_external_id, metric_period_months, race_or_ethnicity) WHERE supervision_type in ('ALL', 'PAROLE', 'PROBATION') ORDER BY state_code, race_or_ethnicity, district, supervision_type, metric_period_months """.format( description=REVOCATIONS_BY_RACE_AND_ETHNICITY_BY_PERIOD_DESCRIPTION, project_id=PROJECT_ID, metrics_dataset=METRICS_DATASET, views_dataset=VIEWS_DATASET, ) REVOCATIONS_BY_RACE_AND_ETHNICITY_BY_PERIOD_VIEW = bqview.BigQueryView( view_id=REVOCATIONS_BY_RACE_AND_ETHNICITY_BY_PERIOD_VIEW_NAME, view_query=REVOCATIONS_BY_RACE_AND_ETHNICITY_BY_PERIOD_QUERY ) if __name__ == '__main__': print(REVOCATIONS_BY_RACE_AND_ETHNICITY_BY_PERIOD_VIEW.view_id) print(REVOCATIONS_BY_RACE_AND_ETHNICITY_BY_PERIOD_VIEW.view_query)
SELECT state_code, termination_month_trunc, 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_expanded ) WHERE supervision_type IN ('ALL', 'PROBATION', 'PAROLE') AND EXTRACT(YEAR FROM termination_month_trunc) >= EXTRACT(YEAR FROM DATE_SUB(CURRENT_DATE('US/Pacific'), INTERVAL 3 YEAR)) GROUP BY state_code, termination_month_trunc, supervision_type, district ORDER BY state_code, year, month, supervision_type, district """.format( description=CASE_TERMINATIONS_BY_TYPE_BY_MONTH_DESCRIPTION, prep_expression=_get_query_prep_statement(project_id=PROJECT_ID, views_dataset=VIEWS_DATASET) ) CASE_TERMINATIONS_BY_TYPE_BY_MONTH_VIEW = bqview.BigQueryView( view_id=CASE_TERMINATIONS_BY_TYPE_BY_MONTH_VIEW_NAME, view_query=CASE_TERMINATIONS_BY_TYPE_BY_MONTH_QUERY) if __name__ == '__main__': print(CASE_TERMINATIONS_BY_TYPE_BY_MONTH_VIEW.view_id) print(CASE_TERMINATIONS_BY_TYPE_BY_MONTH_VIEW.view_query)
AND year = EXTRACT(YEAR FROM CURRENT_DATE('US/Pacific')) AND month = EXTRACT(MONTH FROM CURRENT_DATE('US/Pacific')) AND job.metric_type = 'SUPERVISION_REVOCATION_ANALYSIS' GROUP BY state_code, reported_violations, risk_level, supervision_type, charge_category, district, metric_period_months ) revocation_response_with_violation USING (state_code, metric_period_months, reported_violations, risk_level, supervision_type, charge_category, district) ) rev USING (state_code, violation_type, reported_violations, risk_level, supervision_type, charge_category, district, metric_period_months) WHERE supervision_type IN ('ALL', 'DUAL', 'PAROLE', 'PROBATION') AND total_supervision_count > 0 ORDER BY state_code, district, supervision_type, risk_level, metric_period_months, violation_type, reported_violations, charge_category """.format( description=REVOCATIONS_MATRIX_DISTRIBUTION_BY_RISK_LEVEL_DESCRIPTION, project_id=PROJECT_ID, metrics_dataset=METRICS_DATASET, views_dataset=VIEWS_DATASET, ) REVOCATIONS_MATRIX_DISTRIBUTION_BY_RISK_LEVEL_VIEW = bqview.BigQueryView( view_id=REVOCATIONS_MATRIX_DISTRIBUTION_BY_RISK_LEVEL_VIEW_NAME, view_query=REVOCATIONS_MATRIX_DISTRIBUTION_BY_RISK_LEVEL_QUERY ) if __name__ == '__main__': print(REVOCATIONS_MATRIX_DISTRIBUTION_BY_RISK_LEVEL_VIEW.view_id) print(REVOCATIONS_MATRIX_DISTRIBUTION_BY_RISK_LEVEL_VIEW.view_query)
AND stay_length_bucket IS NULL AND race IS NULL AND ethnicity IS NULL AND gender IS NULL AND person_id IS NULL AND person_external_id IS NULL AND release_facility IS NULL AND return_type IS NULL AND from_supervision_type IS NULL AND source_violation_type IS NULL AND year = EXTRACT(YEAR FROM CURRENT_DATE('US/Pacific')) AND month = EXTRACT(MONTH FROM CURRENT_DATE('US/Pacific')) AND job.metric_type = 'RECIDIVISM_COUNT' ) ret USING (state_code, metric_period_months, district) ORDER BY state_code, metric_period_months, district """.format( description=REINCARCERATIONS_BY_PERIOD_DESCRIPTION, project_id=PROJECT_ID, metrics_dataset=METRICS_DATASET, views_dataset=VIEWS_DATASET, ) REINCARCERATIONS_BY_PERIOD_VIEW = bqview.BigQueryView( view_id=REINCARCERATIONS_BY_PERIOD_VIEW_NAME, view_query=REINCARCERATIONS_BY_PERIOD_QUERY) if __name__ == '__main__': print(REINCARCERATIONS_BY_PERIOD_VIEW.view_id) print(REINCARCERATIONS_BY_PERIOD_VIEW.view_query)
AND supervising_officer_external_id IS NULL AND age_bucket IS NULL AND race IS NULL AND ethnicity IS NULL AND gender IS NOT NULL AND person_id IS NULL AND person_external_id IS NULL AND year = EXTRACT(YEAR FROM CURRENT_DATE('US/Pacific')) AND month = EXTRACT(MONTH FROM CURRENT_DATE('US/Pacific')) AND job.metric_type = 'PROGRAM_REFERRAL' ) ref USING (state_code, year, month, supervision_type, supervising_district_external_id, metric_period_months, gender) WHERE supervision_type in ('ALL', 'PAROLE', 'PROBATION') AND state_code = 'US_ND' ORDER BY state_code, gender, district, supervision_type, metric_period_months """.format( description= FTR_REFERRALS_BY_GENDER_BY_PERIOD_DESCRIPTION, project_id=PROJECT_ID, metrics_dataset=METRICS_DATASET, views_dataset=VIEWS_DATASET, ) FTR_REFERRALS_BY_GENDER_BY_PERIOD_VIEW = bqview.BigQueryView( view_id=FTR_REFERRALS_BY_GENDER_BY_PERIOD_VIEW_NAME, view_query=FTR_REFERRALS_BY_GENDER_BY_PERIOD_QUERY) if __name__ == '__main__': print(FTR_REFERRALS_BY_GENDER_BY_PERIOD_VIEW.view_id) print(FTR_REFERRALS_BY_GENDER_BY_PERIOD_VIEW.view_query)
AND age_bucket IS NULL AND race IS NULL AND ethnicity IS NULL AND gender IS NULL AND person_id IS NULL AND person_external_id IS NULL AND specialized_purpose_for_incarceration IS NULL AND admission_reason_raw_text IS NULL AND admission_date IS NULL AND supervision_type_at_admission IS NULL AND year >= EXTRACT(YEAR FROM DATE_ADD(CURRENT_DATE(), INTERVAL -3 YEAR)) AND job.metric_type = 'INCARCERATION_ADMISSION' GROUP BY state_code, year, month ) USING (state_code, year, month, supervision_type, district) ORDER BY state_code, year, month, district, supervision_type """.format( description=ADMISSIONS_BY_TYPE_BY_MONTH_DESCRIPTION, project_id=PROJECT_ID, metrics_dataset=METRICS_DATASET, views_dataset=VIEWS_DATASET, ) ADMISSIONS_BY_TYPE_BY_MONTH_VIEW = bqview.BigQueryView( view_id=ADMISSIONS_BY_TYPE_BY_MONTH_VIEW_NAME, view_query=ADMISSIONS_BY_TYPE_BY_MONTH_QUERY) if __name__ == '__main__': print(ADMISSIONS_BY_TYPE_BY_MONTH_VIEW.view_id) print(ADMISSIONS_BY_TYPE_BY_MONTH_VIEW.view_query)
PopulationRaceGender.released, PopulationRaceGender.total_jail_person_count, ResidentPopulation.resident_pop, ResidentPopulation.total_resident_pop FROM `{project_id}.{views_dataset}.{population_admissions_releases_race_gender_all_view}` PopulationRaceGender LEFT JOIN `{project_id}.{views_dataset}.{resident_population_counts_view}` ResidentPopulation ON PopulationRaceGender.fips = ResidentPopulation.fips AND EXTRACT(YEAR FROM PopulationRaceGender.day) = ResidentPopulation.year AND PopulationRaceGender.race = ResidentPopulation.race AND PopulationRaceGender.gender = ResidentPopulation.gender WHERE EXTRACT(YEAR FROM day) > {cutoff_year} """.format( description=JAIL_POP_AND_RESIDENT_POP_DESCRIPTION, project_id=PROJECT_ID, views_dataset=VIEWS_DATASET, population_admissions_releases_race_gender_all_view=POPULATION_ADMISSIONS_RELEASES_RACE_GENDER_ALL_VIEW.view_id, resident_population_counts_view=RESIDENT_POPULATION_COUNTS_VIEW.view_id, cutoff_year=CUTOFF_YEAR ) JAIL_POP_AND_RESIDENT_POP_VIEW = bqview.BigQueryView( view_id=JAIL_POP_AND_RESIDENT_POP_VIEW_NAME, view_query=JAIL_POP_AND_RESIDENT_POP_QUERY) if __name__ == '__main__': print(JAIL_POP_AND_RESIDENT_POP_VIEW.view_id) print(JAIL_POP_AND_RESIDENT_POP_VIEW.view_query)