Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version


PostgreSQL 14.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-20), 64-bit



SELECT 1


CREATE TABLE T (
  order_id Integer PRIMARY KEY,
  items_current_order Integer[],
  items_next_order Integer[]
);

INSERT INTO T(order_id, items_current_order, items_next_order) VALUES (4254677, '{1, 1, 1}', '{1, 1}');
INSERT INTO T(order_id, items_current_order, items_next_order) VALUES (4254645, '{1,2,5,6}', '{1,1,6}');

CREATE TABLE



INSERT 0 1



INSERT 0 1


select 
     order_id
     , items_current_order
     , items_next_order
    , (SELECT 
    COUNT(case when arr=matches then matches end)
    FROM unnest(items_current_order) arr cross join unnest(items_next_order)  matches
    where arr is not null and matches is not null )
from T


order_id
items_current_order
items_next_order
count


4254677
{1,1,1}
{1,1}
6

4254645
{1,2,5,6}
{1,1,6}
3



SELECT 2


SELECT * 
FROM T
CROSS JOIN  unnest(items_next_order) as arr(t)


order_id
items_current_order
items_next_order
t


4254677
{1,1,1}
{1,1}
1

4254677
{1,1,1}
{1,1}
1

4254645
{1,2,5,6}
{1,1,6}
1

4254645
{1,2,5,6}
{1,1,6}
1

4254645
{1,2,5,6}
{1,1,6}
6



SELECT 5


SELECT DISTINCT order_id, arr.*
FROM T
CROSS JOIN unnest(T.items_next_order) arr


order_id
arr


4254677
1

4254645
6

4254645
1



SELECT 3


select 
     order_id
     , items_current_order
     , items_next_order
--    , (SELECT 
--    COUNT(case when arr=matches then matches end)
--    FROM unnest(items_current_order) arr cross join unnest(items_next_order)  matches
--    where arr is not null and matches is not null )
  , arr.*
  , matches.*
from T
CROSS JOIN unnest(items_current_order) arr
CROSS JOIN unnest(items_next_order)  matches
WHERE arr=matches


order_id
items_current_order
items_next_order
arr
matches


4254677
{1,1,1}
{1,1}
1
1

4254677
{1,1,1}
{1,1}
1
1

4254677
{1,1,1}
{1,1}
1
1

4254677
{1,1,1}
{1,1}
1
1

4254677
{1,1,1}
{1,1}
1
1

4254677
{1,1,1}
{1,1}
1
1

4254645
{1,2,5,6}
{1,1,6}
1
1

4254645
{1,2,5,6}
{1,1,6}
1
1

4254645
{1,2,5,6}
{1,1,6}
6
6



SELECT 9


select 
     T.order_id
     , items_current_order
     , items_next_order
  , arr.*
  , matches.*
from T
INNER JOIN (SELECT  order_id, arr.*
            FROM T
            CROSS JOIN unnest(T.items_current_order) arr) arr on arr.ordeR_id=T.order_id
INNER JOIN (SELECT DISTINCT order_id, arr.*
            FROM T
            CROSS JOIN unnest(T.items_next_order) arr)  matches on matches.ordeR_id=T.order_id
WHERE arr.arr=matches.arr


order_id
items_current_order
items_next_order
order_id
arr
order_id
arr


4254677
{1,1,1}
{1,1}
4254677
1
4254677
1

4254677
{1,1,1}
{1,1}
4254677
1
4254677
1

4254677
{1,1,1}
{1,1}
4254677
1
4254677
1

4254645
{1,2,5,6}
{1,1,6}
4254645
1
4254645
1

4254645
{1,2,5,6}
{1,1,6}
4254645
6
4254645
6



SELECT 5


select 
  T.order_id
  , items_current_order
  , items_next_order
  , COUNT(CASE WHEN arr.arr=matches.arr THEN 1 END)

from T
INNER JOIN (SELECT DISTINCT order_id, arr.*
            FROM T
            CROSS JOIN unnest(T.items_current_order) arr) arr on arr.ordeR_id=T.order_id
INNER JOIN (SELECT DISTINCT order_id, arr.*
            FROM T
            CROSS JOIN unnest(T.items_next_order) arr)  matches on matches.ordeR_id=T.order_id
WHERE arr.arr=matches.arr
GROUP BY
  T.order_id
  , items_current_order
  , items_next_order


order_id
items_current_order
items_next_order
count


4254645
{1,2,5,6}
{1,1,6}
2

4254677
{1,1,1}
{1,1}
1



SELECT 2
order_id	items_current_order	items_next_order	t
4254677	{1,1,1}	{1,1}	1
4254677	{1,1,1}	{1,1}	1
4254645	{1,2,5,6}	{1,1,6}	1
4254645	{1,2,5,6}	{1,1,6}	1
4254645	{1,2,5,6}	{1,1,6}	6