Date/Campus Ranking using CTE

gistfile1.sql

;with rankings as (
	select id, campus	
	, dense_rank() over (partition by id, campus order by date desc )	/* frequency weight */
		* ( 6 - dense_rank() over (partition by id order by date desc))	/* date weight */
	as 'freqrank'
	from attendance
), 
filter as (
	select id, campus, row_number() over (
		partition by id 
		order by sum(freqrank) desc
	) as 'ranking'
	from rankings
	where freqrank > 0	/* limit to the last 5 recent visits */
	group by id, campus
)
select id, campus from filter
where ranking = 1