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.bq.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')
def setUp(self): 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.bq.dashboard.views.view_manager.bq_utils') self.mock_bq_utils = self.bq_utils_patcher.start() self.client_patcher = mock.patch( 'recidiviz.calculator.bq.dashboard.views.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_name = 'base_dataset' self.mock_dataset = bigquery.dataset.DatasetReference( self.mock_project_id, self.mock_dataset_name) self.client_patcher = mock.patch( 'recidiviz.calculator.bq.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'], 'STANDARD_VIEWS_TO_EXPORT': views_to_export, 'DATAFLOW_VIEWS_TO_EXPORT': views_to_export } self.dashboard_export_config_patcher = mock.patch( 'recidiviz.calculator.bq.dashboard.dashboard_export_manager.dashboard_export_config', **dashboard_export_config_values) self.mock_export_config = self.dashboard_export_config_patcher.start()
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)
UNION ALL -- Unknown Revocations (SELECT state_code, 'UNKNOWN_REVOCATION' as admission_type, count(*) as revocation_count FROM `{project_id}.{views_dataset}.incarceration_admissions_60_days` WHERE admission_reason in ('PAROLE_REVOCATION', 'PROBATION_REVOCATION') and source_supervision_violation_response_id is null GROUP BY state_code, admission_type) UNION ALL -- Non-Technical Revocations (SELECT inc.state_code, 'NON_TECHNICAL' as admission_type, count(*) as revocation_count FROM `{project_id}.{views_dataset}.incarceration_admissions_60_days` inc JOIN `{project_id}.{base_dataset}.state_supervision_violation_response` resp ON inc.source_supervision_violation_response_id = resp.supervision_violation_response_id JOIN `{project_id}.{base_dataset}.state_supervision_violation` viol ON resp.supervision_violation_id = viol.supervision_violation_id WHERE viol.violation_type != 'TECHNICAL' GROUP BY state_code, admission_type) """.format( description=ADMISSIONS_BY_TYPE_60_DAYS_DESCRIPTION, project_id=PROJECT_ID, views_dataset=VIEWS_DATASET, base_dataset=BASE_DATASET ) ADMISSIONS_BY_TYPE_60_DAYS_VIEW = bqview.BigQueryView( view_id=ADMISSIONS_BY_TYPE_60_DAYS_VIEW_NAME, view_query=ADMISSIONS_BY_TYPE_60_DAYS_QUERY) if __name__ == '__main__': print(ADMISSIONS_BY_TYPE_60_DAYS_VIEW.view_id) print(ADMISSIONS_BY_TYPE_60_DAYS_VIEW.view_query)
SELECT BondAmounts.booking_id, -- We don't need to use IF statements for all of the below, but keep them for consistency. IF(BondAmounts.denied_count > 0, True, False) AS denied, IF(BondAmounts.denied_count > 0, NULL, BondAmounts.total_bond_dollars) AS total_bond_dollars, IF((BondAmounts.total_bond_dollars IS NULL AND BondAmounts.denied_count = 0), True, False) AS unknown FROM ( SELECT Bond.booking_id, SUM(Bond.amount_dollars) AS total_bond_dollars, COUNTIF(Bond.denied) AS denied_count, -- unknown_count is not used, but keep it for consistency. COUNTIF(Bond.unknown) AS unknown_count FROM `{project_id}.{views_dataset}.{bond_amounts_unknown_denied_view}` Bond GROUP BY booking_id ) BondAmounts """.format( description=BOND_AMOUNTS_BY_BOOKING_DESCRIPTION, project_id=PROJECT_ID, views_dataset=VIEWS_DATASET, bond_amounts_unknown_denied_view=BOND_AMOUNTS_UNKNOWN_DENIED_VIEW.view_id ) BOND_AMOUNTS_BY_BOOKING_VIEW = bqview.BigQueryView( view_id=BOND_AMOUNTS_BY_BOOKING_VIEW_NAME, view_query=BOND_AMOUNTS_BY_BOOKING_QUERY ) if __name__ == '__main__': print(BOND_AMOUNTS_BY_BOOKING_VIEW.view_id) print(BOND_AMOUNTS_BY_BOOKING_VIEW.view_query)
) SELECT PersonCountTable.day, PersonCountTable.fips, PersonCountTable.charge_text, person_count, admitted, released, CountyNames.state, CountyNames.county_name FROM PersonCountTable FULL JOIN AdmittedTable ON PersonCountTable.day = AdmittedTable.day AND PersonCountTable.fips = AdmittedTable.fips AND PersonCountTable.charge_text = AdmittedTable.charge_text FULL JOIN ReleasedTable ON PersonCountTable.day = ReleasedTable.day AND PersonCountTable.fips = ReleasedTable.fips AND PersonCountTable.charge_text = ReleasedTable.charge_text JOIN `{project_id}.{views_dataset}.{county_names_view}` CountyNames ON PersonCountTable.fips = CountyNames.fips ORDER BY day DESC, fips, person_count DESC """.format( description=CHARGE_TEXT_COUNTS_DESCRIPTION, project_id=PROJECT_ID, base_dataset=BASE_DATASET, views_dataset=VIEWS_DATASET, charge_table=Charge.__tablename__, booking_table=Booking.__tablename__, person_table=Person.__tablename__, county_names_view=COUNTY_NAMES_VIEW.view_id ) CHARGE_TEXT_COUNTS_VIEW = bqview.BigQueryView( view_id=CHARGE_TEXT_COUNTS_VIEW_NAME, view_query=CHARGE_TEXT_COUNTS_QUERY) if __name__ == '__main__': print(CHARGE_TEXT_COUNTS_VIEW.view_id) print(CHARGE_TEXT_COUNTS_VIEW.view_query)
CHARGE_CLASS_SEVERITY_RANKS_DESCRIPTION = \ """ A View of all charge classes and their severity ranks. Severity is ranked where 0 is most severe, and 7 is least severe. """ CHARGE_CLASS_SEVERITY_RANKS_QUERY = \ """ /*{description}*/ SELECT severity, charge_class FROM UNNEST({charge_classes_by_severity_list}) AS charge_class WITH OFFSET AS severity ORDER BY severity """.format( description=CHARGE_CLASS_SEVERITY_RANKS_DESCRIPTION, charge_classes_by_severity_list=str(CHARGE_CLASSES_BY_SEVERITY) ) CHARGE_CLASS_SEVERITY_RANKS_VIEW = bqview.BigQueryView( view_id=CHARGE_CLASS_SEVERITY_RANKS_VIEW_NAME, view_query=CHARGE_CLASS_SEVERITY_RANKS_QUERY) if __name__ == '__main__': print(CHARGE_CLASS_SEVERITY_RANKS_VIEW.view_id) print(CHARGE_CLASS_SEVERITY_RANKS_VIEW.view_query)
) 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)
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)
PROJECT_ID = metadata.project_id() BASE_DATASET = export_config.STATE_BASE_TABLES_BQ_DATASET REVOCATIONS_BY_MONTH_VIEW_NAME = 'revocations_by_month' REVOCATIONS_BY_MONTH_DESCRIPTION = """ Revocations by month """ REVOCATIONS_BY_MONTH_QUERY = \ """ /*{description}*/ SELECT state_code, EXTRACT(YEAR FROM admission_date) as year, EXTRACT(MONTH FROM admission_date) as month, count(*) as revocation_count FROM (SELECT state_code, admission_date FROM `{project_id}.{base_dataset}.state_incarceration_period` WHERE admission_reason in ('PROBATION_REVOCATION', 'PAROLE_REVOCATION')) GROUP BY state_code, year, month having year > EXTRACT(YEAR FROM DATE_ADD(CURRENT_DATE(), INTERVAL -3 YEAR)) ORDER BY year, month ASC """.format( description=REVOCATIONS_BY_MONTH_DESCRIPTION, project_id=PROJECT_ID, base_dataset=BASE_DATASET, ) REVOCATIONS_BY_MONTH_VIEW = bqview.BigQueryView( view_id=REVOCATIONS_BY_MONTH_VIEW_NAME, view_query=REVOCATIONS_BY_MONTH_QUERY) if __name__ == '__main__': print(REVOCATIONS_BY_MONTH_VIEW.view_id) print(REVOCATIONS_BY_MONTH_VIEW.view_query)
(SELECT state_code, EXTRACT(YEAR FROM admission_date) as year, EXTRACT(MONTH FROM admission_date) as month, IFNULL(count(*), 0) as unknown_revocations FROM `{project_id}.{base_dataset}.state_incarceration_period` WHERE admission_reason in ('PAROLE_REVOCATION', 'PROBATION_REVOCATION') and source_supervision_violation_response_id is null GROUP BY state_code, year, month having year > EXTRACT(YEAR FROM DATE_ADD(CURRENT_DATE(), INTERVAL -3 YEAR))) unk_rev ON newadm.year = unk_rev.year AND newadm.month = unk_rev.month FULL OUTER JOIN -- Non-Technical Revocations (SELECT inc.state_code, EXTRACT(YEAR FROM admission_date) as year, EXTRACT(MONTH FROM admission_date) as month, IFNULL(count(*), 0) as non_technicals FROM `{project_id}.{base_dataset}.state_incarceration_period` inc JOIN `{project_id}.{base_dataset}.state_supervision_violation_response` resp ON inc.source_supervision_violation_response_id = resp.supervision_violation_response_id JOIN `{project_id}.{base_dataset}.state_supervision_violation` viol ON resp.supervision_violation_id = viol.supervision_violation_id WHERE viol.violation_type != 'TECHNICAL' GROUP BY state_code, year, month having year > EXTRACT(YEAR FROM DATE_ADD(CURRENT_DATE(), INTERVAL -3 YEAR))) non_tech ON newadm.year = non_tech.year AND newadm.month = non_tech.month ORDER BY year, month ASC """.format( description=ADMISSIONS_BY_TYPE_BY_MONTH_DESCRIPTION, project_id=PROJECT_ID, base_dataset=BASE_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)
revocation. """ SUPERVISION_TERMINATION_BY_PERSON_QUERY = \ """ /*{description}*/ SELECT ss.state_code, EXTRACT(YEAR FROM TIMESTAMP(projected_completion_date)) as projected_year, EXTRACT(MONTH FROM TIMESTAMP(projected_completion_date)) as projected_month, ss.person_id, decision, count(*) as count FROM `{project_id}.{base_dataset}.state_supervision_sentence` ss JOIN `{project_id}.{base_dataset}.state_supervision_sentence_supervision_period_association` assoc on ss.supervision_sentence_id = assoc.supervision_sentence_id JOIN `{project_id}.{base_dataset}.state_supervision_period` sp on sp.supervision_period_id = assoc.supervision_period_id FULL OUTER JOIN `{project_id}.{base_dataset}.state_supervision_violation` sv on sp.supervision_period_id = sv.supervision_period_id FULL OUTER JOIN `{project_id}.{base_dataset}.state_supervision_violation_response` svr on sv.supervision_violation_id = svr.supervision_violation_id WHERE ss.projected_completion_date IS NOT NULL AND termination_date IS NOT NULL GROUP BY state_code, projected_year, projected_month, person_id, decision HAVING projected_year <= EXTRACT(YEAR FROM CURRENT_DATE()) ORDER BY person_id desc """.format( description=SUPERVISION_TERMINATION_BY_PERSON_DESCRIPTION, project_id=PROJECT_ID, base_dataset=BASE_DATASET, ) SUPERVISION_TERMINATION_BY_PERSON_VIEW = bqview.BigQueryView( view_id=SUPERVISION_TERMINATION_BY_PERSON, view_query=SUPERVISION_TERMINATION_BY_PERSON_QUERY) if __name__ == '__main__': print(SUPERVISION_TERMINATION_BY_PERSON_VIEW.view_id) print(SUPERVISION_TERMINATION_BY_PERSON_VIEW.view_query)
SELECT term.state_code, IFNULL(rev.projected_year, term.projected_year) AS projected_year, IFNULL(rev.projected_month, term.projected_month) AS projected_month, term.count AS successful_termination, rev.count AS revocation_termination FROM (SELECT state_code, projected_year, projected_month, decision, IFNULL(count, 0) AS count FROM (SELECT state_code, projected_year, projected_month, decision, count(*) as count FROM `{project_id}.{views_dataset}.supervision_termination_by_person` WHERE decision = 'REVOCATION' GROUP BY state_code, projected_year, projected_month, decision HAVING projected_year > EXTRACT(YEAR FROM DATE_ADD(CURRENT_DATE(), INTERVAL -3 YEAR)))) rev FULL OUTER JOIN (SELECT state_code, projected_year, projected_month, decision, IFNULL(count, 0) AS count FROM (SELECT state_code, projected_year, projected_month, decision, count(*) as count FROM `{project_id}.{views_dataset}.supervision_termination_by_person` WHERE decision is null GROUP BY state_code, projected_year, projected_month, decision HAVING projected_year > EXTRACT(YEAR FROM DATE_ADD(CURRENT_DATE(), INTERVAL -3 YEAR)))) term ON rev.projected_year = term.projected_year and rev.projected_month = term.projected_month ORDER BY projected_year, projected_month ASC """.format( description=SUPERVISION_TERMINATION_BY_TYPE_BY_MONTH_DESCRIPTION, project_id=PROJECT_ID, views_dataset=VIEWS_DATASET, ) SUPERVISION_TERMINATION_BY_TYPE_BY_MONTH_VIEW = bqview.BigQueryView( view_id=SUPERVISION_TERMINATION_BY_TYPE_BY_MONTH_VIEW_NAME, view_query=SUPERVISION_TERMINATION_BY_TYPE_BY_MONTH_QUERY ) if __name__ == '__main__': print(SUPERVISION_TERMINATION_BY_TYPE_BY_MONTH_VIEW.view_id) print(SUPERVISION_TERMINATION_BY_TYPE_BY_MONTH_VIEW.view_query)
) SELECT PersonCountTable.day, PersonCountTable.fips, PersonCountTable.bond_amount_category, person_count, admitted, released, CountyNames.county_name, CountyNames.state FROM PersonCountTable 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 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)
REVOCATIONS_BY_RACE_60_DAYS_DESCRIPTION = \ """ Revocations by race in last 60 days """ REVOCATIONS_BY_RACE_60_DAYS_QUERY = \ """ /*{description}*/ SELECT state_code, race, count(*) as revocation_count FROM (SELECT sip.state_code, spr.race, admission_date FROM `{project_id}.{views_dataset}.incarceration_admissions_60_days` sip join `{project_id}.{base_dataset}.state_person_race` spr on spr.person_id = sip.person_id WHERE sip.admission_reason in ('PROBATION_REVOCATION', 'PAROLE_REVOCATION')) GROUP BY state_code, race ORDER BY race ASC """.format( description=REVOCATIONS_BY_RACE_60_DAYS_DESCRIPTION, project_id=PROJECT_ID, views_dataset=VIEWS_DATASET, base_dataset=BASE_DATASET ) REVOCATIONS_BY_RACE_60_DAYS_VIEW = bqview.BigQueryView( view_id=REVOCATIONS_BY_RACE_60_DAYS_VIEW_NAME, view_query=REVOCATIONS_BY_RACE_60_DAYS_QUERY) if __name__ == '__main__': print(REVOCATIONS_BY_RACE_60_DAYS_VIEW.view_id) print(REVOCATIONS_BY_RACE_60_DAYS_VIEW.view_query)
INCARCERATION_ADMISSIONS_60_DAYS_DESCRIPTION = \ """ Incarceration admissions in last 60 days. This includes new admissions and admissions for supervision revocation. Excludes all other reasons for admission. """ INCARCERATION_ADMISSIONS_60_DAYS_QUERY = \ """ /*{description}*/ SELECT * FROM `{project_id}.{base_dataset}.state_incarceration_period` WHERE admission_reason in ('NEW_ADMISSION', 'PAROLE_REVOCATION', 'PROBATION_REVOCATION') AND admission_date BETWEEN (DATE_ADD(CURRENT_DATE(), INTERVAL -60 DAY)) AND CURRENT_DATE() """.format( description=INCARCERATION_ADMISSIONS_60_DAYS_DESCRIPTION, project_id=PROJECT_ID, base_dataset=BASE_DATASET, ) INCARCERATION_ADMISSIONS_60_DAYS_VIEW = bqview.BigQueryView( view_id=INCARCERATION_ADMISSIONS_60_DAYS_VIEW_NAME, view_query=INCARCERATION_ADMISSIONS_60_DAYS_QUERY ) if __name__ == '__main__': print(INCARCERATION_ADMISSIONS_60_DAYS_VIEW.view_id) print(INCARCERATION_ADMISSIONS_60_DAYS_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)
REINCARCERATION_RATE_BY_STAY_LENGTH_DESCRIPTION = \ """ Reincarceration rate by stay length """ REINCARCERATION_RATE_BY_STAY_LENGTH_QUERY = \ """ /*{description}*/ SELECT state_code, release_cohort, follow_up_period, recidivism_rate, stay_length_bucket FROM `{project_id}.{metrics_dataset}.recidivism_rate_metrics` WHERE methodology = 'PERSON' and age_bucket is null and race is null and ethnicity is null and gender 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_id in -- Get only from the most recent job (SELECT job_id FROM `{project_id}.{views_dataset}.most_recent_calculate_job`) ORDER BY 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)
MOST_RECENT_CALCULATE_JOB_DESCRIPTION = \ """ Job ID of the most recent calculate job All job_ids begin with the format: 'YYYY-MM-DD_HH_MM_SS', so ordering by job_id gives us the most recent job. This format is true of both jobs run locally and run on Dataflow. """ MOST_RECENT_CALCULATE_JOB_QUERY = \ """ /*{description}*/ SELECT state_code, job_id FROM `{project_id}.{metrics_dataset}.recidivism_rate_metrics` GROUP BY state_code, job_id ORDER BY state_code, job_id DESC LIMIT 1 """.format( description=MOST_RECENT_CALCULATE_JOB_DESCRIPTION, project_id=PROJECT_ID, metrics_dataset=METRICS_DATASET, ) MOST_RECENT_CALCULATE_JOB_VIEW = bqview.BigQueryView( view_id=MOST_RECENT_CALCULATE_JOB_VIEW_NAME, view_query=MOST_RECENT_CALCULATE_JOB_QUERY) if __name__ == '__main__': print(MOST_RECENT_CALCULATE_JOB_VIEW.view_id) print(MOST_RECENT_CALCULATE_JOB_VIEW.view_query)
AND NewBond.status = '{bond_status_revoked}' THEN True ELSE False END AS denied FROM ( SELECT Bond.booking_id, Bond.bond_type, Bond.status, -- Set records with Bond.bond_type = '{bond_type_not_required}' OR Bond.status = '{bond_status_posted}' to Bond.amount_dollars = 0. IF((Bond.bond_type = '{bond_type_not_required}' OR Bond.status = '{bond_status_posted}'), 0, Bond.amount_dollars) AS amount_dollars FROM `{project_id}.{base_dataset}.{bond_table}` Bond ) NewBond """.format( description=BOND_AMOUNTS_UNKNOWN_DENIED_DESCRIPTION, bond_type_secured=bond_type_secured, bond_type_not_required=bond_type_not_required, present_without_info=present_without_info, bond_type_denied=bond_type_denied, bond_status_revoked=bond_status_revoked, bond_status_posted=bond_status_posted, project_id=PROJECT_ID, base_dataset=BASE_DATASET, bond_table=Bond.__tablename__ ) BOND_AMOUNTS_UNKNOWN_DENIED_VIEW = bqview.BigQueryView( view_id=BOND_AMOUNTS_UNKNOWN_DENIED_VIEW_NAME, view_query=BOND_AMOUNTS_UNKNOWN_DENIED_QUERY ) if __name__ == '__main__': print(BOND_AMOUNTS_UNKNOWN_DENIED_VIEW.view_id) print(BOND_AMOUNTS_UNKNOWN_DENIED_VIEW.view_query)
REINCARCERATION_RATE_BY_RELEASE_FACILITY_QUERY = \ """ /*{description}*/ SELECT state_code, release_cohort, follow_up_period, recidivism_rate, release_facility FROM `{project_id}.{metrics_dataset}.recidivism_rate_metrics` WHERE methodology = 'PERSON' and age_bucket is null and stay_length_bucket is null and race is null and ethnicity is null and gender is null and return_type is null and from_supervision_type is null and source_violation_type is null and release_facility is not null and release_cohort = EXTRACT(YEAR FROM DATE_ADD(CURRENT_DATE(), INTERVAL -2 YEAR)) and follow_up_period = 1 and job_id in -- Get only from the most recent job (SELECT job_id FROM `{project_id}.{views_dataset}.most_recent_calculate_job`) ORDER BY release_facility """.format( description=REINCARCERATION_RATE_BY_RELEASE_FACILITY_DESCRIPTION, project_id=PROJECT_ID, metrics_dataset=METRICS_DATASET, views_dataset=VIEWS_DATASET ) REINCARCERATION_RATE_BY_RELEASE_FACILITY_VIEW = bqview.BigQueryView( view_id=REINCARCERATION_RATE_BY_RELEASE_FACILITY_VIEW_NAME, view_query=REINCARCERATION_RATE_BY_RELEASE_FACILITY_QUERY ) if __name__ == '__main__': print(REINCARCERATION_RATE_BY_RELEASE_FACILITY_VIEW.view_id) print(REINCARCERATION_RATE_BY_RELEASE_FACILITY_VIEW.view_query)
FROM (SELECT state_code, DATE_TRUNC(admission_date, month) as start_date, DATE_ADD(DATE_ADD(DATE_TRUNC(admission_date, month), INTERVAL 1 MONTH), INTERVAL -1 DAY) as end_date, count(*) as admission_count FROM `{project_id}.{base_dataset}.state_incarceration_period` WHERE admission_reason in ('NEW_ADMISSION', 'PAROLE_REVOCATION', 'PROBATION_REVOCATION') GROUP BY state_code, start_date, end_date ORDER BY end_date desc)) adm FULL OUTER JOIN (SELECT state_code, EXTRACT(YEAR from start_date) as year, EXTRACT(MONTH from start_date) as month, IFNULL(release_count, 0) as releases FROM (SELECT state_code, DATE_TRUNC(release_date, month) as start_date, DATE_ADD(DATE_ADD(DATE_TRUNC(release_date, month), INTERVAL 1 MONTH), INTERVAL -1 DAY) as end_date, count(*) as release_count FROM `{project_id}.{base_dataset}.state_incarceration_period` WHERE release_reason in ('COMMUTED', 'CONDITIONAL_RELEASE', 'SENTENCE_SERVED') GROUP BY state_code, start_date, end_date ORDER BY end_date desc)) rel ON adm.year = rel.year AND adm.month = rel.month WHERE IFNULL(adm.year, rel.year) > EXTRACT(YEAR FROM DATE_ADD(CURRENT_DATE(), INTERVAL -3 YEAR)) ORDER BY year, month ASC """.format( description=ADMISSIONS_VERSUS_RELEASES_BY_MONTH_DESCRIPTION, project_id=PROJECT_ID, base_dataset=BASE_DATASET, ) ADMISSIONS_VERSUS_RELEASES_BY_MONTH_VIEW = bqview.BigQueryView( view_id=ADMISSIONS_VERSUS_RELEASES_BY_MONTH_VIEW_NAME, view_query=ADMISSIONS_VERSUS_RELEASES_BY_MONTH_QUERY) if __name__ == '__main__': print(ADMISSIONS_VERSUS_RELEASES_BY_MONTH_VIEW.view_id) print(ADMISSIONS_VERSUS_RELEASES_BY_MONTH_VIEW.view_query)
COUNTY_NAMES_VIEW_DESCRIPTION = \ """ A view that contains all unique combinations of fips, state name, and county name from Vera's Incarceration Trends dataset. """ COUNTY_NAMES_VIEW_QUERY = \ """ /*{description}*/ SELECT SUBSTR(CAST(yfips AS STRING), 5, 5) AS fips, state, county_name FROM `{project_id}.{vera_dataset}.{incarceration_trends_table}` GROUP BY fips, state, county_name ORDER BY fips """.format( description=COUNTY_NAMES_VIEW_DESCRIPTION, project_id=PROJECT_ID, vera_dataset=VERA_DATASET, incarceration_trends_table=INCARCERATION_TRENDS_TABLE ) COUNTY_NAMES_VIEW = bqview.BigQueryView(view_id=COUNTY_NAMES_VIEW_NAME, view_query=COUNTY_NAMES_VIEW_QUERY) if __name__ == '__main__': print(COUNTY_NAMES_VIEW.view_id) print(COUNTY_NAMES_VIEW.view_query)
DAYS_AT_LIBERTY_BY_MONTH_DESCRIPTION = """ Average days at liberty for reincarcerations by month """ DAYS_AT_LIBERTY_BY_MONTH_QUERY = \ """ /*{description}*/ SELECT state_code, EXTRACT(YEAR FROM start_date) as year, EXTRACT(MONTH FROM start_date) as month, returns, avg_liberty FROM `{project_id}.{metrics_dataset}.recidivism_liberty_metrics` WHERE methodology = 'PERSON' AND age_bucket is null AND stay_length_bucket is null AND race is null AND ethnicity is null AND gender 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 job_id in -- Get only from the most recent job (SELECT job_id FROM `{project_id}.{views_dataset}.most_recent_calculate_job`) AND EXTRACT(YEAR FROM start_date) > EXTRACT(YEAR FROM DATE_ADD(CURRENT_DATE(), INTERVAL -3 YEAR)) -- Get only month buckets AND EXTRACT(MONTH FROM start_date) = EXTRACT(MONTH FROM end_date) ORDER BY year, month """.format( description=DAYS_AT_LIBERTY_BY_MONTH_DESCRIPTION, project_id=PROJECT_ID, metrics_dataset=METRICS_DATASET, views_dataset=VIEWS_DATASET, ) DAYS_AT_LIBERTY_BY_MONTH_VIEW = bqview.BigQueryView( view_id=DAYS_AT_LIBERTY_BY_MONTH_VIEW_NAME, view_query=DAYS_AT_LIBERTY_BY_MONTH_QUERY) if __name__ == '__main__': print(DAYS_AT_LIBERTY_BY_MONTH_VIEW.view_id) print(DAYS_AT_LIBERTY_BY_MONTH_VIEW.view_query)