PGexercises.com | Monthly facility utilisation rate

-- https://pgexercises.com/questions/date/utilisationpermonth.html
-- longer than the recommended solution, but more transparent IMO

-- count available slots per month
with t1 as (
    select
        date_trunc('month',starttime) as month,
        25*LEFT(((DATE_TRUNC('month', starttime)+'1 month'::INTERVAL)-DATE_TRUNC('month', starttime))::TEXT,2)::INTEGER AS len_int
        from cd.bookings
        group by 1, 2),

-- count used slots per month per facility
t2 as(
    select f.name,
        date_trunc('month',starttime) as month,
        sum(slots) as used_slots
        from cd.bookings b
        join cd.facilities f
        on f.facid = b.facid
        group by 1, 2
        order by 1 asc, 2 asc
),

-- line up facility, month, available slots, used slots
t3 as (
    select  name,
            t.month,
            len_int,
            sum(used_slots) as usedslots
    from t1 t
    join t2 d
    on t.month = d.month
    group by 1, 2, 3
    order by name asc
)
-- finally divide one by the other
select name, month, ROUND(100*(usedslots/len_int),1) as uti
from t3