PGexercises.com | 3-table join

-- https://pgexercises.com/questions/joins/threejoin2.html
-- They use one long query, but 'with' is cleaner

with t1 as(
  select m.firstname || ' ' || m.surname as member,
       f.name as facility,
       b.slots * (case when b.memid = 0 then f.guestcost else f.membercost end) as cost
       from cd.members m
       join cd.bookings b
       on m.memid = b.memid
       join cd.facilities f
       on f.facid = b.facid
       where starttime::DATE = '2012-09-14'
  )

select * from t1
where cost > 30
order by 3 desc