Find users who have visited URL A and B with SQL?

Question

Let's say you have a table with user, url, datetime, in which each row is a website visit.

How to find users who have visited both an URL contaning string pattern A and visited an URL containing string pattern B?

The fact it's "containing a string pattern ...", and not a simple equality makes it impossible to use a query with something like

url in ('action1.php', 'action2.php')

like in SQL querying a customer ID who ordered both product A and B.

Answer 1

You can use group by and having:

select user
from t
where url like '%a%' or
      url like '%b%'
group by user
having sum(url like '%a%') > 0 and
       sum(url like '%b%') > 0;

If you don't want to repeat the comparisons, you can leave out the where clause or use:

select user
from (select t.*, (url like '%a%') as has_a, (url like '%n%') as has_b
      from t
     ) t
where has_a or has_b
group by user
having sum(has_a) > 0 and
       sum(has_b) > 0;

Answer 2

Assuming "/testing" and "/staging" are the two URL patterns. You can use this

SELECT   user
FROM     `table`
WHERE    url LIKE '%/testing%' or
         url LIKE '%/staging%'
GROUP BY user
HAVING   (COUNT(url LIKE '%/testing%') > 0 and COUNT(url LIKE '%/staging%') > 0)

If you need more info on pattern matching you can do a search on "pattern matching SQL" and "SQL regular expression".