Iarr Redemptions Fact

-- models/iarr_redemptions_fact.sql
{{ config(materialized='table') }}

SELECT unique_user_id, row_text, brandview_name, retailer_brand_name, sum(unit_amount*product_quantity) as share_amount
FROM (
  SELECT iarr.unique_user_id, iarr.row_text, iarr.brandview_name, iarr.retailer_brand_name, iarr.unit_amount, sum(iarr.product_quantity) as product_quantity
  from fmcg_di.instore_all_rows_report iarr
  left join (
    select distinct unique_user_id
    from fmcg_di.redemptions
    where {% condition deal_id %} redemptions.deal_id {% endcondition %}
  ) r on r.unique_user_id = iarr.unique_user_id
where {% condition retailer_category_id %} iarr.retailer_category_id {% endcondition %}
and iarr.product_validated = 1
and r.unique_user_id is not null
and {% condition receipt_date %} iarr.receipt_date {% endcondition %}
group by 1,2,3,4,5
) X group by 1,2,3,4

# models/schema.yml
version: 2

models:
  - name: iarr_redemptions_fact
    columns:
      - name: retailer_brand_name
      - name: unit_amount
      - name: unique_user_id
      - name: row_text
      - name: brandview_name
      - name: share_amount

Assumptions

• materialized='table' inferred from a LookML persist hint (persist_for / datagroup / sql_trigger_value).
• schema.yml columns come from view dimensions; a primary_key dimension gets unique + not_null tests.

SELECT unique_user_id, row_text, brandview_name, retailer_brand_name, sum(unit_amount*product_quantity) as share_amount
FROM (
  SELECT iarr.unique_user_id, iarr.row_text, iarr.brandview_name, iarr.retailer_brand_name, iarr.unit_amount, sum(iarr.product_quantity) as product_quantity
  from fmcg_di.instore_all_rows_report iarr
  left join (
    select distinct unique_user_id
    from fmcg_di.redemptions
    where {% condition deal_id %} redemptions.deal_id {% endcondition %}
  ) r on r.unique_user_id = iarr.unique_user_id
where {% condition retailer_category_id %} iarr.retailer_category_id {% endcondition %}
and iarr.product_validated = 1
and r.unique_user_id is not null
and {% condition receipt_date %} iarr.receipt_date {% endcondition %}
group by 1,2,3,4,5
) X group by 1,2,3,4

view: iarr_redemptions_fact {
  derived_table: {
    sql: SELECT unique_user_id, row_text, brandview_name, retailer_brand_name, sum(unit_amount*product_quantity) as share_amount
FROM (
  SELECT iarr.unique_user_id, iarr.row_text, iarr.brandview_name, iarr.retailer_brand_name, iarr.unit_amount, sum(iarr.product_quantity) as product_quantity
  from fmcg_di.instore_all_rows_report iarr
  left join (
    select distinct unique_user_id
    from fmcg_di.redemptions
    where {% condition deal_id %} redemptions.deal_id {% endcondition %}
  ) r on r.unique_user_id = iarr.unique_user_id
where {% condition retailer_category_id %} iarr.retailer_category_id {% endcondition %}
and iarr.product_validated = 1
and r.unique_user_id is not null
and {% condition receipt_date %} iarr.receipt_date {% endcondition %}
group by 1,2,3,4,5
) X group by 1,2,3,4 ;;
    persist_for: "1 minute"
  }

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

  dimension: unit_amount {
    type: int
    hidden: yes
    sql: ${TABLE}.unit_amount ;;
  }

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

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

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

  dimension: share_amount {
    type: number
    hidden: yes
    sql: ${TABLE}.share_amount ;;
  }

  measure: total_share_amount {
    type: sum
    value_format: "#.00"
    sql: ${share_amount} ;;
  }
}

- view: iarr_redemptions_fact
  derived_table:
    sql: SELECT unique_user_id, row_text, brandview_name, retailer_brand_name, sum(unit_amount*product_quantity) as share_amount
FROM (
  SELECT iarr.unique_user_id, iarr.row_text, iarr.brandview_name, iarr.retailer_brand_name, iarr.unit_amount, sum(iarr.product_quantity) as product_quantity
  from fmcg_di.instore_all_rows_report iarr
  left join (
    select distinct unique_user_id
    from fmcg_di.redemptions
    where {% condition deal_id %} redemptions.deal_id {% endcondition %}
  ) r on r.unique_user_id = iarr.unique_user_id
where {% condition retailer_category_id %} iarr.retailer_category_id {% endcondition %}
and iarr.product_validated = 1
and r.unique_user_id is not null
and {% condition receipt_date %} iarr.receipt_date {% endcondition %}
group by 1,2,3,4,5
) X group by 1,2,3,4
    persist_for: 1 minute
  fields:
  - dimension: retailer_brand_name
    sql: ${TABLE}.retailer_brand_name
  - dimension: unit_amount
    type: int
    hidden: true
    sql: ${TABLE}.unit_amount
  - dimension: unique_user_id
    type: int
    sql: ${TABLE}.unique_user_id
  - dimension: row_text
    sql: ${TABLE}.row_text
  - dimension: brandview_name
    sql: ${TABLE}.brandview_name
  - dimension: share_amount
    type: number
    hidden: true
    sql: ${TABLE}.share_amount
  - measure: total_share_amount
    type: sum
    value_format: '#.00'
    sql: ${share_amount}