Reference Solutions
-- 1
-- alle Mitglieder
-- all members
select * from Member;

-- 2
-- alle Fahrzeuge
-- all vehicles
select * from vehicle;

-- 3
-- alle Personenwagen
-- all motor cars
select * 
  from motorcar;

-- 4
-- alle Limousinen
-- all limousines
select * 
  from limousine;

-- 5
-- Aktuelles Datum und Uhrzeit
-- current date and time
select current_date, current_timestamp, now();

-- 6
-- die vollen Namen aller Personen (Wenn nicht anders spezifiziert, ist ab jetzt mit “Name” immer der volle Name, d.h. “vorname nachname”, gemeint)
-- the full names of all persons (unless specified otherwise, from now on the full name, “firstname lastname” is required whenever “name” is mentioned)
select firstname || ' ' || lastname from Person;

-- 7
-- die Namen aller persoenlichen Mitglieder
-- the names of all person members
select firstname || ' ' || lastname from personmember;

-- 8
-- die Namen und Heimatstationen der persoenlichen Mitglieder, sortiert nach Station
-- the names and home location of the person members, in the order of the home locations
select firstname || ' ' || lastname, name
  from personmember p join
       location l on p.homelocation = l.id
 order by name;

-- 9
-- die Namen und Heimatstation der Genossenschaftsmitglieder, sortiert nach Namen der Heimatstation
-- names and home locations of the coop members sorted by home location name
select firstname || ' ' || lastname, name
  from coopmember c join
       location l on c.homelocation = l.id
 order by name;

-- 10
-- die Kennzeichen, Marken und Modelle aller Fahrzeuge
-- the licenseplate, make, and model of all vehicles
select licensePlate, make, model from vehicle;

-- 11
-- die Marken und Modelle der Fahrzeuge mit Namen der Heimatstation
-- Make, model, and name of the home location of the vehicles
select make, model, name
  from vehicle v join
       Location l on v.home = l.id;

-- 12
-- die Marken und Modelle der Fahrzeuge mit dem Namen der Heimatstation. Auch die Fahrzeuge ohne Heimatstation sollen angezeigt werden.
-- Make, model, and name of the home location of the vehicles. The make and model of vehicles without home location should be shown as well
select make, model, name
  from vehicle v left outer join
       location l on v.home = l.id;

-- 13
-- die Marken und Modelle der Fahrzeuge mit Heimatstation. Auch die Fahrzeuge ohne Heimatstation und die Stationen ohne Fahrzeuge sollen angezeigt werden.
-- Make, model, and name of the home location of the vehicles. The make and model of vehicles without home location as well as the locations without vehicles should be shown as well
select make, model, name
  from vehicle v full outer join
       Location l on v.home = l.id;

-- 14
-- Fahrzeuge ohne Heimatstation
-- Vehicles without a home location
select *
  from vehicle 
 where home is NULL;

-- 15
-- Stationen ohne Fahrzeuge
-- Locations without vehicles
select licenseplate
  from vehicle v right outer join
       location l on v.home = l.id  
 where l.name is null;

-- 16
-- die Kuerzel, Namen und Addressen der Stationen
-- short name, long name, and address of the locations
select shortName, Name, address 
  from location;

-- 17
-- die Kuerzel, Namen und Orte der Stationen
-- short name, long name, and city of the locations
select shortName, Name, (address).city 
  from location;

-- 18
-- die Reservationen des Fahrzeugs 'ZH-1020'
-- the reservation of vehicle 'ZH-1020'
select v.licenseplate, r.resnumber, r.interval
  from vehicle v join
       reservation r on r.vehicle = v.id
 where v.licensePlate = 'ZH-1020';

-- 19
-- die Reservationen des Mitglieds mit der Nummer 1000
-- the reservations of the member with member number 1000
select m.memberNr, r.resnumber, r.interval
  from reservation r join
       member m on r.member = m.id
 where m.memberNr = 1000;

-- 20
-- die offenen (zukünftigen) Reservationen des Mitglieds mit der Nummer 1000
-- open (future) reservations of the member number 1000
select m.memberNr, r.resnumber, r.interval
  from reservation r join
       member m on r.member = m.id
 where m.memberNr = 1000        
       and (r.interval).endts > current_timestamp;

-- 43
-- alle Fahrzeuge in Zürich
-- all vehicles whose home location is in the city of Zurich
select v.* 
  from vehicle v join
       location l on v.home = l.id
 where (l.address).city = 'Zuerich';

-- 45
-- die Anzahl Limousinen pro Station
-- the locations and number of limousines at each location
select shortname, name, count(*)  
  from limousine m join
       location l on m.home = l.id
 group by shortname, name 
union 
select shortname, name, 0 
  from limousine m right outer join        
       location l on m.home = l.id
 where m.licenseplate is null;

-- 23
-- alle offenen (unbezahlten) Rechnungen
-- all open invoices (unpaid)
select * from invoice where not ispaid;

-- 24
-- alle ueberfaelligen Rechnungen
-- all overdue invoices
select *    From invoice  
 where not ispaid         and dueDate < current_date;

-- 25
-- alle Reservationen, die noch nicht in Rechnung gestellt wurden
select resnumber 
  from reservation r joinUseofvehicle u on u.reservation = r.id 
 where invoice is null;

-- 26
-- die Rechnungen des Mitglieds mit der Nummer 1000
-- the invoices for member number 1000
select *
  from invoice i join
       member m on i.member = m.id
 where m.memberNr = 1000;

-- 27
-- die Kennzeichen der Fahrzeuge der Benutzungen
-- the license plates of the vehicles which have been used (not only reserved)
select usageNumber, licensePlate
  from vehicle v join
       reservation r on r.vehicle = v.id join
       useofvehicle u on u.reservation = r.id
 order by usageNumber;

-- 28
-- die Namen der persoenlichen Mitglieder zusammen mit der Angabe, ob sie bereits einen Unfall hatten
-- the names of the person members plus the indication if the members have had an accident
select firstname || ' ' || lastname, hadAccident from PersonMember;

-- 29
-- die Namen der persoenlichen Mitglieder zusammen mit der Angabe, ob sie bereits einen Unfall hatten (schoen formattiert)
-- the names of the person members plus the indication if the members have had an accident (nicely formattted)
select firstname || ' ' || lastname,
       case hadAccident when true THEN 'Unfall' else 'kein Unfall' end as hadAccident
  from PersonMember;

-- 30
-- welche persoenlichen  Mitglieder hatten bereits einen Unfall?
-- which person members have had an accident?
select firstname || ' ' || lastname as Unfallfahrer
  from PersonMember
 where hadAccident;

-- 31
-- die den einzelnen Benutzungen zugrundeliegenden Kilometerpreise
-- the correct price per kilometer for each use
select usageNumber, licensePlate, ppKilometer
  from vehicle v join
       reservation r on r.vehicle = v.id join
       useofvehicle u on u.reservation = r.id join
       vehicleStaticData vsd on v.type = vsd.Type
 order by usageNumber;

-- 32
-- Entfernungsbasierte Kosten der Benutzungen
-- the distance-based prices for the vehicle uses
select usageNUmber, u.kilometers * ppkilometer as kilometerCost
  from vehicle v join
       reservation r on r.vehicle = v.id join
       useofvehicle u on u.reservation = r.id join        vehicleStaticData vsd on v.type = vsd.Type
 order by usageNumber;

-- 33
-- Die Summe der Benutzungsdauern pro Tag
-- the sum (of hours) of vehicle uses per day
select (r.interval).begints :: date, sum(hours)
  from useofvehicle u join reservation r on u.reservation = r.id
 group by 1
 order by 1;

-- 34
-- Die durchschnittliche Benutzungsdauer
-- The average duration of vehicle use
select avg(hours)
  from UseOfVehicle;

-- 35
-- die kürzeste Entfernung, die während einer Benutzung zurückgelegt wurde
-- The shortest distance (in kilometers) driven in a single vehicle use
select min(kilometers)
  from UseOfVehicle;

-- 36
-- die durchschnittliche Entfernung pro Benutzung
-- the average distance driven per vehicle use
select avg(kilometers)
  from UseOfVehicle;

-- 37
-- die Summe der Entfernungen, die pro Fahrzeug zurückgelegt wurde
-- the sum of distances driven per vehicle
select licenseplate, sum(u.kilometers)
  from UseOfVehicle u join
       Reservation r on u.reservation = r.id join 
       Vehicle v on r.vehicle = v.id
 group by v.id;

-- 38
-- die durchschnittliche Entfernungen, die pro Fahrzeug zurückgelegt wurde (in allen Reservationen)
-- the average distance driven per vehicle (in all reservations)
select avg(kilometers)
  from (select licenseplate, sum(u.kilometers) as kilometers 
	  from UseOfVehicle u join 
	       Reservation r on u.reservation = r.id join
               Vehicle v on r.vehicle = v.id        
         group by v.id) tmp;

-- 39
-- die Namen und Jahresgebuehren der persoenlichen Mitglieder
-- the names and yearly fees of the person members
select firstname || ' ' || lastname as name, 
       case hadAccident when false then basicFee * 0.8  else basicFee  end as yearlyFee
  from PersonMember m join
       MemberFees f on m.type = f.memberType;

-- 40
-- die Namen  und Jahresgebuehren der Firmenmitglieder
-- the names and yearly fees of the company members
select companyName as name,        basicFee    as yearlyFee
  from companyMember m join
       memberFees f on m.type = f.memberType;

-- 41
-- die Namen und Jahresgebuehren der Genossenschaftsmitglieder
-- the names  and yearly fees of the coop members
select firstname || ' ' || lastname as name,        0 as yearlyFee
  from CoopMember m;

-- 42
-- die Namen und Jahresgebuehren aller Mitglieder
-- the names and yearly fees of all members
select companyName as name,         basicFee    as yearlyFee
  from companyMember m join
       memberFees f on m.type = f.memberType 
union  
select firstname || ' ' || lastname as name,        case hadAccident when true then basicFee * 0.8             else basicFee        end as yearlyFee
  from PersonMember m join 
       memberFees f on m.type = f.memberType 
union  
select firstname || ' ' || lastname as name,        0 as yearlyFee  
  from CoopMember m;

-- 43
-- alle saeumigen Mitglieder
-- the members with due and open invoices
select memberNr 
  from invoice i join
       member m on i.member = m.id
 where not ispaid         and dueDate < current_date;

-- 44
-- alle saeumigen persoenlichen Mitglieder
-- the person members with due and open invoices
select memberNr 
  from invoice i join
       personmember m on i.member = m.id
 where not ispaid         and dueDate < current_date;

-- 45
-- die Namen der saeumigen persoenlichen Mitglieder
-- the full names of person members with due and open invoices
select firstname || ' ' || lastname
  from invoice i join
       personmember m on i.member = m.id
 where not ispaid         and dueDate < current_date
 order by lastname;

-- 46
-- die Namen der saeumigen persoenlichen Mitglieder (nun jedes Mitglied nur einmal !)
-- the full names of person members with due and open invoices (make sure that each name appears only once)
select distinct firstname || ' ' || lastname
  from invoice i join
       personmember m on i.member = m.id
 where not ispaid         and dueDate < current_date
 order by 1;

-- 47
-- alle persoenlichen Mitglieder, die in einem Ort wohnen, in dem es eine Station mit Limousinen gibt
-- all person members living in a city with a location which has limousines
select distinct firstname || ' ' || lastname, (l.address).city, (m.address).city
  from personmember m join
       location l on (l.address).city = (m.address).city 
 where exists (select * 
	         from limousine                
                where home = l.id);

-- 48
-- die Reservationsen fuer Fahrzeuge beliebigen Typs an der station ust
-- all reservations for vehicles of any category at the location ust
select resnumber, interval, licensePlate
  from reservation r join
       vehicle v on r.vehicle = v.id join 
       location l on v.home = l.id
 where l.shortname = 'ust';

-- 49
-- die Reservationsen fuer Fahrzeuge beliebigen Typs an der station ust am 11.11.2023 von 9-10 Uhr
-- all reservations for vehicles of any category at the location ust on 11.11.2023 at 9-10
select resnumber, interval, licensePlate
  from reservation r join
       vehicle v on r.vehicle = v.id join 
       location l on v.home = l.id
 where l.shortname = 'ust' 
       and ( ( (r.interval).begints <= '2023-11-11 09:00:00' and (r.interval).endts > '2023-11-11 09:00:00') 
	     or ( (r.interval).begints >= '2023-11-11 09:00:00' and (r.interval).begints <= '2023-11-11 10:00:00'));

-- 50
-- alle Fahrzeuge beliebigen Typs, die am 11.11.2023 um 9:00 Uhr frei waren
-- all vehicles which are free on 11.11.2023 at 9:00
select licensePlate
  from vehicle v 
 where not exists ( select resnumber 
	              from reservation r                    
                     where r.vehicle = v.id  
               		   and ( ( (r.interval).begints <= '2023-11-11 09:00:00' and (r.interval).endts > '2023-11-11 09:00:00')
      				 or ( (r.interval).begints >= '2023-11-11 09:00:00' and (r.interval).begints <= '2023-11-11 10:00:00')))
 order by 1;

-- 51
-- alle Fahrzeuge beliebigen Typs an der Station ust, die am 11.11.2023 um 9:00 Uhr frei waren
-- all vehicles at location ust which are free on 11.11.2023 at 9:00
select licensePlate
  from vehicle v join
       location l on v.home = l.id
 where shortName = 'ust' 
       and not exists ( select resnumber
                          from reservation r                    
                         where r.vehicle = v.id 
			       and ( ( (r.interval).begints <= '2023-11-11 09:00:00' and (r.interval).endts > '2023-11-11 09:00:00')
         			     or ( (r.interval).begints >= '2023-11-11 09:00:00' and (r.interval).begints <= '2023-11-11 10:00:00')));

-- 52
-- alle PKWs mit Kindersitz an der Station grs, die am 11.11.2023 um 9:00 Uhr frei waren
-- all motor cars with a child seat at location ust which are free on 11.11.2023 at 9:00
select licensePlate
  from motorcar v join
       location l on v.home = l.id
 where shortName = 'grs'
       and childSeat 
       and not exists ( select resnumber
	                  from reservation r                   
                         where r.vehicle = v.id
    			       and ( ( (r.interval).begints <= '2023-11-11 09:00:00' and (r.interval).endts > '2023-11-11 09:00:00') 
				     or ( (r.interval).begints >= '2023-11-11 09:00:00' and (r.interval).begints <= '2023-11-11 10:00:00')));

-- 53
-- Kennzeichen mit der Angabe, ob das Fahrzeug am 11.11..2023 von 9-10 Uhr frei war
-- all license plates together with the information whether the vehicle was free on 11.11..2023 from 9-10
select licensePlate,  
       case when (select count(resnumber)                  
                    from reservation r                  
                   where r.vehicle = v.id 
		         and ( ( (r.interval).begints <= '2023-11-11 09:00:00' and (r.interval).endts > '2023-11-11 09:00:00')
			       or ( (r.interval).begints <= '2023-11-11 10:00:00' and (r.interval).endts >= '2023-11-11 10:00:00'))) = 0 then 'frei' else 'reserviert' end as freiOderBelegt   from vehicle v;

-- 54
-- die Kennzeichen der Fahrzeuge zusammen mit der Anzahl der Reservationen am 11.11.2023 von 9-10 Uhr
-- all license plates with the number of reservations free on 09.09.2023 from 9-10
select licensePlate,
       (select count(resnumber)       
          from reservation r       
         where r.vehicle = v.id
	       and ( ( (r.interval).begints <= '2023-11-11 09:00:00' and (r.interval).endts > '2023-11-11 09:00:00')
		     or ( (r.interval).begints >= '2023-11-11 09:00:00' and (r.interval).begints <= '2023-11-11 10:00:00')))
  from vehicle v;

-- 55
-- die Kuerzel der Stationen zusammen mit der Anzahl der Fahrzeuge, die am 11.11.2023 von 9-10 Uhr frei sind
-- the short names of locations with the number of vehicles at each location which are free on 11.11.2023 from 9-10
select shortName, name, 
       (select count(*)        
          from vehicle v        
         where v.home = l.id
	       and not exists ( select resnumber
		                  from reservation r                                
                                 where r.vehicle = v.id
				       and ( ( (r.interval).begints <= '2023-11-11 09:00:00' and (r.interval).endts > '2023-11-11 09:00:00')
					     or ( (r.interval).begints >= '2023-11-11 09:00:00' and (r.interval).begints <= '2023-11-11 10:00:00')))) as vehicleCount
  from location l
 order by 3 desc;

-- 56
-- Benutzer ohne Reservationen
-- members without any reservations
select m.id
  from member m left outer join 
       reservation r on r.member = m.id 
 where r.id is null;

-- 57
-- die Kennzeichen und Anzahl der Reservationen der einzelnen Fahrzeuge
-- the license plates and number of reservations of each vehicle
select licensePlate,         count(*)
  from vehicle v join
       reservation r on r.vehicle = v.id
 group by licensePlate 
union 
select licensePlate, 0   
  from vehicle v left outer join
       reservation r on r.vehicle = v.id
 where r.id is null ;
select licensePlate, count(distinct r.id) 
  from vehicle v left outer join
       reservation r on r.vehicle = v.id 
 group by licenseplate 
 order by 1;

-- 58
-- der hoechste Kilometerstand eines Fahrzeugs
-- the maximum odometer value
select max(kilometers)
  from Vehicle;

-- 59
-- das Fahrzeug mit dem hoechsten Kilometerstand
-- the vehicle with the highest odometer value
select licensePlate
  from Vehicle
 where kilometers = (select max(kilometers) from Vehicle);

-- 60
-- der höchste Benzinverbrauch eines Fahrzeugs
-- the highest fuel consumption of all vehicles
select max(gasconsumption)
  from Vehicle;

-- 61
-- das Fahrzeug mit dem höchsten Benzinverbrauch
-- the vehicle with the highest fuel consumption
select licensePlate
  from Vehicle
 where gasconsumption = (select max(gasconsumption) from Vehicle);

-- 62
-- die Anzahl Limousinen pro Station
-- the number of limousines per location
select shortname, count(v.id)
  from limousine v right outer join 
       location l on v.home = l.id
 group by shortname;

-- 63
-- die maximale Anzahl Limousinen an einer Station
-- the highest number of limousines per location
select max(limocnt) as maxlimo
  from (select shortname, count(v.id) as limocnt from limousine v right outer join location l on v.home = l.id group by shortname) as lc;

-- 64
-- Die Station mit der maximalen Anzahl Limos
-- the location with the highest number of limousines
select shortname, count(v.id) as limocnt
  from limousine v right outer join 
       location l on v.home = l.id
 group by shortname 
having count(v.id) = (select max(limocnt) as maxlimo from (select shortname, count(v.id) as limocnt from limousine v right outer join location l on v.home = l.id group by shortname) as lc);

with limos(shortName, limoCnt) as (
    select shortname, count(v.id) 
      from limousine v right outer join
           location l on v.home = l.id      
     group by shortname)
select shortName, limocnt
 from limos  
 where limoCnt = (select max(limocnt) from limos);

-- 65
-- alle Stationen, die (ueber die Nachbarschaftsbeziehung) in maximal vier Schritten von Mönchaltorf (moe) erreichbar sind
-- all locations that are reachable (via the nextLocation relationship) in no more than four steps from location moe
select id, shortname, nextLocation, secondNextLocation 
  from location; 
update location
   set nextLocation = 1005, secondNextLocation = 1006
 where shortName = 'grs';
update location    
   set nextLocation = 1004, secondNextLocation = 1000
 where shortName = 'swb';  

with recursive neighbors(shortName, next, steps) as
((select l.shortName, n.shortName as next,                   1           
  from location l join
         location n on l.nextLocation = n.id          
 where l.shortName = 'moe')
union all
(select l.shortName, l2.shortName as next, steps + 1
   from location l join
        neighbors n on l.shortName = n.next join
	location l2 on l.nextLocation = l2.id          
  where l2.shortName <> 'moe' and steps <= 3 ))
select  shortName, next, steps
  from  neighbors 
  order by steps, shortName, next;

-- 66
-- der durchschnittliche Spritverbrauch pro Fahrzeug
-- the average fuel consumption (per 100km) per vehicle
select licensePlate, gasConsumption / (kilometers / 100.0) as averageGasConsumption
  from vehicle;  
select licensePlate, 
       case when kilometers = 0 then '0' else to_char(gasConsumption / (kilometers / 100.0), '99D99') end as averageGasConsumption
  from vehicle;

-- 67
-- der maximale durchschnittliche Spritverbrauch pro Fahrzeug
-- the highest average fuel consumption per vehicle
select max(to_char(gasConsumption / (kilometers / 100.0), '99D99')) as maxAverageGasConsumption
  from vehicle
 where kilometers > 0;

-- 68
-- das Fahrzeug mit dem maximalen Spritverbrauch
-- the vehicle with the highest average fuel consumption
with gas(car, gasUsage) as (
select licensePlate, to_char(gasConsumption / (kilometers / 100.0), '99D99') as averageGasConsumption      
  from vehicle
 where kilometers > 0) 
select car, gasUsage   from gas
 where gasUsage = (select max(gasUsage) from gas);

-- 69
-- der maximale, minimale und durchschnittliche Spritverbrauch pro Fahrzeugtyp
-- the highest, average, and smallest average fuel consumption by vehicle category
select type, 
       to_char(max(gasConsumption / (kilometers / 100.0)), '99D99') as maxGasConsumption,
       to_char(min(gasConsumption / (kilometers / 100.0)), '99D99') as minGasConsumption,
       to_char(avg(gasConsumption / (kilometers / 100.0)), '99D99') as avgGasConsumption 
  from vehicle 
 where kilometers > 0
 group by type
 order by avgGasConsumption desc;

-- 70
-- maximaler Spritverbrauch pro Fahrzeug und Benutzer
-- the highest fuel consumption by member and vehicle (for a single reservation)
select max(cast(gas as decimal(10,2))) 
  from (select member, 
	       vehicle,  
	       100 * avg(cast(gasConsumed as decimal(10,2)) / cast(kilometers as decimal(10,2))) as gas
          from useofvehicle u join 
	       reservation r on u.reservation = r.id        
	 where kilometers > 0
         group by member, vehicle ) as gu;

-- 71
-- der Benutzer und das Fahrzeug mit dem hoechsten Benzinverbrauch (für eine einzelne Benutzung)
-- member and vehicle with the highest fuel consumption (for a single reservation)
with gasUse(member, car, gas) as (
select member, vehicle, 
       cast(100 * avg(cast(gasConsumed as decimal(10,2)) / cast(kilometers as decimal(10,2))) as decimal(10,3)) as gas       
  from useofvehicle u join 
       reservation r on u.reservation = r.id      
 where kilometers > 0
 group by member, vehicle )
select * 
  from gasUse gu 
 where gu.gas = (select max(gas) from gasuse);

-- 72
-- das Fahrzeug mit dem maximalen Spritverbrauch
-- the vehicle with the highest fuel consumption  (NOT per kilometer)
with gas(gasUsage) as (
select max(cast(gasConsumption as decimal(10,2))) as gasUsage       
  from vehicle)  
select licensePlate, gasUsage
  from gas, vehicle 
 where gasUsage = gasConsumption;

-- 73
-- Kategorien und der höchste Kilometerstand pro Kategorie
-- Vehicle categories and the highest odometer value for each category
select type, max(kilometers) 
  from vehicle 
 group by type   
 order by type;

-- 74
-- Kategorien und der höchste Kilometerstand pro Kategorie sowie das Kennzeichen des Fahrzeugs (dieser Kategorie) mit diesem Kilometerstand
-- Vehicle categories and the highest odometer value for each category, plus the license plate of the vehicle with this odometer value
with tmp as (
select type, max(kilometers) as maxkm
  from vehicle 
 group by type)
select licenseplate, v.type, t.maxkm
  from vehicle v join
       tmp t on v.type = t.type and v.kilometers = t.maxkm;


-- 75
-- Die Benutzungsdauern der Reservationen
-- the duration of the reservations
select hours from useofvehicle;

-- 76
-- Die Benutzungsdauern der Reservationen (jeden Wert nur einmal)
-- the duration of the reservations, list each value only once
select distinct hours from useofvehicle;

-- 77
-- Die Benutzungsdauern der Reservationen (jeden Wert nur einmal), die längste Benutzung zuerst
-- the duration of the reservations, list each value only once and sort from largest to smallest
select distinct hours 
  from useofvehicle
 order by hours desc;

-- 78
-- Ein Histogramm der Benutzungsdauern der Reservationen, d.h. zu jeder Dauer die Angabe, wie oft sie vorkommt
-- a histogram of reservation durations, i.e., for each duration calculate how often it occurs
select  hours, count(*) as cnt
  from useofvehicle
 group by hours
 order by hours desc;

-- 79
-- die Kennzeichen und Anzahl der Reservationen der einzelnen Fahrzeuge, absteigend sortiert nach Anzahl der Reservationen, zusammen mit der kummulierten Summe der Reservationen und dem Anteil des Fahrzeugs an dieser kummulierten Summe (kummulierte Summe = Summe der Reservationen aller Fahrzeuge bis einschliesslich des aktuellen in der Liste)
-- the license plates and number of reservations per vehicle. order should be by number of reservations (largest to smallest). Also compute the cummulated sum of the reservations and the contribution of the current vehicle to the cummulated sum
select licensePlate, resCnt, 
       sum(resCnt) over (order by resCnt desc ROWS unbounded preceding) as cumulatedCnt,
       (100.0 * resCnt) / sum(resCnt) over(order by resCnt desc ROWS between unbounded preceding and unbounded following) as fraction 
  from (select licensePlate, count(r.id) as resCnt
	  from reservation r right outer join
	       vehicle v on r.vehicle = v.id        
         group by licensePlate) as tmp
 order by resCnt desc;

-- 80
-- Jahr und Monat der Reservationen (Reservationsbeginn, jede Kombination nur einmal)
-- year and month of reservations (reservation begin; each combination only once)
select distinct extract(year from (interval).begints) as jahr, 
                extract(month from (interval).begints) as Monat
  from reservation;

-- 81
-- Jahr und Monat der Reservationen und Anzahl der Reservationen in diesem Monat (ausschlaggebend ist der Reservationsbeginn)
-- year and month of reservations and number of reservations during this month (use reservation begin)
select extract(year from (interval).begints) as jahr, 
       extract(month from (interval).begints) as Monat,
       count(*) as cnt
  from reservation
 group by extract(year from (interval).begints), extract(month from (interval).begints)
 order by 1, 2;

-- 82
-- Jahr und Monat der Reservationen und Summe der Reservationsdauern in diesem Monat (ausschlaggebend ist der Reservationsbeginn)
-- year and month of reservations and sum of reservation durations during this month (use reservation begin)
select extract(year from (interval).begints) as jahr, 
       extract(month from (interval).begints) as Monat,
       sum(hours) as cnt
  from reservation r join 
       useofvehicle u on u.reservation = r.id
 group by extract(year from (interval).begints), extract(month from (interval).begints)
 order by 1, 2;

-- 83
-- Jahr und Monat der Reservationen und Anzahl der Reservationen in diesem Monat (ausschlaggebend ist der Reservationsbeginn); und Bildung einer Rangliste innerhalb der einzelnen Jahre
-- year and month of reservations and number of reservations during this month (use reservation begin). Sort according to number of reservations within each year
select jahr, monat, cnt,
       rank() over(partition by jahr order by cnt desc) as rang
  from (select extract(year from (interval).begints) as jahr, extract(month from (interval).begints) as Monat, count(*) as cnt
	  from reservation        
         group by extract(year from (interval).begints), extract(month from (interval).begints)) t
 order by jahr, rang;

with ResProMonat as (
select extract(year from (interval).begints) as jahr, extract(month from (interval).begints) as Monat, count(*) as cnt
  from reservation        
 group by extract(year from (interval).begints), extract(month from (interval).begints))
select jahr, monat, cnt,
       rank() over(partition by jahr order by cnt desc) as rang
  from ResProMonat
 order by jahr, rang;

-- 84
-- gleich wie oben, Summe der Reservationsdauern anstatt Anzahl Reservationen
-- same as above, but use sum of reservation durations instead of reservation count


-- 85
-- Jahr und Monat der Reservationen und Summe der Reservationsdauern in diesem Monat (ausschlaggebend ist der Reservationsbeginn); und Bildung einer Rangliste innerhalb der einzelnen Jahre. Ausgabe der auf der Rangliste führenden beiden Monate
-- same as above, but use sum of reservation durations instead of reservation count.  Return only the first two months
select *
  from (select jahr, monat, totalhours,
	       rank() over(partition by jahr order by totalhours desc) as rang
	  from (select extract(year from (interval).begints) as jahr, extract(month from (interval).begints) as Monat, sum(hours) as totalhours
		  from reservation r join 
		       useofvehicle u on u.reservation = r.id                
                 group by extract(year from (interval).begints), extract(month from (interval).begints)) t1) t2
 where rang < 3
 order by jahr, rang;

-- 86
-- gleitender 3-Monatsdurchschnitt der Reservationsanzahlen
-- calculate the 3-months moving average of reservation counts
select jahr, monat, cnt, 
       avg(cnt) over(partition by jahr order by monat rows between 1 preceding and 1 following) as mvgavg
  from (select extract(year from (interval).begints) as jahr, 
	       extract(month from (interval).begints) as Monat,
	       count(*) as cnt
	  from reservation
	 group by extract(year from (interval).begints), extract(month from (interval).begints)) t
 order by jahr, monat;

-- 87
-- monatsweise kummulierte Summe der Reservationszahlen innerhalb eines Jahres
-- calculate the cumulated sums of monthly reservation counts per year
select jahr, monat, cnt,
       sum(cnt) over(partition by jahr order by monat rows unbounded preceding ) as cumsum
  from (select extract(year from (interval).begints) as jahr,
	       extract(month from (interval).begints) as Monat,
	       count(*) as cnt
	  from reservation
	 group by extract(year from (interval).begints), extract(month from (interval).begints)) t
 order by jahr, monat;

-- 88
-- Einteilung der Monate des Jahres 2023 in Tertile (drei Quantile) gemäss der Anzahl Reservationen
-- assign the months of the year 2023 to tertiles (three quantiles) based on the number of reservations per month
select jahr, monat, cnt, 
       ntile(3) over(partition by jahr order by cnt ) as tiles
  from (select extract(year from (interval).begints) as jahr,
	       extract(month from (interval).begints) as Monat,
	       count(*) as cnt
	  from reservation
	 group by extract(year from (interval).begints), extract(month from (interval).begints)) t
 where jahr = 2023;

-- 89
-- Anzahl Reservationen gruppiert nach den Dimensionen Mitglied, Fahrzeug, Station
-- The number of reservations grouped over the dimensions member, vehicle, and location
select membernr, v.licenseplate, l.name, count(*) 
  from reservation r join
       vehicle v on r.vehicle = v.id join
        member m on r.member = m.id join 
	location l on v.home = l.id 
 group by grouping sets (membernr, v.licenseplate, l.name, ())   
 order by 1,2,3;

-- 90
-- Anzahl Reservationen gruppiert nach allen möglichen Kombinationen der  Dimensionen Mitglied und Fahrzeugtyp
-- The number of reservations grouped over all possible combinations of the dimensions member (member number) and vehicle type.
select membernr, v.type, count(*)
  from reservation r join
       vehicle v on r.vehicle = v.id join
       member m on r.member = m.id 
 group by cube (membernr, v.type)   
 order by 1,2;

-- 91
-- Anzahl Reservationen gruppiert nach allen möglichen Kombinationen der  Dimensionen Mitglied und Fahrzeugtyp, so dass auch Mitglieder und Fahrzeugtypen ohne Reservationen aufgeführt werden
-- The number of reservations grouped over all possible combinations of the dimensions member (member number) and vehicle type such that also types and members without reservations are shown.
select membernr, v.type, count(resnumber) 
  from reservation r full outer join
       vehicle v on r.vehicle = v.id full join
       member m on r.member = m.id 
 group by cube (membernr, v.type)           
 order by 1,2;

-- 92
-- Anzahl Reservationen gruppiert nach allen möglichen Kombinationen der  Dimensionen Mitglied und Fahrzeugtyp, so dass auch Mitglieder und Fahrzeugtypen ohne Reservationen aufgeführt werden. Ausserdem sollen All- und Nullwerte unterschieden werden.
-- The number of reservations grouped over all possible combinations of the dimensions member (member number) and vehicle type such that also types and members without reservations are shown. In addition distinguish all and null values.
select case when grouping(membernr)=1 then 'all' else membernr::text end as member,
       case when grouping(v.type)=1 then 'all' else v.type end as type,
       count(resnumber)
  from reservation r full outer join
       vehicle v on r.vehicle = v.id full join
       member m on r.member = m.id 
 group by cube (membernr, v.type)          
 order by 1,2;

-- 93
-- Anzahl Reservationen hierarchisch gruppiert nach Jahr, Monat und Tag des Reservationsbeginns
-- The number of reservations hierarchically grouped over year, month, and day of the reservation begin
select extract(year from (r.interval).begints) as year,
       to_char((r.interval).begints, 'YYYY-MM') as month,
       date_trunc('day', (r.interval).begints)::date as day,
       count(resnumber)
  from reservation r
 group by rollup(extract(year from (r.interval).begints), to_char((r.interval).begints, 'YYYY-MM'), date_trunc('day', (r.interval).begints))
 order by 1,2,3;

-- 94
-- alle Benutzer, die fern der Heimat reserviert haben (Fahrzeugstandort <> Heimatstandort des Benutzers)
-- all members who have reserved a vehicle at a location other than their home location
select m.memberNr
  from reservation r join
       member m on r.member = m.id join
       vehicle v on r.vehicle = v.id
 where m.homelocation <> v.home;

-- 95
-- alle Benutzer, die fern der Heimat reserviert haben (Fahrzeugstandort <> Heimatstandort des Benutzers), obwohl es den Fahrzeugtyp an der Heimatstation gibt
-- all members who have reserved a vehicle at a location other than their home location even though the vehicle type exists at their home location
select m.memberNr, r.resNumber
  from reservation r join
       member m on r.member = m.id join
       vehicle v on r.vehicle = v.id
 where m.homelocation <> v.home
       and (select count(*)
	      from vehicle c                        
             where c.home = m.homelocation and c.type = v.type) > 0;

-- 96
-- alle Benutzer, die fern der Heimat reserviert haben (Fahrzeugstandort <> Heimatstandort des Benutzers), obwohl es den Fahrzeugtyp an der Heimatstation gibt und eines der Fahrzeuge des gewuenschten Typs frei war/wäre.
-- all members who have reserved a vehicle at a location other than their home location even though the vehicle type exists at their home location and a vehicle of the desired time would be free during the interval in question
select m.memberNr, r.resNumber
  from reservation r join
       member m on r.member = m.id join
       vehicle v on r.vehicle = v.id
 where m.homelocation <> v.home
       and (select count(*)
	      from vehicle c                        
             where c.home = m.homelocation
	           and c.type = v.type
		   and not exists(select resnumber
			            from reservation r2                                               
                                   where ( (r.interval).begints <= (r2.interval).begints and (r.interval).endts > (r2.interval).begints) 
				           Or ( (r.interval).begints > (r2.interval).begints and (r.interval).begints <= (r2.interval).endts)) ) > 0;

-- 97
-- ein Belegungsplan fuer das Auto '194 SUA' fuer den 11.11. 2023 und die folgenden beiden Tage
-- a free/busy plan for the vehicle '194 SUA' for 11.11. 2023 and the following two days
select (interval).begints, (interval).begints + '1 hour' from reservation;  
with recursive  resTable(license, anfang, ende, isFree, ind) as
((select '194 SUA', 
	 cast('2023-11-11 00:00:00' as Timestamp), cast('2023-11-11 01:00:00' as Timestamp), 
	 not exists ( select resnumber                           
                        from reservation r                           
                       where r.vehicle = v.id
                             and ( ( (r.interval).begints <= '2023-11-11 00:00:00' and (r.interval).endts > '2023-11-11 00:00:00')
	                           or ( (r.interval).begints > '2023-11-11 01:00:00' and (r.interval).begints <= '2023-11-11 01:00:00'))),
         1
    from vehicle v       
   where licensePlate = '194 SUA')
union all
(select r.license, 
	anfang + '1 HOUR', 
	ende + '1 HOUR', 
	not exists ( select resnumber 
		       from reservation r                           
                      where r.vehicle = v.id 
		            and ( ( (r.interval).begints <= anfang + '1 HOUR' and (r.interval).endts > anfang + '1 HOUR') 
				  or ( (r.interval).begints > ende + '1 HOUR' and (r.interval).begints <= ende + '1 HOUR'))),
        ind + 1 
   from resTable r, vehicle v        
  where r.license = v.licensePlate and ind < 72))
select  license, anfang, ende, isFree
  from    resTable;

-- 98
-- ein Belegungsplan fuer die Station Greifensee fuer den 11.11. 2023
-- a free/busy plan for the location Greifensee  for 11.11. 2023
with recursive  resTable(location, license, anfang, ende, isFree, ind) as (
(select shortname,
	licensePlate,
	cast('2023-11-11 00:00:00' as Timestamp),
	cast('2023-11-11 01:00:00' as Timestamp),
	not exists ( select resnumber                           
                       from reservation r                           
                      where r.vehicle = v.id
		            and ( ( (r.interval).begints <= '2023-11-11 00:00:00' and (r.interval).endts > '2023-11-11 00:00:00')
				  or ( (r.interval).begints > '2023-11-11 01:00:00' and (r.interval).begints <= '2023-11-11 01:00:00'))),
	1
   from vehicle v join
        location l on v.home = l.id       
  where shortName = 'grs')
union all
(select location,
	r.license,
	anfang + '1 HOUR',
	ende + '1 HOUR',
	not exists ( select resnumber
		       from reservation r                           
                      where r.vehicle = v.id
		            and ( ( (r.interval).begints <= anfang + '1 HOUR' and (r.interval).endts > anfang + '1 HOUR') 
				  or ( (r.interval).begints > ende + '1 HOUR' and (r.interval).begints <= ende + '1 HOUR'))),
	ind + 1
 from   resTable r, vehicle v        
 where  r.license = v.licensePlate
        and ind < 48))
select location, license, anfang, ende, isFree
  from resTable
 order by location, anfang, license;


