SQL Data analytics

Graduated Devices

Graduated Devices model for data analytics.

source-preserved

Blocks

dbt SQL LKML LookML

graduated_devices.sql · 44 lines

inferred

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

SELECT d.deviceidint, (EXTRACT(epoch from current_date)-d.lasttotalconnecttime)/86400 AS dayswithoutconnection, l.groupid, rc.groupidint, l.creationtime, rc.groupid AS rcnumber, vc.description AS vcclass, store_placeholder.groupid AS store_placeholderlocation, pm.groupid AS pm_location_group_idation, sup.description AS supervisor
FROM
  (SELECT d.accountid, d.deviceid, d.deviceidint, d.isactive, d.lasttotalconnecttime FROM Device d LEFT OUTER JOIN (SELECT l.deviceidint FROM DeviceList l WHERE groupid LIKE 'dual_placeholder1') dual ON d.deviceidint=dual.deviceidint WHERE dual.deviceidint IS NULL) d,
  DeviceList l,
  (SELECT l.deviceidint, l.groupid, l.groupidint FROM DeviceList l WHERE groupid LIKE 'rc_group_id-%' and  groupid NOT LIKE 'rc_group_id-773%') rc,
  (SELECT l.deviceidint, l.groupid FROM DeviceList l WHERE l.groupid LIKE 'pm_location_group_id%') pm,
  (SELECT l.deviceidint, l.groupid FROM DeviceList l WHERE l.groupid LIKE 'store_placeholder%') store_placeholder,
  (SELECT l.deviceidint, g.description FROM DeviceList l, DeviceGroup g WHERE l.groupidint=g.groupidint and l.groupid LIKE 'supervisor_placeholder%') sup,
  (SELECT l.deviceidint, g.description FROM DeviceList l, DeviceGroup g WHERE l.groupidint=g.groupidint and l.groupid LIKE 'vc_placeholder-%' and
    (l.groupid NOT LIKE 'vc_placeholder-all%' and l.groupid NOT LIKE 'vc_placeholder-take%')) vc
WHERE d.deviceidint=l.deviceidint and d.deviceidint=rc.deviceidint and (l.groupid=d.accountid)
and d.deviceidint=vc.deviceidint and d.isactive=1 and sup.deviceidint=d.deviceidint and pm.deviceidint=d.deviceidint and store_placeholder.deviceidint=d.deviceidint

# models/schema.yml
version: 2

models:
  - name: graduated_devices
    columns:
      - name: deviceidint
      - name: dayswithoutconnection
      - name: filtering
      - name: commfilter
        description: "Filter for unit not communicating > 30 days"
      - name: groupid
      - name: groupidint
      - name: creationtime
      - name: rcnumber
      - name: vcclass
      - name: store_placeholderlocation
      - name: pm_location_group_idation
      - name: pm_location_group_id
        description: "PM Location Group ID in Company Platform"
      - name: store_placeholderdloc
        description: "Stored Location Group ID in Company Platform"
      - name: supervisor
        description: "Super-Fore Group ID in Company Platform"
      - name: rc_group
        description: "Combining Group ID in Company Platform"
      - name: vc_group
        description: "Combining Group ID in Company Platform"

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.

graduated_devices.sql · 12 lines

source-preserved validation: parses

SELECT d.deviceidint, (EXTRACT(epoch from current_date)-d.lasttotalconnecttime)/86400 AS dayswithoutconnection, l.groupid, rc.groupidint, l.creationtime, rc.groupid AS rcnumber, vc.description AS vcclass, store_placeholder.groupid AS store_placeholderlocation, pm.groupid AS pm_location_group_idation, sup.description AS supervisor
FROM
  (SELECT d.accountid, d.deviceid, d.deviceidint, d.isactive, d.lasttotalconnecttime FROM Device d LEFT OUTER JOIN (SELECT l.deviceidint FROM DeviceList l WHERE groupid LIKE 'dual_placeholder1') dual ON d.deviceidint=dual.deviceidint WHERE dual.deviceidint IS NULL) d,
  DeviceList l,
  (SELECT l.deviceidint, l.groupid, l.groupidint FROM DeviceList l WHERE groupid LIKE 'rc_group_id-%' and  groupid NOT LIKE 'rc_group_id-773%') rc,
  (SELECT l.deviceidint, l.groupid FROM DeviceList l WHERE l.groupid LIKE 'pm_location_group_id%') pm,
  (SELECT l.deviceidint, l.groupid FROM DeviceList l WHERE l.groupid LIKE 'store_placeholder%') store_placeholder,
  (SELECT l.deviceidint, g.description FROM DeviceList l, DeviceGroup g WHERE l.groupidint=g.groupidint and l.groupid LIKE 'supervisor_placeholder%') sup,
  (SELECT l.deviceidint, g.description FROM DeviceList l, DeviceGroup g WHERE l.groupidint=g.groupidint and l.groupid LIKE 'vc_placeholder-%' and
    (l.groupid NOT LIKE 'vc_placeholder-all%' and l.groupid NOT LIKE 'vc_placeholder-take%')) vc
WHERE d.deviceidint=l.deviceidint and d.deviceidint=rc.deviceidint and (l.groupid=d.accountid)
and d.deviceidint=vc.deviceidint and d.isactive=1 and sup.deviceidint=d.deviceidint and pm.deviceidint=d.deviceidint and store_placeholder.deviceidint=d.deviceidint

graduated_devices.view.lkml · 109 lines

inferred

view: graduated_devices {
  derived_table: {
    sql: SELECT d.deviceidint, (EXTRACT(epoch from current_date)-d.lasttotalconnecttime)/86400 AS dayswithoutconnection, l.groupid, rc.groupidint, l.creationtime, rc.groupid AS rcnumber, vc.description AS vcclass, store_placeholder.groupid AS store_placeholderlocation, pm.groupid AS pm_location_group_idation, sup.description AS supervisor
FROM
  (SELECT d.accountid, d.deviceid, d.deviceidint, d.isactive, d.lasttotalconnecttime FROM Device d LEFT OUTER JOIN (SELECT l.deviceidint FROM DeviceList l WHERE groupid LIKE 'dual_placeholder1') dual ON d.deviceidint=dual.deviceidint WHERE dual.deviceidint IS NULL) d,
  DeviceList l,
  (SELECT l.deviceidint, l.groupid, l.groupidint FROM DeviceList l WHERE groupid LIKE 'rc_group_id-%' and  groupid NOT LIKE 'rc_group_id-773%') rc,
  (SELECT l.deviceidint, l.groupid FROM DeviceList l WHERE l.groupid LIKE 'pm_location_group_id%') pm,
  (SELECT l.deviceidint, l.groupid FROM DeviceList l WHERE l.groupid LIKE 'store_placeholder%') store_placeholder,
  (SELECT l.deviceidint, g.description FROM DeviceList l, DeviceGroup g WHERE l.groupidint=g.groupidint and l.groupid LIKE 'supervisor_placeholder%') sup,
  (SELECT l.deviceidint, g.description FROM DeviceList l, DeviceGroup g WHERE l.groupidint=g.groupidint and l.groupid LIKE 'vc_placeholder-%' and
    (l.groupid NOT LIKE 'vc_placeholder-all%' and l.groupid NOT LIKE 'vc_placeholder-take%')) vc
WHERE d.deviceidint=l.deviceidint and d.deviceidint=rc.deviceidint and (l.groupid=d.accountid)
and d.deviceidint=vc.deviceidint and d.isactive=1 and sup.deviceidint=d.deviceidint and pm.deviceidint=d.deviceidint and store_placeholder.deviceidint=d.deviceidint ;;
  }

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

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

  dimension: filtering {
    type: number
    hidden: yes
    sql: CASE
WHEN ${dayswithoutconnection}>30 THEN 1
ELSE 0 END ;;
  }

  dimension: commfilter {
    label: "Non-Communication Filter"
    description: "Filter for unit not communicating > 30 days"
  }

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

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

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

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

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

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

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

  dimension: pm_location_group_id {
    label: "PM Location"
    description: "PM Location Group ID in Company Platform"
  }

  dimension: store_placeholderdloc {
    label: "Stored Location"
    description: "Stored Location Group ID in Company Platform"
  }

  dimension: supervisor {
    label: "Supervisor/Foreman"
    description: "Super-Fore Group ID in Company Platform"
    sql: ${TABLE}.supervisor ;;
  }

  dimension: rc_group {
    label: "RC Group"
    description: "Combining Group ID in Company Platform"
  }

  dimension: vc_group {
    label: "VC Group"
    description: "Combining Group ID in Company Platform"
  }

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

graduated_devices.view.lookml · 75 lines

source-preserved

- view: graduated_devices
  derived_table:
    sql: SELECT d.deviceidint, (EXTRACT(epoch from current_date)-d.lasttotalconnecttime)/86400 AS dayswithoutconnection, l.groupid, rc.groupidint, l.creationtime, rc.groupid AS rcnumber, vc.description AS vcclass, store_placeholder.groupid AS store_placeholderlocation, pm.groupid AS pm_location_group_idation, sup.description AS supervisor
FROM
  (SELECT d.accountid, d.deviceid, d.deviceidint, d.isactive, d.lasttotalconnecttime FROM Device d LEFT OUTER JOIN (SELECT l.deviceidint FROM DeviceList l WHERE groupid LIKE 'dual_placeholder1') dual ON d.deviceidint=dual.deviceidint WHERE dual.deviceidint IS NULL) d,
  DeviceList l,
  (SELECT l.deviceidint, l.groupid, l.groupidint FROM DeviceList l WHERE groupid LIKE 'rc_group_id-%' and  groupid NOT LIKE 'rc_group_id-773%') rc,
  (SELECT l.deviceidint, l.groupid FROM DeviceList l WHERE l.groupid LIKE 'pm_location_group_id%') pm,
  (SELECT l.deviceidint, l.groupid FROM DeviceList l WHERE l.groupid LIKE 'store_placeholder%') store_placeholder,
  (SELECT l.deviceidint, g.description FROM DeviceList l, DeviceGroup g WHERE l.groupidint=g.groupidint and l.groupid LIKE 'supervisor_placeholder%') sup,
  (SELECT l.deviceidint, g.description FROM DeviceList l, DeviceGroup g WHERE l.groupidint=g.groupidint and l.groupid LIKE 'vc_placeholder-%' and
    (l.groupid NOT LIKE 'vc_placeholder-all%' and l.groupid NOT LIKE 'vc_placeholder-take%')) vc
WHERE d.deviceidint=l.deviceidint and d.deviceidint=rc.deviceidint and (l.groupid=d.accountid)
and d.deviceidint=vc.deviceidint and d.isactive=1 and sup.deviceidint=d.deviceidint and pm.deviceidint=d.deviceidint and store_placeholder.deviceidint=d.deviceidint
  fields:
  - dimension: deviceidint
    type: number
    hidden: true
    sql: ${TABLE}.deviceidint
  - dimension: dayswithoutconnection
    type: number
    hidden: true
    sql: ${TABLE}.dayswithoutconnection
  - dimension: filtering
    type: number
    hidden: true
    sql: CASE
WHEN ${dayswithoutconnection}>30 THEN 1
ELSE 0 END
  - dimension: commfilter
    label: Non-Communication Filter
    description: 'Filter for unit not communicating > 30 days'
  - dimension: groupid
    hidden: true
    sql: ${TABLE}.groupid
  - dimension: groupidint
    type: number
    hidden: true
    sql: ${TABLE}.groupidint
  - dimension: creationtime
    type: number
    hidden: true
    sql: ${TABLE}.creationtime
  - dimension: rcnumber
    hidden: true
    sql: ${TABLE}.rcnumber
  - dimension: vcclass
    hidden: true
    sql: ${TABLE}.vcclass
  - dimension: store_placeholderlocation
    hidden: true
    sql: ${TABLE}.store_placeholderlocation
  - dimension: pm_location_group_idation
    hidden: true
    sql: ${TABLE}.pm_location_group_idation
  - dimension: pm_location_group_id
    label: PM Location
    description: PM Location Group ID in Company Platform
  - dimension: store_placeholderdloc
    label: Stored Location
    description: Stored Location Group ID in Company Platform
  - dimension: supervisor
    label: Supervisor/Foreman
    description: Super-Fore Group ID in Company Platform
    sql: ${TABLE}.supervisor
  - dimension: rc_group
    label: RC Group
    description: Combining Group ID in Company Platform
  - dimension: vc_group
    label: VC Group
    description: Combining Group ID in Company Platform
  - measure: count
    type: count
    drill_fields:
    - detail*

graduated_devices.sql
graduated_devices.sql
graduated_devices.view.lkml
graduated_devices.view.lookml

Graduated Devices

Blocks

Assumptions

Contents