Plan Builds And Costs

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

select
    plans.id as plan_id
  , plans.org_name as org_name
  , stripe.day as day
  , stripe.discounted_amount as amount
  , builds.container_seconds as container_seconds
from plans as plans
   , {{ ref('org_plan_history_raw') }} as stripe
   , {{ ref('plan_builds_day') }} as builds
where plans.id = builds.plan_id
  and plans.org_name = stripe.org_name
  and stripe.day = builds.day

# models/schema.yml
version: 2

models:
  - name: plan_builds_and_costs
    columns:
      - name: org_name
      - name: date
      - name: amount
      - name: container_seconds
      - name: container_hours
      - name: dollars
      - name: dollars_container_hour

Assumptions

• Each ${view.SQL_TABLE_NAME} was mapped to {{ ref('view') }}; confirm a dbt model exists for each referenced view.
• 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
    plans.id as plan_id
  , plans.org_name as org_name
  , stripe.day as day
  , stripe.discounted_amount as amount
  , builds.container_seconds as container_seconds
from plans as plans
   , ${org_plan_history_raw.SQL_TABLE_NAME} as stripe
   , ${plan_builds_day.SQL_TABLE_NAME} as builds
where plans.id = builds.plan_id
  and plans.org_name = stripe.org_name
  and stripe.day = builds.day

view: plan_builds_and_costs {
  derived_table: {
    sql: select
    plans.id as plan_id
  , plans.org_name as org_name
  , stripe.day as day
  , stripe.discounted_amount as amount
  , builds.container_seconds as container_seconds
from plans as plans
   , ${org_plan_history_raw.SQL_TABLE_NAME} as stripe
   , ${plan_builds_day.SQL_TABLE_NAME} as builds
where plans.id = builds.plan_id
  and plans.org_name = stripe.org_name
  and stripe.day = builds.day ;;
  }

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

  dimension: date {
    type: time
    sql: ${TABLE}.day ;;
  }

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

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

  dimension: container_hours {
    type: number
    sql: ${TABLE}.container_seconds / 60 / 60 ;;
  }

  dimension: dollars {
    type: number
    sql: ${amount} ;;
  }

  dimension: dollars_container_hour {
    type: number
    sql: ${dollars} / NULLIF(${container_hours},0) ;;
  }

  measure: average_dollars_container_hour {
    type: average
    sql: ${dollars_container_hour} ;;
  }

  measure: average_container_hours {
    type: average
    sql: ${container_hours} ;;
  }

  measure: total_container_hours {
    type: sum
    sql: ${container_hours} ;;
  }

  measure: total_dollars {
    type: sum
    sql: ${dollars} ;;
  }

  measure: average_dollars {
    type: average
    sql: ${dollars} ;;
  }
}

- view: plan_builds_and_costs
  derived_table:
    sql: select
    plans.id as plan_id
  , plans.org_name as org_name
  , stripe.day as day
  , stripe.discounted_amount as amount
  , builds.container_seconds as container_seconds
from plans as plans
   , ${org_plan_history_raw.SQL_TABLE_NAME} as stripe
   , ${plan_builds_day.SQL_TABLE_NAME} as builds
where plans.id = builds.plan_id
  and plans.org_name = stripe.org_name
  and stripe.day = builds.day
  fields:
  - dimension: org_name
    sql: ${TABLE}.org_name
  - dimension: date
    type: time
    sql: ${TABLE}.day
  - dimension: amount
    type: number
    sql: ${TABLE}.amount
  - dimension: container_seconds
    type: number
    sql: ${TABLE}.container_seconds
  - dimension: container_hours
    type: number
    sql: ${TABLE}.container_seconds / 60 / 60
  - dimension: dollars
    type: number
    sql: ${amount}
  - dimension: dollars_container_hour
    type: number
    sql: ${dollars} / NULLIF(${container_hours},0)
  - measure: average_dollars_container_hour
    type: average
    sql: ${dollars_container_hour}
  - measure: average_container_hours
    type: average
    sql: ${container_hours}
  - measure: total_container_hours
    type: sum
    sql: ${container_hours}
  - measure: total_dollars
    type: sum
    sql: ${dollars}
  - measure: average_dollars
    type: average
    sql: ${dollars}