select 	x2.post_prod as alternate, -- RHS as label
	x1.pre_prod, x2.post_prod, x1.ruleid, 
	x1.pre_prod || ' => '|| x2.post_prod as relationship,
	array_to_string(x1.pre,',') as pre, array_to_string(x1.post,',') as post,
	1 as pathorder, -- filtering attribute
	x1.support, x1.confidence, x1.lift, x1. conviction, x1.rank_support,
	x1.rank_confidence, x1.rank_lift, x1.rank_conviction,
	length(array_to_string(x1.pre,',')) - length(replace(array_to_string(x1.pre,','),',','')) +1 as pre_length,
	length(array_to_string(x1.post,',')) - length(replace(array_to_string(x1.post,','),',','')) +1 as post_length
from
(select a.prod as pre_prod, pre.* from
	(select distinct product as prod from groceries_data) a
INNER JOIN (select ar.*, rank() over (order by ar.support desc) as rank_support,
	rank() over (order by ar.confidence desc) as rank_confidence,
	rank() over (order by ar.lift desc) as rank_lift,
	rank() over (order by ar.conviction) as rank_conviction
from ar_groceries_results.assoc_rules ar) pre ON strpos(array_to_string(pre.pre,','), a.prod) > 0) x1,
(select a.prod as post_prod, post.* from
	(select distinct product as prod from groceries_data) a
INNER JOIN ar_groceries_results.assoc_rules post ON strpos(array_to_string(post.post,','), a.prod) > 0) x2
where x1.ruleid = x2.ruleid
UNION -- add the dupplicated entries
select 	x1.pre_prod as alternate, -- LHS as label
	x1.pre_prod, x2.post_prod, x1.ruleid, 
	x1.pre_prod || ' => '|| x2.post_prod as relationship,
        array_to_string(x1.pre,',') as pre, array_to_string(x1.post,',') as post,
	2 as pathorder, -- filtering attribute
	x1.support, x1.confidence, x1.lift, x1. conviction, x1.rank_support,
	x1.rank_confidence, x1.rank_lift, x1.rank_conviction,
	length(array_to_string(x1.pre,',')) - length(replace(array_to_string(x1.pre,','),',','')) +1 as pre_length,
	length(array_to_string(x1.post,',')) - length(replace(array_to_string(x1.post,','),',','')) +1 as post_length
from
(select a.prod as pre_prod, pre.* from
	(select distinct product as prod from groceries_data) a
INNER JOIN (select ar.*, rank() over (order by ar.support desc) as rank_support,
	rank() over (order by ar.confidence desc) as rank_confidence,
	rank() over (order by ar.lift desc) as rank_lift,
	rank() over (order by ar.conviction) as rank_conviction
from ar_groceries_results.assoc_rules ar) pre ON strpos(array_to_string(pre.pre,','), a.prod) > 0) x1,
(select a.prod as post_prod, post.* from
	(select distinct product as prod from groceries_data) a
INNER JOIN ar_groceries_results.assoc_rules post ON strpos(array_to_string(post.post,','), a.prod) > 0) x2
where x1.ruleid = x2.ruleid