Users By Num Slaves Num Frameworks

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

select
max(cs.external_elb) as external_elb
  , max(num_slaves) as num_slaves
  , max(num_frameworks) as num_frameworks
  , max(cs.user_id) as user_id
  , count(distinct ss.user_id) as num_users
  , max(cs.cluster_name) as cluster_name
  , max(cs.sent_at) as last_heartbeat
  , min(cs.sent_at) as first_heartbeat
from dcos.cluster_summary as cs
left outer join dcos.stint_started as ss
  on lower(cs.external_elb) = lower(ss.external_elb)
where cs.external_elb is not null
and aws_stack_id not like '<% aws_stack_id_placeholder1 %>'
and aws_stack_id not like '<% aws_stack_id_placeholder2 %>'
group by cs.external_elb
order by max(num_slaves) desc

# models/schema.yml
version: 2

models:
  - name: users_by_num_slaves_num_frameworks
    columns:
      - name: is_active
      - name: num_slaves
      - name: num_frameworks
      - name: user_id
      - name: cluster_name
      - name: num_users
      - name: age_in_hours
      - name: age_in_hours_tier

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.

select
max(cs.external_elb) as external_elb
  , max(num_slaves) as num_slaves
  , max(num_frameworks) as num_frameworks
  , max(cs.user_id) as user_id
  , count(distinct ss.user_id) as num_users
  , max(cs.cluster_name) as cluster_name
  , max(cs.sent_at) as last_heartbeat
  , min(cs.sent_at) as first_heartbeat
from dcos.cluster_summary as cs
left outer join dcos.stint_started as ss
  on lower(cs.external_elb) = lower(ss.external_elb)
where cs.external_elb is not null
and aws_stack_id not like '<% aws_stack_id_placeholder1 %>'
and aws_stack_id not like '<% aws_stack_id_placeholder2 %>'
group by cs.external_elb
order by max(num_slaves) desc

view: users_by_num_slaves_num_frameworks {
  derived_table: {
    sql: select
max(cs.external_elb) as external_elb
  , max(num_slaves) as num_slaves
  , max(num_frameworks) as num_frameworks
  , max(cs.user_id) as user_id
  , count(distinct ss.user_id) as num_users
  , max(cs.cluster_name) as cluster_name
  , max(cs.sent_at) as last_heartbeat
  , min(cs.sent_at) as first_heartbeat
from dcos.cluster_summary as cs
left outer join dcos.stint_started as ss
  on lower(cs.external_elb) = lower(ss.external_elb)
where cs.external_elb is not null
and aws_stack_id not like '<% aws_stack_id_placeholder1 %>'
and aws_stack_id not like '<% aws_stack_id_placeholder2 %>'
group by cs.external_elb
order by max(num_slaves) desc ;;
  }

  dimension: is_active {
    type: yesno
    sql: ${TABLE}.last_heartbeat >= (DATEADD(hour,-4, DATE_TRUNC('hour', GETDATE()) )) ;;
  }

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

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

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

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

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

  dimension: age_in_hours {
    type: number
    sql: datediff(hour, ${TABLE}.first_heartbeat, ${TABLE}.last_heartbeat) ;;
  }

  dimension: age_in_hours_tier {
    type: tier
    sql: ${age_in_hours} ;;
  }

  dimension_group: first_heartbeat {
    type: time
    timeframes: [hour, date, week, month]
    sql: ${TABLE}.first_heartbeat ;;
  }

  dimension_group: last_heartbeat {
    type: time
    timeframes: [hour, date, week, month]
    sql: ${TABLE}.last_heartbeat ;;
  }

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

  measure: sum_of_slaves {
    type: sum
    sql: ${num_slaves} ;;
  }

  measure: sum_of_users {
    type: sum
    sql: ${num_users} ;;
  }
}

- view: users_by_num_slaves_num_frameworks
  derived_table:
    sql: select
max(cs.external_elb) as external_elb
  , max(num_slaves) as num_slaves
  , max(num_frameworks) as num_frameworks
  , max(cs.user_id) as user_id
  , count(distinct ss.user_id) as num_users
  , max(cs.cluster_name) as cluster_name
  , max(cs.sent_at) as last_heartbeat
  , min(cs.sent_at) as first_heartbeat
from dcos.cluster_summary as cs
left outer join dcos.stint_started as ss
  on lower(cs.external_elb) = lower(ss.external_elb)
where cs.external_elb is not null
and aws_stack_id not like '<% aws_stack_id_placeholder1 %>'
and aws_stack_id not like '<% aws_stack_id_placeholder2 %>'
group by cs.external_elb
order by max(num_slaves) desc
  fields:
  - dimension: is_active
    type: yesno
    sql: ${TABLE}.last_heartbeat >= (DATEADD(hour,-4, DATE_TRUNC('hour', GETDATE()) ))
  - dimension: num_slaves
    type: number
    sql: ${TABLE}.num_slaves
  - dimension: num_frameworks
    type: number
    sql: ${TABLE}.num_frameworks
  - dimension: user_id
    sql: ${TABLE}.user_id
  - dimension: cluster_name
    sql: ${TABLE}.cluster_name
  - dimension: num_users
    sql: ${TABLE}.num_users
  - dimension: age_in_hours
    type: number
    sql: datediff(hour, ${TABLE}.first_heartbeat, ${TABLE}.last_heartbeat)
  - dimension: age_in_hours_tier
    type: tier
    sql: ${age_in_hours}
  - dimension_group: first_heartbeat
    type: time
    timeframes:
    - hour
    - date
    - week
    - month
    sql: ${TABLE}.first_heartbeat
  - dimension_group: last_heartbeat
    type: time
    timeframes:
    - hour
    - date
    - week
    - month
    sql: ${TABLE}.last_heartbeat
  - measure: count
    type: count
    drill_fields:
    - detail*
  - measure: sum_of_slaves
    type: sum
    sql: ${num_slaves}
  - measure: sum_of_users
    type: sum
    sql: ${num_users}