Search code examples
mysqlcountgroup-by

MySQL select count for each user if greater than 3

I have the following MySQL table (the real table is actually very huge, about 2 million rows):

----------------------------
userId | artistId | trackId
----------------------------
user1  | artist1  | track1
-------|----------|--------
user1  | artist1  | track1
-------|----------|--------
user1  | artist1  | track1
-------|----------|--------
user2  | artist1  | track1
-------|----------|--------
user2  | artist2  | track2
-------|----------|--------
user2  | artist2  | track2
-------|----------|--------
 ....  |   ....   |  ....

What I would like to is: for each user, select artists that users listened more than 3 different tracks of them (i.e., 3 tracks of the same artist). This is because I need to consider this selection as users preferences on artists, so if e.g, a user listened only to 1 or two tracks of an artist, I don't want to consider it as "preferences/likes".

Here is the query that I wrote but I am not sure if this is correct:

select p.userId, p.artistId, p.trackId 
from lastfm_part2 p 
join 
(select userId, artistId, trackId 
from lastfm_part2 
group by userId, artistId, trackId 
having count(trackId) > 3) as m 
on m.userId = p.userId and m.artistId = p.artistId and p.trackID = m.trackId

PS. I need to return all the rows, even though they may seem to be duplicates (same user, same track, same artist), but in reality they are related to different time stamps. Is this query correct?

Solution

  • I tested it in Oracle so maybe MySQL is a little different but the next query did work for me. 

    SELECT p.userId, p.artistId, COUNT( DISTINCT p.trackId )
FROM lastfm_part2
group by userId, artistId
having count( DISTINCT p.trackId ) > 3;