All Company Meetings

-- models/all_company_meetings.sql
{{ config(materialized='table') }}

SELECT
  meeting.date as date
  , meeting.company_id as associated_company_id
  , meeting.contact_point_id as contact_id
  , meeting.my_id as id
  , meeting.points as points
  , meeting.duration as duration
  , meeting.purpose as purpose
  , meeting.type as type
  , meeting.notes as notes
FROM meeting
LEFT JOIN companies ON companies.my_id=meeting.company_id
WHERE companies.associated_contact_id IS NOT NULL
UNION
SELECT DISTINCT
  meeting.date as date
  , companies.my_id as associated_company_id
  , companies.associated_contact_id as contact_id
  , NULL as id
  , 0 as points
  , 0 as duration
  , NULL as purpose
  , NULL as type
  , NULL as notes
FROM companies
INNER JOIN meeting ON meeting.date IS NOT NULL
WHERE companies.associated_contact_id IS NOT NULL

# models/schema.yml
version: 2

models:
  - name: all_company_meetings
    columns:
      - name: purpose
      - name: type
      - name: points
      - name: duration
      - name: id
        tests:
          - unique
          - not_null
      - name: notes

Assumptions

• materialized='table' inferred from a LookML persist hint (persist_for / datagroup / sql_trigger_value).
• schema.yml columns come from view dimensions; a primary_key dimension gets unique + not_null tests.

SELECT
  meeting.date as date
  , meeting.company_id as associated_company_id
  , meeting.contact_point_id as contact_id
  , meeting.my_id as id
  , meeting.points as points
  , meeting.duration as duration
  , meeting.purpose as purpose
  , meeting.type as type
  , meeting.notes as notes
FROM meeting
LEFT JOIN companies ON companies.my_id=meeting.company_id
WHERE companies.associated_contact_id IS NOT NULL
UNION
SELECT DISTINCT
  meeting.date as date
  , companies.my_id as associated_company_id
  , companies.associated_contact_id as contact_id
  , NULL as id
  , 0 as points
  , 0 as duration
  , NULL as purpose
  , NULL as type
  , NULL as notes
FROM companies
INNER JOIN meeting ON meeting.date IS NOT NULL
WHERE companies.associated_contact_id IS NOT NULL

view: all_company_meetings {
  derived_table: {
    sql: SELECT
  meeting.date as date
  , meeting.company_id as associated_company_id
  , meeting.contact_point_id as contact_id
  , meeting.my_id as id
  , meeting.points as points
  , meeting.duration as duration
  , meeting.purpose as purpose
  , meeting.type as type
  , meeting.notes as notes
FROM meeting
LEFT JOIN companies ON companies.my_id=meeting.company_id
WHERE companies.associated_contact_id IS NOT NULL
UNION
SELECT DISTINCT
  meeting.date as date
  , companies.my_id as associated_company_id
  , companies.associated_contact_id as contact_id
  , NULL as id
  , 0 as points
  , 0 as duration
  , NULL as purpose
  , NULL as type
  , NULL as notes
FROM companies
INNER JOIN meeting ON meeting.date IS NOT NULL
WHERE companies.associated_contact_id IS NOT NULL ;;
    persist_for: "4 hours"
  }

  dimension: purpose {
  }

  dimension: type {
  }

  dimension: points {
    type: int
  }

  dimension: duration {
    type: int
  }

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

  dimension: notes {
  }

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

  measure: points_sum {
    type: sum
    sql: ${points} ;;
  }

  measure: count {
    type: count
    filters: [duration: "1 to"]
    sql: ${id} ;;
  }
}

- view: all_company_meetings
  derived_table:
    sql: SELECT
  meeting.date as date
  , meeting.company_id as associated_company_id
  , meeting.contact_point_id as contact_id
  , meeting.my_id as id
  , meeting.points as points
  , meeting.duration as duration
  , meeting.purpose as purpose
  , meeting.type as type
  , meeting.notes as notes
FROM meeting
LEFT JOIN companies ON companies.my_id=meeting.company_id
WHERE companies.associated_contact_id IS NOT NULL
UNION
SELECT DISTINCT
  meeting.date as date
  , companies.my_id as associated_company_id
  , companies.associated_contact_id as contact_id
  , NULL as id
  , 0 as points
  , 0 as duration
  , NULL as purpose
  , NULL as type
  , NULL as notes
FROM companies
INNER JOIN meeting ON meeting.date IS NOT NULL
WHERE companies.associated_contact_id IS NOT NULL
    persist_for: 4 hours
  fields:
  - dimension: purpose
  - dimension: type
  - dimension: points
    type: int
  - dimension: duration
    type: int
  - dimension: id
    type: int
    primary_key: true
    sql: ${TABLE}.my_id
  - dimension: notes
  - dimension_group: date
    type: time
    timeframes:
    - date
    - week
    - month
    sql: ${TABLE}.date
  - measure: points_sum
    type: sum
    sql: ${points}
  - measure: count
    type: count
    filters:
    - duration: 1 to
    sql: ${id}