text: --A
  WITH CONTAGEM_QUARTO_POR_ID AS
           (SELECT Q.ID, COUNT(R.QUARTOID) AS NUM_RESERVAS, Q.TIPOQUARTOID, Q.NUMEROQUARTOANDARID
            FROM RESERVA R
                     INNER JOIN QUARTO Q ON Q.ID = R.QUARTOID
            GROUP BY Q.ID, Q.TIPOQUARTOID, Q.NUMEROQUARTOANDARID)
  SELECT T.TIPOQUARTOID                                                            AS "TIPO DE QUARTO",
         T.NUMEROQUARTOANDARID                                                     AS "ANDAR",
         MAX(T.ID) KEEP (DENSE_RANK FIRST ORDER BY T.NUM_RESERVAS DESC NULLS LAST) AS "ID"
  FROM CONTAGEM_QUARTO_POR_ID T
  WHERE (T.TIPOQUARTOID <> 1)
    AND (T.NUM_RESERVAS > 2)
  GROUP BY T.TIPOQUARTOID, T.NUMEROQUARTOANDARID;
  
  --B
  WITH VALOR_CONSUMO AS (SELECT SUM(P.CUSTO) AS CONSUMO, C3.RESERVAID
                         FROM DESPESAFRIGOBAR D2
                                  INNER JOIN PRODUTO P ON D2.PRODUTOID = P.ID
                                  INNER JOIN CONTA C3 ON C3.NUMUNICO = D2.CONTANUMUNICO
                         GROUP BY D2.CONTANUMUNICO, C3.RESERVAID)
  SELECT C.NOME, VC.CONSUMO
  FROM CLIENTE C
           INNER JOIN RESERVA R ON C.NIF = R.CLIENTENIF
           JOIN VALOR_CONSUMO VC ON VC.RESERVAID = R.ID
  WHERE C.NIF IN (SELECT R2.CLIENTENIF
                  FROM RESERVA R2
                           INNER JOIN CONTA C2 ON R2.ID = C2.RESERVAID
                           INNER JOIN DESPESAFRIGOBAR D2 ON C2.NUMUNICO = D2.CONTANUMUNICO
                  WHERE D2.PRODUTOID IN (SELECT COUNT(D.PRODUTOID)
                                         FROM DESPESAFRIGOBAR D
                                         WHERE D.DATA > ADD_MONTHS(SYSDATE, -24)
                                         GROUP BY D.PRODUTOID
                                         ORDER BY COUNT(D.PRODUTOID) DESC
                                             FETCH FIRST 2 ROWS ONLY))
    AND (R.DATAINICIO BETWEEN (SELECT E.DATAINICIO FROM EPOCAANO E WHERE E.ID = 2) AND (SELECT E.DATAFIM FROM EPOCAANO E WHERE E.ID = 2))
  GROUP BY C.NOME, VC.CONSUMO
  ORDER BY VC.CONSUMO DESC;
  