First Box Facts

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

SELECT customer_id
  , COUNT(*) AS number_of_items_in_first_box
  , COUNT(NULLIF(EXISTS(SELECT 1
                        FROM style_survey_choices AS sss
                        LEFT JOIN style_survey_answers AS ssr
                        ON ssr.id = sss.survey_response_id
                        WHERE item_histories.item_descriptor_id = sss.item_descriptor_id
                        AND ssr.customer_id = item_histories.customer_id), FALSE)) AS number_of_survey_item_in_first_box
  , COUNT(date_returned) AS items_returned_from_first_box
  , AVG(recommendation_score) AS average_first_box_recommendation_score
  , SUM(purchase_price_cents * 0.01) AS first_box_purchase_price
FROM item_histories
LEFT JOIN members
ON item_histories.customer_id = members.id
WHERE TO_CHAR(item_histories.created_at, 'YYYY-MM') = TO_CHAR(members.membership_start_time, 'YYYY-MM')
GROUP BY 1
ORDER BY 1

# models/schema.yml
version: 2

models:
  - name: first_box_facts
    columns:
      - name: customer_id
        tests:
          - unique
          - not_null
      - name: number_of_items_in_first_box
      - name: number_of_survey_item_in_first_box
      - name: survey_item_in_first_box
      - name: items_returned_from_first_box
      - name: average_first_box_recommendation_score
      - name: first_box_purchase_price
      - name: first_box_purchase_price_tiered

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.

SELECT customer_id
  , COUNT(*) AS number_of_items_in_first_box
  , COUNT(NULLIF(EXISTS(SELECT 1
                        FROM style_survey_choices AS sss
                        LEFT JOIN style_survey_answers AS ssr
                        ON ssr.id = sss.survey_response_id
                        WHERE item_histories.item_descriptor_id = sss.item_descriptor_id
                        AND ssr.customer_id = item_histories.customer_id), FALSE)) AS number_of_survey_item_in_first_box
  , COUNT(date_returned) AS items_returned_from_first_box
  , AVG(recommendation_score) AS average_first_box_recommendation_score
  , SUM(purchase_price_cents * 0.01) AS first_box_purchase_price
FROM item_histories
LEFT JOIN members
ON item_histories.customer_id = members.id
WHERE TO_CHAR(item_histories.created_at, 'YYYY-MM') = TO_CHAR(members.membership_start_time, 'YYYY-MM')
GROUP BY 1
ORDER BY 1

view: first_box_facts {
  derived_table: {
    sql: SELECT customer_id
  , COUNT(*) AS number_of_items_in_first_box
  , COUNT(NULLIF(EXISTS(SELECT 1
                        FROM style_survey_choices AS sss
                        LEFT JOIN style_survey_answers AS ssr
                        ON ssr.id = sss.survey_response_id
                        WHERE item_histories.item_descriptor_id = sss.item_descriptor_id
                        AND ssr.customer_id = item_histories.customer_id), FALSE)) AS number_of_survey_item_in_first_box
  , COUNT(date_returned) AS items_returned_from_first_box
  , AVG(recommendation_score) AS average_first_box_recommendation_score
  , SUM(purchase_price_cents * 0.01) AS first_box_purchase_price
FROM item_histories
LEFT JOIN members
ON item_histories.customer_id = members.id
WHERE TO_CHAR(item_histories.created_at, 'YYYY-MM') = TO_CHAR(members.membership_start_time, 'YYYY-MM')
GROUP BY 1
ORDER BY 1 ;;
  }

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

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

  dimension: number_of_survey_item_in_first_box {
    type: number
    sql: COALESCE(${TABLE}.number_of_survey_item_in_first_box, 0) ;;
  }

  dimension: survey_item_in_first_box {
    type: yesno
    sql: ${number_of_survey_item_in_first_box} > 0 ;;
  }

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

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

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

  dimension: first_box_purchase_price_tiered {
    type: tier
    sql: ${first_box_purchase_price} ;;
  }

  measure: count {
    type: count
  }
}

- view: first_box_facts
  derived_table:
    sql: SELECT customer_id
  , COUNT(*) AS number_of_items_in_first_box
  , COUNT(NULLIF(EXISTS(SELECT 1
                        FROM style_survey_choices AS sss
                        LEFT JOIN style_survey_answers AS ssr
                        ON ssr.id = sss.survey_response_id
                        WHERE item_histories.item_descriptor_id = sss.item_descriptor_id
                        AND ssr.customer_id = item_histories.customer_id), FALSE)) AS number_of_survey_item_in_first_box
  , COUNT(date_returned) AS items_returned_from_first_box
  , AVG(recommendation_score) AS average_first_box_recommendation_score
  , SUM(purchase_price_cents * 0.01) AS first_box_purchase_price
FROM item_histories
LEFT JOIN members
ON item_histories.customer_id = members.id
WHERE TO_CHAR(item_histories.created_at, 'YYYY-MM') = TO_CHAR(members.membership_start_time, 'YYYY-MM')
GROUP BY 1
ORDER BY 1
  fields:
  - dimension: customer_id
    type: int
    primary_key: true
    sql: ${TABLE}.customer_id
  - dimension: number_of_items_in_first_box
    type: number
    sql: ${TABLE}.number_of_items_in_first_box
  - dimension: number_of_survey_item_in_first_box
    type: number
    sql: COALESCE(${TABLE}.number_of_survey_item_in_first_box, 0)
  - dimension: survey_item_in_first_box
    type: yesno
    sql: ${number_of_survey_item_in_first_box} > 0
  - dimension: items_returned_from_first_box
    type: number
    sql: ${TABLE}.items_returned_from_first_box
  - dimension: average_first_box_recommendation_score
    type: number
    sql: ${TABLE}.average_first_box_recommendation_score
  - dimension: first_box_purchase_price
    type: number
    sql: ${TABLE}.first_box_purchase_price
  - dimension: first_box_purchase_price_tiered
    type: tier
    sql: ${first_box_purchase_price}
  - measure: count
    type: count