Audience Maritalstatus Distribution Clicks

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

with df_dc_jct as(
  select count(distinct df2.adnxsuid) as usercount, dc.geoid, df2.insertionorderid
  FROM
      analytics_data df2
  JOIN
      audience_coordinates dc
  ON
      df2.lat = dc.latitude
      AND df2.long = dc.longitude
  WHERE df2.isclicked = true
  group by dc.geoid, df2.insertionorderid
)
select
  insertionorderid,
  key,
  ratio_to_report(distribution) over(partition by insertionorderid)
from (
  select  df_dc_jct.insertionorderid,
      da.key,
      sum(df_dc_jct.usercount * geo_dist)  as distribution
  from df_dc_jct
    join (
      select geoid,
        key,
        ratio_to_report(value) over(partition by geoid) as geo_dist
          from audience_maritalstatus
          order by geoid, key
    )  da
    on df_dc_jct.geoid = da.geoid
  group by df_dc_jct.insertionorderid, da.key
)

# models/schema.yml
version: 2

models:
  - name: audience_maritalstatus_distribution_clicks
    columns:
      - name: insertionorder_id
      - name: marital_status

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.

with df_dc_jct as(
  select count(distinct df2.adnxsuid) as usercount, dc.geoid, df2.insertionorderid
  FROM
      analytics_data df2
  JOIN
      audience_coordinates dc
  ON
      df2.lat = dc.latitude
      AND df2.long = dc.longitude
  WHERE df2.isclicked = true
  group by dc.geoid, df2.insertionorderid
)
select
  insertionorderid,
  key,
  ratio_to_report(distribution) over(partition by insertionorderid)
from (
  select  df_dc_jct.insertionorderid,
      da.key,
      sum(df_dc_jct.usercount * geo_dist)  as distribution
  from df_dc_jct
    join (
      select geoid,
        key,
        ratio_to_report(value) over(partition by geoid) as geo_dist
          from audience_maritalstatus
          order by geoid, key
    )  da
    on df_dc_jct.geoid = da.geoid
  group by df_dc_jct.insertionorderid, da.key
)

view: audience_maritalstatus_distribution_clicks {
  derived_table: {
    sql: with df_dc_jct as(
  select count(distinct df2.adnxsuid) as usercount, dc.geoid, df2.insertionorderid
  FROM
      analytics_data df2
  JOIN
      audience_coordinates dc
  ON
      df2.lat = dc.latitude
      AND df2.long = dc.longitude
  WHERE df2.isclicked = true
  group by dc.geoid, df2.insertionorderid
)
select
  insertionorderid,
  key,
  ratio_to_report(distribution) over(partition by insertionorderid)
from (
  select  df_dc_jct.insertionorderid,
      da.key,
      sum(df_dc_jct.usercount * geo_dist)  as distribution
  from df_dc_jct
    join (
      select geoid,
        key,
        ratio_to_report(value) over(partition by geoid) as geo_dist
          from audience_maritalstatus
          order by geoid, key
    )  da
    on df_dc_jct.geoid = da.geoid
  group by df_dc_jct.insertionorderid, da.key
) ;;
  }

  dimension: insertionorder_id {
    sql: ${TABLE}.insertionorderid ;;
  }

  dimension: marital_status {
    sql: ${TABLE}.key ;;
  }

  measure: ratio {
    type: sum
    sql: 100.0* ${TABLE}.ratio_to_report ;;
  }
}

- view: audience_maritalstatus_distribution_clicks
  derived_table:
    sql: with df_dc_jct as(
  select count(distinct df2.adnxsuid) as usercount, dc.geoid, df2.insertionorderid
  FROM
      analytics_data df2
  JOIN
      audience_coordinates dc
  ON
      df2.lat = dc.latitude
      AND df2.long = dc.longitude
  WHERE df2.isclicked = true
  group by dc.geoid, df2.insertionorderid
)
select
  insertionorderid,
  key,
  ratio_to_report(distribution) over(partition by insertionorderid)
from (
  select  df_dc_jct.insertionorderid,
      da.key,
      sum(df_dc_jct.usercount * geo_dist)  as distribution
  from df_dc_jct
    join (
      select geoid,
        key,
        ratio_to_report(value) over(partition by geoid) as geo_dist
          from audience_maritalstatus
          order by geoid, key
    )  da
    on df_dc_jct.geoid = da.geoid
  group by df_dc_jct.insertionorderid, da.key
)
  fields:
  - dimension: insertionorder_id
    sql: ${TABLE}.insertionorderid
  - dimension: marital_status
    sql: ${TABLE}.key
  - measure: ratio
    type: sum
    sql: 100.0* ${TABLE}.ratio_to_report