Promoters With Inventory

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

SELECT *
FROM sync_promoters
LEFT JOIN (
  SELECT
      _id as event_id
    , created_at as event_created_at
    , updated_at as event_updated_at
    , perm_name
    , name
    , card_fee
    , currency
    , venue
    , address
  FROM sync_events
) AS events
ON sync_promoters.owned_event_id = events.event_id
LEFT JOIN (
  SELECT
       perm_name as inventory_perm_name
     , max(artists) as artists
     , max(city) as city
     , max(price) as price
     , max(allocation) as allocation
     , sum(sold) as sold
     , sum(waitinglist) as waitinglist
     , sum(revenue) as revenue
     , sum(views) as views
     , sum(plays) as plays
     , sum(registrations) as registrations
     , max(off_sale) as event_date
FROM ts_inventory
GROUP BY perm_name
) AS inventory
ON events.perm_name = inventory.inventory_perm_name

# models/schema.yml
version: 2

models:
  - name: promoters_with_inventory
    columns:
      - name: _id
      - name: created_at
      - name: updated_at
      - name: first_name
      - name: last_name
      - name: email
      - name: owned_event_id
      - name: event_id
      - name: event_created_at
      - name: perm_name
      - name: name
      - name: venue
      - name: card_fee
      - name: currency
      - name: price
      - name: artists
      - name: city
      - name: allocation
      - name: waitinglist
      - name: revenue
      - name: sold
      - name: plays
      - name: registrations
      - name: event_date

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 *
FROM sync_promoters
LEFT JOIN (
  SELECT
      _id as event_id
    , created_at as event_created_at
    , updated_at as event_updated_at
    , perm_name
    , name
    , card_fee
    , currency
    , venue
    , address
  FROM sync_events
) AS events
ON sync_promoters.owned_event_id = events.event_id
LEFT JOIN (
  SELECT
       perm_name as inventory_perm_name
     , max(artists) as artists
     , max(city) as city
     , max(price) as price
     , max(allocation) as allocation
     , sum(sold) as sold
     , sum(waitinglist) as waitinglist
     , sum(revenue) as revenue
     , sum(views) as views
     , sum(plays) as plays
     , sum(registrations) as registrations
     , max(off_sale) as event_date
FROM ts_inventory
GROUP BY perm_name
) AS inventory
ON events.perm_name = inventory.inventory_perm_name

view: promoters_with_inventory {
  derived_table: {
    sql: SELECT *
FROM sync_promoters
LEFT JOIN (
  SELECT
      _id as event_id
    , created_at as event_created_at
    , updated_at as event_updated_at
    , perm_name
    , name
    , card_fee
    , currency
    , venue
    , address
  FROM sync_events
) AS events
ON sync_promoters.owned_event_id = events.event_id
LEFT JOIN (
  SELECT
       perm_name as inventory_perm_name
     , max(artists) as artists
     , max(city) as city
     , max(price) as price
     , max(allocation) as allocation
     , sum(sold) as sold
     , sum(waitinglist) as waitinglist
     , sum(revenue) as revenue
     , sum(views) as views
     , sum(plays) as plays
     , sum(registrations) as registrations
     , max(off_sale) as event_date
FROM ts_inventory
GROUP BY perm_name
) AS inventory
ON events.perm_name = inventory.inventory_perm_name ;;
  }

  dimension: _id {
    type: string
    hidden: yes
    sql: ${TABLE}._id ;;
  }

  dimension: created_at {
    type: time
    sql: ${TABLE}.created_at ;;
  }

  dimension: updated_at {
    type: time
    sql: ${TABLE}.updated_at ;;
  }

  dimension: first_name {
    type: string
    sql: ${TABLE}.first_name ;;
  }

  dimension: last_name {
    type: string
    sql: ${TABLE}.last_name ;;
  }

  dimension: email {
    type: string
    sql: ${TABLE}.email ;;
  }

  dimension: owned_event_id {
    type: string
    sql: ${TABLE}.owned_event_id ;;
  }

  dimension: event_id {
    type: string
    hidden: yes
    sql: ${TABLE}.event_id ;;
  }

  dimension: event_created_at {
    type: time
    sql: ${TABLE}.event_created_at ;;
  }

  dimension: perm_name {
    type: string
    sql: ${TABLE}.perm_name ;;
  }

  dimension: name {
    label: "Event Name"
    type: string
    sql: ${TABLE}.name ;;
  }

  dimension: venue {
    type: string
    sql: ${TABLE}.venue ;;
  }

  dimension: card_fee {
    type: number
    sql: ${TABLE} card_fee ;;
  }

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

  dimension: price {
    type: number
    value_format: "\"£\"0.00"
    sql: ${TABLE}.price ;;
  }

  dimension: artists {
    type: string
    sql: ${TABLE}.artists ;;
  }

  dimension: city {
    type: string
    sql: ${TABLE}.city ;;
  }

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

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

  dimension: revenue {
    type: number
    hidden: yes
    value_format: "\"£\"#,##0"
    sql: ${TABLE}.revenue ;;
  }

  dimension: sold {
    type: number
    hidden: yes
    sql: ${TABLE}.sold ;;
  }

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

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

  dimension: event_date {
    type: date
    sql: ${TABLE}.event_date ;;
  }

  measure: tickets_sold {
    type: number
    value_format: "#,##0"
    sql: ${TABLE}.sold ;;
  }

  measure: ticket_revenue {
    type: number
    value_format: "$#,##0"
    sql: ${TABLE}.revenue ;;
  }
}

- view: promoters_with_inventory
  derived_table:
    sql: SELECT *
FROM sync_promoters
LEFT JOIN (
  SELECT
      _id as event_id
    , created_at as event_created_at
    , updated_at as event_updated_at
    , perm_name
    , name
    , card_fee
    , currency
    , venue
    , address
  FROM sync_events
) AS events
ON sync_promoters.owned_event_id = events.event_id
LEFT JOIN (
  SELECT
       perm_name as inventory_perm_name
     , max(artists) as artists
     , max(city) as city
     , max(price) as price
     , max(allocation) as allocation
     , sum(sold) as sold
     , sum(waitinglist) as waitinglist
     , sum(revenue) as revenue
     , sum(views) as views
     , sum(plays) as plays
     , sum(registrations) as registrations
     , max(off_sale) as event_date
FROM ts_inventory
GROUP BY perm_name
) AS inventory
ON events.perm_name = inventory.inventory_perm_name
  fields:
  - dimension: _id
    type: string
    hidden: true
    sql: ${TABLE}._id
  - dimension: created_at
    type: time
    sql: ${TABLE}.created_at
  - dimension: updated_at
    type: time
    sql: ${TABLE}.updated_at
  - dimension: first_name
    type: string
    sql: ${TABLE}.first_name
  - dimension: last_name
    type: string
    sql: ${TABLE}.last_name
  - dimension: email
    type: string
    sql: ${TABLE}.email
  - dimension: owned_event_id
    type: string
    sql: ${TABLE}.owned_event_id
  - dimension: event_id
    type: string
    hidden: true
    sql: ${TABLE}.event_id
  - dimension: event_created_at
    type: time
    sql: ${TABLE}.event_created_at
  - dimension: perm_name
    type: string
    sql: ${TABLE}.perm_name
  - dimension: name
    type: string
    label: Event Name
    sql: ${TABLE}.name
  - dimension: venue
    type: string
    sql: ${TABLE}.venue
  - dimension: card_fee
    type: number
    sql: ${TABLE} card_fee
  - dimension: currency
    type: number
    sql: ${TABLE}.currency
  - dimension: price
    type: number
    value_format: '"£"0.00'
    sql: ${TABLE}.price
  - dimension: artists
    type: string
    sql: ${TABLE}.artists
  - dimension: city
    type: string
    sql: ${TABLE}.city
  - dimension: allocation
    type: number
    sql: ${TABLE}.allocation
  - dimension: waitinglist
    type: number
    sql: ${TABLE}.waitinglist
  - dimension: revenue
    type: number
    hidden: true
    value_format: '"£"#,##0'
    sql: ${TABLE}.revenue
  - dimension: sold
    type: number
    hidden: true
    sql: ${TABLE}.sold
  - dimension: plays
    type: number
    sql: ${TABLE}.plays
  - dimension: registrations
    type: number
    sql: ${TABLE}.registrations
  - dimension: event_date
    type: date
    sql: ${TABLE}.event_date
  - measure: tickets_sold
    type: number
    value_format: '#,##0'
    sql: ${TABLE}.sold
  - measure: ticket_revenue
    type: number
    value_format: '$#,##0'
    sql: ${TABLE}.revenue