Question

Classify facilities into equally sized groups of high, average, and low based on their revenue. Order by classification and facility name.
Schema reminder
DB schema

Expected Results

name revenue
Massage Room 1 high
Massage Room 2 high
Tennis Court 2 high
Badminton Court average
Squash Court average
Tennis Court 1 average
Pool Table low
Snooker Table low
Table Tennis low

Your Answer Hint Help Save Run Query

 

Answers and Discussion Show

select name, case when class=1 then 'high'
		when class=2 then 'average'
		else 'low'
		end revenue
	from (
		select facs.name as name, ntile(3) over (order by sum(case
				when memid = 0 then slots * facs.guestcost
				else slots * membercost
			end) desc) as class
		from cd.bookings bks
		inner join cd.facilities facs
			on bks.facid = facs.facid
		group by facs.name
	) as subq
order by class, name;          

This exercise should mostly use familiar concepts, although we do introduce the NTILE window function. NTILE groups values into a passed-in number of groups, as evenly as possible. It outputs a number from 1->number of groups. We then use a CASE statement to turn that number into a label!

Investigate the NTILE window function.

Keyboard shortcuts:


Other hints: