Company Territory Rank

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

select a.*, @curRank := @curRank + 1 AS sales_rank
, a.ttm_net_revenue /
(select 1.0 * sum(SOI_LineQty * SOD_UnitPrice) / count(distinct dic.csa_territorycode) as mean_territory_sales
from fct_company_sales_order_line fisol
inner join dim_company_customer dic
on fisol.company_customer_key = dic.company_customer_key
where fisol.shipdate >= date_add(curdate(), INTERVAL -365 DAY)
and dic.cus_customertype not in('IntlChannelDistributor','OnlineStore','ChannelDistributor','Infomercial','Marketing','RetailChannel','US ChannelDistributor','ECommerce')
and dic.csa_territorycode not like <% parameter territory_code_placeholder %>
) as sales_index
from (
select dic.csa_territorycode, sum(SOI_LineQty * SOD_UnitPrice) as ttm_net_revenue
from fct_company_sales_order_line fisol
inner join dim_company_customer dic
on fisol.company_customer_key = dic.company_customer_key
where fisol.shipdate >= date_add(curdate(), INTERVAL -365 DAY)
and dic.cus_customertype not in('IntlChannelDistributor','OnlineStore','ChannelDistributor','Infomercial','Marketing','RetailChannel','US ChannelDistributor','ECommerce')
and dic.csa_territorycode not like <% parameter territory_code_placeholder %>

group by 1
order by 2 desc
) a
, (SELECT @curRank := 0) r

# models/schema.yml
version: 2

models:
  - name: company_territory_rank
    columns:
      - name: territory_code
        tests:
          - unique
          - not_null
      - name: sales_rank
        description: "Territory rank based on TTM sales"
      - name: sales_index
        description: "Territory sales index vs mean territory TTM sales where 1.0 is equal to the mean"

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 a.*, @curRank := @curRank + 1 AS sales_rank
, a.ttm_net_revenue /
(select 1.0 * sum(SOI_LineQty * SOD_UnitPrice) / count(distinct dic.csa_territorycode) as mean_territory_sales
from fct_company_sales_order_line fisol
inner join dim_company_customer dic
on fisol.company_customer_key = dic.company_customer_key
where fisol.shipdate >= date_add(curdate(), INTERVAL -365 DAY)
and dic.cus_customertype not in('IntlChannelDistributor','OnlineStore','ChannelDistributor','Infomercial','Marketing','RetailChannel','US ChannelDistributor','ECommerce')
and dic.csa_territorycode not like <% parameter territory_code_placeholder %>
) as sales_index
from (
select dic.csa_territorycode, sum(SOI_LineQty * SOD_UnitPrice) as ttm_net_revenue
from fct_company_sales_order_line fisol
inner join dim_company_customer dic
on fisol.company_customer_key = dic.company_customer_key
where fisol.shipdate >= date_add(curdate(), INTERVAL -365 DAY)
and dic.cus_customertype not in('IntlChannelDistributor','OnlineStore','ChannelDistributor','Infomercial','Marketing','RetailChannel','US ChannelDistributor','ECommerce')
and dic.csa_territorycode not like <% parameter territory_code_placeholder %>

group by 1
order by 2 desc
) a
, (SELECT @curRank := 0) r

view: company_territory_rank {
  derived_table: {
    sql: select a.*, @curRank := @curRank + 1 AS sales_rank
, a.ttm_net_revenue /
(select 1.0 * sum(SOI_LineQty * SOD_UnitPrice) / count(distinct dic.csa_territorycode) as mean_territory_sales
from fct_company_sales_order_line fisol
inner join dim_company_customer dic
on fisol.company_customer_key = dic.company_customer_key
where fisol.shipdate >= date_add(curdate(), INTERVAL -365 DAY)
and dic.cus_customertype not in('IntlChannelDistributor','OnlineStore','ChannelDistributor','Infomercial','Marketing','RetailChannel','US ChannelDistributor','ECommerce')
and dic.csa_territorycode not like <% parameter territory_code_placeholder %>
) as sales_index
from (
select dic.csa_territorycode, sum(SOI_LineQty * SOD_UnitPrice) as ttm_net_revenue
from fct_company_sales_order_line fisol
inner join dim_company_customer dic
on fisol.company_customer_key = dic.company_customer_key
where fisol.shipdate >= date_add(curdate(), INTERVAL -365 DAY)
and dic.cus_customertype not in('IntlChannelDistributor','OnlineStore','ChannelDistributor','Infomercial','Marketing','RetailChannel','US ChannelDistributor','ECommerce')
and dic.csa_territorycode not like <% parameter territory_code_placeholder %>

group by 1
order by 2 desc
) a
, (SELECT @curRank := 0) r ;;
    persist_for: "168 hours"
  }

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

  dimension: sales_rank {
    description: "Territory rank based on TTM sales"
    type: int
    sql: ${TABLE}.sales_rank ;;
  }

  dimension: sales_index {
    description: "Territory sales index vs mean territory TTM sales where 1.0 is equal to the mean"
    type: number
    sql: ${TABLE}.sales_index ;;
  }
}

- view: company_territory_rank
  derived_table:
    sql: select a.*, @curRank := @curRank + 1 AS sales_rank
, a.ttm_net_revenue /
(select 1.0 * sum(SOI_LineQty * SOD_UnitPrice) / count(distinct dic.csa_territorycode) as mean_territory_sales
from fct_company_sales_order_line fisol
inner join dim_company_customer dic
on fisol.company_customer_key = dic.company_customer_key
where fisol.shipdate >= date_add(curdate(), INTERVAL -365 DAY)
and dic.cus_customertype not in('IntlChannelDistributor','OnlineStore','ChannelDistributor','Infomercial','Marketing','RetailChannel','US ChannelDistributor','ECommerce')
and dic.csa_territorycode not like <% parameter territory_code_placeholder %>
) as sales_index
from (
select dic.csa_territorycode, sum(SOI_LineQty * SOD_UnitPrice) as ttm_net_revenue
from fct_company_sales_order_line fisol
inner join dim_company_customer dic
on fisol.company_customer_key = dic.company_customer_key
where fisol.shipdate >= date_add(curdate(), INTERVAL -365 DAY)
and dic.cus_customertype not in('IntlChannelDistributor','OnlineStore','ChannelDistributor','Infomercial','Marketing','RetailChannel','US ChannelDistributor','ECommerce')
and dic.csa_territorycode not like <% parameter territory_code_placeholder %>

group by 1
order by 2 desc
) a
, (SELECT @curRank := 0) r
    persist_for: 168 hours
  fields:
  - dimension: territory_code
    primary_key: true
    hidden: true
    sql: ${TABLE}.csa_territorycode
  - dimension: sales_rank
    type: int
    description: Territory rank based on TTM sales
    sql: ${TABLE}.sales_rank
  - dimension: sales_index
    type: number
    description: Territory sales index vs mean territory TTM sales where 1.0 is equal to the mean
    sql: ${TABLE}.sales_index