SQLize | PHPize | SQLtest

WITH t1 AS (SELECT order_id, product_ids FROM orders WHERE order_id IN (SELECT order_id FROM user_actions WHERE order_id NOT IN (SELECT order_id FROM user_actions WHERE action = 'cancel_order'))), t2 AS (SELECT DISTINCT order_id, UNNEST(product_ids) AS product_id FROM t1), t3 AS (SELECT order_id, name FROM t2 LEFT JOIN products USING (product_id)), t4 AS (SELECT order_id, A.name AS name_1, B.name AS name_2 FROM t3 AS A INNER JOIN t3 AS B USING (order_id) WHERE A.name != B.name), t5 AS (SELECT order_id, CASE WHEN name_1 > name_2 THEN STRING_TO_ARRAY(name_2 ', ' name_1, ', ') ELSE STRING_TO_ARRAY(name_1 ', ' name_2, ', ') END AS pair FROM t4) SELECT pair, COUNT(order_id) / 2 AS count_pair FROM t5 GROUP BY pair ORDER BY count_pair DESC, pair ASC