PostgreSQL Exercises

Previous Exercise

Next Exercise

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.
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.
            

          

        

      

      
      

        
Previous Exercise

        

          
        

        
Next Exercise

      

    

      

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: