oracle sql find overlapping values between two sets

I have two tables (‘stock’, ‘website’) have the same structure both has one called ‘product_id’ I want to find out in what percentage the product_id in the ‘website’ table also in ‘stock’ table

Did not find an easy way to achieve. Hope to get some suggestion. tried INTERSECT operator

create table stock (product_id, update) as
(
  select 123, ‘201904’ from dual
  union all select 223, ‘201904’ from dual
  union all select 234, ‘201904’ from dual
  union all select 124, ‘201904’  from dual
  union all select 321, ‘201904’  from dual
  union all select 455, ‘201904’  from dual
 union all select 412, ‘201904’ from dual
);


create table website (product_id, update) as
(
  select 113, ‘201904’ from dual
  union all select 223, ‘201904’ from dual
  union all select 222, ‘201904’ from dual
  union all select 324, ‘201904’  from dual
  union all select 321, ‘201904’  from dual
  union all select 456, ‘201904’  from dual
 union all select 411, ‘201904’ from dual
);

Answer

You can achieve this by using an outer join and then counting the product ids in each table, and then working out the percentage, e.g.:

WITH stock AS (select 123 product_id, '201904' update_dt from dual UNION ALL
               select 223, '201904' from dual UNION ALL
               select 234, '201904' from dual UNION ALL
               select 124, '201904' from dual UNION ALL
               select 321, '201904' from dual UNION ALL
               select 455, '201904' from dual UNION ALL
               select 412, '201904' from dual),
   website AS (select 113 product_id, '201904' update_dt from dual UNION ALL
               select 223, '201904' from dual UNION ALL
               select 222, '201904' from dual UNION ALL
               select 324, '201904' from dual UNION ALL
               select 321, '201904' from dual UNION ALL
               select 456, '201904' from dual UNION ALL
               select 411, '201904' from dual)
SELECT round(100 * COUNT(s.product_id) / COUNT(w.product_id), 2) website_in_stock_percentage
FROM   website w
       LEFT OUTER JOIN stock s ON w.product_id = s.product_id;

WEBSITE_IN_STOCK_PERCENTAGE
---------------------------
                      28.57