Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE stores (
  id INT
);




INSERT INTO stores (id) VALUES (1);
INSERT INTO stores (id) VALUES (2);
INSERT INTO stores (id) VALUES (3);
INSERT INTO stores (id) VALUES (4);
INSERT INTO stores (id) VALUES (5);
1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

CREATE TABLE products (
  id INT,
  store_id INT,
  sku VARCHAR
);
INSERT INTO products (id, store_id, sku) VALUES (1, 1, 'abc1');
INSERT INTO products (id, store_id, sku) VALUES (2, 2, 'abc1');
INSERT INTO products (id, store_id, sku) VALUES (3, 2, 'abc2');
INSERT INTO products (id, store_id, sku) VALUES (4, 3, 'abc1');
INSERT INTO products (id, store_id, sku) VALUES (5, 3, 'abc2');
INSERT INTO products (id, store_id, sku) VALUES (5, 3, 'abc3');
INSERT INTO products (id, store_id, sku) VALUES (5, 3, 'abc4');
INSERT INTO products (id, store_id, sku) VALUES (5, 3, 'abc5');
INSERT INTO products (id, store_id, sku) VALUES (5, 4, 'abc1');
INSERT INTO products (id, store_id, sku) VALUES (5, 5, 'abc1');
1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

with data
  as (select store_id,count(sku) as tot_sku
       from products
       group by store_id
      )
select a.store_id as store_id
      ,b.store_id as other_store_id
      ,count(case when a.sku=b.sku then 1 end) as cnt_overlap 
      ,max(c.tot_sku) as tot_sku
      ,count(case when a.sku=b.sku then 1 end)*100.00/max(c.tot_sku) as pct_overlap
 from products a
 join products b
   on a.store_id <> b.store_id
 join data c
   on a.store_id=c.store_id
group by a.store_id,b.store_id
order by 1


store_id
other_store_id
cnt_overlap
tot_sku
pct_overlap


1
2
1
1
100.0000000000000000

1
3
1
1
100.0000000000000000

1
4
1
1
100.0000000000000000

1
5
1
1
100.0000000000000000

2
1
1
2
50.0000000000000000

2
3
2
2
100.0000000000000000

2
4
1
2
50.0000000000000000

2
5
1
2
50.0000000000000000

3
1
1
5
20.0000000000000000

3
2
2
5
40.0000000000000000

3
4
1
5
20.0000000000000000

3
5
1
5
20.0000000000000000

4
1
1
1
100.0000000000000000

4
2
1
1
100.0000000000000000

4
3
1
1
100.0000000000000000

4
5
1
1
100.0000000000000000

5
1
1
1
100.0000000000000000

5
2
1
1
100.0000000000000000

5
3
1
1
100.0000000000000000

5
4
1
1
100.0000000000000000

store_id	other_store_id	cnt_overlap	tot_sku	pct_overlap
1	2	1	1	100.0000000000000000
1	3	1	1	100.0000000000000000
1	4	1	1	100.0000000000000000
1	5	1	1	100.0000000000000000
2	1	1	2	50.0000000000000000
2	3	2	2	100.0000000000000000
2	4	1	2	50.0000000000000000
2	5	1	2	50.0000000000000000
3	1	1	5	20.0000000000000000
3	2	2	5	40.0000000000000000
3	4	1	5	20.0000000000000000
3	5	1	5	20.0000000000000000
4	1	1	1	100.0000000000000000
4	2	1	1	100.0000000000000000
4	3	1	1	100.0000000000000000
4	5	1	1	100.0000000000000000
5	1	1	1	100.0000000000000000
5	2	1	1	100.0000000000000000
5	3	1	1	100.0000000000000000
5	4	1	1	100.0000000000000000