Top Categories

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

SELECT insurance_product_category_dimension."FirstLevelCategoryName" as category_name,
  RANK () OVER
  (
    ORDER BY COUNT(*) DESC
  ) rank
FROM "Dwh_Fct"."AggrOrderMeasures" AS aggr_order_measures
LEFT JOIN "Dwh_Dim"."DateDimension" AS created_date
  ON (created_date."DateKey") = (aggr_order_measures."CreatedDateKey")
LEFT JOIN "Dwh_Dim"."InsuranceProductCategoryDimension" AS insurance_product_category_dimension
  ON (aggr_order_measures."InsuranceProductCategoryKey") = (insurance_product_category_dimension."InsuranceProductCategoryKey")
WHERE {% condition created_date.day_date %} created_date."DayDate" {% endcondition %}
GROUP BY 1

# models/schema.yml
version: 2

models:
  - name: top_categories
    columns:
      - name: category_name
        tests:
          - unique
          - not_null
      - name: rank
      - name: top_categories
      - name: top_category_names

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 insurance_product_category_dimension."FirstLevelCategoryName" as category_name,
  RANK () OVER
  (
    ORDER BY COUNT(*) DESC
  ) rank
FROM "Dwh_Fct"."AggrOrderMeasures" AS aggr_order_measures
LEFT JOIN "Dwh_Dim"."DateDimension" AS created_date
  ON (created_date."DateKey") = (aggr_order_measures."CreatedDateKey")
LEFT JOIN "Dwh_Dim"."InsuranceProductCategoryDimension" AS insurance_product_category_dimension
  ON (aggr_order_measures."InsuranceProductCategoryKey") = (insurance_product_category_dimension."InsuranceProductCategoryKey")
WHERE {% condition created_date.day_date %} created_date."DayDate" {% endcondition %}
GROUP BY 1

view: top_categories {
  derived_table: {
    sql: SELECT insurance_product_category_dimension."FirstLevelCategoryName" as category_name,
  RANK () OVER
  (
    ORDER BY COUNT(*) DESC
  ) rank
FROM "Dwh_Fct"."AggrOrderMeasures" AS aggr_order_measures
LEFT JOIN "Dwh_Dim"."DateDimension" AS created_date
  ON (created_date."DateKey") = (aggr_order_measures."CreatedDateKey")
LEFT JOIN "Dwh_Dim"."InsuranceProductCategoryDimension" AS insurance_product_category_dimension
  ON (aggr_order_measures."InsuranceProductCategoryKey") = (insurance_product_category_dimension."InsuranceProductCategoryKey")
WHERE {% condition created_date.day_date %} created_date."DayDate" {% endcondition %}
GROUP BY 1 ;;
  }

  dimension: category_name {
    primary_key: yes
    hidden: yes
    sql: ${TABLE}.category_name ;;
  }

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

  dimension: top_categories {
    type: yesno
    hidden: yes
    sql: ${TABLE}.rank < 4 ;;
  }

  dimension: top_category_names {
    sql: CASE WHEN ${top_categories} = 'Yes' then ${category_name} else 'Other' end ;;
  }

  measure: min_rank {
    type: min
    hidden: yes
    sql: ${rank} ;;
  }
}

- view: top_categories
  derived_table:
    sql: SELECT insurance_product_category_dimension."FirstLevelCategoryName" as category_name,
  RANK () OVER
  (
    ORDER BY COUNT(*) DESC
  ) rank
FROM "Dwh_Fct"."AggrOrderMeasures" AS aggr_order_measures
LEFT JOIN "Dwh_Dim"."DateDimension" AS created_date
  ON (created_date."DateKey") = (aggr_order_measures."CreatedDateKey")
LEFT JOIN "Dwh_Dim"."InsuranceProductCategoryDimension" AS insurance_product_category_dimension
  ON (aggr_order_measures."InsuranceProductCategoryKey") = (insurance_product_category_dimension."InsuranceProductCategoryKey")
WHERE {% condition created_date.day_date %} created_date."DayDate" {% endcondition %}
GROUP BY 1
  fields:
  - dimension: category_name
    primary_key: true
    hidden: true
    sql: ${TABLE}.category_name
  - dimension: rank
    type: int
    hidden: true
    sql: ${TABLE}.rank
  - dimension: top_categories
    type: yesno
    hidden: true
    sql: ${TABLE}.rank < 4
  - dimension: top_category_names
    sql: CASE WHEN ${top_categories} = 'Yes' then ${category_name} else 'Other' end
  - measure: min_rank
    type: min
    sql: ${rank}