data.ml
SQL Subscription fitness

Spot Management

Spot Management model for subscription fitness.

source-preserved

Blocks

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

SELECT
    cc.venue_id,
    cv.venue_name,
    cv.venue_subtitle,
    cv.partner_id,
    MAX(DATE(uml.created_on)) as week_ending_on,
    COUNT( DISTINCT uml.user_id) as venue_users_making_changes,
    COUNT( DISTINCT cc.venue_id) as venues_making_changes,
    COUNT(uml.id) as number_of_updates,
    SUM(CASE WHEN uml.spots > uml.previous_spots THEN 1 ELSE 0 END) as spot_change,
    COUNT(*) as count,
    SUM(uml.spots) as spots,
    SUM(uml.previous_spots) as previous_spots,
    SUM(uml.spots_this_week) as spots_this_week,
    SUM(uml.previous_spots_this_week) as previous_spots_this_week,
    SUM(uml.spots_next_month) as spots_next_month,
    SUM(uml.previous_spots_month) as previous_spots_month,
    SUM(CASE WHEN uml.spots > uml.previous_spots THEN 1 ELSE 0 END) / COUNT(*) as percent_that_increased,
    SUM( uml.spots - uml.previous_spots ) as net_instance,
    SUM( uml.spots_this_week - uml.previous_spots_this_week ) as net_week,
    SUM( uml.spots_next_month - uml.previous_spots_month ) as net_month,
    COUNT(DISTINCT cc.class_id) as classes_affected,
    COUNT(DISTINCT cct.id) as schedules_affected,
    SUM(CASE WHEN uml.disabled_status = 'instance_enabled' THEN 1 ELSE 0 END) as number_of_instances_enabled,
    SUM(CASE WHEN uml.update_type = 'instance' THEN 1 ELSE 0 END) as number_of_instances_updated,
    SUM(CASE WHEN uml.update_type = 'all_future' THEN 1 ELSE 0 END) as number_of_all_future_classes_updated
FROM user_managespots_log uml
    JOIN activity_classes_timetable cct ON cct.id = uml.schedule_id
    JOIN activity_classes cc ON cc.class_id = cct.class_id
    JOIN activity_venues cv ON cv.venue_id = cc.venue_id
WHERE
cc.venue_id IN
(
    SELECT DISTINCT vpx.venue_id
    FROM vp_user vpu
    JOIN vp_user_venue_xref vpx ON vpx.`venue_user_id` = vpu.user_id
    JOIN user_activityuser u ON u.id = vpu.user_id
    WHERE vpu.is_managespots = 1
)
AND cc.venue_id NOT IN (<% parameter venue_id_placeholder %>)
AND uml.previous_spots IS NOT NULL
GROUP BY WEEK(uml.created_on), cc.venue_id

# models/schema.yml
version: 2

models:
  - name: spot_management
    columns:
      - name: venue_id
      - name: venue_name
      - name: venue_subtitle
      - name: partner_id
      - name: week_ending_on
      - name: venue_users_making_changes
      - name: venues_making_changes
      - name: number_of_updates
      - name: spot_change
      - name: count
      - name: percent_that_increased
      - name: net_instance
      - name: net_week
      - name: net_month
      - name: classes_affected
      - name: schedules_affected
      - name: number_of_instances_enabled
      - name: number_of_instances_updated
      - name: number_of_all_future_classes_updated

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.
spot_management.sql · 42 lines
source-preserved validation: not checked 
SELECT
    cc.venue_id,
    cv.venue_name,
    cv.venue_subtitle,
    cv.partner_id,
    MAX(DATE(uml.created_on)) as week_ending_on,
    COUNT( DISTINCT uml.user_id) as venue_users_making_changes,
    COUNT( DISTINCT cc.venue_id) as venues_making_changes,
    COUNT(uml.id) as number_of_updates,
    SUM(CASE WHEN uml.spots > uml.previous_spots THEN 1 ELSE 0 END) as spot_change,
    COUNT(*) as count,
    SUM(uml.spots) as spots,
    SUM(uml.previous_spots) as previous_spots,
    SUM(uml.spots_this_week) as spots_this_week,
    SUM(uml.previous_spots_this_week) as previous_spots_this_week,
    SUM(uml.spots_next_month) as spots_next_month,
    SUM(uml.previous_spots_month) as previous_spots_month,
    SUM(CASE WHEN uml.spots > uml.previous_spots THEN 1 ELSE 0 END) / COUNT(*) as percent_that_increased,
    SUM( uml.spots - uml.previous_spots ) as net_instance,
    SUM( uml.spots_this_week - uml.previous_spots_this_week ) as net_week,
    SUM( uml.spots_next_month - uml.previous_spots_month ) as net_month,
    COUNT(DISTINCT cc.class_id) as classes_affected,
    COUNT(DISTINCT cct.id) as schedules_affected,
    SUM(CASE WHEN uml.disabled_status = 'instance_enabled' THEN 1 ELSE 0 END) as number_of_instances_enabled,
    SUM(CASE WHEN uml.update_type = 'instance' THEN 1 ELSE 0 END) as number_of_instances_updated,
    SUM(CASE WHEN uml.update_type = 'all_future' THEN 1 ELSE 0 END) as number_of_all_future_classes_updated
FROM user_managespots_log uml
    JOIN activity_classes_timetable cct ON cct.id = uml.schedule_id
    JOIN activity_classes cc ON cc.class_id = cct.class_id
    JOIN activity_venues cv ON cv.venue_id = cc.venue_id
WHERE
cc.venue_id IN
(
    SELECT DISTINCT vpx.venue_id
    FROM vp_user vpu
    JOIN vp_user_venue_xref vpx ON vpx.`venue_user_id` = vpu.user_id
    JOIN user_activityuser u ON u.id = vpu.user_id
    WHERE vpu.is_managespots = 1
)
AND cc.venue_id NOT IN (<% parameter venue_id_placeholder %>)
AND uml.previous_spots IS NOT NULL
GROUP BY WEEK(uml.created_on), cc.venue_id
spot_management.view.lkml · 234 lines
inferred 
view: spot_management {
  derived_table: {
    sql: SELECT
    cc.venue_id,
    cv.venue_name,
    cv.venue_subtitle,
    cv.partner_id,
    MAX(DATE(uml.created_on)) as week_ending_on,
    COUNT( DISTINCT uml.user_id) as venue_users_making_changes,
    COUNT( DISTINCT cc.venue_id) as venues_making_changes,
    COUNT(uml.id) as number_of_updates,
    SUM(CASE WHEN uml.spots > uml.previous_spots THEN 1 ELSE 0 END) as spot_change,
    COUNT(*) as count,
    SUM(uml.spots) as spots,
    SUM(uml.previous_spots) as previous_spots,
    SUM(uml.spots_this_week) as spots_this_week,
    SUM(uml.previous_spots_this_week) as previous_spots_this_week,
    SUM(uml.spots_next_month) as spots_next_month,
    SUM(uml.previous_spots_month) as previous_spots_month,
    SUM(CASE WHEN uml.spots > uml.previous_spots THEN 1 ELSE 0 END) / COUNT(*) as percent_that_increased,
    SUM( uml.spots - uml.previous_spots ) as net_instance,
    SUM( uml.spots_this_week - uml.previous_spots_this_week ) as net_week,
    SUM( uml.spots_next_month - uml.previous_spots_month ) as net_month,
    COUNT(DISTINCT cc.class_id) as classes_affected,
    COUNT(DISTINCT cct.id) as schedules_affected,
    SUM(CASE WHEN uml.disabled_status = 'instance_enabled' THEN 1 ELSE 0 END) as number_of_instances_enabled,
    SUM(CASE WHEN uml.update_type = 'instance' THEN 1 ELSE 0 END) as number_of_instances_updated,
    SUM(CASE WHEN uml.update_type = 'all_future' THEN 1 ELSE 0 END) as number_of_all_future_classes_updated
FROM user_managespots_log uml
    JOIN activity_classes_timetable cct ON cct.id = uml.schedule_id
    JOIN activity_classes cc ON cc.class_id = cct.class_id
    JOIN activity_venues cv ON cv.venue_id = cc.venue_id
WHERE
cc.venue_id IN
(
    SELECT DISTINCT vpx.venue_id
    FROM vp_user vpu
    JOIN vp_user_venue_xref vpx ON vpx.`venue_user_id` = vpu.user_id
    JOIN user_activityuser u ON u.id = vpu.user_id
    WHERE vpu.is_managespots = 1
)
AND cc.venue_id NOT IN (<% parameter venue_id_placeholder %>)
AND uml.previous_spots IS NOT NULL
GROUP BY WEEK(uml.created_on), cc.venue_id ;;
  }

  dimension: venue_id {
    sql: ${TABLE}.venue_id ;;
  }

  dimension: venue_name {
    sql: ${TABLE}.venue_name ;;
  }

  dimension: venue_subtitle {
    sql: ${TABLE}.venue_subtitle ;;
  }

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

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

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

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

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

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

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

  dimension: percent_that_increased {
    type: number
    value_format: "0.0%"
    sql: ${TABLE}.percent_that_increased ;;
  }

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

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

  dimension: net_month {
    sql: ${TABLE}.net_month ;;
  }

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

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

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

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

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

  measure: total_venue_users_making_changes {
    type: sum
    sql: ${TABLE}.venue_users_making_changes ;;
  }

  measure: total_venues_making_changes {
    type: sum
    sql: ${TABLE}.venues_making_changes ;;
  }

  measure: total_number_of_updates {
    type: sum
    sql: ${TABLE}.number_of_updates ;;
  }

  measure: total_spot_change {
    type: sum
    sql: ${TABLE}.spot_change ;;
  }

  measure: total_count {
    type: sum
    sql: ${TABLE}.count ;;
  }

  measure: spots {
    type: sum
    sql: ${TABLE}.spots ;;
  }

  measure: previous_spots {
    type: sum
    sql: ${TABLE}.previous_spots ;;
  }

  measure: spots_this_week {
    type: sum
    sql: ${TABLE}.spots_this_week ;;
  }

  measure: previous_spots_this_week {
    type: sum
    sql: ${TABLE}.previous_spots_this_week ;;
  }

  measure: spots_next_month {
    type: sum
    sql: ${TABLE}.spots_next_month ;;
  }

  measure: previous_spots_month {
    type: sum
    sql: ${TABLE}.previous_spots_month ;;
  }

  measure: total_percent_that_increased {
    type: number
    value_format: "0.0%"
    sql: ${spot_management.total_spot_change} / ${spot_management.total_count} ;;
  }

  measure: total_net_instance {
    type: sum
    sql: ${TABLE}.net_instance ;;
  }

  measure: total_net_week {
    type: sum
    sql: ${TABLE}.net_week ;;
  }

  measure: total_classes_affected {
    type: sum
    sql: ${TABLE}.classes_affected ;;
  }

  measure: total_schedules_affected {
    type: sum
    sql: ${TABLE}.schedules_affected ;;
  }

  measure: total_number_of_instances_enabled {
    type: sum
    sql: ${TABLE}.number_of_instances_enabled ;;
  }

  measure: total_number_of_instances_updated {
    type: sum
    sql: ${TABLE}.number_of_instances_updated ;;
  }

  measure: total_number_of_all_future_classes_updated {
    type: sum
    sql: ${TABLE}.number_of_all_future_classes_updated ;;
  }
}
spot_management.view.lookml · 157 lines
source-preserved 
- view: spot_management
  derived_table:
    sql: SELECT
    cc.venue_id,
    cv.venue_name,
    cv.venue_subtitle,
    cv.partner_id,
    MAX(DATE(uml.created_on)) as week_ending_on,
    COUNT( DISTINCT uml.user_id) as venue_users_making_changes,
    COUNT( DISTINCT cc.venue_id) as venues_making_changes,
    COUNT(uml.id) as number_of_updates,
    SUM(CASE WHEN uml.spots > uml.previous_spots THEN 1 ELSE 0 END) as spot_change,
    COUNT(*) as count,
    SUM(uml.spots) as spots,
    SUM(uml.previous_spots) as previous_spots,
    SUM(uml.spots_this_week) as spots_this_week,
    SUM(uml.previous_spots_this_week) as previous_spots_this_week,
    SUM(uml.spots_next_month) as spots_next_month,
    SUM(uml.previous_spots_month) as previous_spots_month,
    SUM(CASE WHEN uml.spots > uml.previous_spots THEN 1 ELSE 0 END) / COUNT(*) as percent_that_increased,
    SUM( uml.spots - uml.previous_spots ) as net_instance,
    SUM( uml.spots_this_week - uml.previous_spots_this_week ) as net_week,
    SUM( uml.spots_next_month - uml.previous_spots_month ) as net_month,
    COUNT(DISTINCT cc.class_id) as classes_affected,
    COUNT(DISTINCT cct.id) as schedules_affected,
    SUM(CASE WHEN uml.disabled_status = 'instance_enabled' THEN 1 ELSE 0 END) as number_of_instances_enabled,
    SUM(CASE WHEN uml.update_type = 'instance' THEN 1 ELSE 0 END) as number_of_instances_updated,
    SUM(CASE WHEN uml.update_type = 'all_future' THEN 1 ELSE 0 END) as number_of_all_future_classes_updated
FROM user_managespots_log uml
    JOIN activity_classes_timetable cct ON cct.id = uml.schedule_id
    JOIN activity_classes cc ON cc.class_id = cct.class_id
    JOIN activity_venues cv ON cv.venue_id = cc.venue_id
WHERE
cc.venue_id IN
(
    SELECT DISTINCT vpx.venue_id
    FROM vp_user vpu
    JOIN vp_user_venue_xref vpx ON vpx.`venue_user_id` = vpu.user_id
    JOIN user_activityuser u ON u.id = vpu.user_id
    WHERE vpu.is_managespots = 1
)
AND cc.venue_id NOT IN (<% parameter venue_id_placeholder %>)
AND uml.previous_spots IS NOT NULL
GROUP BY WEEK(uml.created_on), cc.venue_id
  fields:
  - dimension: venue_id
    sql: ${TABLE}.venue_id
  - dimension: venue_name
    sql: ${TABLE}.venue_name
  - dimension: venue_subtitle
    sql: ${TABLE}.venue_subtitle
  - dimension: partner_id
    type: number
    sql: ${TABLE}.partner_id
  - dimension: week_ending_on
    type: time
    sql: ${TABLE}.week_ending_on
  - dimension: venue_users_making_changes
    type: number
    sql: ${TABLE}.venue_users_making_changes
  - dimension: venues_making_changes
    type: number
    sql: ${TABLE}.venues_making_changes
  - dimension: number_of_updates
    type: number
    sql: ${TABLE}.number_of_updates
  - dimension: spot_change
    type: number
    sql: ${TABLE}.spot_change
  - dimension: count
    type: number
    sql: ${TABLE}.count
  - dimension: percent_that_increased
    type: number
    value_format: '0.0%'
    sql: ${TABLE}.percent_that_increased
  - dimension: net_instance
    type: number
    sql: ${TABLE}.net_instance
  - dimension: net_week
    type: number
    sql: ${TABLE}.net_week
  - dimension: net_month
    sql: ${TABLE}.net_month
  - dimension: classes_affected
    type: number
    sql: ${TABLE}.classes_affected
  - dimension: schedules_affected
    type: number
    sql: ${TABLE}.schedules_affected
  - dimension: number_of_instances_enabled
    type: number
    sql: ${TABLE}.number_of_instances_enabled
  - dimension: number_of_instances_updated
    type: number
    sql: ${TABLE}.number_of_instances_updated
  - dimension: number_of_all_future_classes_updated
    type: number
    sql: ${TABLE}.number_of_all_future_classes_updated
  - measure: total_venue_users_making_changes
    type: sum
    sql: ${TABLE}.venue_users_making_changes
  - measure: total_venues_making_changes
    type: sum
    sql: ${TABLE}.venues_making_changes
  - measure: total_number_of_updates
    type: sum
    sql: ${TABLE}.number_of_updates
  - measure: total_spot_change
    type: sum
    sql: ${TABLE}.spot_change
  - measure: total_count
    type: sum
    sql: ${TABLE}.count
  - measure: spots
    type: sum
    sql: ${TABLE}.spots
  - measure: previous_spots
    type: sum
    sql: ${TABLE}.previous_spots
  - measure: spots_this_week
    type: sum
    sql: ${TABLE}.spots_this_week
  - measure: previous_spots_this_week
    type: sum
    sql: ${TABLE}.previous_spots_this_week
  - measure: spots_next_month
    type: sum
    sql: ${TABLE}.spots_next_month
  - measure: previous_spots_month
    type: sum
    sql: ${TABLE}.previous_spots_month
  - measure: total_percent_that_increased
    type: number
    value_format: '0.0%'
    sql: ${spot_management.total_spot_change} / ${spot_management.total_count}
  - measure: total_net_instance
    type: sum
    sql: ${TABLE}.net_instance
  - measure: total_net_week
    type: sum
    sql: ${TABLE}.net_week
  - measure: total_classes_affected
    type: sum
    sql: ${TABLE}.classes_affected
  - measure: total_schedules_affected
    type: sum
    sql: ${TABLE}.schedules_affected
  - measure: total_number_of_instances_enabled
    type: sum
    sql: ${TABLE}.number_of_instances_enabled
  - measure: total_number_of_instances_updated
    type: sum
    sql: ${TABLE}.number_of_instances_updated
  - measure: total_number_of_all_future_classes_updated
    type: sum
    sql: ${TABLE}.number_of_all_future_classes_updated

Contents

  • spot_management.sql
  • spot_management.sql
  • spot_management.view.lkml
  • spot_management.view.lookml