SQL Electronics cloud storage

Day1 View Usage By Mesh

Day1 View Usage By Mesh model for electronics cloud storage.

source-preserved

Blocks

dbt SQL LKML LookML

day1_view_usage_by_mesh.sql · 47 lines

inferred

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

select va.mesh_id,
case when vil.name is null then 'Unknown view ' || va.view_id else vil.name end as view_name,
CASE
   WHEN vil.name = 'Startup' or vil.name = 'WelcomeSplash' THEN 1
   WHEN vil.name like 'SetupAccount%' or vil.name = 'Settings' THEN 2
   WHEN vil.name = 'Scene_Digest' THEN 4
   WHEN vil.name = 'Scene_Timeline' THEN 8
   WHEN vil.name = 'Scene_Album' THEN 16
   WHEN vil.name = 'Scene_Photos' THEN 32
   WHEN vil.name = 'Scene_Mesh' or vil.name = 'MeshStatus' THEN 64
   WHEN vil.name like '%List' THEN 128
   WHEN vil.name = 'Scene_JustAdded' THEN 256
   WHEN vil.name = 'Scene_FullScreen' THEN 512
   WHEN vil.name = 'PlayVideo' or vil.name = 'Scene_Videos' THEN 1024
   WHEN vil.name = 'Scene_Page' THEN 2048
   WHEN vil.name = 'Scene_Favorites' THEN 4096
   WHEN vil.name = 'ImportSelection' or vil.name = 'ContinuousImport' or vil.name = 'Scene_PowerCopy' THEN 8192
   WHEN vil.name like 'Scene_Event%' or vil.name like 'Event%' THEN 16384
   WHEN vil.name = 'Scene_ActionNeeded' THEN 32768
   WHEN vil.name = 'Scene_BackingUp' THEN 65536
   WHEN vil.name like 'Scene_Tag%' or vil.name like 'Tag%' THEN 131072
   WHEN vil.name like 'MomentsPast%' THEN 262144
   WHEN true THEN 0
END AS view_mask
from {{ this }}.view_access va
left join {{ this }}.view_lookup vil on va.view_id = vil.id
join analytics.user_account_map uam on va.mesh_id = uam.mesh_id
where va.view_id is not null and va.view_id > 1
  and va.message_ts <= dateadd( day, 1, uam.created )
group by va.mesh_id, view_name, view_mask

# models/schema.yml
version: 2

models:
  - name: day1_view_usage_by_mesh
    columns:
      - name: mesh_id
      - name: pk
        tests:
          - unique
          - not_null
      - name: view_name
      - name: view_mask

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.

day1_view_usage_by_mesh.sql · 30 lines

source-preserved validation: not checked

select va.mesh_id,
case when vil.name is null then 'Unknown view ' || va.view_id else vil.name end as view_name,
CASE
   WHEN vil.name = 'Startup' or vil.name = 'WelcomeSplash' THEN 1
   WHEN vil.name like 'SetupAccount%' or vil.name = 'Settings' THEN 2
   WHEN vil.name = 'Scene_Digest' THEN 4
   WHEN vil.name = 'Scene_Timeline' THEN 8
   WHEN vil.name = 'Scene_Album' THEN 16
   WHEN vil.name = 'Scene_Photos' THEN 32
   WHEN vil.name = 'Scene_Mesh' or vil.name = 'MeshStatus' THEN 64
   WHEN vil.name like '%List' THEN 128
   WHEN vil.name = 'Scene_JustAdded' THEN 256
   WHEN vil.name = 'Scene_FullScreen' THEN 512
   WHEN vil.name = 'PlayVideo' or vil.name = 'Scene_Videos' THEN 1024
   WHEN vil.name = 'Scene_Page' THEN 2048
   WHEN vil.name = 'Scene_Favorites' THEN 4096
   WHEN vil.name = 'ImportSelection' or vil.name = 'ContinuousImport' or vil.name = 'Scene_PowerCopy' THEN 8192
   WHEN vil.name like 'Scene_Event%' or vil.name like 'Event%' THEN 16384
   WHEN vil.name = 'Scene_ActionNeeded' THEN 32768
   WHEN vil.name = 'Scene_BackingUp' THEN 65536
   WHEN vil.name like 'Scene_Tag%' or vil.name like 'Tag%' THEN 131072
   WHEN vil.name like 'MomentsPast%' THEN 262144
   WHEN true THEN 0
END AS view_mask
from ${TABLE}.view_access va
left join ${TABLE}.view_lookup vil on va.view_id = vil.id
join analytics.user_account_map uam on va.mesh_id = uam.mesh_id
where va.view_id is not null and va.view_id > 1
  and va.message_ts <= dateadd( day, 1, uam.created )
group by va.mesh_id, view_name, view_mask

day1_view_usage_by_mesh.view.lkml · 56 lines

inferred

view: day1_view_usage_by_mesh {
  derived_table: {
    sql: select va.mesh_id,
case when vil.name is null then 'Unknown view ' || va.view_id else vil.name end as view_name,
CASE
   WHEN vil.name = 'Startup' or vil.name = 'WelcomeSplash' THEN 1
   WHEN vil.name like 'SetupAccount%' or vil.name = 'Settings' THEN 2
   WHEN vil.name = 'Scene_Digest' THEN 4
   WHEN vil.name = 'Scene_Timeline' THEN 8
   WHEN vil.name = 'Scene_Album' THEN 16
   WHEN vil.name = 'Scene_Photos' THEN 32
   WHEN vil.name = 'Scene_Mesh' or vil.name = 'MeshStatus' THEN 64
   WHEN vil.name like '%List' THEN 128
   WHEN vil.name = 'Scene_JustAdded' THEN 256
   WHEN vil.name = 'Scene_FullScreen' THEN 512
   WHEN vil.name = 'PlayVideo' or vil.name = 'Scene_Videos' THEN 1024
   WHEN vil.name = 'Scene_Page' THEN 2048
   WHEN vil.name = 'Scene_Favorites' THEN 4096
   WHEN vil.name = 'ImportSelection' or vil.name = 'ContinuousImport' or vil.name = 'Scene_PowerCopy' THEN 8192
   WHEN vil.name like 'Scene_Event%' or vil.name like 'Event%' THEN 16384
   WHEN vil.name = 'Scene_ActionNeeded' THEN 32768
   WHEN vil.name = 'Scene_BackingUp' THEN 65536
   WHEN vil.name like 'Scene_Tag%' or vil.name like 'Tag%' THEN 131072
   WHEN vil.name like 'MomentsPast%' THEN 262144
   WHEN true THEN 0
END AS view_mask
from ${TABLE}.view_access va
left join ${TABLE}.view_lookup vil on va.view_id = vil.id
join analytics.user_account_map uam on va.mesh_id = uam.mesh_id
where va.view_id is not null and va.view_id > 1
  and va.message_ts <= dateadd( day, 1, uam.created )
group by va.mesh_id, view_name, view_mask ;;
  }

  dimension: mesh_id {
    sql: ${TABLE}.mesh_id ;;
  }

  dimension: pk {
    primary_key: yes
    sql: ${mesh_id} || ${view_name} ;;
  }

  dimension: view_name {
    sql: ${TABLE}.view_name ;;
  }

  dimension: view_mask {
    sql: ${TABLE}.view_mask ;;
  }

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

day1_view_usage_by_mesh.view.lookml · 46 lines

source-preserved

- view: day1_view_usage_by_mesh
  derived_table:
    sql: select va.mesh_id,
case when vil.name is null then 'Unknown view ' || va.view_id else vil.name end as view_name,
CASE
   WHEN vil.name = 'Startup' or vil.name = 'WelcomeSplash' THEN 1
   WHEN vil.name like 'SetupAccount%' or vil.name = 'Settings' THEN 2
   WHEN vil.name = 'Scene_Digest' THEN 4
   WHEN vil.name = 'Scene_Timeline' THEN 8
   WHEN vil.name = 'Scene_Album' THEN 16
   WHEN vil.name = 'Scene_Photos' THEN 32
   WHEN vil.name = 'Scene_Mesh' or vil.name = 'MeshStatus' THEN 64
   WHEN vil.name like '%List' THEN 128
   WHEN vil.name = 'Scene_JustAdded' THEN 256
   WHEN vil.name = 'Scene_FullScreen' THEN 512
   WHEN vil.name = 'PlayVideo' or vil.name = 'Scene_Videos' THEN 1024
   WHEN vil.name = 'Scene_Page' THEN 2048
   WHEN vil.name = 'Scene_Favorites' THEN 4096
   WHEN vil.name = 'ImportSelection' or vil.name = 'ContinuousImport' or vil.name = 'Scene_PowerCopy' THEN 8192
   WHEN vil.name like 'Scene_Event%' or vil.name like 'Event%' THEN 16384
   WHEN vil.name = 'Scene_ActionNeeded' THEN 32768
   WHEN vil.name = 'Scene_BackingUp' THEN 65536
   WHEN vil.name like 'Scene_Tag%' or vil.name like 'Tag%' THEN 131072
   WHEN vil.name like 'MomentsPast%' THEN 262144
   WHEN true THEN 0
END AS view_mask
from ${TABLE}.view_access va
left join ${TABLE}.view_lookup vil on va.view_id = vil.id
join analytics.user_account_map uam on va.mesh_id = uam.mesh_id
where va.view_id is not null and va.view_id > 1
  and va.message_ts <= dateadd( day, 1, uam.created )
group by va.mesh_id, view_name, view_mask
  fields:
  - dimension: mesh_id
    sql: ${TABLE}.mesh_id
  - dimension: pk
    primary_key: true
    sql: ${mesh_id} || ${view_name}
  - dimension: view_name
    sql: ${TABLE}.view_name
  - dimension: view_mask
    sql: ${TABLE}.view_mask
  - measure: count
    type: count
    drill_fields:
    - detail*

day1_view_usage_by_mesh.sql
day1_view_usage_by_mesh.sql
day1_view_usage_by_mesh.view.lkml
day1_view_usage_by_mesh.view.lookml

Day1 View Usage By Mesh

Blocks

Assumptions

Contents