SELECT hotel name with highest number of reservations in each year Oracle sql (Query)

Question

I want my SELECT to return the name of the hotel with the highest number of reservations in each year. If there is more than one hotel in that condition, all those hotels should be shown. It also should return the number of reservations for each hotel.

Output should be like:

HOTEL            NUMBER_RESERVATIONS
Hotel Paris             189              --year 2017 hotel w/ highest n/reservations
Hotel Bristol           207              --year 2010 hotel w/ highest n/reservations

I have three tables:

CREATE TABLE hotel (
  name VARCHAR (20),

  CONSTRAINT pk_hotel 
   PRIMARY KEY (name)
);

CREATE TABLE reservation (
  person,
  hotel,
  year DATE,

  CONSTRAINT pk_reservation 
   PRIMARY KEY (hotel, person),

  CONSTRAINT fk_reservation_hotel  
   FOREIGN KEY (hotel)
   REFERENCES hotel(name)

  CONSTRAINT fk_reservation_person
   FOREIGN KEY (person)
   REFERENCES person(pid)
);

CREATE TABLE person (
 pid NUMBER (5),

CONSTRAINT pk_person 
 PRIMARY KEY (pid)
);

Answer 1

You can aggregate to find the number of reservations per year and then use the RANK analytic function to get the top booked hotel(s) for each year:

SELECT hotel,
       num_reservations,
       year
FROM   (
  SELECT t.*,
         RANK() OVER ( PARTITION BY year ORDER BY num_reservations DESC ) AS rnk
  FROM   (
    SELECT hotel,
           COUNT(*) as num_reservations,
           EXTRACT( year FROM year ) AS year
    FROM   reservation
    GROUP BY
           hotel,
           EXTRACT( year FROM year )
  ) t
)
WHERE  rnk = 1;

Which, or the sample data:

CREATE TABLE reservation (
  person VARCHAR2(50),
  hotel  VARCHAR2(50),
  year   DATE
);

INSERT INTO reservation ( person, hotel, year )
SELECT 'Person ' || LEVEL, 'Hotel Paris', DATE '2017-01-01' + LEVEL FROM DUAL CONNECT BY LEVEL <= 168
UNION ALL
SELECT 'Person ' || LEVEL, 'Hotel Paris', DATE '2012-01-01' + LEVEL FROM DUAL CONNECT BY LEVEL <= 52
UNION ALL
SELECT 'Person ' || LEVEL, 'Hotel Bristol', DATE '2017-01-01' + LEVEL FROM DUAL CONNECT BY LEVEL <= 97
UNION ALL
SELECT 'Person ' || LEVEL, 'Hotel Bristol', DATE '2012-01-01' + LEVEL FROM DUAL CONNECT BY LEVEL <= 205;

Outputs:

HOTEL         | NUM_RESERVATIONS | YEAR
:------------ | ---------------: | ---:
Hotel Bristol |              205 | 2012
Hotel Paris   |              168 | 2017

db<>fiddle here