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. I appreciate if someone help me understand if this query is correct.
Thanks
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;
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments