dbt Electronics kids

Order Product Affinity

Order Product Affinity model for electronics kids.

inferred

Blocks

dbt SQL LKML LookML

order_product_affinity.sql · 61 lines

inferred

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

SELECT name_a
, name_b
, joint_frequency
, toc1.name_frequency as name_a_frequency
, toc2.name_frequency as name_b_frequency
FROM (
  SELECT oc1.name as name_a
  , oc2.name as name_b
  , count(*) as joint_frequency
  FROM  (SELECT o.id as order_id
              , oi.variant_id as item_id
              , p.name as name
              FROM spree_line_items oi
              JOIN spree_orders o ON o.id = oi.order_id
              JOIN spree_variants ii ON oi.variant_id = ii.id
              JOIN spree_products p ON ii.product_id = p.id
              GROUP BY order_id, name
              ) as oc1
  JOIN  (SELECT o.id as order_id
              , oi.variant_id as item_id
              , p.name as name
              FROM spree_line_items oi
              JOIN spree_orders o ON o.id = oi.order_id
              JOIN spree_variants ii ON oi.variant_id = ii.id
              JOIN spree_products p ON ii.product_id = p.id
              GROUP BY order_id, name
              ) AS oc2 ON oc1.order_id = oc2.order_id
  GROUP BY name_a, name_b
) as prop
JOIN (SELECT p.name as name
      , count(distinct p.name, o.id) as name_frequency
      FROM spree_line_items oi
      JOIN spree_orders o ON o.id = oi.order_id
      JOIN spree_variants ii ON oi.variant_id = ii.id
      JOIN spree_products p ON ii.product_id = p.id
      GROUP BY p.name
      ) AS toc1 ON prop.name_a = toc1.name
JOIN (SELECT p.name as name
      , count(distinct p.name, o.id) as name_frequency
      FROM spree_line_items oi
      JOIN spree_orders o ON o.id = oi.order_id
      JOIN spree_variants ii ON oi.variant_id = ii.id
      JOIN spree_products p ON ii.product_id = p.id
      GROUP BY p.name
      ) AS toc2 ON prop.name_b = toc2.name
ORDER BY name_a, joint_frequency DESC, name_b

# models/schema.yml
version: 2

models:
  - name: order_product_affinity
    columns:
      - name: product_name_a
      - name: product_name_b
      - name: joint_frequency
      - name: product_a_frequency
      - name: product_b_frequency

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.

order_product_affinity.sql · 46 lines

source-preserved validation: parses

SELECT name_a
, name_b
, joint_frequency
, toc1.name_frequency as name_a_frequency
, toc2.name_frequency as name_b_frequency
FROM (
  SELECT oc1.name as name_a
  , oc2.name as name_b
  , count(*) as joint_frequency
  FROM  (SELECT o.id as order_id
              , oi.variant_id as item_id
              , p.name as name
              FROM spree_line_items oi
              JOIN spree_orders o ON o.id = oi.order_id
              JOIN spree_variants ii ON oi.variant_id = ii.id
              JOIN spree_products p ON ii.product_id = p.id
              GROUP BY order_id, name
              ) as oc1
  JOIN  (SELECT o.id as order_id
              , oi.variant_id as item_id
              , p.name as name
              FROM spree_line_items oi
              JOIN spree_orders o ON o.id = oi.order_id
              JOIN spree_variants ii ON oi.variant_id = ii.id
              JOIN spree_products p ON ii.product_id = p.id
              GROUP BY order_id, name
              ) AS oc2 ON oc1.order_id = oc2.order_id
  GROUP BY name_a, name_b
) as prop
JOIN (SELECT p.name as name
      , count(distinct p.name, o.id) as name_frequency
      FROM spree_line_items oi
      JOIN spree_orders o ON o.id = oi.order_id
      JOIN spree_variants ii ON oi.variant_id = ii.id
      JOIN spree_products p ON ii.product_id = p.id
      GROUP BY p.name
      ) AS toc1 ON prop.name_a = toc1.name
JOIN (SELECT p.name as name
      , count(distinct p.name, o.id) as name_frequency
      FROM spree_line_items oi
      JOIN spree_orders o ON o.id = oi.order_id
      JOIN spree_variants ii ON oi.variant_id = ii.id
      JOIN spree_products p ON ii.product_id = p.id
      GROUP BY p.name
      ) AS toc2 ON prop.name_b = toc2.name
ORDER BY name_a, joint_frequency DESC, name_b

order_product_affinity.view.lkml · 78 lines

inferred

view: order_product_affinity {
  derived_table: {
    sql: SELECT name_a
, name_b
, joint_frequency
, toc1.name_frequency as name_a_frequency
, toc2.name_frequency as name_b_frequency
FROM (
  SELECT oc1.name as name_a
  , oc2.name as name_b
  , count(*) as joint_frequency
  FROM  (SELECT o.id as order_id
              , oi.variant_id as item_id
              , p.name as name
              FROM spree_line_items oi
              JOIN spree_orders o ON o.id = oi.order_id
              JOIN spree_variants ii ON oi.variant_id = ii.id
              JOIN spree_products p ON ii.product_id = p.id
              GROUP BY order_id, name
              ) as oc1
  JOIN  (SELECT o.id as order_id
              , oi.variant_id as item_id
              , p.name as name
              FROM spree_line_items oi
              JOIN spree_orders o ON o.id = oi.order_id
              JOIN spree_variants ii ON oi.variant_id = ii.id
              JOIN spree_products p ON ii.product_id = p.id
              GROUP BY order_id, name
              ) AS oc2 ON oc1.order_id = oc2.order_id
  GROUP BY name_a, name_b
) as prop
JOIN (SELECT p.name as name
      , count(distinct p.name, o.id) as name_frequency
      FROM spree_line_items oi
      JOIN spree_orders o ON o.id = oi.order_id
      JOIN spree_variants ii ON oi.variant_id = ii.id
      JOIN spree_products p ON ii.product_id = p.id
      GROUP BY p.name
      ) AS toc1 ON prop.name_a = toc1.name
JOIN (SELECT p.name as name
      , count(distinct p.name, o.id) as name_frequency
      FROM spree_line_items oi
      JOIN spree_orders o ON o.id = oi.order_id
      JOIN spree_variants ii ON oi.variant_id = ii.id
      JOIN spree_products p ON ii.product_id = p.id
      GROUP BY p.name
      ) AS toc2 ON prop.name_b = toc2.name
ORDER BY name_a, joint_frequency DESC, name_b ;;
  }

  dimension: product_name_a {
    sql: ${TABLE}.name_a ;;
  }

  dimension: product_name_b {
    sql: ${TABLE}.name_b ;;
  }

  dimension: joint_frequency {
    type: number
    sql: ${TABLE}.joint_frequency ;;
  }

  dimension: product_a_frequency {
    type: number
    sql: ${TABLE}.name_a_frequency ;;
  }

  dimension: product_b_frequency {
    type: number
    sql: ${TABLE}.name_b_frequency ;;
  }

  measure: product_affinity {
    type: number
    sql: ${joint_frequency}/(${product_a_frequency}+${product_b_frequency}-${joint_frequency})*100 ;;
  }
}

order_product_affinity.view.lookml · 65 lines

source-preserved

- view: order_product_affinity
  derived_table:
    sql: SELECT name_a
, name_b
, joint_frequency
, toc1.name_frequency as name_a_frequency
, toc2.name_frequency as name_b_frequency
FROM (
  SELECT oc1.name as name_a
  , oc2.name as name_b
  , count(*) as joint_frequency
  FROM  (SELECT o.id as order_id
              , oi.variant_id as item_id
              , p.name as name
              FROM spree_line_items oi
              JOIN spree_orders o ON o.id = oi.order_id
              JOIN spree_variants ii ON oi.variant_id = ii.id
              JOIN spree_products p ON ii.product_id = p.id
              GROUP BY order_id, name
              ) as oc1
  JOIN  (SELECT o.id as order_id
              , oi.variant_id as item_id
              , p.name as name
              FROM spree_line_items oi
              JOIN spree_orders o ON o.id = oi.order_id
              JOIN spree_variants ii ON oi.variant_id = ii.id
              JOIN spree_products p ON ii.product_id = p.id
              GROUP BY order_id, name
              ) AS oc2 ON oc1.order_id = oc2.order_id
  GROUP BY name_a, name_b
) as prop
JOIN (SELECT p.name as name
      , count(distinct p.name, o.id) as name_frequency
      FROM spree_line_items oi
      JOIN spree_orders o ON o.id = oi.order_id
      JOIN spree_variants ii ON oi.variant_id = ii.id
      JOIN spree_products p ON ii.product_id = p.id
      GROUP BY p.name
      ) AS toc1 ON prop.name_a = toc1.name
JOIN (SELECT p.name as name
      , count(distinct p.name, o.id) as name_frequency
      FROM spree_line_items oi
      JOIN spree_orders o ON o.id = oi.order_id
      JOIN spree_variants ii ON oi.variant_id = ii.id
      JOIN spree_products p ON ii.product_id = p.id
      GROUP BY p.name
      ) AS toc2 ON prop.name_b = toc2.name
ORDER BY name_a, joint_frequency DESC, name_b
  fields:
  - dimension: product_name_a
    sql: ${TABLE}.name_a
  - dimension: product_name_b
    sql: ${TABLE}.name_b
  - dimension: joint_frequency
    type: number
    sql: ${TABLE}.joint_frequency
  - dimension: product_a_frequency
    type: number
    sql: ${TABLE}.name_a_frequency
  - dimension: product_b_frequency
    type: number
    sql: ${TABLE}.name_b_frequency
  - measure: product_affinity
    type: number
    sql: ${joint_frequency}/(${product_a_frequency}+${product_b_frequency}-${joint_frequency})*100

order_product_affinity.sql
order_product_affinity.sql
order_product_affinity.view.lkml
order_product_affinity.view.lookml

Order Product Affinity

Blocks

Assumptions

Contents