dbt Telecom services

Moving Seats Line

Moving Seats Line model for telecom services.

inferred

Blocks

dbt SQL LKML LookML

moving_seats_line.sql · 42 lines

inferred

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

SELECT
  ROW_NUMBER() OVER() as RowNumber
  , STRING(companyprofile.CompanyID) as CompanyID
  , companyprofile.google_apps_domain as google_apps_domain
  , unionwrap.PlanName as OfficeID
  , o.Name as OfficeName
  , unionwrap.Seats as Seats
  , unionwrap.ModifiedDate AS ModifiedDate
  , DATE_ADD(unionwrap.ModifiedDate, -7, "DAY") AS LastWeekDate
  , unionwrap.update_type as update_type
FROM
  (SELECT PlanName as PlanName, Seats as Seats, ModifiedDate AS ModifiedDate, update_type as update_type
    FROM (SELECT STRING(plan.name) AS PlanName, INTEGER(lines) AS Seats, TIMESTAMP(modified_date) AS ModifiedDate, "RegularUpdate" as update_type
   FROM [uv_entities.PlanUpdate] WHERE plan.name IN (SELECT STRING(__key__.id) FROM [uv_entities.Office] )
   AND update_type <> 'usage') AS c, (SELECT STRING(plan.name) AS PlanName, INTEGER(Seats) AS Seats, TIMESTAMP(ChurnDate) AS ModifiedDate,
    "Cancel" as update_type FROM (SELECT MAX(date_modified) AS ChurnDate, STRING(__key__.id) AS PlanName FROM [uv_entities.Office]
      WHERE state = 'cancelled'GROUP BY PlanName) AS a JOIN (SELECT plan.name,-SUM(lines) AS Seats FROM [uv_entities.PlanUpdate]
       GROUP BY plan.name) AS b ON a.PlanName = b.plan.name ) AS d ) as unionwrap LEFT JOIN (SELECT string(__key__.id) AS OOfficeID,
company.id AS CompanyID, name, state AS OfficeState FROM [uv_entities.Office] ) AS o ON o.OOfficeID=unionwrap.PlanName
       LEFT JOIN (select __key__.id as CompanyID, google_apps_domain as google_apps_domain from [uv_entities.Company] ) AS companyprofile
       ON companyprofile.CompanyID= o.CompanyID WHERE PlanName is not null and companyprofile.google_apps_domain is not null order by companyprofile.google_apps_domain,unionwrap.ModifiedDate

# models/schema.yml
version: 2

models:
  - name: moving_seats_line
    columns:
      - name: companyid
      - name: rownumber
        tests:
          - unique
          - not_null
      - name: google_apps_domain
      - name: officeid
      - name: officename
      - name: last_week
      - name: this_week
      - name: update_type

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.

moving_seats_line.sql · 21 lines

source-preserved validation: not checked

SELECT
  ROW_NUMBER() OVER() as RowNumber
  , STRING(companyprofile.CompanyID) as CompanyID
  , companyprofile.google_apps_domain as google_apps_domain
  , unionwrap.PlanName as OfficeID
  , o.Name as OfficeName
  , unionwrap.Seats as Seats
  , unionwrap.ModifiedDate AS ModifiedDate
  , DATE_ADD(unionwrap.ModifiedDate, -7, "DAY") AS LastWeekDate
  , unionwrap.update_type as update_type
FROM
  (SELECT PlanName as PlanName, Seats as Seats, ModifiedDate AS ModifiedDate, update_type as update_type
    FROM (SELECT STRING(plan.name) AS PlanName, INTEGER(lines) AS Seats, TIMESTAMP(modified_date) AS ModifiedDate, "RegularUpdate" as update_type
   FROM [uv_entities.PlanUpdate] WHERE plan.name IN (SELECT STRING(__key__.id) FROM [uv_entities.Office] )
   AND update_type <> 'usage') AS c, (SELECT STRING(plan.name) AS PlanName, INTEGER(Seats) AS Seats, TIMESTAMP(ChurnDate) AS ModifiedDate,
    "Cancel" as update_type FROM (SELECT MAX(date_modified) AS ChurnDate, STRING(__key__.id) AS PlanName FROM [uv_entities.Office]
      WHERE state = 'cancelled'GROUP BY PlanName) AS a JOIN (SELECT plan.name,-SUM(lines) AS Seats FROM [uv_entities.PlanUpdate]
       GROUP BY plan.name) AS b ON a.PlanName = b.plan.name ) AS d ) as unionwrap LEFT JOIN (SELECT string(__key__.id) AS OOfficeID,
company.id AS CompanyID, name, state AS OfficeState FROM [uv_entities.Office] ) AS o ON o.OOfficeID=unionwrap.PlanName
       LEFT JOIN (select __key__.id as CompanyID, google_apps_domain as google_apps_domain from [uv_entities.Company] ) AS companyprofile
       ON companyprofile.CompanyID= o.CompanyID WHERE PlanName is not null and companyprofile.google_apps_domain is not null order by companyprofile.google_apps_domain,unionwrap.ModifiedDate

moving_seats_line.view.lkml · 81 lines

inferred

view: moving_seats_line {
  derived_table: {
    sql: SELECT
  ROW_NUMBER() OVER() as RowNumber
  , STRING(companyprofile.CompanyID) as CompanyID
  , companyprofile.google_apps_domain as google_apps_domain
  , unionwrap.PlanName as OfficeID
  , o.Name as OfficeName
  , unionwrap.Seats as Seats
  , unionwrap.ModifiedDate AS ModifiedDate
  , DATE_ADD(unionwrap.ModifiedDate, -7, "DAY") AS LastWeekDate
  , unionwrap.update_type as update_type
FROM
  (SELECT PlanName as PlanName, Seats as Seats, ModifiedDate AS ModifiedDate, update_type as update_type
    FROM (SELECT STRING(plan.name) AS PlanName, INTEGER(lines) AS Seats, TIMESTAMP(modified_date) AS ModifiedDate, "RegularUpdate" as update_type
   FROM [uv_entities.PlanUpdate] WHERE plan.name IN (SELECT STRING(__key__.id) FROM [uv_entities.Office] )
   AND update_type <> 'usage') AS c, (SELECT STRING(plan.name) AS PlanName, INTEGER(Seats) AS Seats, TIMESTAMP(ChurnDate) AS ModifiedDate,
    "Cancel" as update_type FROM (SELECT MAX(date_modified) AS ChurnDate, STRING(__key__.id) AS PlanName FROM [uv_entities.Office]
      WHERE state = 'cancelled'GROUP BY PlanName) AS a JOIN (SELECT plan.name,-SUM(lines) AS Seats FROM [uv_entities.PlanUpdate]
       GROUP BY plan.name) AS b ON a.PlanName = b.plan.name ) AS d ) as unionwrap LEFT JOIN (SELECT string(__key__.id) AS OOfficeID,
company.id AS CompanyID, name, state AS OfficeState FROM [uv_entities.Office] ) AS o ON o.OOfficeID=unionwrap.PlanName
       LEFT JOIN (select __key__.id as CompanyID, google_apps_domain as google_apps_domain from [uv_entities.Company] ) AS companyprofile
       ON companyprofile.CompanyID= o.CompanyID WHERE PlanName is not null and companyprofile.google_apps_domain is not null order by companyprofile.google_apps_domain,unionwrap.ModifiedDate ;;
  }

  dimension: companyid {
    type: int
    sql: ${TABLE}.companyid ;;
  }

  dimension: rownumber {
    type: int
    primary_key: yes
    sql: ${TABLE}.rownumber ;;
  }

  dimension: google_apps_domain {
    type: string
    sql: ${TABLE}.google_apps_domain ;;
  }

  dimension: officeid {
    type: string
    sql: ${TABLE}.officeid ;;
  }

  dimension: officename {
    type: string
    sql: ${TABLE}.officename ;;
  }

  dimension: last_week {
    sql: DATE_ADD(${TABLE}.modifieddate, -7, "DAY") ;;
  }

  dimension: this_week {
    sql: ${TABLE}.modifieddate ;;
  }

  dimension: update_type {
    type: string
    sql: ${TABLE}.update_type ;;
  }

  dimension_group: modified {
    type: time
    timeframes: [time, date, week, month, day_of_week]
    sql: ${TABLE}.modifieddate ;;
  }

  measure: seats {
    type: sum
    drill_fields: [detail*]
    sql: ${TABLE}.seats ;;
  }

  measure: running_total {
    type: running_total
    sql: ${seats} ;;
  }
}

moving_seats_line.view.lookml · 64 lines

source-preserved

- view: moving_seats_line
  derived_table:
    sql: SELECT
  ROW_NUMBER() OVER() as RowNumber
  , STRING(companyprofile.CompanyID) as CompanyID
  , companyprofile.google_apps_domain as google_apps_domain
  , unionwrap.PlanName as OfficeID
  , o.Name as OfficeName
  , unionwrap.Seats as Seats
  , unionwrap.ModifiedDate AS ModifiedDate
  , DATE_ADD(unionwrap.ModifiedDate, -7, "DAY") AS LastWeekDate
  , unionwrap.update_type as update_type
FROM
  (SELECT PlanName as PlanName, Seats as Seats, ModifiedDate AS ModifiedDate, update_type as update_type
    FROM (SELECT STRING(plan.name) AS PlanName, INTEGER(lines) AS Seats, TIMESTAMP(modified_date) AS ModifiedDate, "RegularUpdate" as update_type
   FROM [uv_entities.PlanUpdate] WHERE plan.name IN (SELECT STRING(__key__.id) FROM [uv_entities.Office] )
   AND update_type <> 'usage') AS c, (SELECT STRING(plan.name) AS PlanName, INTEGER(Seats) AS Seats, TIMESTAMP(ChurnDate) AS ModifiedDate,
    "Cancel" as update_type FROM (SELECT MAX(date_modified) AS ChurnDate, STRING(__key__.id) AS PlanName FROM [uv_entities.Office]
      WHERE state = 'cancelled'GROUP BY PlanName) AS a JOIN (SELECT plan.name,-SUM(lines) AS Seats FROM [uv_entities.PlanUpdate]
       GROUP BY plan.name) AS b ON a.PlanName = b.plan.name ) AS d ) as unionwrap LEFT JOIN (SELECT string(__key__.id) AS OOfficeID,
company.id AS CompanyID, name, state AS OfficeState FROM [uv_entities.Office] ) AS o ON o.OOfficeID=unionwrap.PlanName
       LEFT JOIN (select __key__.id as CompanyID, google_apps_domain as google_apps_domain from [uv_entities.Company] ) AS companyprofile
       ON companyprofile.CompanyID= o.CompanyID WHERE PlanName is not null and companyprofile.google_apps_domain is not null order by companyprofile.google_apps_domain,unionwrap.ModifiedDate
  fields:
  - dimension: companyid
    type: int
    sql: ${TABLE}.companyid
  - dimension: rownumber
    type: int
    primary_key: true
    sql: ${TABLE}.rownumber
  - dimension: google_apps_domain
    type: string
    sql: ${TABLE}.google_apps_domain
  - dimension: officeid
    type: string
    sql: ${TABLE}.officeid
  - dimension: officename
    type: string
    sql: ${TABLE}.officename
  - dimension: last_week
    sql: DATE_ADD(${TABLE}.modifieddate, -7, "DAY")
  - dimension: this_week
    sql: ${TABLE}.modifieddate
  - dimension: update_type
    type: string
    sql: ${TABLE}.update_type
  - dimension_group: modified
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    - day_of_week
    sql: ${TABLE}.modifieddate
  - measure: seats
    type: sum
    drill_fields:
    - detail*
    sql: ${TABLE}.seats
  - measure: running_total
    type: running_total
    sql: ${seats}

moving_seats_line.sql
moving_seats_line.sql
moving_seats_line.view.lkml
moving_seats_line.view.lookml

Moving Seats Line

Blocks

Assumptions

Contents