Work out the utilisation percentage for each facility by month
Question
Work out the utilisation percentage for each facility by month, sorted by name and month, rounded to 1 decimal place. Opening time is 8am, closing time is 8.30pm. You can treat every month as a full month, regardless of if there were some dates the club was not open.
Expected Results
| name | month | utilisation |
|---|---|---|
| Badminton Court | Sun Jul 01 2012 00:00:00 GMT+0000 (Coordinated Universal Time) | 23.2 |
| Badminton Court | Wed Aug 01 2012 00:00:00 GMT+0000 (Coordinated Universal Time) | 59.2 |
| Badminton Court | Sat Sep 01 2012 00:00:00 GMT+0000 (Coordinated Universal Time) | 76.0 |
| Massage Room 1 | Sun Jul 01 2012 00:00:00 GMT+0000 (Coordinated Universal Time) | 34.1 |
| Massage Room 1 | Wed Aug 01 2012 00:00:00 GMT+0000 (Coordinated Universal Time) | 63.5 |
| Massage Room 1 | Sat Sep 01 2012 00:00:00 GMT+0000 (Coordinated Universal Time) | 86.4 |
| Massage Room 2 | Sun Jul 01 2012 00:00:00 GMT+0000 (Coordinated Universal Time) | 3.1 |
| Massage Room 2 | Wed Aug 01 2012 00:00:00 GMT+0000 (Coordinated Universal Time) | 10.6 |
| Massage Room 2 | Sat Sep 01 2012 00:00:00 GMT+0000 (Coordinated Universal Time) | 16.3 |
| Pool Table | Sun Jul 01 2012 00:00:00 GMT+0000 (Coordinated Universal Time) | 15.1 |
| Pool Table | Wed Aug 01 2012 00:00:00 GMT+0000 (Coordinated Universal Time) | 41.5 |
| Pool Table | Sat Sep 01 2012 00:00:00 GMT+0000 (Coordinated Universal Time) | 62.8 |
| Pool Table | Tue Jan 01 2013 00:00:00 GMT+0000 (Coordinated Universal Time) | 0.1 |
| Snooker Table | Sun Jul 01 2012 00:00:00 GMT+0000 (Coordinated Universal Time) | 20.1 |
| Snooker Table | Wed Aug 01 2012 00:00:00 GMT+0000 (Coordinated Universal Time) | 42.1 |
| Snooker Table | Sat Sep 01 2012 00:00:00 GMT+0000 (Coordinated Universal Time) | 56.8 |
| Squash Court | Sun Jul 01 2012 00:00:00 GMT+0000 (Coordinated Universal Time) | 21.2 |
| Squash Court | Wed Aug 01 2012 00:00:00 GMT+0000 (Coordinated Universal Time) | 51.6 |
| Squash Court | Sat Sep 01 2012 00:00:00 GMT+0000 (Coordinated Universal Time) | 72.0 |
| Table Tennis | Sun Jul 01 2012 00:00:00 GMT+0000 (Coordinated Universal Time) | 13.4 |
| Table Tennis | Wed Aug 01 2012 00:00:00 GMT+0000 (Coordinated Universal Time) | 39.2 |
| Table Tennis | Sat Sep 01 2012 00:00:00 GMT+0000 (Coordinated Universal Time) | 56.3 |
| Tennis Court 1 | Sun Jul 01 2012 00:00:00 GMT+0000 (Coordinated Universal Time) | 34.8 |
| Tennis Court 1 | Wed Aug 01 2012 00:00:00 GMT+0000 (Coordinated Universal Time) | 59.2 |
| Tennis Court 1 | Sat Sep 01 2012 00:00:00 GMT+0000 (Coordinated Universal Time) | 78.8 |
| Tennis Court 2 | Sun Jul 01 2012 00:00:00 GMT+0000 (Coordinated Universal Time) | 26.7 |
| Tennis Court 2 | Wed Aug 01 2012 00:00:00 GMT+0000 (Coordinated Universal Time) | 62.3 |
| Tennis Court 2 | Sat Sep 01 2012 00:00:00 GMT+0000 (Coordinated Universal Time) | 78.4 |
Your Answer
Your Results
Loading database...
Answers and Discussion
select name, month,
round((100*slots)/
cast(
25*(cast((month + interval '1 month') as date)
- cast (month as date)) as numeric),1) as utilisation
from (
select facs.name as name, date_trunc('month', starttime) as month, sum(slots) as slots
from cd.bookings bks
inner join cd.facilities facs
on bks.facid = facs.facid
group by facs.facid, month
) as inn
order by name, monthThe meat of this query (the inner subquery) is really quite simple: an aggregation to work out the total number of slots used per facility per month. If you've covered the rest of this section and the category on aggregates, you likely didn't find this bit too challenging.
This query does, unfortunately, have some other complexity in it: working out the number of days in each month. We can calculate the number of days between two months by subtracting two timestamps with a month between them. This, unfortunately, gives us back on interval datatype, which we can't use to do mathematics. In this case we've worked around that limitation by converting our timestamps into dates before subtracting. Subtracting date types gives us an integer number of days.
A alternative to this workaround is to convert the interval into an epoch value: that is, a number of seconds. To do this use EXTRACT(EPOCH FROM month)/(24*60*60). This is arguably a much nicer way to do things, but is much less portable to other database systems.