Find out if 2 orders have the same exact items or not

I’m using bigquery and I want to find out if a store have the exact items between their 2 orders. I have manage to make two tables that shows their last order and their second last order. Now I’m stuck at how to join them to return number of store that have the exact same items between their 2 orders.

Last Order Table enter image description here

Second Last Order Table enter image description here

From those 2 tables, the result that I want is to compare how many stores have exact same items and how many store have different items between their orders

  • ‘number of store with exact same items= 1’ -> only store_id = 2 have the same exact items in their 2 orders
  • ‘number of store with different items= 2’ -> store_id = 1 and store_id = 3 have different items between their 2 orders

What I’ve tried so far, I try to join on store_id and item_id but it still return all of the store because they have at least 1 same item between their orders, what I want is if doesn’t have the exact same item it should not be counted

 

with a as (
select distinct store_id, order_date, catalogue_id 
from order_table
order by 2 desc ), 

b as ( 
select *, 
dense_rank() over (partition by store_id order by order_date desc) as rank_order 
from a), 

c as ( 
select * from
b where rank_order = 1 ), 

d as (
select * from 
b where rank_order = 2 ) 

select distinct c.store_id, c.catalogue_id 
from c join d on c.store_id = d.store_id and c.catalogue_id = d.catalogue_id

 

Any help would be greatly appreciated, thank you