Python supervision_specific_district_groupings Examples

Example #1

        district,
        supervision_type,
        COUNT(DISTINCT IF(successful_termination = 1, person_id, NULL)) AS successful_termination_count,
        COUNT(DISTINCT(person_id)) AS projected_completion_count
      FROM success_classifications
      GROUP BY state_code, projected_year, projected_month, district, supervision_type
    )
    
    
    SELECT
        *,
        IEEE_DIVIDE(successful_termination_count, projected_completion_count) as success_rate
    FROM success_counts
    ORDER BY state_code, projected_year, projected_month, supervision_type
    """

SUPERVISION_SUCCESS_BY_MONTH_VIEW_BUILDER = SimpleBigQueryViewBuilder(
    dataset_id=dataset_config.PUBLIC_DASHBOARD_VIEWS_DATASET,
    view_id=SUPERVISION_SUCCESS_BY_MONTH_VIEW_NAME,
    view_query_template=SUPERVISION_SUCCESS_BY_MONTH_VIEW_QUERY_TEMPLATE,
    description=SUPERVISION_SUCCESS_BY_MONTH_VIEW_DESCRIPTION,
    metrics_dataset=dataset_config.DATAFLOW_METRICS_DATASET,
    reference_dataset=dataset_config.REFERENCE_TABLES_DATASET,
    grouped_districts=bq_utils.supervision_specific_district_groupings(
        'supervising_district_external_id', 'judicial_district_code'),
    district_dimension=bq_utils.unnest_district())

if __name__ == '__main__':
    with local_project_id_override(GCP_PROJECT_STAGING):
        SUPERVISION_SUCCESS_BY_MONTH_VIEW_BUILDER.build_and_print()

Example #2

File: supervision_revocations_by_month_by_type_by_demographics.py Project: nikhil-tibrewal/pulse-data

      OR (race_or_ethnicity = 'ALL' AND gender = 'ALL' AND age_bucket != 'ALL') -- Age breakdown
      OR (race_or_ethnicity = 'ALL' AND gender = 'ALL' AND age_bucket = 'ALL') -- Overall breakdown
    GROUP BY state_code, year, month, district, supervision_type, race_or_ethnicity, gender, age_bucket
    ORDER BY state_code, year, month, district, supervision_type, race_or_ethnicity, gender, age_bucket
    """

SUPERVISION_REVOCATIONS_BY_MONTH_BY_TYPE_BY_DEMOGRAPHICS_VIEW_VIEW_BUILDER = SimpleBigQueryViewBuilder(
    dataset_id=dataset_config.PUBLIC_DASHBOARD_VIEWS_DATASET,
    view_id=
    SUPERVISION_REVOCATIONS_BY_MONTH_BY_TYPE_BY_DEMOGRAPHICS_VIEW_VIEW_NAME,
    view_query_template=
    SUPERVISION_REVOCATIONS_BY_MONTH_BY_TYPE_BY_DEMOGRAPHICS_VIEW_VIEW_QUERY_TEMPLATE,
    description=
    SUPERVISION_REVOCATIONS_BY_MONTH_BY_TYPE_BY_DEMOGRAPHICS_VIEW_VIEW_DESCRIPTION,
    reference_dataset=dataset_config.REFERENCE_TABLES_DATASET,
    grouped_districts=bq_utils.supervision_specific_district_groupings(
        'district', 'judicial_district_code'),
    race_or_ethnicity_dimension=bq_utils.unnest_race_and_ethnicity(),
    current_month_condition=bq_utils.current_month_condition(),
    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'),
    district_dimension=bq_utils.unnest_district('district'),
    state_specific_race_or_ethnicity_groupings=bq_utils.
    state_specific_race_or_ethnicity_groupings())

if __name__ == '__main__':
    with local_project_id_override(GAE_PROJECT_STAGING):
        SUPERVISION_REVOCATIONS_BY_MONTH_BY_TYPE_BY_DEMOGRAPHICS_VIEW_VIEW_BUILDER.build_and_print(
        )