SELECT to pick users who both viewed a page

Question

I have a table that logs page views of each user:

+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| view_id      | int(11)      | NO   | PRI | NULL    | auto_increment | 
| page_id      | int(11)      | YES  | MUL | NULL    |                | 
| user_id      | int(11)      | YES  | MUL | NULL    |                | 
+--------------+--------------+------+-----+---------+----------------+

For every pair of users, I would like to generate a count of how many pages they have both looked at.

I simply do not know how to do this. : ) I am using mysql, in case it has a non-standard feature that makes this a breeze.

Answer 1

SELECT DISTINCT page_id
FROM logtable
WHERE user_id = 1 OR user_id = 2
GROUP BY page_id
HAVING COUNT(DISTINCT user_id) = 2

This table returns all pages they both have looked at. If you want the count, then just make this a subquery and count the rows.

SELECT COUNT(*) FROM (the query above) s;

Update, let's do it for all pairs of users then.

SELECT u1.user_id, u2.user_id, COUNT(DISTINCT u1.page_id)
FROM logtable u1, logtable u2
WHERE u1.user_id < u2.user_id
  AND u1.page_id = u2.page_id
GROUP BY u1.user_id, u2.user_id

Answer 2

select u1.user_id, u2.user_id, count(distinct u1.page_id) as NumPages
from logtable u1
  join
  logtable u2
  on u1.page_id = u2.page_id
  and u1.user_id < u2.user_id /* This avoids counting pairs twice */
group by u1.user_id, u2.user_id;

But you should consider filtering this somewhat...

(Edited above to put u1.page_id, it was originally just page_id, which is really bad of me)

Answer 3

select a.user_id as user1, b.user_id as user2, count(distinct a.page_id) as views
from yourtable a, yourtable b
where a.page_id = b.page_id 
and a.user_id < b.user_id
group by a.user_id, b.user_id

change yourtable to the name of your table ..

Answer 4

For users_ids 100 and 200.

SELECT
  page_id
FROM table1
WHERE user_id IN (100, 200)
GROUP BY page_id
HAVING MAX(CASE WHEN user_id = 100 THEN 1 ELSE 0 END) = 1
  AND MAX(CASE WHEN user_id = 200 THEN 1 ELSE 0 END) = 1;