Rainwave SQL

albums_on_rw_and_mw.sql

select
	album_name
from
	rw_albums
where
	sid = 1 and
	album_verified is true and
	album_name in (
		select
			album_name
		from
			rw_albums
		where
			sid = 3 and
			album_verified is true
	)
order by
	album_name;

all_albums_with_song_count.sql

select
	rw_albums.sid,
	album_name,
	album_id,
	count(song_id) as num_of_songs,
	timestamp with time zone 'epoch' + max(song_addedon) * '1 second'::interval as last_song_added
from
	rw_songs
		join
	rw_albums using (album_id)
group by
	album_id,
	album_name,
	rw_albums.sid
order by
	sid,
	album_name;

top_albums_by_total_requests.sql

select
	max(rw_albums.sid) as sid,
	album_name,
	max(album_totalrequests) as requests,
	count(song_id) as total_songs,
	timestamp with time zone 'epoch' + min(song_addedon) * interval '1 second' as first_song_added
from
	rw_albums
		join
	rw_songs using (album_id)
where
	album_verified is true and
	song_verified is true and
	rw_albums.sid = 1
group by
	album_name
order by
	max(album_totalrequests) desc
limit 10;

top_artists_by_average_song_rating.sql

select
	'oc' as station,
	artist_name,
	avg(s.song_rating_avg) as avg_song_rating
from
	rw_song_artist
		join
		(select
			song_id,
			song_rating_avg
		from
			rw_songs
		where
			song_addedon < (extract(epoch from current_timestamp) - 15552000) and
			song_verified is true and
			sid = 2
		) as
	s using (song_id)
		join
	rw_artists using (artist_id)
group by
	artist_name
	having count(song_id) > 4
order by
	avg_song_rating desc
limit 20;

top_worst_songs_by_rating.sql

select
	'rw' as station,
	song_title,
	album_name,
	song_rating_avg,
	song_rating_count
from 
	rw_songs
		join
	rw_albums using (album_id)
where
	rw_songs.sid = 1 and
	song_addedon < (extract(epoch from current_timestamp) - 15552000) and
	song_verified is true
order by
	song_rating_avg
limit 10;