Contact Latest Move In

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

select
  x.contact_id,
  l.id as move_in_lead_id
from
(
  select contact_id, max(m.id) as move_in_id
  from
  production_data.move_ins m
  join production_data.local_resource_leads l on l.id = m.local_resource_lead_id
  join production_data.inquiries i on i.id = l.inquiry_id
  group by 1
) as x
join production_data.move_ins m on m.id = x.move_in_id
join production_data.local_resource_leads l on l.id = m.local_resource_lead_id

# models/schema.yml
version: 2

models:
  - name: contact_latest_move_in
    columns:
      - name: contact_id
        tests:
          - unique
          - not_null
      - name: move_in_lead_id

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
  x.contact_id,
  l.id as move_in_lead_id
from
(
  select contact_id, max(m.id) as move_in_id
  from
  production_data.move_ins m
  join production_data.local_resource_leads l on l.id = m.local_resource_lead_id
  join production_data.inquiries i on i.id = l.inquiry_id
  group by 1
) as x
join production_data.move_ins m on m.id = x.move_in_id
join production_data.local_resource_leads l on l.id = m.local_resource_lead_id

view: contact_latest_move_in {
  derived_table: {
    sql: select
  x.contact_id,
  l.id as move_in_lead_id
from
(
  select contact_id, max(m.id) as move_in_id
  from
  production_data.move_ins m
  join production_data.local_resource_leads l on l.id = m.local_resource_lead_id
  join production_data.inquiries i on i.id = l.inquiry_id
  group by 1
) as x
join production_data.move_ins m on m.id = x.move_in_id
join production_data.local_resource_leads l on l.id = m.local_resource_lead_id ;;
  }

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

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

  measure: count {
    type: count
  }
}

- view: contact_latest_move_in
  derived_table:
    sql: select
  x.contact_id,
  l.id as move_in_lead_id
from
(
  select contact_id, max(m.id) as move_in_id
  from
  production_data.move_ins m
  join production_data.local_resource_leads l on l.id = m.local_resource_lead_id
  join production_data.inquiries i on i.id = l.inquiry_id
  group by 1
) as x
join production_data.move_ins m on m.id = x.move_in_id
join production_data.local_resource_leads l on l.id = m.local_resource_lead_id
  fields:
  - dimension: contact_id
    type: int
    primary_key: true
    sql: ${TABLE}.contact_id
  - dimension: move_in_lead_id
    type: number
    sql: ${TABLE}.move_in_lead_id
  - measure: count
    type: count