"state_code",
"supervision_type",
"metric_period_months",
"district",
"race_or_ethnicity",
"gender",
"age_bucket",
),
description=SUPERVISION_SUCCESS_BY_PERIOD_BY_DEMOGRAPHICS_VIEW_DESCRIPTION,
materialized_metrics_dataset=dataset_config.
DATAFLOW_METRICS_MATERIALIZED_DATASET,
reference_views_dataset=dataset_config.REFERENCE_VIEWS_DATASET,
grouped_districts=state_specific_query_strings.
state_supervision_specific_district_groupings(
"supervising_district_external_id", "judicial_district_code"),
metric_period_condition=bq_utils.metric_period_condition(month_offset=1),
unnested_race_or_ethnicity_dimension=bq_utils.unnest_column(
"race_or_ethnicity", "race_or_ethnicity"),
gender_dimension=bq_utils.unnest_column("gender", "gender"),
age_dimension=bq_utils.unnest_column("age_bucket", "age_bucket"),
state_specific_race_or_ethnicity_groupings=state_specific_query_strings.
state_specific_race_or_ethnicity_groupings(
"prioritized_race_or_ethnicity"),
state_specific_supervision_type_inclusion_filter=state_specific_query_strings
.state_specific_supervision_type_inclusion_filter(),
)
if __name__ == "__main__":
with local_project_id_override(GCP_PROJECT_STAGING):
SUPERVISION_SUCCESS_BY_PERIOD_BY_DEMOGRAPHICS_VIEW_BUILDER.build_and_print(
)
metric_period_months
FROM supervision
FULL OUTER JOIN referrals
USING (state_code, supervision_type, district, metric_period_months, gender)
WHERE supervision_type in ('ALL', 'PAROLE', 'PROBATION')
AND district IS NOT NULL
AND state_code = 'US_ND'
ORDER BY state_code, gender, district, supervision_type, metric_period_months
"""
FTR_REFERRALS_BY_GENDER_BY_PERIOD_VIEW_BUILDER = MetricBigQueryViewBuilder(
dataset_id=dataset_config.DASHBOARD_VIEWS_DATASET,
view_id=FTR_REFERRALS_BY_GENDER_BY_PERIOD_VIEW_NAME,
view_query_template=FTR_REFERRALS_BY_GENDER_BY_PERIOD_QUERY_TEMPLATE,
dimensions=(
"state_code",
"metric_period_months",
"district",
"supervision_type",
"gender",
),
description=FTR_REFERRALS_BY_GENDER_BY_PERIOD_DESCRIPTION,
reference_views_dataset=dataset_config.REFERENCE_VIEWS_DATASET,
metric_period_dimension=bq_utils.unnest_metric_period_months(),
metric_period_condition=bq_utils.metric_period_condition(),
)
if __name__ == "__main__":
with local_project_id_override(GCP_PROJECT_STAGING):
FTR_REFERRALS_BY_GENDER_BY_PERIOD_VIEW_BUILDER.build_and_print()
AND m.metric_period_months = 0
AND {prior_month_metric_period_dimension}
-- Get population count for the last day of the month
AND date_of_stay = DATE_SUB(DATE_ADD(DATE(year, month, 1), INTERVAL 1 MONTH), INTERVAL 1 DAY)
AND job.metric_type = 'INCARCERATION_POPULATION'
GROUP BY state_code, district, metric_period_months
) inc_pop
USING (state_code, district, metric_period_months)
WHERE district IS NOT NULL
ORDER BY state_code, metric_period_months, district
"""
ADMISSIONS_VERSUS_RELEASES_BY_PERIOD_VIEW_BUILDER = SimpleBigQueryViewBuilder(
dataset_id=dataset_config.DASHBOARD_VIEWS_DATASET,
view_id=ADMISSIONS_VERSUS_RELEASES_BY_PERIOD_VIEW_NAME,
view_query_template=ADMISSIONS_VERSUS_RELEASES_BY_PERIOD_QUERY_TEMPLATE,
description=ADMISSIONS_VERSUS_RELEASES_BY_PERIOD_DESCRIPTION,
metrics_dataset=dataset_config.DATAFLOW_METRICS_DATASET,
reference_dataset=dataset_config.REFERENCE_TABLES_DATASET,
district_dimension=bq_utils.unnest_district(
district_column='county_of_residence'),
metric_period_dimension=bq_utils.unnest_metric_period_months(),
metric_period_condition=bq_utils.metric_period_condition(month_offset=1),
prior_month_metric_period_dimension=bq_utils.metric_period_condition(
month_offset=0),
)
if __name__ == '__main__':
with local_project_id_override(GAE_PROJECT_STAGING):
ADMISSIONS_VERSUS_RELEASES_BY_MONTH_VIEW_BUILDER.build_and_print()
