Skip to content

Instantly share code, notes, and snippets.

@hakko
hakko / gist:3389980
Created Aug 18, 2012
Genre radio playlist generation
View gist:3389980
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)