Coupons

view: coupons {
  sql_table_name: public.coupons ;;

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

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

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

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

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

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

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

  dimension: isdiked {
    type: yesno
    sql: ${TABLE}.isdiked ;;
  }

  dimension: isexclusive {
    type: yesno
    sql: ${TABLE}.isexclusive ;;
  }

  dimension: isfeatured {
    type: yesno
    sql: ${TABLE}.isfeatured ;;
  }

  dimension: isnotworking {
    type: yesno
    sql: ${TABLE}.isnotworking ;;
  }

  dimension: isspecialsale {
    type: yesno
    sql: ${TABLE}.isspecialsale ;;
  }

  dimension: isstaffpick {
    type: yesno
    sql: ${TABLE}.isstaffpick ;;
  }

  dimension: isstorewide {
    type: yesno
    sql: ${TABLE}.isstorewide ;;
  }

  dimension: isunreliable {
    type: yesno
    sql: ${TABLE}.isunreliable ;;
  }

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

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

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

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

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

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

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

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

  dimension: isvalid {
    type: yesno
    sql: ${TABLE}.status = 2 AND ${TABLE}.isnotworking = 0 AND (${TABLE}.expirationdate is NULL OR ${TABLE}.expirationdate > 'now') ;;
  }

  dimension: isoffer {
    type: yesno
    sql: ${TABLE}.coupontype = 2 ;;
  }

  dimension_group: creationdate {
    type: time
    timeframes: [time, date, week, month]
    convert_tz: no
    sql: ${TABLE}.creationdate ;;
  }

  dimension_group: expirationdate {
    type: time
    timeframes: [time, date, week, month]
    convert_tz: no
    sql: ${TABLE}.expirationdate ;;
  }

  dimension_group: lastcheckeddate {
    type: time
    timeframes: [time, date, week, month]
    convert_tz: no
    sql: ${TABLE}.lastcheckeddate ;;
  }

  dimension_group: lastmodifieddate {
    type: time
    timeframes: [time, date, week, month]
    convert_tz: no
    sql: ${TABLE}.lastmodifieddate ;;
  }

  dimension_group: startdate {
    type: time
    timeframes: [time, date, week, month]
    convert_tz: no
    sql: ${TABLE}.startdate ;;
  }

  measure: count {
    type: count
    drill_fields: [id, stores.id, stores.name, events.count, transactions.count]
  }

  measure: count_valid {
    type: count
    filters: [status: "2"]
    filters: [isnotworking: "no"]
    filters: [expirationdate_date: "null,after now"]
  }

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

- view: coupons
  sql_table_name: public.coupons
  fields:
  - dimension: id
    type: int
    primary_key: true
    sql: ${TABLE}.id
  - dimension: accountid
    type: int
    sql: ${TABLE}.accountid
  - dimension: affiliatenetworkid
    type: int
    sql: ${TABLE}.affiliatenetworkid
  - dimension: code
    sql: ${TABLE}.code
  - dimension: coupontype
    type: number
    sql: ${TABLE}.coupontype
  - dimension: description
    sql: ${TABLE}.description
  - dimension: editornote
    sql: ${TABLE}.editornote
  - dimension: isdiked
    type: yesno
    sql: ${TABLE}.isdiked
  - dimension: isexclusive
    type: yesno
    sql: ${TABLE}.isexclusive
  - dimension: isfeatured
    type: yesno
    sql: ${TABLE}.isfeatured
  - dimension: isnotworking
    type: yesno
    sql: ${TABLE}.isnotworking
  - dimension: isspecialsale
    type: yesno
    sql: ${TABLE}.isspecialsale
  - dimension: isstaffpick
    type: yesno
    sql: ${TABLE}.isstaffpick
  - dimension: isstorewide
    type: yesno
    sql: ${TABLE}.isstorewide
  - dimension: isunreliable
    type: yesno
    sql: ${TABLE}.isunreliable
  - dimension: smalltitle
    sql: ${TABLE}.smalltitle
  - dimension: status
    type: number
    sql: ${TABLE}.status
  - dimension: storeid
    type: int
    sql: ${TABLE}.storeid
  - dimension: targeturl
    sql: ${TABLE}.targeturl
  - dimension: title
    sql: ${TABLE}.title
  - dimension: url
    sql: ${TABLE}.url
  - dimension: votesdown
    type: int
    sql: ${TABLE}.votesdown
  - dimension: votesup
    type: int
    sql: ${TABLE}.votesup
  - dimension: isvalid
    type: yesno
    sql: ${TABLE}.status = 2 AND ${TABLE}.isnotworking = 0 AND (${TABLE}.expirationdate is NULL OR ${TABLE}.expirationdate > 'now')
  - dimension: isoffer
    type: yesno
    sql: ${TABLE}.coupontype = 2
  - dimension_group: creationdate
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    convert_tz: false
    sql: ${TABLE}.creationdate
  - dimension_group: expirationdate
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    convert_tz: false
    sql: ${TABLE}.expirationdate
  - dimension_group: lastcheckeddate
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    convert_tz: false
    sql: ${TABLE}.lastcheckeddate
  - dimension_group: lastmodifieddate
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    convert_tz: false
    sql: ${TABLE}.lastmodifieddate
  - dimension_group: startdate
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    convert_tz: false
    sql: ${TABLE}.startdate
  - measure: count
    type: count
    drill_fields:
    - id
    - stores.id
    - stores.name
    - events.count
    - transactions.count
  - measure: count_valid
    type: count
    filters:
    - status: 2
    - isnotworking: no
    - expirationdate_date: null,after now
  - measure: ratio
    type: percent_of_total
    sql: ${count}