data.ml
SQL Subscription fitness

Reservation Facts

Reservation Facts model for subscription fitness.

source-preserved

Blocks

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

select
  pr.venue_id
, pr.schedule_id
, pr.class_id
, pr.start_date
, pr.start_time
, count(pr.id) AS classes_attended
, count(1) as count
from user_reservations pr
where pr.cancellation_type is null
  and (pr.status <> 'failed' or pr.status is null)
  and pr.type = 'company'
group by
  1,
  2,
  3,
  4,
  <% parameter placeholder_permission_id %>

# models/schema.yml
version: 2

models:
  - name: reservation_facts
    columns:
      - name: venue_id
      - name: class_id
      - name: schedule_id
      - name: start_date
      - name: classes_attended
      - name: count

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.
reservation_facts.sql · 18 lines
source-preserved validation: not checked 
select
  pr.venue_id
, pr.schedule_id
, pr.class_id
, pr.start_date
, pr.start_time
, count(pr.id) AS classes_attended
, count(1) as count
from user_reservations pr
where pr.cancellation_type is null
  and (pr.status <> 'failed' or pr.status is null)
  and pr.type = 'company'
group by
  1,
  2,
  3,
  4,
  <% parameter placeholder_permission_id %>
reservation_facts.view.lkml · 69 lines
inferred 
view: reservation_facts {
  derived_table: {
    sql: select
  pr.venue_id
, pr.schedule_id
, pr.class_id
, pr.start_date
, pr.start_time
, count(pr.id) AS classes_attended
, count(1) as count
from user_reservations pr
where pr.cancellation_type is null
  and (pr.status <> 'failed' or pr.status is null)
  and pr.type = 'company'
group by
  1,
  2,
  3,
  4,
  <% parameter placeholder_permission_id %> ;;
  }

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

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

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

  dimension: start_date {
    type: time
    hidden: yes
    sql: ${TABLE}.start_date ;;
  }

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

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

  dimension_group: start_time {
    type: time
    timeframes: [time_of_day, hour_of_day]
    sql: ${TABLE}.start_time ;;
  }

  measure: latest_classtime {
    hidden: yes
    sql: max(${start_time_time_of_day}) ;;
  }
}
reservation_facts.view.lookml · 53 lines
source-preserved 
- view: reservation_facts
  derived_table:
    sql: select
  pr.venue_id
, pr.schedule_id
, pr.class_id
, pr.start_date
, pr.start_time
, count(pr.id) AS classes_attended
, count(1) as count
from user_reservations pr
where pr.cancellation_type is null
  and (pr.status <> 'failed' or pr.status is null)
  and pr.type = 'company'
group by
  1,
  2,
  3,
  4,
  <% parameter placeholder_permission_id %>
  fields:
  - dimension: venue_id
    type: number
    hidden: true
    sql: ${TABLE}.venue_id
  - dimension: class_id
    type: number
    hidden: true
    sql: ${TABLE}.class_id
  - dimension: schedule_id
    type: number
    hidden: true
    sql: ${TABLE}.schedule_id
  - dimension: start_date
    type: time
    hidden: true
    sql: ${TABLE}.start_date
  - dimension: classes_attended
    type: number
    hidden: true
    sql: ${TABLE}.classes_attended
  - dimension: count
    type: number
    hidden: true
    sql: ${TABLE}.count
  - dimension_group: start_time
    type: time
    timeframes:
    - time_of_day
    - hour_of_day
    sql: ${TABLE}.start_time
  - measure: latest_classtime
    sql: max(${start_time_time_of_day})

Contents

  • reservation_facts.sql
  • reservation_facts.sql
  • reservation_facts.view.lkml
  • reservation_facts.view.lookml