Return photos of a tagged user in which other users have also been tagged a specific number of times

user_11567492 0
Viewed 31

I need to find photos in which a user has been tagged in together with other users ten or more times. One photo has many tags and a user belongs to a tag.

For example, John and Jane have been tagged together in ten photos. I need a query which will return those ten photos.

What I have so far:

      select count(*) tag_count, tags.user_id from tags
      join users on users.id = tags.user_id
      where tags.user_id != 1 and photo_id in
         (select photo_id from tags where user_id = 1)
      group by tags.user_id
      having count(tags.id) >= 10
      order by tag_count desc;

This returns the tag count of users tagged together in a photo with a specific user (ID: 1) at least ten times, but I am not sure how to adapt this query to get a list of the actual photo records.

Any suggestions would be appreciated.

postgresql sql
1 Answers

There may be a simpler solution in POSTGRESQL but in MSSQL you can user INNER JOIN to join tags on itself looking for the same photo and a different user. You can then use DENSE_RANK to count DISTINCT photos and filter for quantities >= 10.

SELECT user_id, photo_id
FROM (SELECT DISTINCT t1.user_id, t1.photo_id, DENSE_RANK() OVER (PARTITION BY t1.user_id ORDER BY t1.id) + DENSE_RANK() OVER (PARTITION BY t1.user_id ORDER BY t1.id DESC) - 1 AS tag_count
      FROM tags t1
      INNER JOIN tags t2 ON t2.photo_id = t1.photo_id AND t2.user_id != t1.user_id) tmp
WHERE tag_count >= 10
user_31940050
Related