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;