dbt Delivery service

Transport Ratings 2

Transport Ratings 2 model for delivery service.

inferred

Blocks

dbt SQL LKML LookML

transport_ratings_2.sql · 53 lines

inferred

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

Select metros_id
      ,metro_name
      ,driver_rating_count
      ,((Total_Driver_Rating_1 + Total_Driver_Rating_2 + Total_Driver_Rating_3 + Total_Driver_Rating_4 + Total_Driver_Rating_5) / driver_rating_count)  as driver_weighted_average_ratings
      ,(Total_Driver_Rating_1 + Total_Driver_Rating_2 + Total_Driver_Rating_3 + Total_Driver_Rating_4 + Total_Driver_Rating_5)  as driver_weighted_total_ratings
      ,overall_rating_count
      ,((Total_Overall_Rating_1 + Total_Overall_Rating_2 + Total_Overall_Rating_3 + Total_Overall_Rating_4 + Total_Overall_Rating_5) / overall_rating_count)  as overall_weighted_average_ratings
      ,(Total_Overall_Rating_1 + Total_Overall_Rating_2 + Total_Overall_Rating_3 + Total_Overall_Rating_4 + Total_Overall_Rating_5)  as overall_weighted_total_ratings
      ,((NPS_Promoter - NPS_Detracter)/overall_rating_count) as Net_Promoter_Score
  From
    (Select
             metros.id as metros_id
            ,metros.name as metro_name
            ,IFNULL(SUM(CASE WHEN tr.driver_rating = 1 then 1 END),0) as Total_Driver_Rating_1
            ,IFNULL(SUM(CASE WHEN tr.driver_rating = 2 then 2 END),0) as Total_Driver_Rating_2
            ,IFNULL(SUM(CASE WHEN tr.driver_rating = 3 then 3 END),0) as Total_Driver_Rating_3
            ,IFNULL(SUM(CASE WHEN tr.driver_rating = 4 then 4 END),0) as Total_Driver_Rating_4
            ,IFNULL(SUM(CASE WHEN tr.driver_rating = 5 then 5 END),0) as Total_Driver_Rating_5
            ,Count(tr.driver_rating) as driver_rating_count
            ,IFNULL(SUM(CASE WHEN tr.overall_rating = 1 then 1 END),0) as Total_Overall_Rating_1
            ,IFNULL(SUM(CASE WHEN tr.overall_rating = 2 then 2 END),0) as Total_Overall_Rating_2
            ,IFNULL(SUM(CASE WHEN tr.overall_rating = 3 then 3 END),0) as Total_Overall_Rating_3
            ,IFNULL(SUM(CASE WHEN tr.overall_rating = 4 then 4 END),0) as Total_Overall_Rating_4
            ,IFNULL(SUM(CASE WHEN tr.overall_rating = 5 then 5 END),0) as Total_Overall_Rating_5
            ,Count(tr.overall_rating) as overall_rating_count
            ,IFNULL(SUM(CASE WHEN (tr.shipment_company_nps = 9 OR tr.shipment_company_nps = 10) THEN 1 END),0) as NPS_Promoter
            ,IFNULL(SUM(CASE WHEN tr.shipment_company_nps < 7 THEN 1 END),0) as NPS_Detracter
        FROM transport_ratings as tr
        LEFT JOIN shipment_companyeries
          ON shipment_companyeries.id = tr.transport_id
        LEFT JOIN orders
          ON shipment_companyeries.order_id = orders.id
        LEFT JOIN metros
          ON orders.metro_id=metros.id
      Where tr.id > 1940
      Group by 1,2
      ) a
 Group by 1,2

# models/schema.yml
version: 2

models:
  - name: transport_ratings_2
    columns:
      - name: metros_id
        tests:
          - unique
          - not_null
      - name: metro_name

Assumptions

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.

transport_ratings_2.sql · 38 lines

source-preserved validation: parses

Select metros_id
      ,metro_name
      ,driver_rating_count
      ,((Total_Driver_Rating_1 + Total_Driver_Rating_2 + Total_Driver_Rating_3 + Total_Driver_Rating_4 + Total_Driver_Rating_5) / driver_rating_count)  as driver_weighted_average_ratings
      ,(Total_Driver_Rating_1 + Total_Driver_Rating_2 + Total_Driver_Rating_3 + Total_Driver_Rating_4 + Total_Driver_Rating_5)  as driver_weighted_total_ratings
      ,overall_rating_count
      ,((Total_Overall_Rating_1 + Total_Overall_Rating_2 + Total_Overall_Rating_3 + Total_Overall_Rating_4 + Total_Overall_Rating_5) / overall_rating_count)  as overall_weighted_average_ratings
      ,(Total_Overall_Rating_1 + Total_Overall_Rating_2 + Total_Overall_Rating_3 + Total_Overall_Rating_4 + Total_Overall_Rating_5)  as overall_weighted_total_ratings
      ,((NPS_Promoter - NPS_Detracter)/overall_rating_count) as Net_Promoter_Score
  From
    (Select
             metros.id as metros_id
            ,metros.name as metro_name
            ,IFNULL(SUM(CASE WHEN tr.driver_rating = 1 then 1 END),0) as Total_Driver_Rating_1
            ,IFNULL(SUM(CASE WHEN tr.driver_rating = 2 then 2 END),0) as Total_Driver_Rating_2
            ,IFNULL(SUM(CASE WHEN tr.driver_rating = 3 then 3 END),0) as Total_Driver_Rating_3
            ,IFNULL(SUM(CASE WHEN tr.driver_rating = 4 then 4 END),0) as Total_Driver_Rating_4
            ,IFNULL(SUM(CASE WHEN tr.driver_rating = 5 then 5 END),0) as Total_Driver_Rating_5
            ,Count(tr.driver_rating) as driver_rating_count
            ,IFNULL(SUM(CASE WHEN tr.overall_rating = 1 then 1 END),0) as Total_Overall_Rating_1
            ,IFNULL(SUM(CASE WHEN tr.overall_rating = 2 then 2 END),0) as Total_Overall_Rating_2
            ,IFNULL(SUM(CASE WHEN tr.overall_rating = 3 then 3 END),0) as Total_Overall_Rating_3
            ,IFNULL(SUM(CASE WHEN tr.overall_rating = 4 then 4 END),0) as Total_Overall_Rating_4
            ,IFNULL(SUM(CASE WHEN tr.overall_rating = 5 then 5 END),0) as Total_Overall_Rating_5
            ,Count(tr.overall_rating) as overall_rating_count
            ,IFNULL(SUM(CASE WHEN (tr.shipment_company_nps = 9 OR tr.shipment_company_nps = 10) THEN 1 END),0) as NPS_Promoter
            ,IFNULL(SUM(CASE WHEN tr.shipment_company_nps < 7 THEN 1 END),0) as NPS_Detracter
        FROM transport_ratings as tr
        LEFT JOIN shipment_companyeries
          ON shipment_companyeries.id = tr.transport_id
        LEFT JOIN orders
          ON shipment_companyeries.order_id = orders.id
        LEFT JOIN metros
          ON orders.metro_id=metros.id
      Where tr.id > 1940
      Group by 1,2
      ) a
 Group by 1,2

transport_ratings_2.view.lkml · 85 lines

inferred

view: transport_ratings_2 {
  derived_table: {
    sql: Select metros_id
      ,metro_name
      ,driver_rating_count
      ,((Total_Driver_Rating_1 + Total_Driver_Rating_2 + Total_Driver_Rating_3 + Total_Driver_Rating_4 + Total_Driver_Rating_5) / driver_rating_count)  as driver_weighted_average_ratings
      ,(Total_Driver_Rating_1 + Total_Driver_Rating_2 + Total_Driver_Rating_3 + Total_Driver_Rating_4 + Total_Driver_Rating_5)  as driver_weighted_total_ratings
      ,overall_rating_count
      ,((Total_Overall_Rating_1 + Total_Overall_Rating_2 + Total_Overall_Rating_3 + Total_Overall_Rating_4 + Total_Overall_Rating_5) / overall_rating_count)  as overall_weighted_average_ratings
      ,(Total_Overall_Rating_1 + Total_Overall_Rating_2 + Total_Overall_Rating_3 + Total_Overall_Rating_4 + Total_Overall_Rating_5)  as overall_weighted_total_ratings
      ,((NPS_Promoter - NPS_Detracter)/overall_rating_count) as Net_Promoter_Score
  From
    (Select
             metros.id as metros_id
            ,metros.name as metro_name
            ,IFNULL(SUM(CASE WHEN tr.driver_rating = 1 then 1 END),0) as Total_Driver_Rating_1
            ,IFNULL(SUM(CASE WHEN tr.driver_rating = 2 then 2 END),0) as Total_Driver_Rating_2
            ,IFNULL(SUM(CASE WHEN tr.driver_rating = 3 then 3 END),0) as Total_Driver_Rating_3
            ,IFNULL(SUM(CASE WHEN tr.driver_rating = 4 then 4 END),0) as Total_Driver_Rating_4
            ,IFNULL(SUM(CASE WHEN tr.driver_rating = 5 then 5 END),0) as Total_Driver_Rating_5
            ,Count(tr.driver_rating) as driver_rating_count
            ,IFNULL(SUM(CASE WHEN tr.overall_rating = 1 then 1 END),0) as Total_Overall_Rating_1
            ,IFNULL(SUM(CASE WHEN tr.overall_rating = 2 then 2 END),0) as Total_Overall_Rating_2
            ,IFNULL(SUM(CASE WHEN tr.overall_rating = 3 then 3 END),0) as Total_Overall_Rating_3
            ,IFNULL(SUM(CASE WHEN tr.overall_rating = 4 then 4 END),0) as Total_Overall_Rating_4
            ,IFNULL(SUM(CASE WHEN tr.overall_rating = 5 then 5 END),0) as Total_Overall_Rating_5
            ,Count(tr.overall_rating) as overall_rating_count
            ,IFNULL(SUM(CASE WHEN (tr.shipment_company_nps = 9 OR tr.shipment_company_nps = 10) THEN 1 END),0) as NPS_Promoter
            ,IFNULL(SUM(CASE WHEN tr.shipment_company_nps < 7 THEN 1 END),0) as NPS_Detracter
        FROM transport_ratings as tr
        LEFT JOIN shipment_companyeries
          ON shipment_companyeries.id = tr.transport_id
        LEFT JOIN orders
          ON shipment_companyeries.order_id = orders.id
        LEFT JOIN metros
          ON orders.metro_id=metros.id
      Where tr.id > 1940
      Group by 1,2
      ) a
 Group by 1,2 ;;
  }

  dimension: metros_id {
    type: number
    primary_key: yes
    sql: ${TABLE}.metros_id ;;
  }

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

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

  measure: driver_weighted_average_ratings {
    sql: ${TABLE}.driver_weighted_average_ratings ;;
  }

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

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

  measure: overall_weighted_average_ratings {
    sql: ${TABLE}.overall_weighted_average_ratings ;;
  }

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

  measure: Net_Promoter_Score {
    type: percentage
    sql: ${TABLE}.Net_Promoter_Score ;;
  }
}

transport_ratings_2.view.lookml · 66 lines

source-preserved

- view: transport_ratings_2
  derived_table:
    sql: Select metros_id
      ,metro_name
      ,driver_rating_count
      ,((Total_Driver_Rating_1 + Total_Driver_Rating_2 + Total_Driver_Rating_3 + Total_Driver_Rating_4 + Total_Driver_Rating_5) / driver_rating_count)  as driver_weighted_average_ratings
      ,(Total_Driver_Rating_1 + Total_Driver_Rating_2 + Total_Driver_Rating_3 + Total_Driver_Rating_4 + Total_Driver_Rating_5)  as driver_weighted_total_ratings
      ,overall_rating_count
      ,((Total_Overall_Rating_1 + Total_Overall_Rating_2 + Total_Overall_Rating_3 + Total_Overall_Rating_4 + Total_Overall_Rating_5) / overall_rating_count)  as overall_weighted_average_ratings
      ,(Total_Overall_Rating_1 + Total_Overall_Rating_2 + Total_Overall_Rating_3 + Total_Overall_Rating_4 + Total_Overall_Rating_5)  as overall_weighted_total_ratings
      ,((NPS_Promoter - NPS_Detracter)/overall_rating_count) as Net_Promoter_Score
  From
    (Select
             metros.id as metros_id
            ,metros.name as metro_name
            ,IFNULL(SUM(CASE WHEN tr.driver_rating = 1 then 1 END),0) as Total_Driver_Rating_1
            ,IFNULL(SUM(CASE WHEN tr.driver_rating = 2 then 2 END),0) as Total_Driver_Rating_2
            ,IFNULL(SUM(CASE WHEN tr.driver_rating = 3 then 3 END),0) as Total_Driver_Rating_3
            ,IFNULL(SUM(CASE WHEN tr.driver_rating = 4 then 4 END),0) as Total_Driver_Rating_4
            ,IFNULL(SUM(CASE WHEN tr.driver_rating = 5 then 5 END),0) as Total_Driver_Rating_5
            ,Count(tr.driver_rating) as driver_rating_count
            ,IFNULL(SUM(CASE WHEN tr.overall_rating = 1 then 1 END),0) as Total_Overall_Rating_1
            ,IFNULL(SUM(CASE WHEN tr.overall_rating = 2 then 2 END),0) as Total_Overall_Rating_2
            ,IFNULL(SUM(CASE WHEN tr.overall_rating = 3 then 3 END),0) as Total_Overall_Rating_3
            ,IFNULL(SUM(CASE WHEN tr.overall_rating = 4 then 4 END),0) as Total_Overall_Rating_4
            ,IFNULL(SUM(CASE WHEN tr.overall_rating = 5 then 5 END),0) as Total_Overall_Rating_5
            ,Count(tr.overall_rating) as overall_rating_count
            ,IFNULL(SUM(CASE WHEN (tr.shipment_company_nps = 9 OR tr.shipment_company_nps = 10) THEN 1 END),0) as NPS_Promoter
            ,IFNULL(SUM(CASE WHEN tr.shipment_company_nps < 7 THEN 1 END),0) as NPS_Detracter
        FROM transport_ratings as tr
        LEFT JOIN shipment_companyeries
          ON shipment_companyeries.id = tr.transport_id
        LEFT JOIN orders
          ON shipment_companyeries.order_id = orders.id
        LEFT JOIN metros
          ON orders.metro_id=metros.id
      Where tr.id > 1940
      Group by 1,2
      ) a
 Group by 1,2
  fields:
  - dimension: metros_id
    type: number
    primary_key: true
    sql: ${TABLE}.metros_id
  - dimension: metro_name
    sql: ${TABLE}.metro_name
  - measure: driver_rating_count
    type: number
    sql: ${TABLE}.driver_rating_count
  - measure: driver_weighted_average_ratings
    sql: ${TABLE}.driver_weighted_average_ratings
  - measure: driver_weighted_total_ratings
    type: number
    sql: ${TABLE}.driver_weighted_total_ratings
  - measure: overall_rating_count
    type: number
    sql: ${TABLE}.overall_rating_count
  - measure: overall_weighted_average_ratings
    sql: ${TABLE}.overall_weighted_average_ratings
  - measure: overall_weighted_total_ratings
    type: number
    sql: ${TABLE}.overall_weighted_total_ratings
  - measure: Net_Promoter_Score
    type: percentage
    sql: ${TABLE}.Net_Promoter_Score

transport_ratings_2.sql
transport_ratings_2.sql
transport_ratings_2.view.lkml
transport_ratings_2.view.lookml

Transport Ratings 2

Blocks

Assumptions

Contents