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)
