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)
