I have two queries in which I would like to find their common values. I'm trying to ultimately find out what percentage of users have visited both webpages.
SELECT DISTINCT user_id
FROM table
WHERE url ='y'
ORDER BY user_id;
SELECT DISTINCT user_id
FROM table
WHERE url ='z'
ORDER BY user_id;
I've tried a
NOT IN
and a
UNION
but haven't had much luck - though I could easily be doing it wrong. I'm new.
One method is to use conditional aggregation. To get information for each user:
select user_id,
sum(url = 'y') as y_visits,
sum(url = 'z') as z_visits
from t
group by user_id;
To get the list of users, add a having clause:
having y_visits >= 1 and z_visits >- 1
To get summary information:
select y_visitor, z_visitor, count(*)
from (select user_id,
max(url = 'y') as y_visitor,
max(url = 'z') as z_visitor
from t
group by user_id
) yz
group by y_visitor, z_visitor;
To get a simple percentage:
select avg(y_visitor = 1 and z_visitor = 1) as p_VisitedBothYandZ
from (select user_id,
max(url = 'y') as y_visitor,
max(url = 'z') as z_visitor
from t
group by url
) yz;
