Question

Produce a list of the top three revenue generating facilities (including ties). Output facility name and rank, sorted by rank and facility name.
Schema reminder
DB schema

Expected Results

name rank
Massage Room 1 1
Massage Room 2 2
Tennis Court 2 3

Your Answer Hint Help Save Run Query

 

Answers and Discussion Show

select name, rank from (
	select facs.name as name, rank() over (order by sum(case
				when memid = 0 then slots * facs.guestcost
				else slots * membercost
			end) desc) as rank
		from cd.bookings bks
		inner join cd.facilities facs
			on bks.facid = facs.facid
		group by facs.name
	) as subq
	where rank <= 3
order by rank;          

This question doesn't introduce any new concepts, and is just intended to give you the opportunity to practise what you already know. We use the CASE statement to calculate the revenue for each slot, and aggregate that on a per-facility basis using SUM. We then use the RANK window function to produce a ranking, wrap it all up in a subquery, and extract everything with a rank less than or equal to 3.

Yet another question based on the RANK window function! Remember the relative complexity of calculating the revenue of a facility, since you need to count for the different costs for the GUEST user..

Keyboard shortcuts:


Other hints: