MySQL: Get number of users who bought product b AFTER they bought product a

Question

I'm struggling building a query selecting users who previously bought another product (which is not the same they bought now). I want to know how many users who bought product B, previously bought product A.

My table looks like this:

User ID | Product ID | Date 
      1 | B          | 2020/12/05
      2 | B          | 2020/12/04
      1 | A          | 2020/12/03
      3 | A          | 2020/12/03
      3 | B          | 2020/12/02
      4 | B          | 2020/12/02
      4 | B          | 2020/12/01

It should deliver as a result 1, because User 1 bought B after A. User 3 bought A after B and therefore doesn't count. User 4 bought only product B and therefor doesn't count.

Would be really glad if you can help!

Answer 1

You can use aggregation:

select count(*)
from (
    select user_id
    from mytable
    group by user_id
    having min(case when product_id = 'A' then date end)
         < max(case when product_id = 'B' then date end)
) t

An alternative uses a subquery and count(distinct):

select count(distinct user_id) 
from mytable t
where product_id = 'B' and exists (
    select 1 from mytable t1 where t1.user_id = t.user_id and t1.product_id = 'A' and t1.date < t.date
)