Sku Recommendations

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

WITH order_sku as (
SELECT
  orders.id AS "order_id",
  variants.sku AS "sku"
FROM store_line_items AS line_items
LEFT JOIN store_orders AS orders ON line_items.order_id = orders.id
LEFT JOIN store_variants AS variants ON line_items.variant_id = variants.id
GROUP BY orders.id, variants.sku
),
total_order_sku as (
SELECT
  variants.sku as "sku",
  count(distinct variants.sku || ' ' || orders.id) as sku_frequency
FROM store_line_items AS line_items
LEFT JOIN store_orders AS orders ON line_items.order_id = orders.id
LEFT JOIN store_variants AS variants ON line_items.variant_id = variants.id
GROUP BY variants.sku
)

SELECT sku_a
      , sku_b
      , joint_frequency
      , top1.sku_frequency as sku_a_frequency
      , top2.sku_frequency as sku_b_frequency
FROM (
        SELECT sku1.sku as sku_a
        , sku2.sku as sku_b
        , count(*) as joint_frequency
        FROM order_sku as sku1
        JOIN order_sku as sku2 ON sku1.order_id = sku2.order_id
        GROUP BY sku_a, sku_b
      ) as prop
JOIN total_order_sku as top1 ON prop.sku_a = top1.sku
JOIN total_order_sku as top2 ON prop.sku_b = top2.sku
ORDER BY sku_a, joint_frequency DESC, sku_b

# models/schema.yml
version: 2

models:
  - name: sku_recommendations
    columns:
      - name: sku_a
      - name: sku_b

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 order_sku as (
SELECT
  orders.id AS "order_id",
  variants.sku AS "sku"
FROM store_line_items AS line_items
LEFT JOIN store_orders AS orders ON line_items.order_id = orders.id
LEFT JOIN store_variants AS variants ON line_items.variant_id = variants.id
GROUP BY orders.id, variants.sku
),
total_order_sku as (
SELECT
  variants.sku as "sku",
  count(distinct variants.sku || ' ' || orders.id) as sku_frequency
FROM store_line_items AS line_items
LEFT JOIN store_orders AS orders ON line_items.order_id = orders.id
LEFT JOIN store_variants AS variants ON line_items.variant_id = variants.id
GROUP BY variants.sku
)

SELECT sku_a
      , sku_b
      , joint_frequency
      , top1.sku_frequency as sku_a_frequency
      , top2.sku_frequency as sku_b_frequency
FROM (
        SELECT sku1.sku as sku_a
        , sku2.sku as sku_b
        , count(*) as joint_frequency
        FROM order_sku as sku1
        JOIN order_sku as sku2 ON sku1.order_id = sku2.order_id
        GROUP BY sku_a, sku_b
      ) as prop
JOIN total_order_sku as top1 ON prop.sku_a = top1.sku
JOIN total_order_sku as top2 ON prop.sku_b = top2.sku
ORDER BY sku_a, joint_frequency DESC, sku_b

view: sku_recommendations {
  derived_table: {
    sql: WITH order_sku as (
SELECT
  orders.id AS "order_id",
  variants.sku AS "sku"
FROM store_line_items AS line_items
LEFT JOIN store_orders AS orders ON line_items.order_id = orders.id
LEFT JOIN store_variants AS variants ON line_items.variant_id = variants.id
GROUP BY orders.id, variants.sku
),
total_order_sku as (
SELECT
  variants.sku as "sku",
  count(distinct variants.sku || ' ' || orders.id) as sku_frequency
FROM store_line_items AS line_items
LEFT JOIN store_orders AS orders ON line_items.order_id = orders.id
LEFT JOIN store_variants AS variants ON line_items.variant_id = variants.id
GROUP BY variants.sku
)

SELECT sku_a
      , sku_b
      , joint_frequency
      , top1.sku_frequency as sku_a_frequency
      , top2.sku_frequency as sku_b_frequency
FROM (
        SELECT sku1.sku as sku_a
        , sku2.sku as sku_b
        , count(*) as joint_frequency
        FROM order_sku as sku1
        JOIN order_sku as sku2 ON sku1.order_id = sku2.order_id
        GROUP BY sku_a, sku_b
      ) as prop
JOIN total_order_sku as top1 ON prop.sku_a = top1.sku
JOIN total_order_sku as top2 ON prop.sku_b = top2.sku
ORDER BY sku_a, joint_frequency DESC, sku_b ;;
  }

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

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

  measure: joint_frequency {
    type: sum
    hidden: yes
    sql: ${TABLE}.joint_frequency ;;
  }

  measure: sku_a_frequency {
    type: sum
    hidden: yes
    sql: ${TABLE}.sku_a_frequency ;;
  }

  measure: sku_b_frequency {
    type: sum
    hidden: yes
    sql: ${TABLE}.sku_b_frequency ;;
  }

  measure: affinity {
    type: number
    value_format: "0.0%"
    sql: ${joint_frequency} / (${sku_a_frequency} + ${sku_b_frequency} - ${joint_frequency}) * 1.0 ;;
  }
}

- view: sku_recommendations
  derived_table:
    sql: WITH order_sku as (
SELECT
  orders.id AS "order_id",
  variants.sku AS "sku"
FROM store_line_items AS line_items
LEFT JOIN store_orders AS orders ON line_items.order_id = orders.id
LEFT JOIN store_variants AS variants ON line_items.variant_id = variants.id
GROUP BY orders.id, variants.sku
),
total_order_sku as (
SELECT
  variants.sku as "sku",
  count(distinct variants.sku || ' ' || orders.id) as sku_frequency
FROM store_line_items AS line_items
LEFT JOIN store_orders AS orders ON line_items.order_id = orders.id
LEFT JOIN store_variants AS variants ON line_items.variant_id = variants.id
GROUP BY variants.sku
)

SELECT sku_a
      , sku_b
      , joint_frequency
      , top1.sku_frequency as sku_a_frequency
      , top2.sku_frequency as sku_b_frequency
FROM (
        SELECT sku1.sku as sku_a
        , sku2.sku as sku_b
        , count(*) as joint_frequency
        FROM order_sku as sku1
        JOIN order_sku as sku2 ON sku1.order_id = sku2.order_id
        GROUP BY sku_a, sku_b
      ) as prop
JOIN total_order_sku as top1 ON prop.sku_a = top1.sku
JOIN total_order_sku as top2 ON prop.sku_b = top2.sku
ORDER BY sku_a, joint_frequency DESC, sku_b
  fields:
  - dimension: sku_a
    sql: ${TABLE}.sku_a
  - dimension: sku_b
    sql: ${TABLE}.sku_b
  - measure: joint_frequency
    type: sum
    sql: ${TABLE}.joint_frequency
  - measure: sku_a_frequency
    type: sum
    sql: ${TABLE}.sku_a_frequency
  - measure: sku_b_frequency
    type: sum
    sql: ${TABLE}.sku_b_frequency
  - measure: affinity
    type: number
    value_format: '0.0%'
    sql: ${joint_frequency} / (${sku_a_frequency} + ${sku_b_frequency} - ${joint_frequency}) * 1.0