Active Roster Profile

view: active_roster_profile {
  sql_table_name: ActiveRosterProfile ;;

  dimension: athletic_association {
    type: int
    sql: ${TABLE}.AthleticAssociation ;;
  }

  dimension: commitment_level {
    type: int
    sql: ${TABLE}.CommitmentLevel ;;
  }

  dimension: dominant_side {
    type: yesno
    sql: ${TABLE}.DominantSide ;;
  }

  dimension: enrollment_year {
    type: int
    sql: ${TABLE}.EnrollmentYear ;;
  }

  dimension: first_name {
    sql: ${TABLE}.FirstName ;;
  }

  dimension: geo_point {
    sql: ${TABLE}.GeoPoint ;;
  }

  dimension: height {
    type: number
    sql: ${TABLE}.Height ;;
  }

  dimension: id_that_represents_this_person_in_all_databases {
    sql: ${TABLE}.<redacted> ;;
  }

  dimension: is_certified {
    type: int
    sql: ${TABLE}.IsCertified ;;
  }

  dimension: is_high_school {
    sql: ${TABLE}.IsHighSchool ;;
  }

  dimension: is_upgraded {
    type: int
    sql: ${TABLE}.IsUpgraded ;;
  }

  dimension: last_name {
    sql: ${TABLE}.LastName ;;
  }

  dimension: latest_athlete_rank_total_points {
    type: number
    sql: ${TABLE}.LatestAthleteRankTotalPoints ;;
  }

  dimension: link_code {
    sql: ${TABLE}.LinkCode ;;
  }

  dimension: organization_id {
    type: int
    sql: ${TABLE}.OrganizationId ;;
  }

  dimension: profile_id {
    type: int
    sql: ${TABLE}.ProfileId ;;
  }

  dimension: projected_competition_level {
    type: int
    sql: ${TABLE}.ProjectedCompetitionLevel ;;
  }

  dimension: sport_id {
    type: int
    sql: ${TABLE}.SportId ;;
  }

  dimension: team_city {
    sql: ${TABLE}.TeamCity ;;
  }

  dimension: team_id {
    type: int
    sql: ${TABLE}.TeamId ;;
  }

  dimension: team_state {
    sql: ${TABLE}.TeamState ;;
  }

  dimension: username {
    sql: ${TABLE}.Username ;;
  }

  dimension: weight {
    type: number
    sql: ${TABLE}.Weight ;;
  }

  dimension: is_highschool {
    type: yesno
    sql: ${TABLE}.athletic_association = 512 ;;
  }

  dimension: is_club {
    type: yesno
    sql: ${TABLE}.athletic_association = 1024 ;;
  }

  dimension: is_juco {
    type: yesno
    sql: ${TABLE}.athletic_association in (4|16|32|128|256) ;;
  }

  dimension: athletic_association_text {
    sql: CASE
  WHEN ${active_roster_profile.athletic_association} in (4,16,32,128,256)
  THEN 'Juco'
  WHEN ${active_roster_profile.athletic_association} = 512
  THEN 'Highschool'
  WHEN ${active_roster_profile.athletic_association} = 1024
  THEN 'Club'
  ELSE 'Other'
END ;;
  }

  dimension: sport {
    sql: CASE
  WHEN ${active_roster_profile.sport_id} = 1
  THEN 'Baseball'
  WHEN ${active_roster_profile.sport_id} = 16
  THEN 'Basketball'
  WHEN ${active_roster_profile.sport_id} = 256
  THEN 'Football'
  WHEN ${active_roster_profile.sport_id} = 4
  THEN 'Lacrosse'
  WHEN ${active_roster_profile.sport_id} = 64
  THEN 'Basketball'
  ELSE 'Other'
END ;;
  }

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

  measure: count_disinct {
    type: count_distinct
    sql: ${profile_id} ;;
  }
}

- view: active_roster_profile
  sql_table_name: ActiveRosterProfile
  fields:
  - dimension: athletic_association
    type: int
    sql: ${TABLE}.AthleticAssociation
  - dimension: commitment_level
    type: int
    sql: ${TABLE}.CommitmentLevel
  - dimension: dominant_side
    type: yesno
    sql: ${TABLE}.DominantSide
  - dimension: enrollment_year
    type: int
    sql: ${TABLE}.EnrollmentYear
  - dimension: first_name
    sql: ${TABLE}.FirstName
  - dimension: geo_point
    sql: ${TABLE}.GeoPoint
  - dimension: height
    type: number
    sql: ${TABLE}.Height
  - dimension: id_that_represents_this_person_in_all_databases
    sql: ${TABLE}.<redacted>
  - dimension: is_certified
    type: int
    sql: ${TABLE}.IsCertified
  - dimension: is_high_school
    sql: ${TABLE}.IsHighSchool
  - dimension: is_upgraded
    type: int
    sql: ${TABLE}.IsUpgraded
  - dimension: last_name
    sql: ${TABLE}.LastName
  - dimension: latest_athlete_rank_total_points
    type: number
    sql: ${TABLE}.LatestAthleteRankTotalPoints
  - dimension: link_code
    sql: ${TABLE}.LinkCode
  - dimension: organization_id
    type: int
    sql: ${TABLE}.OrganizationId
  - dimension: profile_id
    type: int
    sql: ${TABLE}.ProfileId
  - dimension: projected_competition_level
    type: int
    sql: ${TABLE}.ProjectedCompetitionLevel
  - dimension: sport_id
    type: int
    sql: ${TABLE}.SportId
  - dimension: team_city
    sql: ${TABLE}.TeamCity
  - dimension: team_id
    type: int
    sql: ${TABLE}.TeamId
  - dimension: team_state
    sql: ${TABLE}.TeamState
  - dimension: username
    sql: ${TABLE}.Username
  - dimension: weight
    type: number
    sql: ${TABLE}.Weight
  - dimension: is_highschool
    type: yesno
    sql: ${TABLE}.athletic_association = 512
  - dimension: is_club
    type: yesno
    sql: ${TABLE}.athletic_association = 1024
  - dimension: is_juco
    type: yesno
    sql: ${TABLE}.athletic_association in (4|16|32|128|256)
  - dimension: athletic_association_text
    sql: CASE
  WHEN ${active_roster_profile.athletic_association} in (4,16,32,128,256)
  THEN 'Juco'
  WHEN ${active_roster_profile.athletic_association} = 512
  THEN 'Highschool'
  WHEN ${active_roster_profile.athletic_association} = 1024
  THEN 'Club'
  ELSE 'Other'
END
  - dimension: sport
    sql: CASE
  WHEN ${active_roster_profile.sport_id} = 1
  THEN 'Baseball'
  WHEN ${active_roster_profile.sport_id} = 16
  THEN 'Basketball'
  WHEN ${active_roster_profile.sport_id} = 256
  THEN 'Football'
  WHEN ${active_roster_profile.sport_id} = 4
  THEN 'Lacrosse'
  WHEN ${active_roster_profile.sport_id} = 64
  THEN 'Basketball'
  ELSE 'Other'
END
  - measure: count
    type: count
    drill_fields:
    - detail*
  - measure: count_disinct
    type: count_distinct
    sql: ${profile_id}