title: Joining two tables to fetch count
body: <p>I've two tables with the following schema</p>
  
  <pre><code>GROUP_ID | PURCHASE_ID |ITEMS  ---&gt; TABLE1
    1            21         X
    1            21         Y
    1            21         Z
    2            22         X
  
  
  GROUP_ID  |CUSTOMER_ID |ITEMS  ---&gt;TABLE2
  1             ABC        X
  1             ABC        Y
  1             ABC        Z
  1             ABC        A
  1             ABC        B
  </code></pre>
  
  <p>A single GROUP_ID and PURCHASE_ID can have multiple items and similarly a single GROUP_ID and CUSTOMER_ID can have multiple ITEMS.The number of items bought will be only two or three per GROUP_ID and per PURCHASE_ID but a given CUSTOMER_ID,GROUP_ID can have a n number of items.</p>
  
  <p>I want to query for each GROUP_ID and PURCHASE_ID and ITEMS[set], how many customers have bought at least all the ITEMS.</p>
  
  <pre><code>select distinct GROUP_ID,PURCHASE_ID,count(object_id)over(partition by GROUP_ID,PURCHASE_ID) from 
          (select a.GROUP_ID GROUP_ID,a.PURCHASE_ID PURCHASE_ID,b.CUSTOMER_ID object_id from      
          (select GROUP_ID,PURCHASE_ID,items,count(items)over(partition by GROUP_ID,PURCHASE_ID) val from  TABLE1)a,
          (select GROUP_ID,CUSTOMER_ID,ITEMS from TABLE2)b
          where a.GROUP_ID=b.GROUP_ID and a.items=b.ITEMS and val=3
          group by a.GROUP_ID,a.PURCHASE_ID,b.CUSTOMER_ID
          having count(*)=3)
  </code></pre>
  
  <p>For the above given data for GROUP_ID=1 and PURCHASE_ID=21 the count should be 1 since there exists a customer with ID ABC who has the subset of items [X,Y,Z]
  I've written code to fetch the count of customers for three items as above. Is there any way to optimize this or achieve this.</p>
  
  <p>Any help is much appreciated</p>