create_product_aggregation_dim_table.sql

CREATE OR REPLACE TABLE product_aggregation_dim
AS
WITH RECURSIVE parent_nodes (
    node_id
  , node_natural_key
  , node_name
  , level_name
  , parent_node_id
  , is_root
  , is_leaf
  , level_number
  , node_sort_order
  , node_json
  , node_json_path
    )
AS (
    -- Anchor Clause
    SELECT
        node_id
      , node_natural_key
      , node_name
      , level_name
      , parent_node_id
      , is_root
      , is_leaf
      , level_number
      , node_sort_order
      , node_json
      -- We must start a new NODE_JSON array b/c each node will be represented as a root node...
      , [node_json] AS node_json_path
       FROM product_reporting_dim
       -- We do NOT filter the anchor, because we want EVERY node in the hierarchy to be a root node...
    UNION ALL
    -- Recursive Clause
    SELECT
        nodes.node_id
      , nodes.node_natural_key
      , nodes.node_name
      , nodes.level_name
      , nodes.parent_node_id
      , nodes.is_root
      , nodes.is_leaf
      , nodes.level_number
      , nodes.node_sort_order
      , nodes.node_json
      , array_append (parent_nodes.node_json_path
                    , nodes.node_json
                     ) AS node_json_path
       FROM product_reporting_dim AS nodes
          JOIN
            parent_nodes
          ON nodes.parent_node_id = parent_nodes.node_id
)
SELECT -- Ancestor columns (we take the first array element to get the anchor root)
        node_json_path[1].node_id             AS ancestor_node_id
      , node_json_path[1].node_natural_key    AS ancestor_node_natural_key
      , node_json_path[1].node_name           AS ancestor_node_name
      , node_json_path[1].level_name          AS ancestor_level_name
      , node_json_path[1].level_number        AS ancestor_level_number
      , node_json_path[1].is_root             AS ancestor_is_root
      , node_json_path[1].is_leaf             AS ancestor_is_leaf
      , node_json_path[1].node_sort_order     AS ancestor_node_sort_order
      -- Descendant columns
      , node_id                               AS descendant_node_id
      , node_natural_key                      AS descendant_node_natural_key
      , node_name                             AS descendant_node_name
      , level_name                            AS descendant_level_name
      , level_number                          AS descendant_level_number
      , is_root                               AS descendant_is_root
      , is_leaf                               AS descendant_is_leaf
      , node_sort_order                       AS descendant_node_sort_order
      --
      , (level_number - node_json_path[1].level_number) AS net_level
  FROM parent_nodes
ORDER BY node_sort_order ASC;