Upcache

view: upcache {
  sql_table_name: public_upcache ;;

  dimension: user_id {
    type: int
    primary_key: yes
    sql: ${TABLE}.user_id ;;
  }

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

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

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

  dimension: days_since_joined {
    type: number
    sql: DATEDIFF(CURDATE(), ${account_creation_date}) ;;
  }

  dimension: acquired_on_weekend {
    type: yesno
    sql: ${TABLE}.acquired_on_weekend = 1 ;;
  }

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

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

  dimension: acquisition_secondary {
    type: int
    sql: ${TABLE}.acquisition_secondary ;;
  }

  dimension: birthday {
    hidden: yes
    sql: ${TABLE}.birthday ;;
  }

  dimension: age {
    type: int
    sql: FLOOR(DATEDIFF(CURRENT_DATE, STR_TO_DATE(${birthday}, '%d/%m/%Y'))/365.25) ;;
  }

  dimension: age_tier {
    type: tier
    sql: ${age} ;;
  }

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

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

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

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

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

  dimension: is_paying_user {
    type: yesno
    sql: ${TABLE}.is_paying_user = 1 ;;
  }

  dimension: is_whale {
    type: yesno
    sql: ${TABLE}.is_whale = 1 ;;
  }

  dimension: last_login {
    type: time
    sql: FROM_UNIXTIME(${TABLE}.last_login_time) ;;
  }

  dimension: last_purchase {
    type: time
    sql: FROM_UNIXTIME(${TABLE}.last_purchase_time) ;;
  }

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

  dimension: logged_in_times {
    type: int
    sql: ${TABLE}.logged_in_times ;;
  }

  dimension: paid_within_24hrs {
    type: yesno
    sql: ${TABLE}.paid_within_24hrs = 1 ;;
  }

  dimension: time_in_game {
    type: int
    sql: ${TABLE}.time_in_game ;;
  }

  dimension: days_between_signup_and_purchase {
    type: number
    sql: DATEDIFF(${first_purchase_date}, ${account_creation_date}) ;;
  }

  dimension: days_between_signup_and_purchase_tiered {
    type: tier
    sql: ${days_between_signup_and_purchase} ;;
  }

  dimension: timezone {
    type: int
    sql: ${TABLE}.timezone ;;
  }

  dimension: completed_quests {
    type: int
    sql: ${TABLE}.completed_quests ;;
  }

  dimension: completed_tutorial {
    type: yesno
    sql: ${TABLE}.completed_tutorial = 1 ;;
  }

  dimension: fb_credit_balance {
    type: int
    sql: ${TABLE}.fb_credit_balance ;;
  }

  dimension: fb_gamer_status {
    type: int
    sql: ${TABLE}.fb_gamer_status ;;
  }

  dimension: fb_notifications_sent {
    type: int
    sql: ${TABLE}.fb_notifications_sent ;;
  }

  dimension: friends_at_least_10 {
    type: int
    sql: ${TABLE}.friends_at_least_10 ;;
  }

  dimension: friends_in_game {
    type: int
    sql: ${TABLE}.friends_in_game ;;
  }

  dimension: gamebucks_balance {
    type: int
    sql: ${TABLE}.gamebucks_balance ;;
  }

  dimension: gamebucks_cur_balance {
    type: int
    sql: ${TABLE}.gamebucks_cur_balance ;;
  }

  dimension: gamebucks_refunded {
    type: int
    sql: ${TABLE}.gamebucks_refunded ;;
  }

  dimension: gamebucks_spent {
    type: int
    sql: ${TABLE}.gamebucks_spent ;;
  }

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

  dimension: initial_friends_in_game {
    type: int
    sql: ${TABLE}.initial_friends_in_game ;;
  }

  dimension: items_looted {
    type: int
    sql: ${TABLE}.items_looted ;;
  }

  dimension: items_purchased {
    type: int
    sql: ${TABLE}.items_purchased ;;
  }

  dimension: last_fb_notification_time {
    type: int
    sql: ${TABLE}.last_fb_notification_time ;;
  }

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

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

  dimension: num_purchases {
    type: int
    sql: ${TABLE}.num_purchases ;;
  }

  dimension: payer_promo_gamebucks_earned {
    type: int
    sql: ${TABLE}.payer_promo_gamebucks_earned ;;
  }

  dimension: payer_promo_offered {
    type: int
    sql: ${TABLE}.payer_promo_offered ;;
  }

  dimension: player_level {
    type: int
    sql: ${TABLE}.player_level ;;
  }

  dimension: quests_completed {
    type: int
    sql: ${TABLE}.quests_completed ;;
  }

  dimension: speedups_purchased {
    type: int
    sql: ${TABLE}.speedups_purchased ;;
  }

  dimension: tech_unlocks_purchased {
    type: int
    sql: ${TABLE}.tech_unlocks_purchased ;;
  }

  dimension: techs_purchased {
    type: int
    sql: ${TABLE}.techs_purchased ;;
  }

  dimension_group: account_creation {
    type: time
    timeframes: [time, date, hour, dow, hod, week, month]
    sql: FROM_UNIXTIME(${TABLE}.account_creation_time) ;;
  }

  dimension_group: birth {
    type: time
    timeframes: [date, month, year]
    sql: STR_TO_DATE(${birthday}, '%d/%m/%Y') ;;
  }

  dimension_group: first_purchase {
    type: time
    timeframes: [time, date]
    sql: FROM_UNIXTIME(${TABLE}.time_of_first_purchase) ;;
  }

  dimension_group: upcache_time {
    type: time
    timeframes: [time, date, week, month]
    sql: FROM_UNIXTIME(${TABLE}.upcache_time) ;;
  }

  measure: count {
    type: count
  }

  measure: count_percent_of_total {
    type: percent_of_total
    sql: ${count} ;;
  }

  measure: count_pct_of_total {
    label: "Count (Percent of Total)"
    type: percent_of_total
    sql: ${count} ;;
  }

  measure: total_items_purchased {
    type: sum
    sql: ${items_purchased} ;;
  }

  measure: average_items_purchased {
    type: avg
    sql: ${items_purchased} ;;
  }

  measure: average_age {
    type: avg
    sql: ${age} ;;
  }

  measure: total_time_in_game {
    type: sum
    sql: ${time_in_game} ;;
  }

  measure: average_time_in_game {
    type: average
    sql: ${time_in_game} ;;
  }

  measure: average_days_btwn_signup_and_purchase {
    type: average
    sql: ${days_between_signup_and_purchase} ;;
  }
}

- view: upcache
  sql_table_name: public_upcache
  fields:
  - dimension: user_id
    type: int
    primary_key: true
    sql: ${TABLE}.user_id
  - dimension: country
    sql: ${TABLE}.country
  - dimension: country_tier
    sql: ${TABLE}.country_tier
  - dimension: currency
    sql: ${TABLE}.currency
  - dimension: days_since_joined
    type: number
    sql: DATEDIFF(CURDATE(), ${account_creation_date})
  - dimension: acquired_on_weekend
    type: yesno
    sql: ${TABLE}.acquired_on_weekend = 1
  - dimension: acquisition_ad_partner
    sql: ${TABLE}.acquisition_ad_partner
  - dimension: acquisition_campaign
    sql: ${TABLE}.acquisition_campaign
  - dimension: acquisition_secondary
    type: int
    sql: ${TABLE}.acquisition_secondary
  - dimension: birthday
    hidden: true
    sql: ${TABLE}.birthday
  - dimension: age
    type: int
    sql: FLOOR(DATEDIFF(CURRENT_DATE, STR_TO_DATE(${birthday}, '%d/%m/%Y'))/365.25)
  - dimension: age_tier
    type: tier
    sql: ${age}
  - dimension: browser_name
    sql: ${TABLE}.browser_name
  - dimension: email
    sql: ${TABLE}.email
  - dimension: facebook_id
    sql: ${TABLE}.facebook_id
  - dimension: facebook_name
    sql: ${TABLE}.facebook_name
  - dimension: gender
    sql: ${TABLE}.gender
  - dimension: is_paying_user
    type: yesno
    sql: ${TABLE}.is_paying_user = 1
  - dimension: is_whale
    type: yesno
    sql: ${TABLE}.is_whale = 1
  - dimension: last_login
    type: time
    sql: FROM_UNIXTIME(${TABLE}.last_login_time)
  - dimension: last_purchase
    type: time
    sql: FROM_UNIXTIME(${TABLE}.last_purchase_time)
  - dimension: locale
    sql: ${TABLE}.locale
  - dimension: logged_in_times
    type: int
    sql: ${TABLE}.logged_in_times
  - dimension: paid_within_24hrs
    type: yesno
    sql: ${TABLE}.paid_within_24hrs = 1
  - dimension: time_in_game
    type: int
    sql: ${TABLE}.time_in_game
  - dimension: days_between_signup_and_purchase
    type: number
    sql: DATEDIFF(${first_purchase_date}, ${account_creation_date})
  - dimension: days_between_signup_and_purchase_tiered
    type: tier
    sql: ${days_between_signup_and_purchase}
  - dimension: timezone
    type: int
    sql: ${TABLE}.timezone
  - dimension: completed_quests
    type: int
    sql: ${TABLE}.completed_quests
  - dimension: completed_tutorial
    type: yesno
    sql: ${TABLE}.completed_tutorial = 1
  - dimension: fb_credit_balance
    type: int
    sql: ${TABLE}.fb_credit_balance
  - dimension: fb_gamer_status
    type: int
    sql: ${TABLE}.fb_gamer_status
  - dimension: fb_notifications_sent
    type: int
    sql: ${TABLE}.fb_notifications_sent
  - dimension: friends_at_least_10
    type: int
    sql: ${TABLE}.friends_at_least_10
  - dimension: friends_in_game
    type: int
    sql: ${TABLE}.friends_in_game
  - dimension: gamebucks_balance
    type: int
    sql: ${TABLE}.gamebucks_balance
  - dimension: gamebucks_cur_balance
    type: int
    sql: ${TABLE}.gamebucks_cur_balance
  - dimension: gamebucks_refunded
    type: int
    sql: ${TABLE}.gamebucks_refunded
  - dimension: gamebucks_spent
    type: int
    sql: ${TABLE}.gamebucks_spent
  - dimension: home_region
    sql: ${TABLE}.home_region
  - dimension: initial_friends_in_game
    type: int
    sql: ${TABLE}.initial_friends_in_game
  - dimension: items_looted
    type: int
    sql: ${TABLE}.items_looted
  - dimension: items_purchased
    type: int
    sql: ${TABLE}.items_purchased
  - dimension: last_fb_notification_time
    type: int
    sql: ${TABLE}.last_fb_notification_time
  - dimension: money_refunded
    type: number
    sql: ${TABLE}.money_refunded
  - dimension: money_spent
    type: number
    sql: ${TABLE}.money_spent
  - dimension: num_purchases
    type: int
    sql: ${TABLE}.num_purchases
  - dimension: payer_promo_gamebucks_earned
    type: int
    sql: ${TABLE}.payer_promo_gamebucks_earned
  - dimension: payer_promo_offered
    type: int
    sql: ${TABLE}.payer_promo_offered
  - dimension: player_level
    type: int
    sql: ${TABLE}.player_level
  - dimension: quests_completed
    type: int
    sql: ${TABLE}.quests_completed
  - dimension: speedups_purchased
    type: int
    sql: ${TABLE}.speedups_purchased
  - dimension: tech_unlocks_purchased
    type: int
    sql: ${TABLE}.tech_unlocks_purchased
  - dimension: techs_purchased
    type: int
    sql: ${TABLE}.techs_purchased
  - dimension_group: account_creation
    type: time
    timeframes:
    - time
    - date
    - hour
    - dow
    - hod
    - week
    - month
    sql: FROM_UNIXTIME(${TABLE}.account_creation_time)
  - dimension_group: birth
    type: time
    timeframes:
    - date
    - month
    - year
    sql: STR_TO_DATE(${birthday}, '%d/%m/%Y')
  - dimension_group: first_purchase
    type: time
    timeframes:
    - time
    - date
    sql: FROM_UNIXTIME(${TABLE}.time_of_first_purchase)
  - dimension_group: upcache_time
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: FROM_UNIXTIME(${TABLE}.upcache_time)
  - measure: count
    type: count
  - measure: count_percent_of_total
    type: percent_of_total
    sql: ${count}
  - measure: count_pct_of_total
    type: percent_of_total
    label: Count (Percent of Total)
    sql: ${count}
  - measure: total_items_purchased
    type: sum
    sql: ${items_purchased}
  - measure: average_items_purchased
    type: avg
    sql: ${items_purchased}
  - measure: average_age
    type: avg
    sql: ${age}
  - measure: total_time_in_game
    type: sum
    sql: ${time_in_game}
  - measure: average_time_in_game
    type: average
    sql: ${time_in_game}
  - measure: average_days_btwn_signup_and_purchase
    type: average
    sql: ${days_between_signup_and_purchase}