Device Ids By Store

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

WITH dibs AS (
  SELECT
  zone_name AS store
  , device_id
  FROM daily_summary_by_span
  WHERE {% condition span_name %} span_name {% endcondition %}
  AND {% condition the_date_local %} the_date_local {% endcondition %}
  AND {% condition device_type %} device_type {% endcondition %}
  GROUP BY 1,2)

SELECT
d1.store AS store_1
, d2.store AS store_2
, COUNT(DISTINCT d2.device_id) AS cross_shoppers
FROM dibs AS d1
INNER JOIN dibs AS d2
ON d1.device_id = d2.device_id
WHERE d1.store <> d2.store
GROUP BY 1,2

# models/schema.yml
version: 2

models:
  - name: device_ids_by_store
    columns:
      - name: compound_key
        tests:
          - unique
          - not_null
      - name: store_1
      - name: store_2
      - name: cross_shoppers

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 dibs AS (
  SELECT
  zone_name AS store
  , device_id
  FROM daily_summary_by_span
  WHERE {% condition span_name %} span_name {% endcondition %}
  AND {% condition the_date_local %} the_date_local {% endcondition %}
  AND {% condition device_type %} device_type {% endcondition %}
  GROUP BY 1,2)

SELECT
d1.store AS store_1
, d2.store AS store_2
, COUNT(DISTINCT d2.device_id) AS cross_shoppers
FROM dibs AS d1
INNER JOIN dibs AS d2
ON d1.device_id = d2.device_id
WHERE d1.store <> d2.store
GROUP BY 1,2

view: device_ids_by_store {
  derived_table: {
    sql: WITH dibs AS (
  SELECT
  zone_name AS store
  , device_id
  FROM daily_summary_by_span
  WHERE {% condition span_name %} span_name {% endcondition %}
  AND {% condition the_date_local %} the_date_local {% endcondition %}
  AND {% condition device_type %} device_type {% endcondition %}
  GROUP BY 1,2)

SELECT
d1.store AS store_1
, d2.store AS store_2
, COUNT(DISTINCT d2.device_id) AS cross_shoppers
FROM dibs AS d1
INNER JOIN dibs AS d2
ON d1.device_id = d2.device_id
WHERE d1.store <> d2.store
GROUP BY 1,2 ;;
  }

  dimension: compound_key {
    primary_key: yes
    hidden: yes
    sql: ${store_1}||${store_2} ;;
  }

  dimension: store_1 {
  }

  dimension: store_2 {
  }

  dimension: cross_shoppers {
    type: number
  }

  measure: total_cross_shoppers {
    type: sum
    sql: ${cross_shoppers} ;;
  }

  measure: percent_of_total_cross_shoppers {
    type: percent_of_total
    sql: ${cross_shoppers} ;;
  }
}

- view: device_ids_by_store
  derived_table:
    sql: WITH dibs AS (
  SELECT
  zone_name AS store
  , device_id
  FROM daily_summary_by_span
  WHERE {% condition span_name %} span_name {% endcondition %}
  AND {% condition the_date_local %} the_date_local {% endcondition %}
  AND {% condition device_type %} device_type {% endcondition %}
  GROUP BY 1,2)

SELECT
d1.store AS store_1
, d2.store AS store_2
, COUNT(DISTINCT d2.device_id) AS cross_shoppers
FROM dibs AS d1
INNER JOIN dibs AS d2
ON d1.device_id = d2.device_id
WHERE d1.store <> d2.store
GROUP BY 1,2
  fields:
  - dimension: compound_key
    primary_key: true
    hidden: true
    sql: ${store_1}||${store_2}
  - dimension: store_1
  - dimension: store_2
  - dimension: cross_shoppers
    type: number
  - measure: total_cross_shoppers
    type: sum
    sql: ${cross_shoppers}
  - measure: percent_of_total_cross_shoppers
    type: percent_of_total
    sql: ${cross_shoppers}