Find the top three revenue generating facilities
Question
Produce a list of the top three revenue generating facilities (including ties). Output facility name and rank, sorted by rank and facility name.
Expected Results
| name | rank |
|---|---|
| Massage Room 1 | 1 |
| Massage Room 2 | 2 |
| Tennis Court 2 | 3 |
Your Answer
Your Results
Loading database...
Answers and Discussion
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.