Assistance to fix query to identify recursive relationships in data in SQL DW

Question

I have a table which has hierarchical relationships.

My input data is

product_identifier  parent_product_identifier  Zone
------------------  -------------------------  ----
1                   5                          E
2                   6                          F
3                   7                          G
4                   8                          H
5                   11                         R 
6                   12                         B
7                   13                         C
8                   14                         D
11                  15                         A

And expected output is :

product  parent_product_identifier  hierarchy  zone
-------  -------------------------  ---------  ----
1        5                          3          A
2        6                          2          B
3        7                          2          C
4        8                          2          D 
5        11                         2          A

I did try this approach :

with parent as (
  select
    product_identifier,
    parent_product_identifier,
    Zone,
    1 AS hierarchy,
  from
    temp
),
child as (
  select
    product_identifier,
    parent_product_identifier,
    Zone,
    p.hierarchy + 1,
  from
    temp c
  inner join
    parent p
  on
    c.parent_product_identifier = p.product_identifier
  and zone is not null
)
  select
    product_identifier,
    parent_product_identifier,
    Zone,
    hierarchy
  from
    parent
  union all
    select
    product_identifier,
    parent_product_identifier,
    Zone,
    hierarchy
  from
    child

But i am unable to achieve hierarchy of 3 how can i fix my query?

Note : my version of SQL DW does not support recursion. Is there any other way to do the same.

Answer 1

That query was a beast. There probably is a better way.

• DBFiddle: https://dbfiddle.uk/jSdYcwWM

WITH dt AS (
  --add the root potion of the recursion
  SELECT
    temp.product_identifier,
    temp.parent_product_identifier,
    temp.Zone,
    1 AS hierarchy,
    parent_product_identifier AS current_parent --used to keep track as we walk the parents
  FROM temp

  UNION ALL

  SELECT
     dt.product_identifier, 
     dt.parent_product_identifier, 
     temp.Zone, 
     dt.hierarchy+1, --increase by one as we walk up the chain
     temp.parent_product_identifier AS current_parent
  FROM dt
     INNER JOIN temp
     ON temp.product_identifier = dt.current_parent
)
SELECT 
  product_identifier,
  parent_product_identifier,
  hierarchy,
  Zone
FROM dt
WHERE hierarchy > 1                                      --exclude rows that don't have a parent present
AND hierarchy = (
   SELECT MAX(hierarchy) FROM dt dt2 
   WHERE dt2.product_identifier = dt.product_identifier) --exclude rows we used along the way to build the recursion
ORDER BY product_identifier;