CREATE TABLE atrbtn_old_dr_pth_noprsnt_conv
DISTRIBUTE BY hash(num_conv) COMPRESS low 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 atrbtn_table_old_direct_noprsnt
PARTITION BY indiv_prod_id
ORDER BY mat_intractn_dt_ts
MODE (nonoverlapping)
PATTERN ('(B|C|D|E|F)+.(act|ape|aps|apu|fun|rea){1}')
SYMBOLS (mat_intractn_typ_cd = 'EMLOP' AS B,
mat_intractn_typ_cd = 'CLKNI' AS C,
mat_intractn_typ_cd = 'CLKIN' AS D,
mat_intractn_typ_cd = 'CLKAC' AS E,
mat_intractn_typ_cd = 'CLKCO' AS F,
mat_intractn_typ_cd = 'ACTVD' AS act,
mat_intractn_typ_cd = 'APEAP' AS ape,
mat_intractn_typ_cd = 'APEST' AS aps,
mat_intractn_typ_cd = 'APESU' AS apu,
mat_intractn_typ_cd = 'FUNDD' AS fun,
mat_intractn_typ_cd = 'REACT' AS rea)
RESULT (first(indiv_prod_id of ANY(B,C,D,E,F)) AS indiv_prod_id,
count(mat_intractn_typ_cd of B) AS num_EMLOP,
count(mat_intractn_typ_cd of C) AS num_CLKNI,
count(mat_intractn_typ_cd of D) AS num_CLKIN,
count(mat_intractn_typ_cd of E) AS num_CLKAC,
count(mat_intractn_typ_cd of F) AS num_CLKCO,
LAST_NOTNULL(mat_intractn_typ_cd OF ANY(act,ape,aps,apu,fun,rea))
AS conversion_evt,
ACCUMULATE (mat_intractn_typ_cd OF ANY(B,C,D,E,F)) AS intractn_path)
) GROUP BY 1,2,3,4,5) a;