data.ml
SQL Mobile ticketing

Event Entries Markback Counts

Event Entries Markback Counts model for mobile ticketing.

source-preserved

Blocks

event_entries_markback_counts.sql · 31 lines
inferred 
-- models/event_entries_markback_counts.sql
{{ config(materialized='view') }}

SELECT
  s.ticket_id as ticket_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
GROUP BY s.ticket_id

# models/schema.yml
version: 2

models:
  - name: event_entries_markback_counts
    columns:
      - name: ticket_id
        tests:
          - unique
          - not_null
      - name: marked_up
      - name: marked_back

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.
event_entries_markback_counts.sql · 15 lines
source-preserved validation: parses 
SELECT
  s.ticket_id as ticket_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
GROUP BY s.ticket_id
event_entries_markback_counts.view.lkml · 50 lines
inferred 
view: event_entries_markback_counts {
  derived_table: {
    sql: SELECT
  s.ticket_id as ticket_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
GROUP BY s.ticket_id ;;
  }

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

  dimension: marked_up {
    type: number
    hidden: yes
    sql: IFNULL(abs(${TABLE}.marked_up),0) ;;
  }

  dimension: marked_back {
    type: number
    hidden: yes
    sql: IFNULL(${TABLE}.marked_back,0) ;;
  }

  measure: total_marked_up {
    description: "Total number of event_entries marked up"
    type: sum
    sql: ${marked_up} ;;
  }

  measure: total_marked_back {
    description: "Total number of event_entries marked back"
    type: sum
    sql: ${marked_back} ;;
  }
}
event_entries_markback_counts.view.lookml · 39 lines
source-preserved 
- view: event_entries_markback_counts
  derived_table:
    sql: SELECT
  s.ticket_id as ticket_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
GROUP BY s.ticket_id
  fields:
  - dimension: ticket_id
    type: number
    primary_key: true
    hidden: true
    sql: ${TABLE}.ticket_id
  - dimension: marked_up
    type: number
    hidden: true
    sql: IFNULL(abs(${TABLE}.marked_up),0)
  - dimension: marked_back
    type: number
    hidden: true
    sql: IFNULL(${TABLE}.marked_back,0)
  - measure: total_marked_up
    type: sum
    description: Total number of event_entries marked up
    sql: ${marked_up}
  - measure: total_marked_back
    type: sum
    description: Total number of event_entries marked back
    sql: ${marked_back}

Contents

  • event_entries_markback_counts.sql
  • event_entries_markback_counts.sql
  • event_entries_markback_counts.view.lkml
  • event_entries_markback_counts.view.lookml