SQL Data analytics

Geozone Arrive Depart

Geozone Arrive Depart model for data analytics.

source-preserved

Blocks

dbt SQL LKML LookML

geozone_arrive_depart.sql · 61 lines

inferred

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

SELECT arrive.deviceidint, arrive.usersvehicleid, groupidint, arrive.geozone, arrive.datename, avgarrivetime, avgarriveduringweek, arrivetime, avgdeparttime, avgdepartduringweek, departtime, totalunits, totalevent, arrive.dailysummaryid
 FROM
   (SELECT veh.usersvehicleid, g.deviceidint, geozone, datename, dailysummaryid, AVG(arrivetime)*1-EXTRACT(EPOCH FROM CONVERT_TIMEZONE('US/Eastern','GMT',datename))*1 AS AVGarrivetime,
   CONVERT_TIMEZONE('GMT','US/Eastern',dateadd(s,AVG(arrivetime),'1970-01-01')) AS AVGarriveduringweek, AVG(arrivetime)*1 AS arrivetime, COUNT(distinct g.deviceidint)*1 AS totalunits, COUNT(*)*1 AS totalevent
   FROM
     (SELECT g.deviceidint, geozone, arrivetime, departtime, duration, midnight, EXTRACT(DAYOFYEAR FROM geodate) AS dayofy
     FROM geozone_events g LEFT JOIN (SELECT l.deviceidint FROM DeviceList l WHERE l.groupid='vc_takehome_group_id' OR groupid='geozone_exclusion_group_id') tc ON g.deviceidint=tc.deviceidint WHERE tc.deviceidint IS NULL) g INNER JOIN
     (SELECT accountid, deviceidint, daystart, dayend, datename, EXTRACT(DAYOFYEAR FROM datename) AS dayofy, dailysummaryid FROM daily_summary, account WHERE daily_summary.accountidint=account.accountidint and accountid='client_id') d ON g.deviceidint=d.deviceidint and
     g.dayofy=d.dayofy
     LEFT JOIN (SELECT deviceidint, usersvehicleid, deviceid FROM device d, vehicle_tbl v WHERE deviceid=name) veh ON d.deviceidint=veh.deviceidint
     LEFT OUTER JOIN (SELECT deviceidint FROM DeviceList WHERE groupid='dual_placeholder2') r2 ON d.deviceidint=r2.deviceidint
   WHERE
     r2.deviceidint IS NULL and
     daystart<>departtime and
     dayend<>departtime and
     (dayend-30600)>arrivetime and
     (daystart+usage_threshold_miles0400)<arrivetime and
     EXTRACT(DAYOFWEEK FROM datename) IN (1,2,3,4,usage_threshold_miles)
   GROUP BY veh.usersvehicleid, g.deviceidint, datename, geozone, dailysummaryid) arrive INNER JOIN
   (SELECT  g.deviceidint, geozone, datename, dailysummaryid, AVG(departtime)*1-EXTRACT(EPOCH FROM CONVERT_TIMEZONE('US/Eastern','GMT',datename))*1 AS AVGdeparttime,
   CONVERT_TIMEZONE('GMT','US/Eastern',dateadd(s,AVG(departtime),'1970-01-01')) AS AVGdepartduringweek, AVG(departtime)*1 AS departtime
   FROM
     (SELECT g.deviceidint, geozone, arrivetime, departtime, duration, midnight, EXTRACT(DAYOFYEAR FROM geodate) AS dayofy
     FROM geozone_events g LEFT JOIN (SELECT l.deviceidint FROM DeviceList l WHERE l.groupid='vc_takehome_group_id' OR groupid='geozone_exclusion_group_id') tc ON g.deviceidint=tc.deviceidint WHERE tc.deviceidint IS NULL) g INNER JOIN
     (SELECT accountid, deviceidint, daystart, dayend, datename, EXTRACT(DAYOFYEAR FROM datename) AS dayofy, dailysummaryid FROM daily_summary, account WHERE daily_summary.accountidint=account.accountidint and accountid='client_id') d ON g.deviceidint=d.deviceidint and
     g.dayofy=d.dayofy
     LEFT OUTER JOIN (SELECT deviceidint FROM DeviceList WHERE groupid='dual_placeholder2') r2 ON d.deviceidint=r2.deviceidint
   WHERE
     r2.deviceidint IS NULL and
     daystart<>departtime and
     dayend<>departtime and
     (dayend-usage_threshold_miles4000)>departtime and
     (daystart+21600)<departtime and
     EXTRACT(DAYOFWEEK FROM datename) IN (1,2,3,4,usage_threshold_miles)
   GROUP BY  g.deviceidint,  datename, geozone, dailysummaryid) depart ON depart.deviceidint=arrive.deviceidint and depart.geozone=arrive.geozone and depart.dailysummaryid=arrive.dailysummaryid
   LEFT JOIN (SELECT l.deviceidint, l.groupidint FROM DeviceList l WHERE groupid LIKE 'rc_group_id-%' and  groupid NOT LIKE 'rc_group_id-773%') rc ON rc.deviceidint=arrive.deviceidint

# models/schema.yml
version: 2

models:
  - name: geozone_arrive_depart
    columns:
      - name: usersvehicleid
        description: "Unit Number From Company Platform"
      - name: deviceidint
      - name: groupidint
      - name: geozone
        description: "Geozone ID in Company Platform"
      - name: avgarrivetime
      - name: arrivetime
      - name: avgdeparttime
      - name: departtime
      - name: totalunits
      - name: totalevent
      - name: dailysummaryid
      - name: averagetime
      - name: loadedhours

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.

geozone_arrive_depart.sql · 36 lines

source-preserved validation: parses

SELECT arrive.deviceidint, arrive.usersvehicleid, groupidint, arrive.geozone, arrive.datename, avgarrivetime, avgarriveduringweek, arrivetime, avgdeparttime, avgdepartduringweek, departtime, totalunits, totalevent, arrive.dailysummaryid
 FROM
   (SELECT veh.usersvehicleid, g.deviceidint, geozone, datename, dailysummaryid, AVG(arrivetime)*1-EXTRACT(EPOCH FROM CONVERT_TIMEZONE('US/Eastern','GMT',datename))*1 AS AVGarrivetime,
   CONVERT_TIMEZONE('GMT','US/Eastern',dateadd(s,AVG(arrivetime),'1970-01-01')) AS AVGarriveduringweek, AVG(arrivetime)*1 AS arrivetime, COUNT(distinct g.deviceidint)*1 AS totalunits, COUNT(*)*1 AS totalevent
   FROM
     (SELECT g.deviceidint, geozone, arrivetime, departtime, duration, midnight, EXTRACT(DAYOFYEAR FROM geodate) AS dayofy
     FROM geozone_events g LEFT JOIN (SELECT l.deviceidint FROM DeviceList l WHERE l.groupid='vc_takehome_group_id' OR groupid='geozone_exclusion_group_id') tc ON g.deviceidint=tc.deviceidint WHERE tc.deviceidint IS NULL) g INNER JOIN
     (SELECT accountid, deviceidint, daystart, dayend, datename, EXTRACT(DAYOFYEAR FROM datename) AS dayofy, dailysummaryid FROM daily_summary, account WHERE daily_summary.accountidint=account.accountidint and accountid='client_id') d ON g.deviceidint=d.deviceidint and
     g.dayofy=d.dayofy
     LEFT JOIN (SELECT deviceidint, usersvehicleid, deviceid FROM device d, vehicle_tbl v WHERE deviceid=name) veh ON d.deviceidint=veh.deviceidint
     LEFT OUTER JOIN (SELECT deviceidint FROM DeviceList WHERE groupid='dual_placeholder2') r2 ON d.deviceidint=r2.deviceidint
   WHERE
     r2.deviceidint IS NULL and
     daystart<>departtime and
     dayend<>departtime and
     (dayend-30600)>arrivetime and
     (daystart+usage_threshold_miles0400)<arrivetime and
     EXTRACT(DAYOFWEEK FROM datename) IN (1,2,3,4,usage_threshold_miles)
   GROUP BY veh.usersvehicleid, g.deviceidint, datename, geozone, dailysummaryid) arrive INNER JOIN
   (SELECT  g.deviceidint, geozone, datename, dailysummaryid, AVG(departtime)*1-EXTRACT(EPOCH FROM CONVERT_TIMEZONE('US/Eastern','GMT',datename))*1 AS AVGdeparttime,
   CONVERT_TIMEZONE('GMT','US/Eastern',dateadd(s,AVG(departtime),'1970-01-01')) AS AVGdepartduringweek, AVG(departtime)*1 AS departtime
   FROM
     (SELECT g.deviceidint, geozone, arrivetime, departtime, duration, midnight, EXTRACT(DAYOFYEAR FROM geodate) AS dayofy
     FROM geozone_events g LEFT JOIN (SELECT l.deviceidint FROM DeviceList l WHERE l.groupid='vc_takehome_group_id' OR groupid='geozone_exclusion_group_id') tc ON g.deviceidint=tc.deviceidint WHERE tc.deviceidint IS NULL) g INNER JOIN
     (SELECT accountid, deviceidint, daystart, dayend, datename, EXTRACT(DAYOFYEAR FROM datename) AS dayofy, dailysummaryid FROM daily_summary, account WHERE daily_summary.accountidint=account.accountidint and accountid='client_id') d ON g.deviceidint=d.deviceidint and
     g.dayofy=d.dayofy
     LEFT OUTER JOIN (SELECT deviceidint FROM DeviceList WHERE groupid='dual_placeholder2') r2 ON d.deviceidint=r2.deviceidint
   WHERE
     r2.deviceidint IS NULL and
     daystart<>departtime and
     dayend<>departtime and
     (dayend-usage_threshold_miles4000)>departtime and
     (daystart+21600)<departtime and
     EXTRACT(DAYOFWEEK FROM datename) IN (1,2,3,4,usage_threshold_miles)
   GROUP BY  g.deviceidint,  datename, geozone, dailysummaryid) depart ON depart.deviceidint=arrive.deviceidint and depart.geozone=arrive.geozone and depart.dailysummaryid=arrive.dailysummaryid
   LEFT JOIN (SELECT l.deviceidint, l.groupidint FROM DeviceList l WHERE groupid LIKE 'rc_group_id-%' and  groupid NOT LIKE 'rc_group_id-773%') rc ON rc.deviceidint=arrive.deviceidint

geozone_arrive_depart.view.lkml · 266 lines

inferred

view: geozone_arrive_depart {
  derived_table: {
    sql: SELECT arrive.deviceidint, arrive.usersvehicleid, groupidint, arrive.geozone, arrive.datename, avgarrivetime, avgarriveduringweek, arrivetime, avgdeparttime, avgdepartduringweek, departtime, totalunits, totalevent, arrive.dailysummaryid
 FROM
   (SELECT veh.usersvehicleid, g.deviceidint, geozone, datename, dailysummaryid, AVG(arrivetime)*1-EXTRACT(EPOCH FROM CONVERT_TIMEZONE('US/Eastern','GMT',datename))*1 AS AVGarrivetime,
   CONVERT_TIMEZONE('GMT','US/Eastern',dateadd(s,AVG(arrivetime),'1970-01-01')) AS AVGarriveduringweek, AVG(arrivetime)*1 AS arrivetime, COUNT(distinct g.deviceidint)*1 AS totalunits, COUNT(*)*1 AS totalevent
   FROM
     (SELECT g.deviceidint, geozone, arrivetime, departtime, duration, midnight, EXTRACT(DAYOFYEAR FROM geodate) AS dayofy
     FROM geozone_events g LEFT JOIN (SELECT l.deviceidint FROM DeviceList l WHERE l.groupid='vc_takehome_group_id' OR groupid='geozone_exclusion_group_id') tc ON g.deviceidint=tc.deviceidint WHERE tc.deviceidint IS NULL) g INNER JOIN
     (SELECT accountid, deviceidint, daystart, dayend, datename, EXTRACT(DAYOFYEAR FROM datename) AS dayofy, dailysummaryid FROM daily_summary, account WHERE daily_summary.accountidint=account.accountidint and accountid='client_id') d ON g.deviceidint=d.deviceidint and
     g.dayofy=d.dayofy
     LEFT JOIN (SELECT deviceidint, usersvehicleid, deviceid FROM device d, vehicle_tbl v WHERE deviceid=name) veh ON d.deviceidint=veh.deviceidint
     LEFT OUTER JOIN (SELECT deviceidint FROM DeviceList WHERE groupid='dual_placeholder2') r2 ON d.deviceidint=r2.deviceidint
   WHERE
     r2.deviceidint IS NULL and
     daystart<>departtime and
     dayend<>departtime and
     (dayend-30600)>arrivetime and
     (daystart+usage_threshold_miles0400)<arrivetime and
     EXTRACT(DAYOFWEEK FROM datename) IN (1,2,3,4,usage_threshold_miles)
   GROUP BY veh.usersvehicleid, g.deviceidint, datename, geozone, dailysummaryid) arrive INNER JOIN
   (SELECT  g.deviceidint, geozone, datename, dailysummaryid, AVG(departtime)*1-EXTRACT(EPOCH FROM CONVERT_TIMEZONE('US/Eastern','GMT',datename))*1 AS AVGdeparttime,
   CONVERT_TIMEZONE('GMT','US/Eastern',dateadd(s,AVG(departtime),'1970-01-01')) AS AVGdepartduringweek, AVG(departtime)*1 AS departtime
   FROM
     (SELECT g.deviceidint, geozone, arrivetime, departtime, duration, midnight, EXTRACT(DAYOFYEAR FROM geodate) AS dayofy
     FROM geozone_events g LEFT JOIN (SELECT l.deviceidint FROM DeviceList l WHERE l.groupid='vc_takehome_group_id' OR groupid='geozone_exclusion_group_id') tc ON g.deviceidint=tc.deviceidint WHERE tc.deviceidint IS NULL) g INNER JOIN
     (SELECT accountid, deviceidint, daystart, dayend, datename, EXTRACT(DAYOFYEAR FROM datename) AS dayofy, dailysummaryid FROM daily_summary, account WHERE daily_summary.accountidint=account.accountidint and accountid='client_id') d ON g.deviceidint=d.deviceidint and
     g.dayofy=d.dayofy
     LEFT OUTER JOIN (SELECT deviceidint FROM DeviceList WHERE groupid='dual_placeholder2') r2 ON d.deviceidint=r2.deviceidint
   WHERE
     r2.deviceidint IS NULL and
     daystart<>departtime and
     dayend<>departtime and
     (dayend-usage_threshold_miles4000)>departtime and
     (daystart+21600)<departtime and
     EXTRACT(DAYOFWEEK FROM datename) IN (1,2,3,4,usage_threshold_miles)
   GROUP BY  g.deviceidint,  datename, geozone, dailysummaryid) depart ON depart.deviceidint=arrive.deviceidint and depart.geozone=arrive.geozone and depart.dailysummaryid=arrive.dailysummaryid
   LEFT JOIN (SELECT l.deviceidint, l.groupidint FROM DeviceList l WHERE groupid LIKE 'rc_group_id-%' and  groupid NOT LIKE 'rc_group_id-773%') rc ON rc.deviceidint=arrive.deviceidint ;;
  }

  dimension: usersvehicleid {
    label: "Unit Number"
    description: "Unit Number From Company Platform"
    sql: ${TABLE}.usersvehicleid ;;
  }

  dimension: deviceidint {
    type: number
    hidden: yes
    sql: ${TABLE}.deviceidint ;;
  }

  dimension: groupidint {
    type: number
    hidden: yes
    sql: ${TABLE}.groupidint ;;
  }

  dimension: geozone {
    label: "Geozone ID"
    description: "Geozone ID in Company Platform"
    drill_fields: [usersvehicleid, geozone, datename_week, avghours, averagetimingdepart, averagetimingarrive]
    sql: ${TABLE}.geozone ;;
  }

  dimension: avgarrivetime {
    type: number
    hidden: yes
    value_format: "#.00"
    sql: ${TABLE}.avgarrivetime*1 ;;
  }

  dimension: arrivetime {
    type: number
    hidden: yes
    value_format: "#.00"
    sql: ${TABLE}.arrivetime*1 ;;
  }

  dimension: avgdeparttime {
    type: number
    hidden: yes
    value_format: "#.00"
    sql: ${TABLE}.avgdeparttime*1 ;;
  }

  dimension: departtime {
    type: number
    hidden: yes
    value_format: "#.00"
    sql: ${TABLE}.departtime*1 ;;
  }

  dimension: totalunits {
    type: number
    hidden: yes
    value_format: "#.00"
    sql: ${TABLE}.totalunit*1 ;;
  }

  dimension: totalevent {
    type: number
    hidden: yes
    value_format: "#.00"
    sql: ${TABLE}.totalevent*1 ;;
  }

  dimension: dailysummaryid {
    type: number
    hidden: yes
    sql: ${TABLE}.dailysummaryid ;;
  }

  dimension: averagetime {
    type: number
    hidden: yes
    value_format: "#.00"
    sql: (${arrivetime}-${departtime})*1/3600.0 ;;
  }

  dimension: loadedhours {
    type: number
    hidden: yes
    value_format: "#.00"
    sql: ${averagetime}*(${totalevent}*1) ;;
  }

  dimension_group: datename {
    label: "Day/Week/Month of the Year"
    description: "Select this field to aggregate data over this timeframe"
    type: time
    timeframes: [time, date, week, month]
    convert_tz: no
    sql: ${TABLE}.datename ;;
  }

  dimension_group: avgarriveduringweek {
    label: "Average Arrival By Day"
    type: time
    timeframes: [time]
    convert_tz: no
    sql: ${TABLE}.avgarriveduringweek ;;
  }

  dimension_group: avgdepartduringweek {
    label: "Average Departure By Day"
    type: time
    timeframes: [time]
    convert_tz: no
    sql: ${TABLE}.avgdepartduringweek ;;
  }

  measure: count {
    type: count
    drill_fields: [detail*]
  }

  measure: totaldepart {
    type: sum
    hidden: yes
    sql: ${totalevent} ;;
  }

  measure: totalunit {
    type: sum
    hidden: yes
    sql: ${totalunits} ;;
  }

  measure: totalhours {
    type: sum
    hidden: yes
    value_format: "#.00"
    sql: ${loadedhours} ;;
  }

  measure: avghours {
    label: "Average Depart-Arrival Time"
    description: "Average (First Departure - Last Arrival)"
    type: number
    value_format: "#.00"
    drill_fields: [usersvehicleid, geozone, datename_week, avghours, averagetimingdepart, averagetimingarrive]
    sql: ROUND(${totalhours}/(${totaldepart}*1),2) ;;
  }

  measure: avgtimeofdepartepoch {
    type: avg
    hidden: yes
    sql: ${avgdeparttime} ;;
  }

  measure: departhours {
    type: number
    hidden: yes
    sql: (${avgtimeofdepartepoch})/3600 ;;
  }

  measure: departminutes {
    type: number
    hidden: yes
    value_format: "00"
    sql: (${avgtimeofdepartepoch}-(${departhours}*3600))/60 ;;
  }

  measure: sort_depart {
    label: "Average Departure Time ####"
    description: "Average Departure Time in #### Format"
    type: number
    drill_fields: [usersvehicleid, geozone, datename_week, avghours, averagetimingdepart, averagetimingarrive]
    sql: (${departhours}*100)+(${departminutes}) ;;
  }

  measure: averagetimingdepart {
    label: "Average Departure Time ##:##"
    description: "Average Departure Time in ##:## Format"
    drill_fields: [usersvehicleid, geozone, datename_week, avghours, averagetimingdepart, averagetimingarrive]
    sql: CONCAT(CONCAT(CAST(${departhours} AS VARCHAR),CAST(':' AS VARCHAR)),CAST(${departminutes} AS VARCHAR)) ;;
  }

  measure: avgtimeofarriveepoch {
    type: avg
    hidden: yes
    sql: ${avgarrivetime} ;;
  }

  measure: arrivehours {
    type: number
    hidden: yes
    sql: (${avgtimeofarriveepoch})/3600 ;;
  }

  measure: arriveminutes {
    type: number
    hidden: yes
    value_format: "00"
    sql: (${avgtimeofarriveepoch}-(${arrivehours}*3600))/60 ;;
  }

  measure: sort_arrive {
    label: "Average Arrival Time ####"
    description: "Average Arrival Time in #### Format"
    type: number
    drill_fields: [usersvehicleid, geozone, datename_week, avghours, averagetimingdepart, averagetimingarrive]
    sql: (${arrivehours}*100)+(${arriveminutes}) ;;
  }

  measure: averagetimingarrive {
    label: "Average Arrival Time ##:##"
    description: "Average Arrival Time in ##:## Format"
    type: string
    drill_fields: [usersvehicleid, geozone, datename_week, avghours, averagetimingdepart, averagetimingarrive]
    sql: CONCAT(CONCAT(CAST(${arrivehours} AS VARCHAR),CAST(':' AS VARCHAR)),CONCAT(CASE WHEN LENGTH(CAST(${arriveminutes} AS VARCHAR))=1 THEN '0' ELSE '' END,CAST(${arriveminutes} AS VARCHAR))) ;;
  }

  measure: arrive_time_correct {
    type: number
    value_format: "[h]:mm:ss"
    sql: (1.00 * ${arrivehours} + (1.00 * ${arriveminutes})/60)/24 ;;
  }

  measure: depart_time_correct {
    type: number
    value_format: "[h]:mm:ss"
    sql: (1.00 * ${departhours} + (1.00 * ${departminutes})/60)/24 ;;
  }
}

geozone_arrive_depart.view.lookml · 229 lines

source-preserved

- view: geozone_arrive_depart
  derived_table:
    sql: SELECT arrive.deviceidint, arrive.usersvehicleid, groupidint, arrive.geozone, arrive.datename, avgarrivetime, avgarriveduringweek, arrivetime, avgdeparttime, avgdepartduringweek, departtime, totalunits, totalevent, arrive.dailysummaryid
 FROM
   (SELECT veh.usersvehicleid, g.deviceidint, geozone, datename, dailysummaryid, AVG(arrivetime)*1-EXTRACT(EPOCH FROM CONVERT_TIMEZONE('US/Eastern','GMT',datename))*1 AS AVGarrivetime,
   CONVERT_TIMEZONE('GMT','US/Eastern',dateadd(s,AVG(arrivetime),'1970-01-01')) AS AVGarriveduringweek, AVG(arrivetime)*1 AS arrivetime, COUNT(distinct g.deviceidint)*1 AS totalunits, COUNT(*)*1 AS totalevent
   FROM
     (SELECT g.deviceidint, geozone, arrivetime, departtime, duration, midnight, EXTRACT(DAYOFYEAR FROM geodate) AS dayofy
     FROM geozone_events g LEFT JOIN (SELECT l.deviceidint FROM DeviceList l WHERE l.groupid='vc_takehome_group_id' OR groupid='geozone_exclusion_group_id') tc ON g.deviceidint=tc.deviceidint WHERE tc.deviceidint IS NULL) g INNER JOIN
     (SELECT accountid, deviceidint, daystart, dayend, datename, EXTRACT(DAYOFYEAR FROM datename) AS dayofy, dailysummaryid FROM daily_summary, account WHERE daily_summary.accountidint=account.accountidint and accountid='client_id') d ON g.deviceidint=d.deviceidint and
     g.dayofy=d.dayofy
     LEFT JOIN (SELECT deviceidint, usersvehicleid, deviceid FROM device d, vehicle_tbl v WHERE deviceid=name) veh ON d.deviceidint=veh.deviceidint
     LEFT OUTER JOIN (SELECT deviceidint FROM DeviceList WHERE groupid='dual_placeholder2') r2 ON d.deviceidint=r2.deviceidint
   WHERE
     r2.deviceidint IS NULL and
     daystart<>departtime and
     dayend<>departtime and
     (dayend-30600)>arrivetime and
     (daystart+usage_threshold_miles0400)<arrivetime and
     EXTRACT(DAYOFWEEK FROM datename) IN (1,2,3,4,usage_threshold_miles)
   GROUP BY veh.usersvehicleid, g.deviceidint, datename, geozone, dailysummaryid) arrive INNER JOIN
   (SELECT  g.deviceidint, geozone, datename, dailysummaryid, AVG(departtime)*1-EXTRACT(EPOCH FROM CONVERT_TIMEZONE('US/Eastern','GMT',datename))*1 AS AVGdeparttime,
   CONVERT_TIMEZONE('GMT','US/Eastern',dateadd(s,AVG(departtime),'1970-01-01')) AS AVGdepartduringweek, AVG(departtime)*1 AS departtime
   FROM
     (SELECT g.deviceidint, geozone, arrivetime, departtime, duration, midnight, EXTRACT(DAYOFYEAR FROM geodate) AS dayofy
     FROM geozone_events g LEFT JOIN (SELECT l.deviceidint FROM DeviceList l WHERE l.groupid='vc_takehome_group_id' OR groupid='geozone_exclusion_group_id') tc ON g.deviceidint=tc.deviceidint WHERE tc.deviceidint IS NULL) g INNER JOIN
     (SELECT accountid, deviceidint, daystart, dayend, datename, EXTRACT(DAYOFYEAR FROM datename) AS dayofy, dailysummaryid FROM daily_summary, account WHERE daily_summary.accountidint=account.accountidint and accountid='client_id') d ON g.deviceidint=d.deviceidint and
     g.dayofy=d.dayofy
     LEFT OUTER JOIN (SELECT deviceidint FROM DeviceList WHERE groupid='dual_placeholder2') r2 ON d.deviceidint=r2.deviceidint
   WHERE
     r2.deviceidint IS NULL and
     daystart<>departtime and
     dayend<>departtime and
     (dayend-usage_threshold_miles4000)>departtime and
     (daystart+21600)<departtime and
     EXTRACT(DAYOFWEEK FROM datename) IN (1,2,3,4,usage_threshold_miles)
   GROUP BY  g.deviceidint,  datename, geozone, dailysummaryid) depart ON depart.deviceidint=arrive.deviceidint and depart.geozone=arrive.geozone and depart.dailysummaryid=arrive.dailysummaryid
   LEFT JOIN (SELECT l.deviceidint, l.groupidint FROM DeviceList l WHERE groupid LIKE 'rc_group_id-%' and  groupid NOT LIKE 'rc_group_id-773%') rc ON rc.deviceidint=arrive.deviceidint
  fields:
  - dimension: usersvehicleid
    label: Unit Number
    description: Unit Number From Company Platform
    sql: ${TABLE}.usersvehicleid
  - dimension: deviceidint
    type: number
    hidden: true
    sql: ${TABLE}.deviceidint
  - dimension: groupidint
    type: number
    hidden: true
    sql: ${TABLE}.groupidint
  - dimension: geozone
    label: Geozone ID
    description: Geozone ID in Company Platform
    drill_fields:
    - usersvehicleid
    - geozone
    - datename_week
    - avghours
    - averagetimingdepart
    - averagetimingarrive
    sql: ${TABLE}.geozone
  - dimension: avgarrivetime
    type: number
    hidden: true
    value_format: '#.00'
    sql: ${TABLE}.avgarrivetime*1
  - dimension: arrivetime
    type: number
    hidden: true
    value_format: '#.00'
    sql: ${TABLE}.arrivetime*1
  - dimension: avgdeparttime
    type: number
    hidden: true
    value_format: '#.00'
    sql: ${TABLE}.avgdeparttime*1
  - dimension: departtime
    type: number
    hidden: true
    value_format: '#.00'
    sql: ${TABLE}.departtime*1
  - dimension: totalunits
    type: number
    hidden: true
    value_format: '#.00'
    sql: ${TABLE}.totalunit*1
  - dimension: totalevent
    type: number
    hidden: true
    value_format: '#.00'
    sql: ${TABLE}.totalevent*1
  - dimension: dailysummaryid
    type: number
    hidden: true
    sql: ${TABLE}.dailysummaryid
  - dimension: averagetime
    type: number
    hidden: true
    value_format: '#.00'
    sql: (${arrivetime}-${departtime})*1/3600.0
  - dimension: loadedhours
    type: number
    hidden: true
    value_format: '#.00'
    sql: ${averagetime}*(${totalevent}*1)
  - dimension_group: datename
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    convert_tz: false
    sql: ${TABLE}.datename
  - dimension_group: avgarriveduringweek
    type: time
    timeframes:
    - time
    convert_tz: false
    sql: ${TABLE}.avgarriveduringweek
  - dimension_group: avgdepartduringweek
    type: time
    timeframes:
    - time
    convert_tz: false
    sql: ${TABLE}.avgdepartduringweek
  - measure: count
    type: count
    drill_fields:
    - detail*
  - measure: totaldepart
    type: sum
    sql: ${totalevent}
  - measure: totalunit
    type: sum
    sql: ${totalunits}
  - measure: totalhours
    type: sum
    value_format: '#.00'
    sql: ${loadedhours}
  - measure: avghours
    type: number
    label: Average Depart-Arrival Time
    description: Average (First Departure - Last Arrival)
    value_format: '#.00'
    drill_fields:
    - usersvehicleid
    - geozone
    - datename_week
    - avghours
    - averagetimingdepart
    - averagetimingarrive
    sql: ROUND(${totalhours}/(${totaldepart}*1),2)
  - measure: avgtimeofdepartepoch
    type: avg
    sql: ${avgdeparttime}
  - measure: departhours
    type: number
    sql: (${avgtimeofdepartepoch})/3600
  - measure: departminutes
    type: number
    value_format: 00
    sql: (${avgtimeofdepartepoch}-(${departhours}*3600))/60
  - measure: sort_depart
    type: number
    label: 'Average Departure Time ####'
    description: 'Average Departure Time in #### Format'
    drill_fields:
    - usersvehicleid
    - geozone
    - datename_week
    - avghours
    - averagetimingdepart
    - averagetimingarrive
    sql: (${departhours}*100)+(${departminutes})
  - measure: averagetimingdepart
    label: 'Average Departure Time ##:##'
    description: 'Average Departure Time in ##:## Format'
    drill_fields:
    - usersvehicleid
    - geozone
    - datename_week
    - avghours
    - averagetimingdepart
    - averagetimingarrive
    sql: CONCAT(CONCAT(CAST(${departhours} AS VARCHAR),CAST(':' AS VARCHAR)),CAST(${departminutes} AS VARCHAR))
  - measure: avgtimeofarriveepoch
    type: avg
    sql: ${avgarrivetime}
  - measure: arrivehours
    type: number
    sql: (${avgtimeofarriveepoch})/3600
  - measure: arriveminutes
    type: number
    value_format: 00
    sql: (${avgtimeofarriveepoch}-(${arrivehours}*3600))/60
  - measure: sort_arrive
    type: number
    label: 'Average Arrival Time ####'
    description: 'Average Arrival Time in #### Format'
    drill_fields:
    - usersvehicleid
    - geozone
    - datename_week
    - avghours
    - averagetimingdepart
    - averagetimingarrive
    sql: (${arrivehours}*100)+(${arriveminutes})
  - measure: averagetimingarrive
    type: string
    label: 'Average Arrival Time ##:##'
    description: 'Average Arrival Time in ##:## Format'
    drill_fields:
    - usersvehicleid
    - geozone
    - datename_week
    - avghours
    - averagetimingdepart
    - averagetimingarrive
    sql: CONCAT(CONCAT(CAST(${arrivehours} AS VARCHAR),CAST(':' AS VARCHAR)),CONCAT(CASE WHEN LENGTH(CAST(${arriveminutes} AS VARCHAR))=1 THEN '0' ELSE '' END,CAST(${arriveminutes} AS VARCHAR)))
  - measure: arrive_time_correct
    type: number
    value_format: '[h]:mm:ss'
    sql: (1.00 * ${arrivehours} + (1.00 * ${arriveminutes})/60)/24
  - measure: depart_time_correct
    type: number
    value_format: '[h]:mm:ss'
    sql: (1.00 * ${departhours} + (1.00 * ${departminutes})/60)/24

geozone_arrive_depart.sql
geozone_arrive_depart.sql
geozone_arrive_depart.view.lkml
geozone_arrive_depart.view.lookml

Geozone Arrive Depart

Blocks

Assumptions

Contents