order_items(order_id, product_id, revenue)

Find the three distinct products that appear together in the same order most often. A triple counts once per order however many line items it has, and {1,2,3} is the same triple as {3,2,1}.

Return product_a, product_b, product_c with the ids ascending across the row, and order_count. Return every triple tied for the top count, ordered by the three ids.

Enforcing a < b < c in the self join is what stops the same triple being counted six times in six different orders.

Tables

order_items

order_id  product_id  revenue
--------  ----------  -------
1         1           10.00
1         2           10.00
1         2           5.00
1         3           10.00
2         1           10.00
2         2           10.00
2         3           10.00
3         4           10.00
... 7 more row(s)

Expected result

product_a  product_b  product_c  order_count
---------  ---------  ---------  -----------
1          2          3          2
4          5          6          2