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:

Song A by Artist A
Song B by Artist B
Song C by Artist A
Song D by Artist C
Song E by Artist D
Song F by Artist E
Song G by Artist F
Song H by Artist A
Song I by Artist A
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:

Song A by Artist A
Song B by Artist B
Song C by Artist A
Song D by Artist C
Song E by Artist D
Song F by Artist E
Song G by Artist F
Song J by Artist G
Song K by Artist H (previously #11)
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

Vamsi Prabhala · Accepted Answer · 2015-08-18 02:13:12Z

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

edited Aug 18, 2015 at 2:13

answered Aug 16, 2015 at 21:58

Vamsi Prabhala

49.4k4 gold badges41 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Andrew Over a year ago

Hey @vkp, thanks for the suggestion. I'm getting an error ("wrong number of arguments (0 for 1..2)"). Could you combine your answer with my "def self.top_tracks_past14..." syntax to perhaps iron out any issues/confusion on my end?

Vamsi Prabhala Over a year ago

@Andrew.. i modified to include the query in your function. let me know how it goes as i am not sure about Ruby.

Andrew Over a year ago

your edited answer returns a different error that can be fixed by taking out the comma after "tracks.*" However, it doesn't seem to be returning the 10 tracks with the most plays in the last 14 days (with a max of 2 per artist). Instead, it seems to be returning the 10 tracks in order of id (1 through 10).

Vamsi Prabhala Over a year ago

this fiddle shows the logic behind the query sqlfiddle.com/#!15/06991/7 .. could you please post the results you get along with data in the tables?

Andrew Over a year ago

The results and data I get from running the SQL in your fiddle, or results and data I get from running the SQL in the app?

|

Collectives™ on Stack Overflow

Limiting SQL results in Rails using Postgres

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related