Driver Peak Hod Vs Driverpool

-- models/driver_peak_hod_vs_driverpool.sql
{{ config(materialized='view') }}

Select
         driver_pool.metro_id
        ,peak_hour.metros_name
        ,peak_hour.Active_Date_Local
        ,peak_hour.Peak_Driver_HoD_Count
        ,driver_pool.Authorized_Driver_Count
        ,(peak_hour.Peak_Driver_HoD_Count / driver_pool.Authorized_Driver_Count) as peak_to_pool
   From
        (Select metros_name
               ,Active_Date_Local
               ,Peak_Driver_HoD_Count
          From {{ ref('driver_peak_worked_hod') }} as  a
        Group By 1,2,3
        ) peak_hour
   LEFT JOIN
      (Select
              metros.id as metro_id
             ,case when metros.name IN ('Houston East','Houston Northwest','Houston Southwest','Houston') THEN 'Houston'
                else metros.name
                  end metros_name
             ,count(distinct users.id) as Authorized_Driver_Count
          from users
          join metros
            on users.metro_id = metros.id
        where labor_pool_id = 1
        and authorized = "true"
        and roles_mask = 16
      Group By 1,2
      ) driver_pool
    on peak_hour.metros_name = driver_pool.metros_name
Group By 1,2,3,4,5

# models/schema.yml
version: 2

models:
  - name: driver_peak_hod_vs_driverpool
    columns:
      - name: metro_id
        tests:
          - unique
          - not_null
      - name: metros_name
      - name: Peak_Driver_HoD_Count
      - name: Authorized_Driver_Count
      - name: Total_Peak_Driver_Count
      - name: Total_Authorized_Driver_Count
      - name: Total_peak_to_pool

Assumptions

• Each ${view.SQL_TABLE_NAME} was mapped to {{ ref('view') }}; confirm a dbt model exists for each referenced view.
• materialized='view' is the default; no persist hint found.
• schema.yml columns come from view dimensions; a primary_key dimension gets unique + not_null tests.

Select
         driver_pool.metro_id
        ,peak_hour.metros_name
        ,peak_hour.Active_Date_Local
        ,peak_hour.Peak_Driver_HoD_Count
        ,driver_pool.Authorized_Driver_Count
        ,(peak_hour.Peak_Driver_HoD_Count / driver_pool.Authorized_Driver_Count) as peak_to_pool
   From
        (Select metros_name
               ,Active_Date_Local
               ,Peak_Driver_HoD_Count
          From ${driver_peak_worked_hod.SQL_TABLE_NAME} as  a
        Group By 1,2,3
        ) peak_hour
   LEFT JOIN
      (Select
              metros.id as metro_id
             ,case when metros.name IN ('Houston East','Houston Northwest','Houston Southwest','Houston') THEN 'Houston'
                else metros.name
                  end metros_name
             ,count(distinct users.id) as Authorized_Driver_Count
          from users
          join metros
            on users.metro_id = metros.id
        where labor_pool_id = 1
        and authorized = "true"
        and roles_mask = 16
      Group By 1,2
      ) driver_pool
    on peak_hour.metros_name = driver_pool.metros_name
Group By 1,2,3,4,5

view: driver_peak_hod_vs_driverpool {
  derived_table: {
    sql: Select
         driver_pool.metro_id
        ,peak_hour.metros_name
        ,peak_hour.Active_Date_Local
        ,peak_hour.Peak_Driver_HoD_Count
        ,driver_pool.Authorized_Driver_Count
        ,(peak_hour.Peak_Driver_HoD_Count / driver_pool.Authorized_Driver_Count) as peak_to_pool
   From
        (Select metros_name
               ,Active_Date_Local
               ,Peak_Driver_HoD_Count
          From ${driver_peak_worked_hod.SQL_TABLE_NAME} as  a
        Group By 1,2,3
        ) peak_hour
   LEFT JOIN
      (Select
              metros.id as metro_id
             ,case when metros.name IN ('Houston East','Houston Northwest','Houston Southwest','Houston') THEN 'Houston'
                else metros.name
                  end metros_name
             ,count(distinct users.id) as Authorized_Driver_Count
          from users
          join metros
            on users.metro_id = metros.id
        where labor_pool_id = 1
        and authorized = "true"
        and roles_mask = 16
      Group By 1,2
      ) driver_pool
    on peak_hour.metros_name = driver_pool.metros_name
Group By 1,2,3,4,5 ;;
  }

  dimension: metro_id {
    type: int
    primary_key: yes
  }

  dimension: metros_name {
    sql: ${TABLE}.metros_name ;;
  }

  dimension: Peak_Driver_HoD_Count {
    sql: ${TABLE}.Peak_Driver_HoD_Count ;;
  }

  dimension: Authorized_Driver_Count {
    type: number
    sql: ${TABLE}.Authorized_Driver_Count ;;
  }

  dimension: Total_Peak_Driver_Count {
    type: max
    sql: ${Peak_Driver_HoD_Count} ;;
  }

  dimension: Total_Authorized_Driver_Count {
    type: sum
    sql: ${Authorized_Driver_Count} ;;
  }

  dimension: Total_peak_to_pool {
    type: number
    sql: ${Total_Peak_Driver_Count}/${Total_Authorized_Driver_Count} ;;
  }

  dimension_group: Active_Date_Local {
    type: time
    timeframes: [date, week, month]
    convert_tz: no
    sql: ${TABLE}.Active_Date_Local ;;
  }

  measure: peak_to_pool {
    type: number
    sql: ${TABLE}.peak_to_pool ;;
  }
}

- view: driver_peak_hod_vs_driverpool
  derived_table:
    sql: Select
         driver_pool.metro_id
        ,peak_hour.metros_name
        ,peak_hour.Active_Date_Local
        ,peak_hour.Peak_Driver_HoD_Count
        ,driver_pool.Authorized_Driver_Count
        ,(peak_hour.Peak_Driver_HoD_Count / driver_pool.Authorized_Driver_Count) as peak_to_pool
   From
        (Select metros_name
               ,Active_Date_Local
               ,Peak_Driver_HoD_Count
          From ${driver_peak_worked_hod.SQL_TABLE_NAME} as  a
        Group By 1,2,3
        ) peak_hour
   LEFT JOIN
      (Select
              metros.id as metro_id
             ,case when metros.name IN ('Houston East','Houston Northwest','Houston Southwest','Houston') THEN 'Houston'
                else metros.name
                  end metros_name
             ,count(distinct users.id) as Authorized_Driver_Count
          from users
          join metros
            on users.metro_id = metros.id
        where labor_pool_id = 1
        and authorized = "true"
        and roles_mask = 16
      Group By 1,2
      ) driver_pool
    on peak_hour.metros_name = driver_pool.metros_name
Group By 1,2,3,4,5
  fields:
  - dimension: metro_id
    type: int
    primary_key: true
  - dimension: metros_name
    sql: ${TABLE}.metros_name
  - dimension: Peak_Driver_HoD_Count
    sql: ${TABLE}.Peak_Driver_HoD_Count
  - dimension: Authorized_Driver_Count
    type: number
    sql: ${TABLE}.Authorized_Driver_Count
  - dimension: Total_Peak_Driver_Count
    type: max
    sql: ${Peak_Driver_HoD_Count}
  - dimension: Total_Authorized_Driver_Count
    type: sum
    sql: ${Authorized_Driver_Count}
  - dimension: Total_peak_to_pool
    type: number
    sql: ${Total_Peak_Driver_Count}/${Total_Authorized_Driver_Count}
  - dimension_group: Active_Date_Local
    type: time
    timeframes:
    - date
    - week
    - month
    convert_tz: false
    sql: ${TABLE}.Active_Date_Local
  - measure: peak_to_pool
    type: number
    sql: ${TABLE}.peak_to_pool