SQL Data analytics

Daily Alert

Daily Alert model for data analytics.

source-preserved

Blocks

dbt SQL LKML LookML

daily_alert.sql · 57 lines

inferred

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

SELECT d.accountid, d.usersvehicleid, d.yearmakemodel, pm.groupid, pm.description, NVL2(calbat,calbat,0)+NVL2(atbat,atbat,0) AS battery, NVL2(fault,fault,0) AS fault, (CASE WHEN faultcode IS NULL THEN 'No Fault Decoded' ELSE REPLACE(faultcode,'type=OBDII mil=1 ','') END) AS faultcode
FROM (SELECT accountid, deviceidint, usersvehicleid, yearmakemodel FROM Device d, vehicle_tbl v WHERE v.name=d.deviceid) d
LEFT JOIN (SELECT e.deviceidint, COUNT(*) AS calbat
  FROM (SELECT deviceidint, daystart FROM daily_summary WHERE daystart>= EXTRACT(EPOCH FROM dateadd(d,-1,current_date))) ds INNER JOIN
  (SELECT deviceidint FROM Device WHERE devicecode='calamp') d  ON d.deviceidint=ds.deviceidint,
  eventdata e
  WHERE d.deviceidint=e.deviceidint and
  e.timestamp>daystart and vbatteryvolts<=9.usage_threshold_miles and vbatteryvolts>7.0 and   inputmask=0 and statuscode=62498
  GROUP BY e.deviceidint) cal ON cal.deviceidint=d.deviceidint
LEFT JOIN (SELECT e.deviceidint, COUNT(*) AS fault
  FROM (SELECT deviceidint, daystart FROM daily_summary WHERE daystart>= EXTRACT(EPOCH FROM dateadd(d,-1,current_date))) ds
  INNER JOIN eventdata e ON ds.deviceidint=e.deviceidint
  WHERE
  e.timestamp>daystart and statusCode IN (63761,6376usage_threshold_miles)
  GROUP BY e.deviceidint) fault ON fault.deviceidint=d.deviceidint
 LEFT JOIN (SELECT DISTINCT e.deviceidint, e.faultcode
  FROM (SELECT deviceidint, daystart FROM daily_summary WHERE daystart>= EXTRACT(EPOCH FROM dateadd(d,-1,current_date))) ds
  INNER JOIN eventdata e ON ds.deviceidint=e.deviceidint
  WHERE
  e.timestamp>daystart and statusCode IN (63761,6376usage_threshold_miles) and faultcode LIKE '%mil=1%') fcode ON fcode.deviceidint=d.deviceidint

LEFT JOIN (SELECT e.deviceidint, COUNT(*) AS atbat
  FROM (SELECT deviceidint, daystart FROM daily_summary WHERE daystart>= EXTRACT(EPOCH FROM dateadd(d,-1,current_date))) ds INNER JOIN
  (SELECT deviceidint FROM Device WHERE devicecode='atrack') d ON d.deviceidint=ds.deviceidint,
  eventdata e
  WHERE d.deviceidint=e.deviceidint and
  e.timestamp>daystart and statusCode=64784
  GROUP BY e.deviceidint) bat ON bat.deviceidint=d.deviceidint
RIGHT JOIN (SELECT l.accountid, l.deviceidint, g.groupid, g.description
    FROM DeviceList l, DeviceGroup g
    WHERE g.groupidint=l.groupidint and (l.groupid LIKE 'pm_location_group_id%')) pm ON pm.deviceidint=d.deviceidint
WHERE calbat>0 OR fault>0 or atbat>0

# models/schema.yml
version: 2

models:
  - name: daily_alert
    columns:
      - name: accountid
      - name: usersvehicleid
        description: "Unit Number From Company Platform"
      - name: yearmakemodel
        description: "Equipment Type Listed in Company Platform"
      - name: groupid
      - name: description
        description: "PM Location Group ID in Company Platform"
      - name: battery
      - name: low_battery_registered
        description: "If Low Battery Registered Then 1 Else 0"
      - name: faultcode
      - name: fault
      - name: fault_registered
        description: "If Fault Registered Then 1 Else 0"

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.

daily_alert.sql · 32 lines

source-preserved validation: not checked

SELECT d.accountid, d.usersvehicleid, d.yearmakemodel, pm.groupid, pm.description, NVL2(calbat,calbat,0)+NVL2(atbat,atbat,0) AS battery, NVL2(fault,fault,0) AS fault, (CASE WHEN faultcode IS NULL THEN 'No Fault Decoded' ELSE REPLACE(faultcode,'type=OBDII mil=1 ','') END) AS faultcode
FROM (SELECT accountid, deviceidint, usersvehicleid, yearmakemodel FROM Device d, vehicle_tbl v WHERE v.name=d.deviceid) d
LEFT JOIN (SELECT e.deviceidint, COUNT(*) AS calbat
  FROM (SELECT deviceidint, daystart FROM daily_summary WHERE daystart>= EXTRACT(EPOCH FROM dateadd(d,-1,current_date))) ds INNER JOIN
  (SELECT deviceidint FROM Device WHERE devicecode='calamp') d  ON d.deviceidint=ds.deviceidint,
  eventdata e
  WHERE d.deviceidint=e.deviceidint and
  e.timestamp>daystart and vbatteryvolts<=9.usage_threshold_miles and vbatteryvolts>7.0 and   inputmask=0 and statuscode=62498
  GROUP BY e.deviceidint) cal ON cal.deviceidint=d.deviceidint
LEFT JOIN (SELECT e.deviceidint, COUNT(*) AS fault
  FROM (SELECT deviceidint, daystart FROM daily_summary WHERE daystart>= EXTRACT(EPOCH FROM dateadd(d,-1,current_date))) ds
  INNER JOIN eventdata e ON ds.deviceidint=e.deviceidint
  WHERE
  e.timestamp>daystart and statusCode IN (63761,6376usage_threshold_miles)
  GROUP BY e.deviceidint) fault ON fault.deviceidint=d.deviceidint
 LEFT JOIN (SELECT DISTINCT e.deviceidint, e.faultcode
  FROM (SELECT deviceidint, daystart FROM daily_summary WHERE daystart>= EXTRACT(EPOCH FROM dateadd(d,-1,current_date))) ds
  INNER JOIN eventdata e ON ds.deviceidint=e.deviceidint
  WHERE
  e.timestamp>daystart and statusCode IN (63761,6376usage_threshold_miles) and faultcode LIKE '%mil=1%') fcode ON fcode.deviceidint=d.deviceidint

LEFT JOIN (SELECT e.deviceidint, COUNT(*) AS atbat
  FROM (SELECT deviceidint, daystart FROM daily_summary WHERE daystart>= EXTRACT(EPOCH FROM dateadd(d,-1,current_date))) ds INNER JOIN
  (SELECT deviceidint FROM Device WHERE devicecode='atrack') d ON d.deviceidint=ds.deviceidint,
  eventdata e
  WHERE d.deviceidint=e.deviceidint and
  e.timestamp>daystart and statusCode=64784
  GROUP BY e.deviceidint) bat ON bat.deviceidint=d.deviceidint
RIGHT JOIN (SELECT l.accountid, l.deviceidint, g.groupid, g.description
    FROM DeviceList l, DeviceGroup g
    WHERE g.groupidint=l.groupidint and (l.groupid LIKE 'pm_location_group_id%')) pm ON pm.deviceidint=d.deviceidint
WHERE calbat>0 OR fault>0 or atbat>0

daily_alert.view.lkml · 118 lines

inferred

view: daily_alert {
  derived_table: {
    sql: SELECT d.accountid, d.usersvehicleid, d.yearmakemodel, pm.groupid, pm.description, NVL2(calbat,calbat,0)+NVL2(atbat,atbat,0) AS battery, NVL2(fault,fault,0) AS fault, (CASE WHEN faultcode IS NULL THEN 'No Fault Decoded' ELSE REPLACE(faultcode,'type=OBDII mil=1 ','') END) AS faultcode
FROM (SELECT accountid, deviceidint, usersvehicleid, yearmakemodel FROM Device d, vehicle_tbl v WHERE v.name=d.deviceid) d
LEFT JOIN (SELECT e.deviceidint, COUNT(*) AS calbat
  FROM (SELECT deviceidint, daystart FROM daily_summary WHERE daystart>= EXTRACT(EPOCH FROM dateadd(d,-1,current_date))) ds INNER JOIN
  (SELECT deviceidint FROM Device WHERE devicecode='calamp') d  ON d.deviceidint=ds.deviceidint,
  eventdata e
  WHERE d.deviceidint=e.deviceidint and
  e.timestamp>daystart and vbatteryvolts<=9.usage_threshold_miles and vbatteryvolts>7.0 and   inputmask=0 and statuscode=62498
  GROUP BY e.deviceidint) cal ON cal.deviceidint=d.deviceidint
LEFT JOIN (SELECT e.deviceidint, COUNT(*) AS fault
  FROM (SELECT deviceidint, daystart FROM daily_summary WHERE daystart>= EXTRACT(EPOCH FROM dateadd(d,-1,current_date))) ds
  INNER JOIN eventdata e ON ds.deviceidint=e.deviceidint
  WHERE
  e.timestamp>daystart and statusCode IN (63761,6376usage_threshold_miles)
  GROUP BY e.deviceidint) fault ON fault.deviceidint=d.deviceidint
 LEFT JOIN (SELECT DISTINCT e.deviceidint, e.faultcode
  FROM (SELECT deviceidint, daystart FROM daily_summary WHERE daystart>= EXTRACT(EPOCH FROM dateadd(d,-1,current_date))) ds
  INNER JOIN eventdata e ON ds.deviceidint=e.deviceidint
  WHERE
  e.timestamp>daystart and statusCode IN (63761,6376usage_threshold_miles) and faultcode LIKE '%mil=1%') fcode ON fcode.deviceidint=d.deviceidint

LEFT JOIN (SELECT e.deviceidint, COUNT(*) AS atbat
  FROM (SELECT deviceidint, daystart FROM daily_summary WHERE daystart>= EXTRACT(EPOCH FROM dateadd(d,-1,current_date))) ds INNER JOIN
  (SELECT deviceidint FROM Device WHERE devicecode='atrack') d ON d.deviceidint=ds.deviceidint,
  eventdata e
  WHERE d.deviceidint=e.deviceidint and
  e.timestamp>daystart and statusCode=64784
  GROUP BY e.deviceidint) bat ON bat.deviceidint=d.deviceidint
RIGHT JOIN (SELECT l.accountid, l.deviceidint, g.groupid, g.description
    FROM DeviceList l, DeviceGroup g
    WHERE g.groupidint=l.groupidint and (l.groupid LIKE 'pm_location_group_id%')) pm ON pm.deviceidint=d.deviceidint
WHERE calbat>0 OR fault>0 or atbat>0 ;;
  }

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

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

  dimension: yearmakemodel {
    label: "Year/Make/Model"
    description: "Equipment Type Listed in Company Platform"
    sql: ${TABLE}.yearmakemodel ;;
  }

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

  dimension: description {
    label: "PM Location"
    description: "PM Location Group ID in Company Platform"
    sql: ${TABLE}.description ;;
  }

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

  dimension: low_battery_registered {
    label: "Low Battery Registered"
    description: "If Low Battery Registered Then 1 Else 0"
    type: int
    sql: CASE
WHEN ${battery} > 0 THEN 1
ELSE 0 END ;;
  }

  dimension: faultcode {
    label: "Decoded Fault Code"
    sql: ${TABLE}.faultcode ;;
  }

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

  dimension: fault_registered {
    label: "Fault Code Registered"
    description: "If Fault Registered Then 1 Else 0"
    type: int
    sql: CASE
WHEN ${fault} > 0 THEN 1
ELSE 0 END ;;
  }

  measure: count {
    type: count
    drill_fields: [usersvehicleid, yearmakemodel, description, low_battery_registered, fault_registered]
  }

  measure: total_number_of_battery_units {
    label: "Low Battery Units Registered"
    type: sum
    drill_fields: [usersvehicleid, yearmakemodel, description, low_battery_registered, fault_registered]
    sql: ${low_battery_registered} ;;
  }

  measure: total_number_of_fault_registered {
    label: "CEL Units Registered"
    type: sum
    drill_fields: [usersvehicleid, yearmakemodel, description, low_battery_registered, fault_registered]
    sql: ${fault_registered} ;;
  }
}

daily_alert.view.lookml · 106 lines

source-preserved

- view: daily_alert
  derived_table:
    sql: SELECT d.accountid, d.usersvehicleid, d.yearmakemodel, pm.groupid, pm.description, NVL2(calbat,calbat,0)+NVL2(atbat,atbat,0) AS battery, NVL2(fault,fault,0) AS fault, (CASE WHEN faultcode IS NULL THEN 'No Fault Decoded' ELSE REPLACE(faultcode,'type=OBDII mil=1 ','') END) AS faultcode
FROM (SELECT accountid, deviceidint, usersvehicleid, yearmakemodel FROM Device d, vehicle_tbl v WHERE v.name=d.deviceid) d
LEFT JOIN (SELECT e.deviceidint, COUNT(*) AS calbat
  FROM (SELECT deviceidint, daystart FROM daily_summary WHERE daystart>= EXTRACT(EPOCH FROM dateadd(d,-1,current_date))) ds INNER JOIN
  (SELECT deviceidint FROM Device WHERE devicecode='calamp') d  ON d.deviceidint=ds.deviceidint,
  eventdata e
  WHERE d.deviceidint=e.deviceidint and
  e.timestamp>daystart and vbatteryvolts<=9.usage_threshold_miles and vbatteryvolts>7.0 and   inputmask=0 and statuscode=62498
  GROUP BY e.deviceidint) cal ON cal.deviceidint=d.deviceidint
LEFT JOIN (SELECT e.deviceidint, COUNT(*) AS fault
  FROM (SELECT deviceidint, daystart FROM daily_summary WHERE daystart>= EXTRACT(EPOCH FROM dateadd(d,-1,current_date))) ds
  INNER JOIN eventdata e ON ds.deviceidint=e.deviceidint
  WHERE
  e.timestamp>daystart and statusCode IN (63761,6376usage_threshold_miles)
  GROUP BY e.deviceidint) fault ON fault.deviceidint=d.deviceidint
 LEFT JOIN (SELECT DISTINCT e.deviceidint, e.faultcode
  FROM (SELECT deviceidint, daystart FROM daily_summary WHERE daystart>= EXTRACT(EPOCH FROM dateadd(d,-1,current_date))) ds
  INNER JOIN eventdata e ON ds.deviceidint=e.deviceidint
  WHERE
  e.timestamp>daystart and statusCode IN (63761,6376usage_threshold_miles) and faultcode LIKE '%mil=1%') fcode ON fcode.deviceidint=d.deviceidint

LEFT JOIN (SELECT e.deviceidint, COUNT(*) AS atbat
  FROM (SELECT deviceidint, daystart FROM daily_summary WHERE daystart>= EXTRACT(EPOCH FROM dateadd(d,-1,current_date))) ds INNER JOIN
  (SELECT deviceidint FROM Device WHERE devicecode='atrack') d ON d.deviceidint=ds.deviceidint,
  eventdata e
  WHERE d.deviceidint=e.deviceidint and
  e.timestamp>daystart and statusCode=64784
  GROUP BY e.deviceidint) bat ON bat.deviceidint=d.deviceidint
RIGHT JOIN (SELECT l.accountid, l.deviceidint, g.groupid, g.description
    FROM DeviceList l, DeviceGroup g
    WHERE g.groupidint=l.groupidint and (l.groupid LIKE 'pm_location_group_id%')) pm ON pm.deviceidint=d.deviceidint
WHERE calbat>0 OR fault>0 or atbat>0
  fields:
  - dimension: accountid
    hidden: true
    sql: ${TABLE}.accountid
  - dimension: usersvehicleid
    label: Unit Number
    description: Unit Number From Company Platform
    sql: ${TABLE}.usersvehicleid
  - dimension: yearmakemodel
    label: Year/Make/Model
    description: Equipment Type Listed in Company Platform
    sql: ${TABLE}.yearmakemodel
  - dimension: groupid
    hidden: true
    sql: ${TABLE}.groupid
  - dimension: description
    label: PM Location
    description: PM Location Group ID in Company Platform
    sql: ${TABLE}.description
  - dimension: battery
    type: number
    hidden: true
    sql: ${TABLE}.battery
  - dimension: low_battery_registered
    type: int
    label: Low Battery Registered
    description: If Low Battery Registered Then 1 Else 0
    sql: CASE
WHEN ${battery} > 0 THEN 1
ELSE 0 END
  - dimension: faultcode
    label: Decoded Fault Code
    sql: ${TABLE}.faultcode
  - dimension: fault
    type: number
    hidden: true
    sql: ${TABLE}.fault
  - dimension: fault_registered
    type: int
    label: Fault Code Registered
    description: If Fault Registered Then 1 Else 0
    sql: CASE
WHEN ${fault} > 0 THEN 1
ELSE 0 END
  - measure: count
    type: count
    drill_fields:
    - usersvehicleid
    - yearmakemodel
    - description
    - low_battery_registered
    - fault_registered
  - measure: total_number_of_battery_units
    type: sum
    label: Low Battery Units Registered
    drill_fields:
    - usersvehicleid
    - yearmakemodel
    - description
    - low_battery_registered
    - fault_registered
    sql: ${low_battery_registered}
  - measure: total_number_of_fault_registered
    type: sum
    label: CEL Units Registered
    drill_fields:
    - usersvehicleid
    - yearmakemodel
    - description
    - low_battery_registered
    - fault_registered
    sql: ${fault_registered}

daily_alert.sql
daily_alert.sql
daily_alert.view.lkml
daily_alert.view.lookml

Daily Alert

Blocks

Assumptions

Contents