Public Affiliate Intent Tracking

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

SELECT
  base.affiliate_lead_id,
  i1.created_at AS first_intent_created_at,
  (CASE WHEN i1.id = i2.id THEN NULL ELSE i2.created_at END) AS second_intent_created_at,
  t1.name AS first_intent_status,
  (CASE WHEN i1.id = i2.id THEN NULL ELSE t2.name END) AS second_intent_status,
  t1.slug AS first_intent_slug,
  (CASE WHEN i1.id = i2.id THEN NULL ELSE t2.slug END) AS second_intent_slug
FROM
  (
    SELECT
      affiliate_lead_id
      , MIN(id) AS min_id
      , MAX(id) AS max_id
    FROM
      public_affiliate_intent_tracking
    GROUP BY
      1
  ) AS base
  LEFT JOIN public_affiliate_intent_tracking as i1 ON i1.id = base.min_id
  LEFT JOIN public_affiliate_intent_trackingtype as t1 on (i1.intent_id = t1.id)
  LEFT JOIN public_affiliate_intent_tracking as i2 ON i2.id = base.max_id
  LEFT JOIN public_affiliate_intent_trackingtype as t2 on (i2.intent_id = t2.id)

# models/schema.yml
version: 2

models:
  - name: public_affiliate_intent_tracking
    columns:
      - name: affiliate_lead_id
        tests:
          - unique
          - not_null
      - name: first_intent_status
      - name: second_intent_status
      - name: first_intent_slug
      - name: second_intent_slug
      - name: just_browsing_opt_in_indicator
      - name: buyer_ready_to_buy_test
      - name: browsing_to_accepted_lag_seconds

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
  base.affiliate_lead_id,
  i1.created_at AS first_intent_created_at,
  (CASE WHEN i1.id = i2.id THEN NULL ELSE i2.created_at END) AS second_intent_created_at,
  t1.name AS first_intent_status,
  (CASE WHEN i1.id = i2.id THEN NULL ELSE t2.name END) AS second_intent_status,
  t1.slug AS first_intent_slug,
  (CASE WHEN i1.id = i2.id THEN NULL ELSE t2.slug END) AS second_intent_slug
FROM
  (
    SELECT
      affiliate_lead_id
      , MIN(id) AS min_id
      , MAX(id) AS max_id
    FROM
      public_affiliate_intent_tracking
    GROUP BY
      1
  ) AS base
  LEFT JOIN public_affiliate_intent_tracking as i1 ON i1.id = base.min_id
  LEFT JOIN public_affiliate_intent_trackingtype as t1 on (i1.intent_id = t1.id)
  LEFT JOIN public_affiliate_intent_tracking as i2 ON i2.id = base.max_id
  LEFT JOIN public_affiliate_intent_trackingtype as t2 on (i2.intent_id = t2.id)

view: public_affiliate_intent_tracking {
  derived_table: {
    sql: SELECT
  base.affiliate_lead_id,
  i1.created_at AS first_intent_created_at,
  (CASE WHEN i1.id = i2.id THEN NULL ELSE i2.created_at END) AS second_intent_created_at,
  t1.name AS first_intent_status,
  (CASE WHEN i1.id = i2.id THEN NULL ELSE t2.name END) AS second_intent_status,
  t1.slug AS first_intent_slug,
  (CASE WHEN i1.id = i2.id THEN NULL ELSE t2.slug END) AS second_intent_slug
FROM
  (
    SELECT
      affiliate_lead_id
      , MIN(id) AS min_id
      , MAX(id) AS max_id
    FROM
      public_affiliate_intent_tracking
    GROUP BY
      1
  ) AS base
  LEFT JOIN public_affiliate_intent_tracking as i1 ON i1.id = base.min_id
  LEFT JOIN public_affiliate_intent_trackingtype as t1 on (i1.intent_id = t1.id)
  LEFT JOIN public_affiliate_intent_tracking as i2 ON i2.id = base.max_id
  LEFT JOIN public_affiliate_intent_trackingtype as t2 on (i2.intent_id = t2.id) ;;
  }

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

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

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

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

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

  dimension: just_browsing_opt_in_indicator {
  }

  dimension: buyer_ready_to_buy_test {
  }

  dimension: browsing_to_accepted_lag_seconds {
    sql: DATEDIFF(s, CAST(${first_intent_created_time} AS TIMESTAMP), CAST(${second_intent_created_time} AS TIMESTAMP)) ;;
  }

  dimension_group: first_intent_created {
    type: time
    timeframes: [time, date]
    sql: ${TABLE}.first_intent_created_at ;;
  }

  dimension_group: second_intent_created {
    type: time
    timeframes: [time, date]
    sql: ${TABLE}.second_intent_created_at ;;
  }

  measure: count {
    type: count
    hidden: yes
    drill_fields: [detail*]
  }
}

- view: public_affiliate_intent_tracking
  derived_table:
    sql: SELECT
  base.affiliate_lead_id,
  i1.created_at AS first_intent_created_at,
  (CASE WHEN i1.id = i2.id THEN NULL ELSE i2.created_at END) AS second_intent_created_at,
  t1.name AS first_intent_status,
  (CASE WHEN i1.id = i2.id THEN NULL ELSE t2.name END) AS second_intent_status,
  t1.slug AS first_intent_slug,
  (CASE WHEN i1.id = i2.id THEN NULL ELSE t2.slug END) AS second_intent_slug
FROM
  (
    SELECT
      affiliate_lead_id
      , MIN(id) AS min_id
      , MAX(id) AS max_id
    FROM
      public_affiliate_intent_tracking
    GROUP BY
      1
  ) AS base
  LEFT JOIN public_affiliate_intent_tracking as i1 ON i1.id = base.min_id
  LEFT JOIN public_affiliate_intent_trackingtype as t1 on (i1.intent_id = t1.id)
  LEFT JOIN public_affiliate_intent_tracking as i2 ON i2.id = base.max_id
  LEFT JOIN public_affiliate_intent_trackingtype as t2 on (i2.intent_id = t2.id)
  fields:
  - dimension: affiliate_lead_id
    type: number
    primary_key: true
    hidden: true
    sql: ${TABLE}.affiliate_lead_id
  - dimension: first_intent_status
    sql: ${TABLE}.first_intent_status
  - dimension: second_intent_status
    sql: ${TABLE}.second_intent_status
  - dimension: first_intent_slug
    sql: ${TABLE}.first_intent_slug
  - dimension: second_intent_slug
    sql: ${TABLE}.second_intent_slug
  - dimension: just_browsing_opt_in_indicator
  - dimension: buyer_ready_to_buy_test
  - dimension: browsing_to_accepted_lag_seconds
    sql: DATEDIFF(s, CAST(${first_intent_created_time} AS TIMESTAMP), CAST(${second_intent_created_time} AS TIMESTAMP))
  - dimension_group: first_intent_created
    type: time
    timeframes:
    - time
    - date
    sql: ${TABLE}.first_intent_created_at
  - dimension_group: second_intent_created
    type: time
    timeframes:
    - time
    - date
    sql: ${TABLE}.second_intent_created_at
  - measure: count
    type: count
    drill_fields:
    - detail*