Keeping tally of users who meet more than one condition

I have a database of users (represented by visitorId) who belong to a specific channel and exhibited certain behaviors, as logged under the eCommerceActionType field.

channel	visitorId	eCommerceActionType
Social	1	page_view
Social	1	added_to_cart
Referral	2	added_to_cart
Referral	2	purchased
Social	3	page_view
Social	3	added_to_cart
Social	3	purchased
Direct	4	page_view
Direct	4	added_to_cart

I want to output a table that keeps a tally of users who both “added_to_cart” and “purchased” by channel. So the result should look like:

Channel	cart_and_purchase
Social	1
Referral	1
Direct	0

What is the most efficient query to produce this table?

Appreciate any help I can get.

Answer

You can use two levels of aggregation:

select channel,
       sum(case when num_added_to_cart > 0 and num_purchased > 0 then 1 else 0 end) as cart_and_purchase
from (select channel, visitorid,
             sum(case when eCommerceActionType = 'added_to_cart' then 1 else 0 end) as num_added_to_cart,
             sum(case when eCommerceActionType = 'purchased' then 1 else 0 end) as num_purchased
      from t
      group by channel, visitorid
     ) t
group by channel;