dbt Mobile ticketing

Event Game Stat Facts

Event Game Stat Facts model for mobile ticketing.

inferred

Blocks

dbt SQL LKML LookML

event_game_stat_facts.sql · 43 lines

inferred

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

SELECT
  gs.game_id AS game_id
  , COUNT(DISTINCT gs.fan_id) AS total_fans
  , SUM(CASE WHEN gs.action = 0 THEN 1 ELSE 0 END) AS pre_registers
  , SUM(CASE WHEN gs.action = 1 THEN 1 ELSE 0 END) AS request_tickets
  , SUM(CASE WHEN gs.action = 2 THEN 1 ELSE 0 END) AS purchases
  , SUM(CASE WHEN gs.action = 3 THEN 1 ELSE 0 END) AS bails
  , SUM(CASE WHEN gs.action = 4 THEN 1 ELSE 0 END) AS category_requests
  , SUM(CASE WHEN gs.action = 5 THEN 1 ELSE 0 END) AS pre_reg_notifications
  , SUM(CASE WHEN gs.action = 6 THEN 1 ELSE 0 END) AS request_bumps
  , SUM(CASE WHEN gs.action = 7 THEN 1 ELSE 0 END) AS barcode_submissions
  , SUM(CASE WHEN gs.action = 101 THEN 1 ELSE 0 END) AS memories_requests
  , SUM(CASE WHEN gs.action = 102 THEN 1 ELSE 0 END) AS memories_purchases
  , SUM(CASE WHEN gs.action = 104 THEN 1 ELSE 0 END) AS memories_category_requests
FROM game_stat AS gs
GROUP BY game_id

# models/schema.yml
version: 2

models:
  - name: event_game_stat_facts
    columns:
      - name: game_id
        tests:
          - unique
          - not_null
      - name: registrations
      - name: total_fans
      - name: pre_registers
      - name: request_tickets
      - name: purchases
      - name: bails
      - name: category_requests
      - name: pre_reg_notifications
      - name: request_bumps
      - name: barcode_submissions
      - name: memories_requests
      - name: memories_purchases
      - name: memories_category_requests

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_game_stat_facts.sql · 16 lines

source-preserved validation: parses

SELECT
  gs.game_id AS game_id
  , COUNT(DISTINCT gs.fan_id) AS total_fans
  , SUM(CASE WHEN gs.action = 0 THEN 1 ELSE 0 END) AS pre_registers
  , SUM(CASE WHEN gs.action = 1 THEN 1 ELSE 0 END) AS request_tickets
  , SUM(CASE WHEN gs.action = 2 THEN 1 ELSE 0 END) AS purchases
  , SUM(CASE WHEN gs.action = 3 THEN 1 ELSE 0 END) AS bails
  , SUM(CASE WHEN gs.action = 4 THEN 1 ELSE 0 END) AS category_requests
  , SUM(CASE WHEN gs.action = 5 THEN 1 ELSE 0 END) AS pre_reg_notifications
  , SUM(CASE WHEN gs.action = 6 THEN 1 ELSE 0 END) AS request_bumps
  , SUM(CASE WHEN gs.action = 7 THEN 1 ELSE 0 END) AS barcode_submissions
  , SUM(CASE WHEN gs.action = 101 THEN 1 ELSE 0 END) AS memories_requests
  , SUM(CASE WHEN gs.action = 102 THEN 1 ELSE 0 END) AS memories_purchases
  , SUM(CASE WHEN gs.action = 104 THEN 1 ELSE 0 END) AS memories_category_requests
FROM game_stat AS gs
GROUP BY game_id

event_game_stat_facts.view.lkml · 144 lines

inferred

view: event_game_stat_facts {
  derived_table: {
    sql: SELECT
  gs.game_id AS game_id
  , COUNT(DISTINCT gs.fan_id) AS total_fans
  , SUM(CASE WHEN gs.action = 0 THEN 1 ELSE 0 END) AS pre_registers
  , SUM(CASE WHEN gs.action = 1 THEN 1 ELSE 0 END) AS request_tickets
  , SUM(CASE WHEN gs.action = 2 THEN 1 ELSE 0 END) AS purchases
  , SUM(CASE WHEN gs.action = 3 THEN 1 ELSE 0 END) AS bails
  , SUM(CASE WHEN gs.action = 4 THEN 1 ELSE 0 END) AS category_requests
  , SUM(CASE WHEN gs.action = 5 THEN 1 ELSE 0 END) AS pre_reg_notifications
  , SUM(CASE WHEN gs.action = 6 THEN 1 ELSE 0 END) AS request_bumps
  , SUM(CASE WHEN gs.action = 7 THEN 1 ELSE 0 END) AS barcode_submissions
  , SUM(CASE WHEN gs.action = 101 THEN 1 ELSE 0 END) AS memories_requests
  , SUM(CASE WHEN gs.action = 102 THEN 1 ELSE 0 END) AS memories_purchases
  , SUM(CASE WHEN gs.action = 104 THEN 1 ELSE 0 END) AS memories_category_requests
FROM game_stat AS gs
GROUP BY game_id ;;
  }

  dimension: game_id {
    type: int
    primary_key: yes
  }

  dimension: registrations {
    type: number
    sql: ${request_tickets} + ${category_requests} ;;
  }

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

  dimension: pre_registers {
    type: number
  }

  dimension: request_tickets {
    type: number
  }

  dimension: purchases {
    type: number
  }

  dimension: bails {
    type: number
  }

  dimension: category_requests {
    type: number
  }

  dimension: pre_reg_notifications {
    type: number
  }

  dimension: request_bumps {
    type: number
  }

  dimension: barcode_submissions {
    type: number
  }

  dimension: memories_requests {
    type: number
  }

  dimension: memories_purchases {
    type: number
  }

  dimension: memories_category_requests {
    type: number
  }

  measure: total_registrations {
    type: sum
    sql: ${registrations} ;;
  }

  measure: total_pre_registers {
    type: sum
    sql: ${pre_registers} ;;
  }

  measure: percent_pre_to_registered {
    type: number
    sql: ${total_registrations}/NULLIF(${total_pre_registers},0) ;;
  }

  measure: total_request_tickets {
    type: sum
    sql: ${request_tickets} ;;
  }

  measure: total_purchases {
    type: sum
    sql: ${purchases} ;;
  }

  measure: total_bails {
    type: sum
    sql: ${bails} ;;
  }

  measure: total_category_requests {
    type: sum
    sql: ${category_requests} ;;
  }

  measure: total_pre_reg_notifications {
    type: sum
    sql: ${pre_reg_notifications} ;;
  }

  measure: total_request_bumps {
    type: sum
    sql: ${request_bumps} ;;
  }

  measure: total_barcode_submissions {
    type: sum
    sql: ${barcode_submissions} ;;
  }

  measure: total_memories_requests {
    type: sum
    sql: ${memories_requests} ;;
  }

  measure: total_memories_purchases {
    type: sum
    sql: ${memories_purchases} ;;
  }

  measure: total_memories_category_requests {
    type: sum
    sql: ${memories_category_requests} ;;
  }
}

event_game_stat_facts.view.lookml · 89 lines

source-preserved

- view: event_game_stat_facts
  derived_table:
    sql: SELECT
  gs.game_id AS game_id
  , COUNT(DISTINCT gs.fan_id) AS total_fans
  , SUM(CASE WHEN gs.action = 0 THEN 1 ELSE 0 END) AS pre_registers
  , SUM(CASE WHEN gs.action = 1 THEN 1 ELSE 0 END) AS request_tickets
  , SUM(CASE WHEN gs.action = 2 THEN 1 ELSE 0 END) AS purchases
  , SUM(CASE WHEN gs.action = 3 THEN 1 ELSE 0 END) AS bails
  , SUM(CASE WHEN gs.action = 4 THEN 1 ELSE 0 END) AS category_requests
  , SUM(CASE WHEN gs.action = 5 THEN 1 ELSE 0 END) AS pre_reg_notifications
  , SUM(CASE WHEN gs.action = 6 THEN 1 ELSE 0 END) AS request_bumps
  , SUM(CASE WHEN gs.action = 7 THEN 1 ELSE 0 END) AS barcode_submissions
  , SUM(CASE WHEN gs.action = 101 THEN 1 ELSE 0 END) AS memories_requests
  , SUM(CASE WHEN gs.action = 102 THEN 1 ELSE 0 END) AS memories_purchases
  , SUM(CASE WHEN gs.action = 104 THEN 1 ELSE 0 END) AS memories_category_requests
FROM game_stat AS gs
GROUP BY game_id
  fields:
  - dimension: game_id
    type: int
    primary_key: true
  - dimension: registrations
    type: number
    sql: ${request_tickets} + ${category_requests}
  - dimension: total_fans
    type: number
    sql: ${TABLE}.total_fans
  - dimension: pre_registers
    type: number
  - dimension: request_tickets
    type: number
  - dimension: purchases
    type: number
  - dimension: bails
    type: number
  - dimension: category_requests
    type: number
  - dimension: pre_reg_notifications
    type: number
  - dimension: request_bumps
    type: number
  - dimension: barcode_submissions
    type: number
  - dimension: memories_requests
    type: number
  - dimension: memories_purchases
    type: number
  - dimension: memories_category_requests
    type: number
  - measure: total_registrations
    type: sum
    sql: ${registrations}
  - measure: total_pre_registers
    type: sum
    sql: ${pre_registers}
  - measure: percent_pre_to_registered
    type: number
    sql: ${total_registrations}/NULLIF(${total_pre_registers},0)
  - measure: total_request_tickets
    type: sum
    sql: ${request_tickets}
  - measure: total_purchases
    type: sum
    sql: ${purchases}
  - measure: total_bails
    type: sum
    sql: ${bails}
  - measure: total_category_requests
    type: sum
    sql: ${category_requests}
  - measure: total_pre_reg_notifications
    type: sum
    sql: ${pre_reg_notifications}
  - measure: total_request_bumps
    type: sum
    sql: ${request_bumps}
  - measure: total_barcode_submissions
    type: sum
    sql: ${barcode_submissions}
  - measure: total_memories_requests
    type: sum
    sql: ${memories_requests}
  - measure: total_memories_purchases
    type: sum
    sql: ${memories_purchases}
  - measure: total_memories_category_requests
    type: sum
    sql: ${memories_category_requests}

event_game_stat_facts.sql
event_game_stat_facts.sql
event_game_stat_facts.view.lkml
event_game_stat_facts.view.lookml

Event Game Stat Facts

Blocks

Assumptions

Contents