Product Recommendations

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

WITH order_product as (
SELECT
  orders.id AS "order_id",
  products.name AS "product"
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
LEFT JOIN store_products AS products ON variants.product_id = products.id
GROUP BY orders.id, products.name
),
total_order_product as (
SELECT
  products.name  as "product",
  count(distinct products.name  || ' ' || orders.id) as product_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
LEFT JOIN store_products AS products ON variants.product_id = products.id
GROUP BY products.name
)

SELECT product_a
      , product_b
      , joint_frequency
      , top1.product_frequency as product_a_frequency
      , top2.product_frequency as product_b_frequency
FROM (
        SELECT product1.product as product_a
        , product2.product as product_b
        , count(*) as joint_frequency
        FROM order_product as product1
        JOIN order_product as product2 ON product1.order_id = product2.order_id
        GROUP BY product_a, product_b
      ) as prop
JOIN total_order_product as top1 ON prop.product_a = top1.product
JOIN total_order_product as top2 ON prop.product_b = top2.product
ORDER BY product_a, joint_frequency DESC, product_b

# models/schema.yml
version: 2

models:
  - name: product_recommendations
    columns:
      - name: product_a
      - name: product_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_product as (
SELECT
  orders.id AS "order_id",
  products.name AS "product"
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
LEFT JOIN store_products AS products ON variants.product_id = products.id
GROUP BY orders.id, products.name
),
total_order_product as (
SELECT
  products.name  as "product",
  count(distinct products.name  || ' ' || orders.id) as product_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
LEFT JOIN store_products AS products ON variants.product_id = products.id
GROUP BY products.name
)

SELECT product_a
      , product_b
      , joint_frequency
      , top1.product_frequency as product_a_frequency
      , top2.product_frequency as product_b_frequency
FROM (
        SELECT product1.product as product_a
        , product2.product as product_b
        , count(*) as joint_frequency
        FROM order_product as product1
        JOIN order_product as product2 ON product1.order_id = product2.order_id
        GROUP BY product_a, product_b
      ) as prop
JOIN total_order_product as top1 ON prop.product_a = top1.product
JOIN total_order_product as top2 ON prop.product_b = top2.product
ORDER BY product_a, joint_frequency DESC, product_b

view: product_recommendations {
  derived_table: {
    sql: WITH order_product as (
SELECT
  orders.id AS "order_id",
  products.name AS "product"
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
LEFT JOIN store_products AS products ON variants.product_id = products.id
GROUP BY orders.id, products.name
),
total_order_product as (
SELECT
  products.name  as "product",
  count(distinct products.name  || ' ' || orders.id) as product_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
LEFT JOIN store_products AS products ON variants.product_id = products.id
GROUP BY products.name
)

SELECT product_a
      , product_b
      , joint_frequency
      , top1.product_frequency as product_a_frequency
      , top2.product_frequency as product_b_frequency
FROM (
        SELECT product1.product as product_a
        , product2.product as product_b
        , count(*) as joint_frequency
        FROM order_product as product1
        JOIN order_product as product2 ON product1.order_id = product2.order_id
        GROUP BY product_a, product_b
      ) as prop
JOIN total_order_product as top1 ON prop.product_a = top1.product
JOIN total_order_product as top2 ON prop.product_b = top2.product
ORDER BY product_a, joint_frequency DESC, product_b ;;
  }

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

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

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

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

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

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

- view: product_recommendations
  derived_table:
    sql: WITH order_product as (
SELECT
  orders.id AS "order_id",
  products.name AS "product"
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
LEFT JOIN store_products AS products ON variants.product_id = products.id
GROUP BY orders.id, products.name
),
total_order_product as (
SELECT
  products.name  as "product",
  count(distinct products.name  || ' ' || orders.id) as product_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
LEFT JOIN store_products AS products ON variants.product_id = products.id
GROUP BY products.name
)

SELECT product_a
      , product_b
      , joint_frequency
      , top1.product_frequency as product_a_frequency
      , top2.product_frequency as product_b_frequency
FROM (
        SELECT product1.product as product_a
        , product2.product as product_b
        , count(*) as joint_frequency
        FROM order_product as product1
        JOIN order_product as product2 ON product1.order_id = product2.order_id
        GROUP BY product_a, product_b
      ) as prop
JOIN total_order_product as top1 ON prop.product_a = top1.product
JOIN total_order_product as top2 ON prop.product_b = top2.product
ORDER BY product_a, joint_frequency DESC, product_b
  fields:
  - dimension: product_a
    sql: ${TABLE}.product_a
  - dimension: product_b
    sql: ${TABLE}.product_b
  - measure: joint_frequency
    type: sum
    sql: ${TABLE}.joint_frequency
  - measure: product_a_frequency
    type: sum
    sql: ${TABLE}.product_a_frequency
  - measure: product_b_frequency
    type: sum
    sql: ${TABLE}.product_b_frequency
  - measure: affinity
    type: number
    value_format: '0.0%'
    sql: ${joint_frequency} / (${product_a_frequency} + ${product_b_frequency} - ${joint_frequency}) * 1.0