SQL Telemedicine service

Call Coupon Aggregate

Call Coupon Aggregate model for telemedicine service.

source-preserved

Blocks

dbt SQL LKML LookML

call_coupon_aggregate.sql · 35 lines

inferred

-- models/call_coupon_aggregate.sql
{{ config(materialized='view') }}

SELECT call.call_content_type_id AS call_content_type_id
 , call.call_object_id AS call_object_id
 , COUNT(distinct coupon.id) AS number_coupons
 , SUM(COALESCE(coupon.discount, 0)) AS total_discount
 , STRING_AGG(LOWER(coupon.code), '; ' ORDER BY coupon.code) AS coupon_codes
 , STRING_AGG(LOWER(COALESCE(campaign.name, '(no campaign)') || ':"' || coupon.code) || '":$' || coupon.discount, '; ' ORDER BY campaign.name) AS campaigns_with_coupons
 , BOOL_OR(COALESCE(campaign.name, '(no campaign)') != 'share' AND coupon.discount >= 40 AND coupon.is_first_call_only) AS has_fvf
FROM combined_call AS call
LEFT JOIN coupons_membercoupon AS membercoupon ON (call.call_content_type_id = membercoupon.call_content_type_id AND call.call_object_id = membercoupon.call_object_id)
LEFT JOIN coupons_coupon AS coupon ON coupon.id = membercoupon.coupon_id
LEFT JOIN coupons_campaign AS campaign ON coupon.campaign_id = campaign.id
GROUP BY call.call_content_type_id, call.call_object_id

# models/schema.yml
version: 2

models:
  - name: call_coupon_aggregate
    columns:
      - name: call_content_type_id_and_call_object_id
        tests:
          - unique
          - not_null
      - name: call_content_type_id
      - name: call_object_id
      - name: number_coupons
      - name: total_discount
      - name: total_discount_tier
      - name: has_discount
      - name: coupon_codes
      - name: campaigns_with_coupons
      - name: has_fvf

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.

call_coupon_aggregate.sql · 12 lines

source-preserved validation: parses

SELECT call.call_content_type_id AS call_content_type_id
 , call.call_object_id AS call_object_id
 , COUNT(distinct coupon.id) AS number_coupons
 , SUM(COALESCE(coupon.discount, 0)) AS total_discount
 , STRING_AGG(LOWER(coupon.code), '; ' ORDER BY coupon.code) AS coupon_codes
 , STRING_AGG(LOWER(COALESCE(campaign.name, '(no campaign)') || ':"' || coupon.code) || '":$' || coupon.discount, '; ' ORDER BY campaign.name) AS campaigns_with_coupons
 , BOOL_OR(COALESCE(campaign.name, '(no campaign)') != 'share' AND coupon.discount >= 40 AND coupon.is_first_call_only) AS has_fvf
FROM combined_call AS call
LEFT JOIN coupons_membercoupon AS membercoupon ON (call.call_content_type_id = membercoupon.call_content_type_id AND call.call_object_id = membercoupon.call_object_id)
LEFT JOIN coupons_coupon AS coupon ON coupon.id = membercoupon.coupon_id
LEFT JOIN coupons_campaign AS campaign ON coupon.campaign_id = campaign.id
GROUP BY call.call_content_type_id, call.call_object_id

call_coupon_aggregate.view.lkml · 92 lines

inferred

view: call_coupon_aggregate {
  derived_table: {
    sql: SELECT call.call_content_type_id AS call_content_type_id
 , call.call_object_id AS call_object_id
 , COUNT(distinct coupon.id) AS number_coupons
 , SUM(COALESCE(coupon.discount, 0)) AS total_discount
 , STRING_AGG(LOWER(coupon.code), '; ' ORDER BY coupon.code) AS coupon_codes
 , STRING_AGG(LOWER(COALESCE(campaign.name, '(no campaign)') || ':"' || coupon.code) || '":$' || coupon.discount, '; ' ORDER BY campaign.name) AS campaigns_with_coupons
 , BOOL_OR(COALESCE(campaign.name, '(no campaign)') != 'share' AND coupon.discount >= 40 AND coupon.is_first_call_only) AS has_fvf
FROM combined_call AS call
LEFT JOIN coupons_membercoupon AS membercoupon ON (call.call_content_type_id = membercoupon.call_content_type_id AND call.call_object_id = membercoupon.call_object_id)
LEFT JOIN coupons_coupon AS coupon ON coupon.id = membercoupon.coupon_id
LEFT JOIN coupons_campaign AS campaign ON coupon.campaign_id = campaign.id
GROUP BY call.call_content_type_id, call.call_object_id ;;
  }

  dimension: call_content_type_id_and_call_object_id {
    primary_key: yes
    hidden: yes
    sql: CASE WHEN ${TABLE}.call_content_type_id IS NULL THEN NULL
     ELSE CONCAT(${TABLE}.call_content_type_id::text, ':', ${TABLE}.call_object_id::text)
END ;;
  }

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

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

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

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

  dimension: total_discount_tier {
    type: tier
    sql: ${TABLE}.total_discount ;;
  }

  dimension: has_discount {
    type: yesno
    sql: ${total_discount} > 0 ;;
  }

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

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

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

  measure: fvf_calls {
    type: count
    filters: [has_fvf: "Yes"]
  }

  measure: non_fvf_calls {
    type: count
    filters: [has_fvf: "No"]
  }

  measure: discounted_calls {
    type: count
    filters: [has_discount: "Yes"]
  }

  measure: sum_number_coupons {
    type: sum
    sql: ${number_coupons} ;;
  }

  measure: sum_discounts {
    type: number
    sql: SUM(${total_discount}) ;;
  }
}

call_coupon_aggregate.view.lookml · 64 lines

source-preserved

- view: call_coupon_aggregate
  derived_table:
    sql: SELECT call.call_content_type_id AS call_content_type_id
 , call.call_object_id AS call_object_id
 , COUNT(distinct coupon.id) AS number_coupons
 , SUM(COALESCE(coupon.discount, 0)) AS total_discount
 , STRING_AGG(LOWER(coupon.code), '; ' ORDER BY coupon.code) AS coupon_codes
 , STRING_AGG(LOWER(COALESCE(campaign.name, '(no campaign)') || ':"' || coupon.code) || '":$' || coupon.discount, '; ' ORDER BY campaign.name) AS campaigns_with_coupons
 , BOOL_OR(COALESCE(campaign.name, '(no campaign)') != 'share' AND coupon.discount >= 40 AND coupon.is_first_call_only) AS has_fvf
FROM combined_call AS call
LEFT JOIN coupons_membercoupon AS membercoupon ON (call.call_content_type_id = membercoupon.call_content_type_id AND call.call_object_id = membercoupon.call_object_id)
LEFT JOIN coupons_coupon AS coupon ON coupon.id = membercoupon.coupon_id
LEFT JOIN coupons_campaign AS campaign ON coupon.campaign_id = campaign.id
GROUP BY call.call_content_type_id, call.call_object_id
  fields:
  - dimension: call_content_type_id_and_call_object_id
    primary_key: true
    hidden: true
    sql: CASE WHEN ${TABLE}.call_content_type_id IS NULL THEN NULL
     ELSE CONCAT(${TABLE}.call_content_type_id::text, ':', ${TABLE}.call_object_id::text)
END
  - dimension: call_content_type_id
    type: int
    sql: ${TABLE}.call_content_type_id
  - dimension: call_object_id
    type: int
    sql: ${TABLE}.call_object_id
  - dimension: number_coupons
    type: number
    sql: ${TABLE}.number_coupons
  - dimension: total_discount
    type: number
    sql: ${TABLE}.total_discount
  - dimension: total_discount_tier
    type: tier
    sql: ${TABLE}.total_discount
  - dimension: has_discount
    type: yesno
    sql: ${total_discount} > 0
  - dimension: coupon_codes
    sql: ${TABLE}.coupon_codes
  - dimension: campaigns_with_coupons
    sql: ${TABLE}.campaigns_with_coupons
  - dimension: has_fvf
    type: yesno
    sql: ${TABLE}.has_fvf
  - measure: fvf_calls
    type: count
    filters:
    - has_fvf: Yes
  - measure: non_fvf_calls
    type: count
    filters:
    - has_fvf: No
  - measure: discounted_calls
    type: count
    filters:
    - has_discount: Yes
  - measure: sum_number_coupons
    type: sum
    sql: ${number_coupons}
  - measure: sum_discounts
    type: number
    sql: SUM(${total_discount})

call_coupon_aggregate.sql
call_coupon_aggregate.sql
call_coupon_aggregate.view.lkml
call_coupon_aggregate.view.lookml

Call Coupon Aggregate

Blocks

Assumptions

Contents