dbt Electronics cloud storage

One And Done

One And Done model for electronics cloud storage.

inferred

Blocks

dbt SQL LKML LookML

one_and_done.sql · 51 lines

inferred

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

select
  -- Query from active/ActivityDropoff.sql
  q2.account_created,
  sum( q2.gone_after_day0 ) as gone_after_day0,
  sum( q2.came_back ) as came_back,
  sum( q2.number_accounts ) as total_number_accounts,
  cast(cast(sum( q2.gone_after_day0 ) as decimal( 10, 2 ))/ sum( q2.number_accounts ) * 100 as decimal( 10,2)) as pct_gone
from (
       select
         account_created,
         case when q.days_since_account_created_to_last_activity = 0 then count(*) else 0 end gone_after_day0,
         case when q.days_since_account_created_to_last_activity > 0 then count(*) else 0 end came_back,
         count(*) as number_accounts,
         days_since_account_created_to_last_activity,
         latest_activity
       from (
              select
                sum( dma.num_activities ) as num_activities,
                max(dma.activity_date) as latest_activity,
                trunc(uam.created) as account_created,
                datediff( day, trunc(uam.created), max( dma.activity_date ) ) as days_since_account_created_to_last_activity,
                dma.mesh_id
              from {{ this }}.daily_activity dma
                join analytics.user_account_map uam on dma.mesh_id = uam.mesh_id
              where true
                    and uam.created >= '2014-05-20'
                    and dma.activity_date <= trunc( getDate() )
              -- and uam.mesh_id = 'EDCD0919-1BB4-44F5-A1CB-15ECB85E96B3'
              group by dma.mesh_id, trunc(uam.created)
            ) q
       where q.days_since_account_created_to_last_activity >= 0
       group by days_since_account_created_to_last_activity, account_created, latest_activity
       order by account_created, days_since_account_created_to_last_activity ) q2
group by q2.account_created
order by q2.account_created

# models/schema.yml
version: 2

models:
  - name: one_and_done
    columns:
      - name: account_created
      - name: created_date_with_mark_date
      - name: gone_after_day0
      - name: came_back
      - name: total_number_accounts
      - name: pct_gone

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.

one_and_done.sql · 35 lines

source-preserved validation: not checked

select
  -- Query from active/ActivityDropoff.sql
  q2.account_created,
  sum( q2.gone_after_day0 ) as gone_after_day0,
  sum( q2.came_back ) as came_back,
  sum( q2.number_accounts ) as total_number_accounts,
  cast(cast(sum( q2.gone_after_day0 ) as decimal( 10, 2 ))/ sum( q2.number_accounts ) * 100 as decimal( 10,2)) as pct_gone
from (
       select
         account_created,
         case when q.days_since_account_created_to_last_activity = 0 then count(*) else 0 end gone_after_day0,
         case when q.days_since_account_created_to_last_activity > 0 then count(*) else 0 end came_back,
         count(*) as number_accounts,
         days_since_account_created_to_last_activity,
         latest_activity
       from (
              select
                sum( dma.num_activities ) as num_activities,
                max(dma.activity_date) as latest_activity,
                trunc(uam.created) as account_created,
                datediff( day, trunc(uam.created), max( dma.activity_date ) ) as days_since_account_created_to_last_activity,
                dma.mesh_id
              from ${TABLE}.daily_activity dma
                join analytics.user_account_map uam on dma.mesh_id = uam.mesh_id
              where true
                    and uam.created >= '2014-05-20'
                    and dma.activity_date <= trunc( getDate() )
              -- and uam.mesh_id = 'EDCD0919-1BB4-44F5-A1CB-15ECB85E96B3'
              group by dma.mesh_id, trunc(uam.created)
            ) q
       where q.days_since_account_created_to_last_activity >= 0
       group by days_since_account_created_to_last_activity, account_created, latest_activity
       order by account_created, days_since_account_created_to_last_activity ) q2
group by q2.account_created
order by q2.account_created

one_and_done.view.lkml · 76 lines

inferred

view: one_and_done {
  derived_table: {
    sql: select
  -- Query from active/ActivityDropoff.sql
  q2.account_created,
  sum( q2.gone_after_day0 ) as gone_after_day0,
  sum( q2.came_back ) as came_back,
  sum( q2.number_accounts ) as total_number_accounts,
  cast(cast(sum( q2.gone_after_day0 ) as decimal( 10, 2 ))/ sum( q2.number_accounts ) * 100 as decimal( 10,2)) as pct_gone
from (
       select
         account_created,
         case when q.days_since_account_created_to_last_activity = 0 then count(*) else 0 end gone_after_day0,
         case when q.days_since_account_created_to_last_activity > 0 then count(*) else 0 end came_back,
         count(*) as number_accounts,
         days_since_account_created_to_last_activity,
         latest_activity
       from (
              select
                sum( dma.num_activities ) as num_activities,
                max(dma.activity_date) as latest_activity,
                trunc(uam.created) as account_created,
                datediff( day, trunc(uam.created), max( dma.activity_date ) ) as days_since_account_created_to_last_activity,
                dma.mesh_id
              from ${TABLE}.daily_activity dma
                join analytics.user_account_map uam on dma.mesh_id = uam.mesh_id
              where true
                    and uam.created >= '2014-05-20'
                    and dma.activity_date <= trunc( getDate() )
              -- and uam.mesh_id = 'EDCD0919-1BB4-44F5-A1CB-15ECB85E96B3'
              group by dma.mesh_id, trunc(uam.created)
            ) q
       where q.days_since_account_created_to_last_activity >= 0
       group by days_since_account_created_to_last_activity, account_created, latest_activity
       order by account_created, days_since_account_created_to_last_activity ) q2
group by q2.account_created
order by q2.account_created ;;
  }

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

  dimension: created_date_with_mark_date {
    sql: CASE
WHEN ${marker_dates.name} IS NULL
THEN to_char(${account_created},'YYYY-MM-DD')
ELSE to_char(${account_created},'YYYY-MM-DD') || ' - ' || ${marker_dates.name}
END ;;
  }

  dimension: gone_after_day0 {
    type: number
    sql: ${TABLE}.gone_after_day0 ;;
  }

  dimension: came_back {
    type: number
    sql: ${TABLE}.came_back ;;
  }

  dimension: total_number_accounts {
    type: number
    sql: ${TABLE}.total_number_accounts ;;
  }

  dimension: pct_gone {
    type: number
    sql: ${TABLE}.pct_gone ;;
  }

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

one_and_done.view.lookml · 62 lines

source-preserved

- view: one_and_done
  derived_table:
    sql: select
  -- Query from active/ActivityDropoff.sql
  q2.account_created,
  sum( q2.gone_after_day0 ) as gone_after_day0,
  sum( q2.came_back ) as came_back,
  sum( q2.number_accounts ) as total_number_accounts,
  cast(cast(sum( q2.gone_after_day0 ) as decimal( 10, 2 ))/ sum( q2.number_accounts ) * 100 as decimal( 10,2)) as pct_gone
from (
       select
         account_created,
         case when q.days_since_account_created_to_last_activity = 0 then count(*) else 0 end gone_after_day0,
         case when q.days_since_account_created_to_last_activity > 0 then count(*) else 0 end came_back,
         count(*) as number_accounts,
         days_since_account_created_to_last_activity,
         latest_activity
       from (
              select
                sum( dma.num_activities ) as num_activities,
                max(dma.activity_date) as latest_activity,
                trunc(uam.created) as account_created,
                datediff( day, trunc(uam.created), max( dma.activity_date ) ) as days_since_account_created_to_last_activity,
                dma.mesh_id
              from ${TABLE}.daily_activity dma
                join analytics.user_account_map uam on dma.mesh_id = uam.mesh_id
              where true
                    and uam.created >= '2014-05-20'
                    and dma.activity_date <= trunc( getDate() )
              -- and uam.mesh_id = 'EDCD0919-1BB4-44F5-A1CB-15ECB85E96B3'
              group by dma.mesh_id, trunc(uam.created)
            ) q
       where q.days_since_account_created_to_last_activity >= 0
       group by days_since_account_created_to_last_activity, account_created, latest_activity
       order by account_created, days_since_account_created_to_last_activity ) q2
group by q2.account_created
order by q2.account_created
  fields:
  - dimension: account_created
    sql: ${TABLE}.account_created
  - dimension: created_date_with_mark_date
    sql: CASE
WHEN ${marker_dates.name} IS NULL
THEN to_char(${account_created},'YYYY-MM-DD')
ELSE to_char(${account_created},'YYYY-MM-DD') || ' - ' || ${marker_dates.name}
END
  - dimension: gone_after_day0
    type: number
    sql: ${TABLE}.gone_after_day0
  - dimension: came_back
    type: number
    sql: ${TABLE}.came_back
  - dimension: total_number_accounts
    type: number
    sql: ${TABLE}.total_number_accounts
  - dimension: pct_gone
    type: number
    sql: ${TABLE}.pct_gone
  - measure: count
    type: count
    drill_fields:
    - detail*

one_and_done.sql
one_and_done.sql
one_and_done.view.lkml
one_and_done.view.lookml

One And Done

Blocks

Assumptions

Contents