Question Classify facilities into equally sized groups of high, average, and low based on their revenue. Order by classification and facility name.
Answers and Discussion Show
select name, case when class=1 then 'high'
when class=2 then 'average'
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.
- Alt-h: Show/Hide Help menu
- Alt-r: Run query
- Alt-x: Run selected text as query
- Alt-s: Run query by cursor (delimited by whitespace/semi-colon)
- You can double click on each of the panes of Expected Result/Your answer to quickly resize them.
- If you have trouble remembering the database schema, you can leave this popup open while you work on your answer.
- Don't forget to use the hint button if you're stuck!