Geozone

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

SELECT g.dailysummaryid, g.datename, g.deviceidint, g.geodate, g.totaltime, g.totalgeozones,  NVL2(col.nongeozonetime,col.nongeozonetime,0) AS nongeozonetime, NVL2(col.geozonetime,col.geozonetime,0) AS geozonetime, NVL2(col.nullgeozonetime,col.nullgeozonetime,0) AS nullgeozonetime, col.deviceidint AS colsupervisor
FROM
(SELECT d.dailysummaryid, d.datename, d.deviceidint, geodate, SUM(duration)*1.0 AS totaltime, COUNT(DISTINCT geozone) AS totalgeozones
  FROM
  (SELECT deviceidint, geozone, geodate, arrivetime, departtime, duration, midnight, EXTRACT(DAYOFYEAR FROM geodate) AS dayofy FROM geozone_events) g,
  (SELECT dailysummaryid, datename, deviceidint, daystart, dayend, EXTRACT(DAYOFYEAR FROM datename) AS dayofy FROM daily_summary) d
  WHERE
  g.deviceidint=d.deviceidint and
  g.dayofy=d.dayofy
  GROUP BY d.dailysummaryid, d.deviceidint, datename, geodate) g
LEFT JOIN (SELECT d.dailysummaryid, d.deviceidint, n.nongeozonetime, g.geozonetime, nul.nullgeozonetime FROM
  daily_summary d LEFT JOIN
  (SELECT dailysummaryid, d.deviceidint, SUM(case when dayend<last_timestamp then (dayend-first_timestamp) ELSE total_duration END) AS nongeozonetime
  FROM coloc_summary c, daily_summary d
  WHERE d.deviceidint=c.deviceidint and total_duration>600 and daystart<=first_timestamp and dayend>=first_timestamp and geozoneid='' and address<>''
  GROUP BY dailysummaryid, d.deviceidint) n ON d.dailysummaryid=n.dailysummaryid
  LEFT JOIN
  (SELECT dailysummaryid, d.deviceidint, SUM(case when dayend<last_timestamp then (dayend-first_timestamp) ELSE total_duration END) AS geozonetime
  FROM coloc_summary c, daily_summary d
  WHERE d.deviceidint=c.deviceidint and total_duration>600 and daystart<=first_timestamp and dayend>=first_timestamp and geozoneid<>'' and address<>''
  GROUP BY dailysummaryid, d.deviceidint) g ON d.dailysummaryid=g.dailysummaryid
  LEFT JOIN
  (SELECT dailysummaryid, d.deviceidint, SUM(case when dayend<last_timestamp then (dayend-first_timestamp) ELSE total_duration END) AS nullgeozonetime
  FROM coloc_summary c, daily_summary d
  WHERE d.deviceidint=c.deviceidint and total_duration>600 and daystart<=first_timestamp and dayend>=first_timestamp and address=''
  GROUP BY dailysummaryid, d.deviceidint) nul ON d.dailysummaryid=nul.dailysummaryid
WHERE
nongeozonetime IS NOT NULL OR g.geozonetime IS NOT NULL OR nul.nullgeozonetime IS NOT NULL) col
ON g.dailysummaryid=col.dailysummaryid

# models/schema.yml
version: 2

models:
  - name: geozone
    columns:
      - name: dailysummaryid
      - name: deviceidint
      - name: geodate
      - name: totaltime
        description: "Unit Geozone Time in Seconds By Day"
      - name: totalgeozones
        description: "Vehicle Unique Geozones Visits By Day"
      - name: nongeozonetime
      - name: geozonetime
      - name: nullgeozonetime
      - name: colsupervisor
        description: "Co-Location Supervisor Unit Number"

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.

SELECT g.dailysummaryid, g.datename, g.deviceidint, g.geodate, g.totaltime, g.totalgeozones,  NVL2(col.nongeozonetime,col.nongeozonetime,0) AS nongeozonetime, NVL2(col.geozonetime,col.geozonetime,0) AS geozonetime, NVL2(col.nullgeozonetime,col.nullgeozonetime,0) AS nullgeozonetime, col.deviceidint AS colsupervisor
FROM
(SELECT d.dailysummaryid, d.datename, d.deviceidint, geodate, SUM(duration)*1.0 AS totaltime, COUNT(DISTINCT geozone) AS totalgeozones
  FROM
  (SELECT deviceidint, geozone, geodate, arrivetime, departtime, duration, midnight, EXTRACT(DAYOFYEAR FROM geodate) AS dayofy FROM geozone_events) g,
  (SELECT dailysummaryid, datename, deviceidint, daystart, dayend, EXTRACT(DAYOFYEAR FROM datename) AS dayofy FROM daily_summary) d
  WHERE
  g.deviceidint=d.deviceidint and
  g.dayofy=d.dayofy
  GROUP BY d.dailysummaryid, d.deviceidint, datename, geodate) g
LEFT JOIN (SELECT d.dailysummaryid, d.deviceidint, n.nongeozonetime, g.geozonetime, nul.nullgeozonetime FROM
  daily_summary d LEFT JOIN
  (SELECT dailysummaryid, d.deviceidint, SUM(case when dayend<last_timestamp then (dayend-first_timestamp) ELSE total_duration END) AS nongeozonetime
  FROM coloc_summary c, daily_summary d
  WHERE d.deviceidint=c.deviceidint and total_duration>600 and daystart<=first_timestamp and dayend>=first_timestamp and geozoneid='' and address<>''
  GROUP BY dailysummaryid, d.deviceidint) n ON d.dailysummaryid=n.dailysummaryid
  LEFT JOIN
  (SELECT dailysummaryid, d.deviceidint, SUM(case when dayend<last_timestamp then (dayend-first_timestamp) ELSE total_duration END) AS geozonetime
  FROM coloc_summary c, daily_summary d
  WHERE d.deviceidint=c.deviceidint and total_duration>600 and daystart<=first_timestamp and dayend>=first_timestamp and geozoneid<>'' and address<>''
  GROUP BY dailysummaryid, d.deviceidint) g ON d.dailysummaryid=g.dailysummaryid
  LEFT JOIN
  (SELECT dailysummaryid, d.deviceidint, SUM(case when dayend<last_timestamp then (dayend-first_timestamp) ELSE total_duration END) AS nullgeozonetime
  FROM coloc_summary c, daily_summary d
  WHERE d.deviceidint=c.deviceidint and total_duration>600 and daystart<=first_timestamp and dayend>=first_timestamp and address=''
  GROUP BY dailysummaryid, d.deviceidint) nul ON d.dailysummaryid=nul.dailysummaryid
WHERE
nongeozonetime IS NOT NULL OR g.geozonetime IS NOT NULL OR nul.nullgeozonetime IS NOT NULL) col
ON g.dailysummaryid=col.dailysummaryid

view: geozone {
  derived_table: {
    sql: SELECT g.dailysummaryid, g.datename, g.deviceidint, g.geodate, g.totaltime, g.totalgeozones,  NVL2(col.nongeozonetime,col.nongeozonetime,0) AS nongeozonetime, NVL2(col.geozonetime,col.geozonetime,0) AS geozonetime, NVL2(col.nullgeozonetime,col.nullgeozonetime,0) AS nullgeozonetime, col.deviceidint AS colsupervisor
FROM
(SELECT d.dailysummaryid, d.datename, d.deviceidint, geodate, SUM(duration)*1.0 AS totaltime, COUNT(DISTINCT geozone) AS totalgeozones
  FROM
  (SELECT deviceidint, geozone, geodate, arrivetime, departtime, duration, midnight, EXTRACT(DAYOFYEAR FROM geodate) AS dayofy FROM geozone_events) g,
  (SELECT dailysummaryid, datename, deviceidint, daystart, dayend, EXTRACT(DAYOFYEAR FROM datename) AS dayofy FROM daily_summary) d
  WHERE
  g.deviceidint=d.deviceidint and
  g.dayofy=d.dayofy
  GROUP BY d.dailysummaryid, d.deviceidint, datename, geodate) g
LEFT JOIN (SELECT d.dailysummaryid, d.deviceidint, n.nongeozonetime, g.geozonetime, nul.nullgeozonetime FROM
  daily_summary d LEFT JOIN
  (SELECT dailysummaryid, d.deviceidint, SUM(case when dayend<last_timestamp then (dayend-first_timestamp) ELSE total_duration END) AS nongeozonetime
  FROM coloc_summary c, daily_summary d
  WHERE d.deviceidint=c.deviceidint and total_duration>600 and daystart<=first_timestamp and dayend>=first_timestamp and geozoneid='' and address<>''
  GROUP BY dailysummaryid, d.deviceidint) n ON d.dailysummaryid=n.dailysummaryid
  LEFT JOIN
  (SELECT dailysummaryid, d.deviceidint, SUM(case when dayend<last_timestamp then (dayend-first_timestamp) ELSE total_duration END) AS geozonetime
  FROM coloc_summary c, daily_summary d
  WHERE d.deviceidint=c.deviceidint and total_duration>600 and daystart<=first_timestamp and dayend>=first_timestamp and geozoneid<>'' and address<>''
  GROUP BY dailysummaryid, d.deviceidint) g ON d.dailysummaryid=g.dailysummaryid
  LEFT JOIN
  (SELECT dailysummaryid, d.deviceidint, SUM(case when dayend<last_timestamp then (dayend-first_timestamp) ELSE total_duration END) AS nullgeozonetime
  FROM coloc_summary c, daily_summary d
  WHERE d.deviceidint=c.deviceidint and total_duration>600 and daystart<=first_timestamp and dayend>=first_timestamp and address=''
  GROUP BY dailysummaryid, d.deviceidint) nul ON d.dailysummaryid=nul.dailysummaryid
WHERE
nongeozonetime IS NOT NULL OR g.geozonetime IS NOT NULL OR nul.nullgeozonetime IS NOT NULL) col
ON g.dailysummaryid=col.dailysummaryid ;;
  }

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

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

  dimension: geodate {
    hidden: yes
    sql: ${TABLE}.geodate ;;
  }

  dimension: totaltime {
    label: "Unit Geozone Time"
    description: "Unit Geozone Time in Seconds By Day"
    type: number
    sql: ${TABLE}.totaltime ;;
  }

  dimension: totalgeozones {
    label: "Geozones Visited"
    description: "Vehicle Unique Geozones Visits By Day"
    type: number
    sql: ${TABLE}.totalgeozones ;;
  }

  dimension: nongeozonetime {
    type: number
    hidden: yes
    value_format: "#.##"
    sql: ${TABLE}.nongeozonetime ;;
  }

  dimension: geozonetime {
    type: number
    hidden: yes
    value_format: "#.##"
    sql: ${TABLE}.geozonetime ;;
  }

  dimension: nullgeozonetime {
    type: number
    hidden: yes
    value_format: "#.##"
    sql: ${TABLE}.nullgeozonetime ;;
  }

  dimension: colsupervisor {
    label: "Supervisor Unit Number"
    description: "Co-Location Supervisor Unit Number"
    type: number
    sql: ${TABLE}.colsupervisor ;;
  }

  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 ;;
  }

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

  measure: totalsumgeozonetime {
    label: "Total Geozone Time in Seconds"
    description: "Total Geozone Time in Seconds Over Timeframe"
    type: sum
    hidden: yes
    value_format: "#,##0"
    sql: ${totaltime} ;;
  }

  measure: totalgeozonetime {
    label: "Total Geozone Time"
    description: "Geozone Hours Over Timeframe"
    type: average
    value_format: "#.##"
    sql: ${totaltime}/3600.0 ;;
  }

  measure: totalnongeozonetime {
    label: "Total Non-Geozone Time"
    description: "Non-Geozone Hours Over Timeframe"
    type: number
    sql: ROUND(24-${totalgeozonetime},2) ;;
  }

  measure: nongeozonecolocation {
    label: "Non-Geozone Co-Location"
    description: "Non-Geozone Co-Location Hours Over Timeframe"
    type: average
    value_format: "#.##"
    sql: ((CASE WHEN ${nullgeozonetime}>3600 THEN 0 ELSE ${nullgeozonetime} END)+${nongeozonetime})/3600.0 ;;
  }

  measure: geozonecolocation {
    label: "Geozone Co-Location"
    description: "Geozone Co-Location Hours Over Timeframe"
    type: average
    value_format: "#.##"
    sql: ((CASE WHEN ${nullgeozonetime}>3600 THEN ${nullgeozonetime} ELSE 0 END)+${geozonetime})/3600.0 ;;
  }

  measure: colopercent {
    label: "Co-Location % Outside Geozone"
    description: "Co-Location % Outside Geozone = Non-Geozone Colocation / Total Non-Geozone Time"
    type: number
    value_format: "#.00\%"
    sql: ROUND(100.0*${nongeozonecolocation}/NULLIF(${totalnongeozonetime},0),2) ;;
  }
}

- view: geozone
  derived_table:
    sql: SELECT g.dailysummaryid, g.datename, g.deviceidint, g.geodate, g.totaltime, g.totalgeozones,  NVL2(col.nongeozonetime,col.nongeozonetime,0) AS nongeozonetime, NVL2(col.geozonetime,col.geozonetime,0) AS geozonetime, NVL2(col.nullgeozonetime,col.nullgeozonetime,0) AS nullgeozonetime, col.deviceidint AS colsupervisor
FROM
(SELECT d.dailysummaryid, d.datename, d.deviceidint, geodate, SUM(duration)*1.0 AS totaltime, COUNT(DISTINCT geozone) AS totalgeozones
  FROM
  (SELECT deviceidint, geozone, geodate, arrivetime, departtime, duration, midnight, EXTRACT(DAYOFYEAR FROM geodate) AS dayofy FROM geozone_events) g,
  (SELECT dailysummaryid, datename, deviceidint, daystart, dayend, EXTRACT(DAYOFYEAR FROM datename) AS dayofy FROM daily_summary) d
  WHERE
  g.deviceidint=d.deviceidint and
  g.dayofy=d.dayofy
  GROUP BY d.dailysummaryid, d.deviceidint, datename, geodate) g
LEFT JOIN (SELECT d.dailysummaryid, d.deviceidint, n.nongeozonetime, g.geozonetime, nul.nullgeozonetime FROM
  daily_summary d LEFT JOIN
  (SELECT dailysummaryid, d.deviceidint, SUM(case when dayend<last_timestamp then (dayend-first_timestamp) ELSE total_duration END) AS nongeozonetime
  FROM coloc_summary c, daily_summary d
  WHERE d.deviceidint=c.deviceidint and total_duration>600 and daystart<=first_timestamp and dayend>=first_timestamp and geozoneid='' and address<>''
  GROUP BY dailysummaryid, d.deviceidint) n ON d.dailysummaryid=n.dailysummaryid
  LEFT JOIN
  (SELECT dailysummaryid, d.deviceidint, SUM(case when dayend<last_timestamp then (dayend-first_timestamp) ELSE total_duration END) AS geozonetime
  FROM coloc_summary c, daily_summary d
  WHERE d.deviceidint=c.deviceidint and total_duration>600 and daystart<=first_timestamp and dayend>=first_timestamp and geozoneid<>'' and address<>''
  GROUP BY dailysummaryid, d.deviceidint) g ON d.dailysummaryid=g.dailysummaryid
  LEFT JOIN
  (SELECT dailysummaryid, d.deviceidint, SUM(case when dayend<last_timestamp then (dayend-first_timestamp) ELSE total_duration END) AS nullgeozonetime
  FROM coloc_summary c, daily_summary d
  WHERE d.deviceidint=c.deviceidint and total_duration>600 and daystart<=first_timestamp and dayend>=first_timestamp and address=''
  GROUP BY dailysummaryid, d.deviceidint) nul ON d.dailysummaryid=nul.dailysummaryid
WHERE
nongeozonetime IS NOT NULL OR g.geozonetime IS NOT NULL OR nul.nullgeozonetime IS NOT NULL) col
ON g.dailysummaryid=col.dailysummaryid
  fields:
  - dimension: dailysummaryid
    type: number
    hidden: true
    sql: ${TABLE}.dailysummaryid
  - dimension: deviceidint
    type: number
    hidden: true
    sql: ${TABLE}.deviceidint
  - dimension: geodate
    hidden: true
    sql: ${TABLE}.geodate
  - dimension: totaltime
    type: number
    label: Unit Geozone Time
    description: Unit Geozone Time in Seconds By Day
    sql: ${TABLE}.totaltime
  - dimension: totalgeozones
    type: number
    label: Geozones Visited
    description: Vehicle Unique Geozones Visits By Day
    sql: ${TABLE}.totalgeozones
  - dimension: nongeozonetime
    type: number
    hidden: true
    value_format: '#.##'
    sql: ${TABLE}.nongeozonetime
  - dimension: geozonetime
    type: number
    hidden: true
    value_format: '#.##'
    sql: ${TABLE}.geozonetime
  - dimension: nullgeozonetime
    type: number
    hidden: true
    value_format: '#.##'
    sql: ${TABLE}.nullgeozonetime
  - dimension: colsupervisor
    type: number
    label: Supervisor Unit Number
    description: Co-Location Supervisor Unit Number
    sql: ${TABLE}.colsupervisor
  - dimension_group: datename
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    convert_tz: false
    sql: ${TABLE}.datename
  - measure: count
    type: count
    drill_fields:
    - detail*
  - measure: totalsumgeozonetime
    type: sum
    label: Total Geozone Time in Seconds
    description: Total Geozone Time in Seconds Over Timeframe
    value_format: '#,##0'
    sql: ${totaltime}
  - measure: totalgeozonetime
    type: average
    label: Total Geozone Time
    description: Geozone Hours Over Timeframe
    value_format: '#.##'
    sql: ${totaltime}/3600.0
  - measure: totalnongeozonetime
    type: number
    label: Total Non-Geozone Time
    description: Non-Geozone Hours Over Timeframe
    sql: ROUND(24-${totalgeozonetime},2)
  - measure: nongeozonecolocation
    type: average
    label: Non-Geozone Co-Location
    description: Non-Geozone Co-Location Hours Over Timeframe
    value_format: '#.##'
    sql: ((CASE WHEN ${nullgeozonetime}>3600 THEN 0 ELSE ${nullgeozonetime} END)+${nongeozonetime})/3600.0
  - measure: geozonecolocation
    type: average
    label: Geozone Co-Location
    description: Geozone Co-Location Hours Over Timeframe
    value_format: '#.##'
    sql: ((CASE WHEN ${nullgeozonetime}>3600 THEN ${nullgeozonetime} ELSE 0 END)+${geozonetime})/3600.0
  - measure: colopercent
    type: number
    label: 'Co-Location % Outside Geozone'
    description: 'Co-Location % Outside Geozone = Non-Geozone Colocation / Total Non-Geozone Time'
    value_format: '#.00\%'
    sql: ROUND(100.0*${nongeozonecolocation}/NULLIF(${totalnongeozonetime},0),2)