Solutions and notes to pgexercises.com

solutions.sql

-- basic --

select * from cd.facilities;

select name, membercost  from cd.facilities;

select * from cd.facilities where membercost > 0;

select  facid, name, membercost, monthlymaintenance from cd.facilities where membercost < monthlymaintenance * 1/50.0;

select  facid, name, membercost, monthlymaintenance from cd.facilities where membercost < monthlymaintenance * 1/50.0 and membercost > 0;

select * from cd.facilities where name like '%Tennis%'

 select * from cd.facilities where facid in (1, 5);

select name, case when monthlymaintenance > 100 then 'expensive' else 'cheap' end from cd.facilities;

select memid, surname, firstname, joindate from cd.members where joindate <'2012-09-01';

select distinct  surname  from cd.members order by surname limit 10;


select surname from cd.members union select name from cd.facilities;

select joindate from cd.members order by joindate desc limit 1;

select firstname, surname, joindate from cd.members order by joindate desc limit 1;

-- === joins and subqueries === --

select starttime from cd.bookings 
join cd.members 
on cd.members.memid = cd.bookings.memid
where cd.members.firstname = 'David' and cd.members.surname='Farrell';

select b.starttime, f.name 
from bookings b 
join facilities f on b.facid = f.facid
where date_trunc('day', b.starttime) = '2012-09-21' 
  and f.name ilike 'Tennis court%'
order by b.starttime;


-- Produce a list of all members who have recommended another member
-- not accepted

select distinct firstname, surname 
from cd.members 
where memid in (
    select recommendedby 
    from cd.members 
    where recommendedby is not null);

-- with inner join
select distinct l.firstname, l.surname 
from cd.members l join cd.members r 
on l.memid = r.recommendedby 
order by l.surname;



--Produce a list of all members, along with their recommender
--my answer
select distinct 
  l.firstname as memfname, 
  l.surname as memsname,
  r.firstname as recfname, 
  r.surname as recsname
from cd.members l 
left join cd.members r 
on r.memid = l.recommendedby 
order by memsname, memfname;

-- should be
 select
	l.firstname as memfname, 
	l.surname as memsname,
	r.firstname as recfname, 
	r.surname as recsname
	
from cd.members l 
left outer join cd.members r 
on r.memid = l.recommendedby 
order by memsname, memfname;

-- comment: my solution may not produce output for members with same firstname and surname (but different memid) - here Darren Smith is affected..

--
--Produce a list of all members who have used a tennis court
select distinct 
  m.firstname || ' '|| m.surname as member, 
  f.name as facility 
from cd.members m 
join cd.bookings b on m.memid= b.memid 
join cd.facilities f on f.facid = b.facid 
where f.name ilike 'tennis court%' 
order by member,facility;


--Produce a list of costly bookings
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 
  date_trunc('day', b.starttime) = '2012-09-14' 
and (b.slots * (case when b.memid = 0 then f.guestcost else f.membercost end)) > 30
order by cost desc;


--Produce a list of all members, along with their recommender, using no joins.

-- oh no! too long have I beed thinking 
-- my query didn't have distinct clause and was therefore invalid

select distinct 
  m.firstname || ' ' || m.surname as member, 
  ( select  firstname || ' ' || surname 
    from cd.members 
    where memid = m.recommendedby) as recommender 
from cd.members m
order by member;

-- costly bokings with subquery

select  
  member,
  facility,
  cost
from (
        select
        date_trunc('day', b.starttime) as d,
        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
) as ff
where 
  ff.d = '2012-09-14' and ff.cost > 30
order by cost desc;

-- === Modifying data ===

-- Insert: facid: 9, Name: 'Spa', membercost: 20, guestcost: 30, initialoutlay: 100000, monthlymaintenance: 800.
insert into cd.facility(facid, name, membercost, guestcist, initialoutlay, monthlymaintenance)
values (9, 'Spa', 20, 30, 100000,800); 

-- multiple values

insert into cd.facilities(facid, name, membercost, guestcost, initialoutlay, monthlymaintenance)
values (9, 'Spa', 20, 30, 100000,800), (10, 'Squash Court 2', 3.5, 17.5, 5000, 80);

--Insert calculated data into a table
-- I failed!
-- this is my trial:
insert into cd.facilities(facid, name, membercost, guestcost, initialoutlay, monthlymaintenance)
values (select max(f.facid) + 1 as facid from cd.facilities f, 'Spa', 20, 30, 100000,800);

--this is how it should be done:
insert into cd.facilities (facid, name, membercost, guestcost, initialoutlay, monthlymaintenance)
select (select max(facid) from cd.facilities)+1, 'Spa', 20, 30, 100000, 800;

-- Update some existing data
update cd.facilities set initialoutlay=10000 where name='Tennis Court 2';

-- multi update

update cd.facilities set guestcost=30,membercost=6 where name ilike 'Tennis Court%';

-- Update a row based on the contents of another row 
update cd.facilities set 
  guestcost= 1.1*(select guestcost from cd.facilities where name='Tennis Court 1'),
  membercost=1.1*(select membercost from cd.facilities where name='Tennis Court 1') 
where name='Tennis Court 2';

-- delete all bookings
delete from cd.bookings;

--Delete a member from the cd.members table
delete from cd.members where memid=37;

-- delete all members that haven't booked enything
-- didn't work
delete from cd.members where memid in (select m.memid  from cd.members m join cd.bookings b on b.memid=m.memid group by m.memid having count(*) = 0)
-- correct
delete from cd.members where memid not in (select memid from cd.bookings);       

-- == Aggregation ==

--Count the number of facilities
select count(*) from cd.facilities;

--Count the number of expensive facilities
select count(*) from cd.facilities where guestcost>10;

--Count the number of recommendations each member makes.
select recommendedby,  count(*) from cd.members where recommendedby is not null group by recommendedby order by recommendedby;

--List the total slots booked per facility
select facid, sum(slots) from cd.bookings group by facid order by facid;

--List the total slots booked per facility in a given month (sept 2012)
select facid, sum(slots) from cd.bookings where starttime < '2012-10-01' and starttime >= '2012-09-01' group by facid order by sum;

-- List the total slots booked per facility per month
select facid, date_part('month', starttime) as  month, sum(slots) as "Total Slots" 
from cd.bookings 
where date_part('year', starttime) = 2012 
group by facid, month 
order by facid, month;

-- Find the count of members who have made at least one booking
select count(*) from (select count(*) from cd.bookings group by memid) as count;

--List facilities with more than 1000 slots booked
select facid, sum(slots) as s 
from cd.bookings 
group by facid 
having sum(slots) > 1000 
order by facid;
-- Find the total revenue of each facility
-- this is my accepted original solution (three selects, omg)
select name, rev from (
select r.name as name , sum(r.rev)as rev from (
	
	select f.name as name, 
	b.slots * (case when b.memid = 0 then f.guestcost else f.membercost end) as rev 
	from cd.bookings b 
	join cd.facilities f 
	on b.facid = f.facid
) as r
group by r.name) as x
order by x.rev;

-- this is suggested one:
 select facs.name, sum(slots * case
			when memid = 0 then facs.guestcost
			else facs.membercost
		end) as revenue
	from cd.bookings bks
	inner join cd.facilities facs
		on bks.facid = facs.facid
	group by facs.name
order by revenue;
-- Find facilities with a total revenue less than 1000
--
--https://pgexercises.com/questions/aggregates/facrev2.html
select name, revenue from (
select f.name as name, 
        sum(b.slots * (case when b.memid=0 then f.guestcost else f.membercost end)) as revenue 
from cd.bookings b join cd.facilities f on b.facid = f.facid 
group by f.facid  
order by revenue
) as x  where x.revenue < 1000;


--Output the facility id that has the highest number of slots booked
select facid, max(sums.s) as max_slots 
from (select facid, sum(b.slots) as s from cd.bookings b group by facid) as sums 
group by facid 
order by max_slots 
desc limit 1;

-- it appears the above solution is invalid - for facilities with same number of slots. So the correct one is
-- (using CTE - common table expressions):
with sum as (select facid, sum(slots) as totalslots
	from cd.bookings
	group by facid
)
select facid, totalslots 
	from sum
	where totalslots = (select max(totalslots) from sum);

-- List the total slots booked per facility per month, part 2
select facid,  extract(month from starttime) as month, sum(slots) as slots 
from cd.bookings 
where extract(year from starttime) = 2012 
group by rollup(facid, month) 
order by facid, month;

-- List the total hours booked per named facility
select f.facid, f.name, round(sum(b.slots)/2.0,2) as "Total Hours" from cd.facilities f join cd.bookings b on f.facid=b.facid  group by f.facid order by facid;

--List each member's first booking after September 1st 2012
select m.surname as surname, m.firstname as firstname, m.memid as memid, min(b.starttime) as starttime 
from cd.members m join cd.bookings b on m.memid=b.memid 
where b.starttime > '2012-09-01'  
group by m.memid 
order by m.memid;

--Produce a list of member names, with each row containing the total member count
---- of course I created "obvious" solution:
select (select count(*)  from cd.members) as count, m.firstname as firstname, m.surname as surname 
from cd.members m 
order by m.joindate;

---- no way could I expect this one:
select count(*) over(), firstname, surname
from cd.members
order by joindate          


--Produce a numbered list of members
select row_number() over(), firstname, surname from cd.members;

--Output the facility id that has the highest number of slots booked, again
with sms as (
  select facid, sum(slots) as sum, max(sum(slots)) over () as m 
  from cd.bookings group by facid) 
select facid, sum  from sms where sum = m;

---- well, rank() function should be used, but :(...

--Rank members by (rounded) hours used
select 
  m.firstname, 
  m.surname, 
  round((sum(b.slots) / 2) / 10.0, 0) * 10 as hours, 
  rank() over (order by round((sum(b.slots) / 2 ) /10.0, 0) * 10 desc) rank 
from cd.members m 
join cd.bookings b on b.memid = m.memid 
group by m.memid 
order by rank, m.surname, m.firstname;

--Find the top three revenue generating facilities
-- Mindblowing.
select name, rank() over (order by rev desc) r from 
(select name, sum((case when b.memid = 0 then f.guestcost else f.membercost end)*b.slots) as rev
 from cd.bookings b join cd.facilities f on f.facid = b.facid 
 group by f.facid
) as sums
order by r, name
limit 3;

-- however, ties would give incorrect answer due to limit.
-- So I thought, ok, let's use WHEN with rank:
select name, rank() over (order by rev desc) r from 
(select name, sum((case when b.memid = 0 then f.guestcost else f.membercost end)*b.slots) as rev
 from cd.bookings b join cd.facilities f on f.facid = b.facid 
 group by f.facid
) as sums
where r <= 3
order by rank, name;

-- but this does not work due to "ERROR:  column "r" does not exist
--LINE 6: where r <= 3
--              ^
--SQL state: 42703
--Character: 248

-- And a correct solution is to move rank() call so subquery
select name, rank from 
(select name, rank() over (order by sum( (case when b.memid = 0 then f.guestcost else f.membercost end)*b.slots) desc) rank
 from cd.bookings b join cd.facilities f on f.facid = b.facid 
 group by f.facid
) as sums
where rank <= 3
order by rank, name;

--Classify facilities by value
-- oh, this one looks scary but was easy and accepted on second try (first one failed due to classification typo)
select name, case when rank=1 then 'high' when rank=2 then 'average' when rank =3 then 'low' end revenue from 
(select name, ntile(3) over (order by sum( (case when b.memid = 0 then f.guestcost else f.membercost end)*b.slots) desc) rank
 from cd.bookings b join cd.facilities f on f.facid = b.facid 
 group by f.facid
) as sums
order by sums.rank, name;

--Calculate the payback time for each facility
-- this one was interesting - because I assumed that I have data for each day, but in reality I should have generated dates by hand...
with all_revs as (
	select date(starttime) d, 
					sum(case when b.memid = 0 
						then slots * f.guestcost 
						else slots * f.membercost 
						end) rev
	from cd.facilities f join cd.bookings b ON b.facid = f.facid
	where date(starttime) >= ('2012-08-01'::DATE - '16 days'::INTERVAL) and date(starttime) < '2012-09-01'
	group by d
	order by d),
 all_avgs as (
select d, rev, avg(rev) over (rows 16 preceding exclude current row) aa
from all_revs)
select d, aa from all_avgs 
where extract(year from d) = 2012 and extract(month from d) = 8;