Statewide Hybrid Workforce

CREATE TABLE cbid_and_classification_list (
  "reporting_year" BIGINT,
  "reporting_month" BIGINT,
  "org_code" VARCHAR,
  "department_name" VARCHAR,
  "class_code" BIGINT,
  "class_title" VARCHAR,
  "bargaining_unit" VARCHAR,
  "employee_count" BIGINT,
  "not_telework_eligible" BIGINT,
  "remote_centered" BIGINT,
  "office_centered" BIGINT,
  "eligible_not_teleworking" BIGINT
);

CREATE TABLE data_submission_status (
  "report_month" TIMESTAMP,
  "department" VARCHAR,
  "submission_status" VARCHAR
);

CREATE TABLE detail_by_agency_department (
  "report_month" TIMESTAMP,
  "org_code" VARCHAR,
  "department" VARCHAR,
  "all_reported_employees" DOUBLE,
  "telework_eligible_employees" DOUBLE,
  "telework_eligible_all_reported" VARCHAR  -- Telework Eligible / All Reported,
  "remote_centered_employees" DOUBLE,
  "remote_centered_all_reported" VARCHAR  -- Remote Centered / All Reported,
  "remote_centered_telework_eligible" VARCHAR  -- Remote Centered / Telework Eligible,
  "office_centered_employees" DOUBLE,
  "office_centered_all_reported" VARCHAR  -- Office Centered / All Reported,
  "office_centered_telework_eligible" VARCHAR  -- Office Centered / Telework Eligible,
  "teleworker_remote_office" DOUBLE  -- Teleworker (remote + Office),
  "teleworker_all_reported" VARCHAR  -- Teleworker / All Reported,
  "teleworker_telework_eligible" VARCHAR  -- Teleworker / Telework Eligible,
  "not_teleworking_employees_from_eligible" DOUBLE,
  "not_teleworking_all_reported" VARCHAR  -- Not Teleworking / All Reported,
  "not_teleworking_telework_eligible" VARCHAR  -- Not Teleworking / Telework Eligible,
  "not_teleworking_not_eligible" DOUBLE  -- Not Teleworking + Not Eligible,
  "not_teleworking_not_eligible_all_reported" VARCHAR  -- Not Teleworking + Not Eligible / All Reported,
  "telework_days_from_eligible" VARCHAR  -- Telework Days % From Eligible
);

CREATE TABLE employee_by_classification (
  "reporting_year" DOUBLE,
  "reporting_month" DOUBLE,
  "org_code" VARCHAR,
  "department_name" VARCHAR,
  "class_code" DOUBLE,
  "class_title" VARCHAR,
  "bargaining_unit" VARCHAR,
  "employee_count" DOUBLE,
  "not_telework_eligible" DOUBLE,
  "remote_centered" DOUBLE,
  "office_centered" DOUBLE,
  "eligible_not_teleworking" DOUBLE
);

CREATE TABLE in_office_days_detail (
  "report_month" TIMESTAMP,
  "department" VARCHAR,
  "n_0_days" BIGINT  -- 0 Days,
  "n_1_day" BIGINT  -- 1 Day,
  "n_2_days" BIGINT  -- 2 Days,
  "n_3_days" BIGINT  -- 3 Days,
  "n_4_days" BIGINT  -- 4 Days,
  "n_5_days" BIGINT  -- 5 Days,
  "total" BIGINT
);

CREATE TABLE in_office_days_trend (
  "report_month" TIMESTAMP,
  "department" VARCHAR,
  "avg_office_days_week" DOUBLE
);

CREATE TABLE state_hybrid_workforce_trend (
  "report_month" TIMESTAMP,
  "all_staff" BIGINT,
  "not_eligible_for_telework" BIGINT,
  "telework_eligible" BIGINT,
  "eligible_not_teleworking" BIGINT  -- Eligible - Not Teleworking,
  "office_centered" BIGINT,
  "remote_centered" BIGINT
);

CREATE TABLE telework_days_detail (
  "report_month" TIMESTAMP,
  "department" VARCHAR,
  "n_0_days" BIGINT  -- 0 Days,
  "n_1_day" BIGINT  -- 1 Day,
  "n_2_days" BIGINT  -- 2 Days,
  "n_3_days" BIGINT  -- 3 Days,
  "n_4_days" BIGINT  -- 4 Days,
  "n_5_days" BIGINT  -- 5 Days,
  "total" BIGINT
);

CREATE TABLE telework_days_trend (
  "report_month" TIMESTAMP,
  "department" VARCHAR,
  "avg_telework_days_week" DOUBLE
);