title: Count exclusive and shared product_ids for each merchant_id
body: <p>For each <code>merchant_id</code> I would like to count <code>product_id</code> that exist both in <code>store_a</code> and <code>store_b</code> which is then classified as a <code>'shared_product</code></p>
  
  <p>And count <code>product_id</code> that exist only in <code>store_a</code> or <code>store_b</code> respectively which is then classified as <code>'exclusive_product_storeA'</code> or <code>'exclusive_product_storeB'</code></p>
  
  <p>My query does not show  the <code>shared_product</code> , it only shows <code>'exclusive_product_storeA'</code> and <code>'exclusive_product_storeB'</code></p>
  
  <p>Please not there are only <strong>store_a</strong> and <strong>store_b</strong> but multiple <strong>merchant_id</strong>'s</p>
  
  <p><strong>Table</strong></p>
  
  <pre><code>merchant_id   product_id    store_id    product_status
  1             8328          store_a     new_product
  1             4234          store_a     new_product
  1             8328          store_b     new_product 
  1             4234          store_b     new_product
  1             5943          store_b     old_product
  
  2             1244          store_a     old_product
  2             1244          store_b     old_product
  2             2353          store_a     old_product 
  2             2353          store_b     old_product
  2             5943          store_a     new_product
  
  3             9838          store_a     old_product
  3             9838          store_b     old_product
  3             1244          store_a     old_product
  3             1244          store_b     old_product
  3             6544          store_a     old_product
  3             6544          store_b     old_product
  3             3443          store_a     old_product 
  
  
  
  </code></pre>
  
  <p><strong>Output table</strong></p>
  
  <pre><code>merchant_id    product_status   product_state              count_product
  1              new_product      shared_products             2
  1              old_product      exclusive_product_storeB    1
  
  2              old_product      shared_products             2
  2              new_product      exclusive_product_storeB    1
  
  3              old_product      shared_products             3
  3              old_product      exclusive_product_storeA    1
  
  
  
  </code></pre>
  
  <p><strong>Query</strong></p>
  
  <pre><code>select merchant_id, product_status, product_state, count(*) as cnt
  from (select merchant_id, product_id, product_status,
               (case when max(store_id) &lt;&gt; min(store_id) then 'shared'
                     when max(store_id) = 'store_a' then 'only store_a'
                     else 'only store_b'
                end) as product_state
        from t
        where store_id in ('store_a', 'store_b)
        group by merchant_id, product_id, product_status
       ) mp
  group by merchant_id, product_status, product_state;
  
  
  </code></pre>