PostgreSQL Exercises

Previous Exercise

Next Exercise

Classify facilities by value

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!
            

          

        

      

      
      

        
Previous Exercise

        

          
        

        
Next Exercise

      

    

      

Investigate the NTILE window function.
      

      

        

	  
Keyboard shortcuts:

	  
	  

	  
Other hints: