Shapley Value Functions Example 2 Step 1 - Teradata Vantage

Step 1 uses nPath to create conversion counts.

CREATE MULTISET TABLE attrconv AS (
  SELECT (
    IND_EMLOP || IND_CLKNI || IND_CLKIN || IND_CLKAC || IND_CLKCO) AS IND,
    num_conv FROM (
      SELECT CASE WHEN num_EMLOP > 0 THEN '1' ELSE '0' END AS IND_EMLOP
        , CASE WHEN num_CLKNI > 0 THEN '1' ELSE '0' END AS IND_CLKNI
        , CASE WHEN num_CLKIN > 0 THEN '1' ELSE '0' END AS IND_CLKIN
        , CASE WHEN num_CLKAC > 0 THEN '1' ELSE '0' END AS IND_CLKAC
        , CASE WHEN num_CLKCO > 0 THEN '1' ELSE '0' END AS IND_CLKCO
        , COUNT (conversion_evt) AS num_conv
      FROM nPath (
        ON attrib PARTITION BY ind ORDER BY ts
        USING
        Mode (nonoverlapping)
        Pattern ('(B|C|D|E|F)+.(act|ape|aps|apu|fun|rea){1}')
        Symbols (
          int_type = 'EMLOP' AS B,
          int_type = 'CLKNI' AS C,
          int_type = 'CLKIN' AS D,
          int_type = 'CLKAC' AS E,
          int_type = 'CLKCO' AS F,
          int_type = 'ACTVD' AS act,
          int_type = 'APEAP' AS ape,
          int_type = 'APEST' AS aps,
          int_type = 'APESU' AS apu,
          int_type = 'FUNDD' AS fun,
          int_type = 'REACT' AS rea
        )
        Result (
          FIRST (ind of ANY(B,C,D,E,F)) AS ind,
          COUNT (int_type of B) AS num_EMLOP,
          COUNT (int_type of C) AS num_CLKNI,
          COUNT (int_type of D) AS num_CLKIN,
          COUNT (int_type of E) AS num_CLKAC,
          COUNT (int_type of F) AS num_CLKCO,
          LAST_NOTNULL (int_type OF ANY (act,ape,aps,apu,fun,rea)) AS conversion_evt,
          ACCUMULATE (int_type OF ANY (B,C,D,E,F)
        ) AS intractn_path
      )
    ) AS dt GROUP BY 1,2,3,4,5
  ) a
) WITH DATA;

This query returns the following table:

SELECT * FROM attrconv ORDER BY ind;