Limiting SQL results in Rails using Postgres

Question

I have an app that includes music charts to showcase the top tracks (it shows the top 10).

However, I'm trying to limit the charts so that any particular user cannot have more than 2 tracks on the top charts at the same time. If Artist A normally would have 4 of the top 10 slots, only the top 2 tracks by Artist A would be shown (and #11 and #12 on the list would be bumped up 2 spots each, presuming they aren't also by Artist A of course).

So, let's say this is the top charts section right now:

1. Song A by Artist A
2. Song B by Artist B
3. Song C by Artist A
4. Song D by Artist C
5. Song E by Artist D
6. Song F by Artist E
7. Song G by Artist F
8. Song H by Artist A
9. Song I by Artist A
10. Song J by Artist G

I would like to limit the SQL results so #8 and #9 aren't included (because only up to 2 tracks per artist would be allowed in the query results) and the list would instead become:

1. Song A by Artist A
2. Song B by Artist B
3. Song C by Artist A
4. Song D by Artist C
5. Song E by Artist D
6. Song F by Artist E
7. Song G by Artist F
8. Song J by Artist G
9. Song K by Artist H (previously #11)
10. Song L by Artist I (previously #12)

FYI, I'm using Postgres, and this is what I have right now. It counts plays per track in the last 14 days to generate the top 10 list. I would like to modify it to get the desired limitation noted above.

def self.top_tracks_past14(max=3)
  Track.find_by_sql(["select COALESCE(sum(plays.clicks), 0), tracks.*
    from tracks
    left join plays
    on tracks.id = plays.track_id
    and plays.created_at > now() - interval '14 days'
    inner join albums
    on tracks.album_id = albums.id
    inner join users
    on albums.user_id = users.id
    group by tracks.id
    order by 1 desc limit ?", max])
end

Answer 1

select trackid, userid, totalclicks from 
(
select *, 
row_number() over(partition by userid order by totalclicks desc) as rn
from
 (
select COALESCE(sum(plays.clicks), 0) as totalclicks,plays.track_id as trackid,
users.id as userid
from tracks
left join plays
on tracks.id = plays.track_id
and plays.created_at > now() - interval '14 days'
inner join albums
on tracks.album_id = albums.id
inner join users
on albums.user_id = users.id
group by plays.track_id, users.id
  ) t
) t1
where t1.rn <= 2
order by 1 desc
limit 10; 

You can use row_number function to only select 2 rows per user amongst the top tracks.

Edit: As per OP's request

All the columns from tracks, albums, users, plays will be available in the outer query. Be sure to select the columns you need from these tables if you need to exclude the calculated rn from your selection.

def self.top_tracks_past14(max=3)
Track.find_by_sql(["select t1.trackid, t1.userid, t1.totalclicks from 
(
select t.trackid, t.userid, t.totalclicks, 
row_number() over(partition by t.userid order by t.totalclicks desc) as rn
from
 (
select COALESCE(sum(plays.clicks), 0) as totalclicks,plays.track_id as trackid
,users.id as userid
from tracks
left join plays
on tracks.id = plays.track_id
and plays.created_at > now() - interval '14 days'
inner join albums
on tracks.album_id = albums.id
inner join users
on albums.user_id = users.id
group by plays.track_id, users.id
  ) t
) t1
where t1.rn <= 2
order by t1.totalclicks desc limit ?", max])
end