Event Additional Allocation Fperformances

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

SELECT
  e.id,
  sum(s.marked_up) as marked_up,
  sum(s.marked_back) as marked_back
FROM
  (
    SELECT
      tm.ticketid as ticket_id,
      tm.markedback as marked_backed,
      IF(tm.markedback < 0, sum(tm.markedback), 0) as marked_up,
      IF(tm.markedback > 0, sum(tm.markedback), 0) as marked_back
    FROM event_ticket_changes as tm
    GROUP BY tm.id
  ) as s
LEFT JOIN event_entries as t on t.id = s.ticket_id
LEFT JOIN occasions as e on e.id = t.eventid
GROUP BY e.id

# models/schema.yml
version: 2

models:
  - name: event_additional_allocation_fperformances
    columns:
      - name: event_id
        tests:
          - unique
          - not_null
      - name: marked_up
      - name: marked_back
      - name: received
        description: "Indicates if additional allocation has been received for an event"

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
  e.id,
  sum(s.marked_up) as marked_up,
  sum(s.marked_back) as marked_back
FROM
  (
    SELECT
      tm.ticketid as ticket_id,
      tm.markedback as marked_backed,
      IF(tm.markedback < 0, sum(tm.markedback), 0) as marked_up,
      IF(tm.markedback > 0, sum(tm.markedback), 0) as marked_back
    FROM event_ticket_changes as tm
    GROUP BY tm.id
  ) as s
LEFT JOIN event_entries as t on t.id = s.ticket_id
LEFT JOIN occasions as e on e.id = t.eventid
GROUP BY e.id

view: event_additional_allocation_fperformances {
  derived_table: {
    sql: SELECT
  e.id,
  sum(s.marked_up) as marked_up,
  sum(s.marked_back) as marked_back
FROM
  (
    SELECT
      tm.ticketid as ticket_id,
      tm.markedback as marked_backed,
      IF(tm.markedback < 0, sum(tm.markedback), 0) as marked_up,
      IF(tm.markedback > 0, sum(tm.markedback), 0) as marked_back
    FROM event_ticket_changes as tm
    GROUP BY tm.id
  ) as s
LEFT JOIN event_entries as t on t.id = s.ticket_id
LEFT JOIN occasions as e on e.id = t.eventid
GROUP BY e.id ;;
  }

  dimension: event_id {
    type: number
    primary_key: yes
    hidden: yes
    sql: ${TABLE}.id ;;
  }

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

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

  dimension: received {
    description: "Indicates if additional allocation has been received for an event"
    type: yesno
    sql: IF(abs(${marked_up}) > 0, 1, 0) ;;
  }

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

- view: event_additional_allocation_fperformances
  derived_table:
    sql: SELECT
  e.id,
  sum(s.marked_up) as marked_up,
  sum(s.marked_back) as marked_back
FROM
  (
    SELECT
      tm.ticketid as ticket_id,
      tm.markedback as marked_backed,
      IF(tm.markedback < 0, sum(tm.markedback), 0) as marked_up,
      IF(tm.markedback > 0, sum(tm.markedback), 0) as marked_back
    FROM event_ticket_changes as tm
    GROUP BY tm.id
  ) as s
LEFT JOIN event_entries as t on t.id = s.ticket_id
LEFT JOIN occasions as e on e.id = t.eventid
GROUP BY e.id
  fields:
  - dimension: event_id
    type: number
    primary_key: true
    hidden: true
    sql: ${TABLE}.id
  - dimension: marked_up
    type: number
    hidden: true
    sql: ${TABLE}.marked_up
  - dimension: marked_back
    type: number
    hidden: true
    sql: ${TABLE}.marked_back
  - dimension: received
    type: yesno
    description: Indicates if additional allocation has been received for an event
    sql: IF(abs(${marked_up}) > 0, 1, 0)
  - measure: count
    type: count
    drill_fields:
    - detail*