CODEWITHSUNDEEP
neo4j
Fijn
Fijn

Reputation: 1

Neo4J - how to select records with max count

I struggle to write a query, that will return info about most played tracks for every user. I go with something like this:

match (l:Listener)-[lo:LOGS]->(s:Scrobble)-[f:FEATURES]->(t:Track)<-[p:PERFORMS]-(a:Artist) with l, a, count(*) as numberofScrobbles return l.name, a.title, numberofScrobbles

and get a list of values: User name - Artist name - Number of scrobbled tracks created by given artist.

My goal is to acquire most favorite artist for every user (artist with most scrobbles for each user). and it worth noting that for each other, more than one artist might with the most scrobbles. The closest i get is with this:

match (l:Listener)-[lo:LOGS]->(s:Scrobble)-[f:FEATURES]->(t:Track)<-[p:PERFORMS]-(a:Artist) with l,a,count(*) as numberofScrobbles return l.name, max(numberOfScrobbles)

which gives me number of tracks played by a favourite artist for given user, but how can I join proper artist's name to this result? and it worth mentioning that each user might have more than one favorite artists.

Any clues/tips? many thanks!

Upvotes: 0

Views: 128

Answers (1)

Nathan Smith
Nathan Smith

Reputation: 881

This would be my approach.

MATCH (l:Listener)-[lo:LOGS]->(s:Scrobble)-[f:FEATURES]->(t:Track)<-[p:PERFORMS]-(a:Artist)
WITH l, a, count(*) AS numberOfScrobbles
WITH l, numberOfScrobbles, collect(a.title) AS artists
ORDER BY numberOfScrobbles DESC
WITH l, collect(numberOfScrobbles) AS scrobbleCountList, collect(artists) AS artistsList
RETURN l.name as listnerName, 
scrobbleCountList[0] as highestScrobbleCount, 
artistsList[0] as artists

The second line counts the number of scrobbles for each listner-artist pair.

l        a                  numberOfScrobbles
Nathan   Lang Lang          4
Nathan   Martha Argerich    3
Nathan   Sviatoslav Richter 4

The third and fourth lines gather all the artists who had that number of Scrobbles for each listener sorted in descending order by scrobble count.

l       numberOfScrobbles artists
Nathan  4                 ["Lang Lang", "Sviatoslav Richter"]
Nathan  3                 ["Martha Argerich"]

The fifth line gathers the numberOfScrobbles and the artists into lists so that we have one row per listner.

l       scrobbleCountList artistsList
Nathan  [4, 3]            [["Lang Lang", "Sviatoslav Richter"], ["Martha Argerich"]]

The last three lines return the listner name and the first values from the scrobbleCountList and artistsList.

l      highestScrobbleCount artists
Nathan 4                    ["Lang Lang", "Sviatoslav Richter"]

Upvotes: 0

Related Questions