Skip to content

Instantly share code, notes, and snippets.

@hakko hakko/gist:3389980
Created Aug 18, 2012

Embed
What would you like to do?
Genre radio playlist generation
select artist_id, track_id from (
select att.track_id, att.artist_id, tag.tag_count as tag_weight, rank() over
(partition by att.artist_id order by (random()*(110 - rank + (play_count/3))) desc) as artist_rank from library.artisttoptrackplaycount att
inner join (
select toptag.artist_id, sum(tag_count) as tag_count from (
select artist_id, max(tag_count) as tag_count from music.artisttoptag att
inner join music.tag t on att.tag_id = t.id
where coalesce(t.corrected_id, t.id) in (
select id from music.tag where tag_name in ('riot grrrl', 'punk')
) group by artist_id, coalesce(t.corrected_id, t.id)
) toptag group by artist_id
) tag on tag.artist_id = att.artist_id
) ranked_tracks
where ranked_tracks.artist_rank <= 3
order by random() * ranked_tracks.tag_weight * ranked_tracks.tag_weight desc
limit 20;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.